When should we use table partitioning in Postgresql?

Question

Context: we have two fairly large tables in our database, one holding 80 millions records and the other 160 millions. We're seeing performances issues and are thinking about using table partitioning for these 2 tables.

My question is: is there a number of records that indicates that we should partition or not to keep good performances ? I know there isn't a "one size fits all" answer, but there might be a general advice such as "passed X millions records, you should partition the table". There is a lot of guidance regarding how to partition, but not "when".

• Mitch Wheat: if you are "... seeing performances issues.." at around 160 million rows then you likely don't have the right indexes., or have too small an instance.

Answer 1

No, there is no real row number threshold. If you only have queries that select rows by primary key, the size of the table doesn't really matter.

Partitioning is also primarily a management tool to quickly remove no longer needed rows, not so much a performance tool.

It can be used to improve performance, but only if you have queries that only need a (small) subset of all the rows. If all queries (or at least all performance critical one) do contain the partitioning key, then partitioning can help with performance.

You also need to choose the partitioning key based on then number of partitions this results in. With Postgres 12 or later "thousands" of partitions are feasible (I have heard of users using ~20000 partitions successfully, but I think that's already a stretch). An excessive amount of partitions will most probably be not practical as it will make the planning of the queries a lot slower.

You should also take the fact into account, that a partitioned tables is limited in what the primary key can be - it has to include the partition key. So if you have foreign keys referencing the partitioned table, this might get complicated.

• Laurenz Albe: Well spoken. One other advantage of partitioning is that it is easier for autovacuum to handle several partitions than a single huge table, even with parallel vacuum.

Answer 2

First work around is to create unique constraints on each partition instead of a partitioned table. Second work around is to trigger some of columns from both tables into replicated columns tables. Retrieve some data from replicated some from other tables. It will fine tune tables and data perfornace.