When your index doesn't fit into memory anymore, you've got a big table, and if you want to improve performance on such a table in PostgreSQL, table partitions may be the way to go. This recent post from the informatics blog on UNEP-WCMC explores the basics of PostgreSQL table partitioning from a performance point of view.
First, the post explores the two fundamental mechanisms of PostgreSQL table partitioning:
- Table inheritance
- Table check constraints
Table inheritance allows new tables to be created using the same column definitions as the inherited parent, and from there the post examines more specific strategies for optimizing performance, such as careful use of check constraints.
Beyond the how-tos, the post also warns of a number of pitfalls:
- Gotcha #1: In order to enable exclusion of tables by the planner, a setting called
constraint_exclusionneeds to be turned on
- Gotcha #2: When creating child tables, any constraints other than NOT NULL or CHECK are not inherited. They need to be specified for every partition (e.g. foreign keys, indexes).
- Gotcha #3: There is no practical way to enforce uniqueness of a serial id column across partitions. If application logic depends on that assumption it either needs to be extended to consider the partitioning key together with the numeric id, or a different type of id generator needs to be used (e.g. UUID)
- Gotcha #4: There is no way to define a foreign key referencing a partitioned table (other than one targeting a single partition).
For a look at all the ups and downs of large-table performance optimization in PostgreSQL, check out the full post.