Horton
Menu

Re: pg_upgrade failing for 200+ million Large Objects

Justin Pryzby <pryzby@telsasoft.com>

View thread
From: Justin Pryzby <pryzby@telsasoft.com>
To: Alexander Korotkov <aekorotkov@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Nathan Bossart <nathandbossart@gmail.com>, Michael Banck <mbanck@gmx.net>, Laurenz Albe <laurenz.albe@cybertec.at>, vignesh C <vignesh21@gmail.com>, "Kumar, Sachin" <ssetiya@amazon.com>, Robins Tharakan <tharakan@gmail.com>, Jan Wieck <jan@wi3ck.info>, Bruce Momjian <bruce@momjian.us>, Andrew Dunstan <andrew@dunslane.net>, Magnus Hagander <magnus@hagander.net>, Peter Eisentraut <peter.eisentraut@enterprisedb.com>, pgsql-hackers@postgresql.org
Date: 2024-07-26T20:36:20Z
Lists: pgsql-hackers 
On Wed, Jul 24, 2024 at 09:17:51AM -0500, Justin Pryzby wrote:
> With partitioning, we have a lot of tables, some of them wide (126
> partitioned tables, 8942 childs, total 1019315 columns).

On Fri, Jul 26, 2024 at 10:53:30PM +0300, Alexander Korotkov wrote:
> It would be nice to identify such cases and check which memory contexts are
> growing and why.

I reproduced the problem with this schema:

SELECT format('CREATE TABLE p(i int, %s) PARTITION BY RANGE(i)', array_to_string(a, ', ')) FROM (SELECT array_agg(format('i%s int', i))a FROM generate_series(1,999)i);
SELECT format('CREATE TABLE t%s PARTITION OF p FOR VALUES FROM (%s)TO(%s)', i,i,i+1) FROM generate_series(1,999)i;

This used over 4 GB of RAM.
3114201 pryzbyj   20   0 5924520   4.2g  32476 T   0.0  53.8   0:27.35 postgres: pryzbyj postgres [local] UPDATE

The large context is:
2024-07-26 15:22:19.280 CDT [3114201] LOG:  level: 1; CacheMemoryContext: 5211209088 total in 50067 blocks; 420688 free (14 chunks); 5210788400 used

Note that there seemed to be no issue when I created 999 tables without
partitioning:

SELECT format('CREATE TABLE t%s(LIKE p)', i,i,i+1) FROM generate_series(1,999)i;

-- 
Justin

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Count individual SQL commands in pg_restore's --transaction-size mode.

  2. Reduce number of commands dumpTableSchema emits for binary upgrade.

  3. Invent --transaction-size option for pg_restore.

  4. Rearrange pg_dump's handling of large objects for better efficiency.

  5. Add temporal PRIMARY KEY and UNIQUE constraints

  6. Fix typo and case in messages