Effective table partitioning

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.
  • Subpartitioning

We are developing these features in  pg_pathman extension with keeping in mind  declarative partitioning syntax project by  Amit Langote.