Subject: Re: Large number of partitions of a table
To: pgsql-admin@lists.postgresql.org
References: <YeTYA63gEXNePfkv@admin.sibptus.ru>
From: Ron <ronljohnsonjr@gmail.com>
Message-ID: <2bdcb53c-c1a2-1813-befe-c51381836bd3@gmail.com>
Date: Sun, 16 Jan 2022 22:32:38 -0600
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
 Thunderbird/78.14.0
MIME-Version: 1.0
In-Reply-To: <YeTYA63gEXNePfkv@admin.sibptus.ru>
Content-Type: multipart/alternative;
 boundary="------------B7329643CF3D9938ACBF9FC7"
Content-Language: en-US
Archived-At: 
 <https://www.postgresql.org/message-id/2bdcb53c-c1a2-1813-befe-c51381836bd3%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------B7329643CF3D9938ACBF9FC7
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit

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.

--------------B7329643CF3D9938ACBF9FC7
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    On 1/16/22 8:44 PM, Victor Sudakov wrote:<br>
    <blockquote type="cite" cite="mid:YeTYA63gEXNePfkv@admin.sibptus.ru">
      <pre class="moz-quote-pre" wrap="">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
<a class="moz-txt-link-freetext" href="https://www.depesz.com/2021/01/17/are-there-limits-to-partition-counts/">https://www.depesz.com/2021/01/17/are-there-limits-to-partition-counts/</a>
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 </pre>
    </blockquote>
    <br>
    My experience is with range partitions on v12.<br>
    <br>
    It will <b>speed up</b> 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.<br>
    <br>
    <blockquote type="cite" cite="mid:YeTYA63gEXNePfkv@admin.sibptus.ru">
      <pre class="moz-quote-pre" wrap="">or have some other adverse effects.</pre>
    </blockquote>
    <br>
    Queries can easily be <b>much much slower</b> if the partition key
    is <b>not</b> in the WHERE clause.<br>
    <br>
    <div class="moz-signature">-- <br>
      Angular momentum makes the world go 'round.</div>
  </body>
</html>

--------------B7329643CF3D9938ACBF9FC7--