On 1/16/22 8:44 PM, Victor Sudakov wrote:
Dear Colleagues,
Do you have success (or disaster) stories for having a large number of
partitions in a table (like maybe 10000) in a production database?
I've found a great article
https://www.depesz.com/2021/01/17/are-there-limits-to-partition-counts/
and basically it says 70000 partitions are OK but would like to hear
more opinions especially from production experience.
If a table itself has e.g. 50 indexes, partitioning it will create 10000
extra tables and 50*10000=500000 indexes, isn't it a heavy burden on the
system catalogs (pg_statistic etc). It may slow down ANALYZE
My experience is with range partitions on v12.
It will
speed up ANALYZE and VACUUM, since those operations are performed against the (smaller) child tables, not the (empty) parent table. If the table is partitioned chronologically and the older children never get modified, you won't need to analyze or vacuum them at all.
or have some other adverse effects.
Queries can easily be
much much slower if the partition key is
not in the WHERE clause.
--
Angular momentum makes the world go 'round.
