PostgreSQL 10 High Performance - Third Edition

Common partitioning mistakes

There are enough partitioning problems that people run into repeatedly that it's worth mentioning, and the most common are as follows:

• Not turning on constraint_exclusion and therefore always including every partition.
• Failing to add all the same indexes or constraints to each partition that existed in the parent.
• Forgetting to assign the same permissions to each child table as the parent.
• Writing queries that don't filter on the partitioned key field. The WHERE clause needs to filter on constants. A parameterized query will therefore not work right at all, and functions that can change their value such as CURRENT_DATE will also optimize poorly. In general, keep the WHERE clauses as simple as possible, to improve ...