From: Victor Sudakov <vas@sibptus.ru>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-admin@lists.postgresql.org, Ron <ronljohnsonjr@gmail.com>
Subject: Re: Large number of partitions of a table
Date: Tue, 18 Jan 2022 05:53:21 +0000
Message-ID: <YeZV0QSOly8Pnx5d@admin.sibptus.ru> (raw)
In-Reply-To: <254601.1642480213@sss.pgh.pa.us>
References: <YeTYA63gEXNePfkv@admin.sibptus.ru>
<3919502.1642393668@sss.pgh.pa.us>
<YeUAx56Juri43xGY@admin.sibptus.ru>
<671340a5-1960-e5dc-1d09-4b7f36cda2c8@gmail.com>
<YeYr88f3jXHlijU9@admin.sibptus.ru>
<254601.1642480213@sss.pgh.pa.us>
Tom Lane wrote:
> Victor Sudakov <vas@sibptus.ru> writes:
> > What's the worst thing to happen if someone runs "SELECT COUNT(*) FROM t" where t has 10000 partitions?
>
> > 1. The backend will crash?
>
> Well, yeah, actually. An ill-advised query will blow out your backend's
> memory consumption, potentially leading to a SIGKILL from the dreaded OOM
> killer[1] (if you're on Linux), resulting in a backend crash and cluster
> restart.
Why should "SELECT COUNT(*) FROM t" ever consume more than work_mem
even if t has 10000 partitions? I've just run it with 3000 partitions
and it does not even create temporary files let alone consume any
significant amount of RAM.
It is very difficult for me to even imagine a query that would consume
work_mem*N where N>4 or something. It has to be a very sophisticated
query though documentation says this is possible. And the reasonable
amount of work_mem could be around 32MB.
Even considering parallelism, how would a query to a table with 10000
partitions consume enough RAM to cause OOM, what is the mechanism?
>
> > 3. Only this particular query (spanning multiple partitions) will be very slow?
>
> Guaranteed.
>
> > 4. ?
>
> You could run the shared lock table to full or nearly full, causing
> concurrent queries to fail all the while your SELECT COUNT(*) is
> leisurely proceeding towards completion.
What's the limit on the shared lock table?
> > Also, what if it is not a SELECT but an UPDATE query spanning multiple partitions? Does it make any difference?
>
> On v14, no. Before that, you have the O(N^2) planner issues
> I mentioned upthread.
>
> All of these things can be worked around, but you have to ask
> yourself if the benefits you get from using more rather than
> fewer partitions are worth fighting these sorts of fires.
Thank you for the valuable comments!
>
> [1] https://www.postgresql.org/docs/current/kernel-resources.html#LINUX-MEMORY-OVERCOMMIT
Reading this, I'm not sure if getting a "Cannot allocate memory" error
is really any better for Postgres than being killed by the OOM killer.
I'm not arguing here but just stating that I genuinely don't know.
--
Victor Sudakov VAS4-RIPE
http://vas.tomsk.ru/
2:5005/49@fidonet
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: vas@sibptus.ru, tgl@sss.pgh.pa.us, ronljohnsonjr@gmail.com
Subject: Re: Large number of partitions of a table
In-Reply-To: <YeZV0QSOly8Pnx5d@admin.sibptus.ru>
* 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