PostgreSQL 9.0 High Performance

Chapter 15. Partitioning Data

As databases grow, it's common to have a table or two become unmanageably large. If the table itself is much larger than physical memory, and even its indexes stop fitting comfortably, query execution time will escalate. One way you can deal with large tables is to partition them, which breaks the table into a series of smaller, related tables instead. You don't have to change your application, just keep querying the same table. But when the query can be answered just using a subset of the data, this optimization can occur, rather than scanning the whole thing.

Table range partitioning

Returning to the Dell Store 2 example database used in Chapter 10, Query Optimization, consider the structure of the orders table: