Christopher B. Browne's Home Page

8. Varying Database Workloads

There are three particularly common sorts of database "work" that have very different performance characteristics.

The patterns of data accesses in these three kinds of uses of databases are very different, and often conflict, to a considerable degree. Notably, " data warehouse" applications involve many long-running queries that slow down transaction processing. In a system that is supporting customer activity, you cannot afford to have today's sales injured by analysis work that might, at best, improve things later, or that, more likely, is doing administrative analysis work on past history.

Moreover, " data warehouse" queries have a very different handling of parallelism than online systems. Online systems may have many updates taking place simultaneously, and management of that must focus on ensuring that those updates are handled consistently and quickly. In contrast, systems used for reporting are likely to be looking at past historical data, and are not likely to be updating data to any significant degree.

Ideally, these sorts of database usage should be separated to separate database instances, so that " transactional" processing takes place on a database instance configured for that purpose, while " analytical" processing takes place on systems configured for that purpose.

8.1. Purging Transactional Data

One thing that you'll find you need is some sort of strategy for purging out old data from the " transactional" system. Otherwise, numerous problems arise:


If this was useful, let others know by an Affero rating

Contact me at