Re: Large number of partitions of a table

Victor Sudakov <vas(at)sibptus(dot)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