Partitioning Data

The previous chapter elaborated on replication and scalability; however, 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 times 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 as before. But when the query can be answered by just using a subset of the data, that optimization can occur, rather than scanning the whole thing. This is purely a performance improvement feature, and does not require any change to the application or queries. It only works well in case of large or, one can say, huge tables.

Partition is only beneficial in cases when the size of a table exceeds the size of the physical memory of the PostgreSQL.

In this chapter we will be covering these topics:

• Table inheritance
• List and range partitioning
• Horizontal partitioning