Date: Tue, 18 Jan 2022 05:53:21 +0000
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
Message-ID: <YeZV0QSOly8Pnx5d@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>
 <254601.1642480213@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <254601.1642480213@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/YeZV0QSOly8Pnx5d%40admin.sibptus.ru>
Precedence: bulk

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