Traditional for PostgreSQL table partitioning through inheritance has many disadvantages, including the performance degradation with large number of partitions. We develop new partitioning subsystem working at the query planning and execution levels.
We identify the following important features of effective partitioning:
- HASH, RANGE, LIST partition type support
- Fast partitions selection – select required partitions without linear-time scan of them (see blog post).
- Filter condition simplification – simplify filter conditions, which comes to particular partitions (see blog post).
- Hash-join pushdown – automatically switch from join of partitioned tables to joins of individual partitions when possible.
- Partition selection at execution time – in some cases it’s impossible to select the required partitions during query planning, but it’s possible to do this at execution time, when values of parameters are already known. These cases are: nested loops, parameters obtained from subqueries, generic plans for prepared statements.
We are developing these features in pg_pathman extension with keeping in mind declarative partitioning syntax project by Amit Langote.