From: Tom Lane <tgl@sss.pgh.pa.us>
To: Victor Sudakov <vas@sibptus.ru>
Cc: pgsql-admin@lists.postgresql.org, Ron <ronljohnsonjr@gmail.com>
Subject: Re: Large number of partitions of a table
Date: Mon, 17 Jan 2022 23:30:13 -0500
Message-ID: <254601.1642480213@sss.pgh.pa.us> (raw)
In-Reply-To: <YeYr88f3jXHlijU9@admin.sibptus.ru>
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>
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. On other platforms, you will likely end up in swap purgatory,
which is actually worse because it destroys the whole platform's
responsiveness and there may not be any easy way to get out of it.
> 2. The whole cluster will crash?
Not unless the OOM killer decides to kill the postmaster. That's
been seen to happen :-( but I think recent kernels have gotten
better about targeting the misbehaving backend not its parent process.
(Once upon a time they deliberately targeted the parent, which is
the main reason for the advice in [1] to disable the OOM killer.)
> 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.
> 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.
regards, tom lane
[1] https://www.postgresql.org/docs/current/kernel-resources.html#LINUX-MEMORY-OVERCOMMIT
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: tgl@sss.pgh.pa.us, vas@sibptus.ru, ronljohnsonjr@gmail.com
Subject: Re: Large number of partitions of a table
In-Reply-To: <254601.1642480213@sss.pgh.pa.us>
* 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