Date: Tue, 18 Jan 2022 08:19:11 +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: <YeZ3/4TSd+xHAlDl@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>
 <YeZV0QSOly8Pnx5d@admin.sibptus.ru>
 <268771.1642489687@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <268771.1642489687@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/YeZ3/4TSd%2BxHAlDl%40admin.sibptus.ru>
Precedence: bulk

Tom Lane wrote:
> Victor Sudakov <vas@sibptus.ru> writes:
> > Tom Lane wrote:
> >> 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?
> 
> Sure, COUNT(*)'s runtime memory consumption is negligible.
> But you're not thinking about overhead --- specifically,
> 
> 1. 10000 relcache entries for the base tables.
> 
> 2. If you have N indexes per table, N*10000 relcache entries for
> the indexes.  (The planner will probably have looked at all those
> indexes, even if it didn't find any use for them.)
> 
> 3. 10000 SeqScan plan nodes and associated rangetable entries,
> 
> 4. Likewise, 10000 instances of executor per-node state.
> 
> 5. 10000 lock table entries (both shared and local lock tables).
> 
> 6. Probably a few per-relation things I didn't think of.

I see your point about all that query-related stuff. I hope the
testing of queries in a staging environment should help to detect such
situations. 

What about the system catalogs however? Will the extra 10000
tables and 500000 indexes negatively impact the performance of the
system catalogs? Are there any caveats you could think of?

> 
> Basically, this is likely to work until it doesn't ... 

Oh, this can be said about many things beyond the very trivial.
Unless there are immediately visible and well-known limitations, I
would risk it.

-- 
Victor Sudakov VAS4-RIPE
http://vas.tomsk.ru/
2:5005/49@fidonet