From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Large number of partitions of a table
Date: Sun, 16 Jan 2022 22:32:38 -0600
Message-ID: <2bdcb53c-c1a2-1813-befe-c51381836bd3@gmail.com> (raw)
In-Reply-To: <YeTYA63gEXNePfkv@admin.sibptus.ru>
References: <YeTYA63gEXNePfkv@admin.sibptus.ru>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Large number of partitions of a table
In-Reply-To: <2bdcb53c-c1a2-1813-befe-c51381836bd3@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox