huge RAM use in multi-command ALTER of table heirarchy

From: Justin Pryzby <pryzby(at)telsasoft(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: huge RAM use in multi-command ALTER of table heirarchy
Date: 2017-07-06 18:04:20
Message-ID: 20170706180420.GH30252@telsasoft.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

I've seen this before while doing SET STATISTICS on a larger number of columns
using xargs, but just came up while doing ADD of a large number of columns.
Seems to be roughly linear in number of children but superlinear WRT columns.
I think having to do with catalog update / cache invalidation with many
ALTERs*children*columns?

32 cols and 2 children => 12MB
256 cols and 11 children => 74MB
256 cols and 111 children => 582MB
512 cols and 11 children => 229MB

(in our "huge" case, there were ~1600 columns and maybe even more children)

I was testing with this command
PGHOST=/tmp PGPORT=9999 sh -ec 'for maxcols in 512 ; do ~/src/postgresql.install/bin/postgres -D ~/src/postgres.dat -c port=9999 & sleep 4; cols=$(for d in `seq 1 $maxcols`; do echo "ADD c$d int,"; done |xargs); PGOPTIONS="-c client_min_messages=warning" psql postgres -qc "DROP TABLE t CASCADE" || [ $? -eq 1 ]; psql postgres -qc "CREATE TABLE t()"; for c in `seq 1 11`; do psql postgres -qc "CREATE TABLE c$c() INHERITS(t)"; done; for d in `seq 1 $maxcols`; do echo "ALTER TABLE t ADD c$d int;"; done |PGOPTIONS="-c client_min_messages=DEBUG3 -c log_statement_stats=on" psql postgres -c "ALTER TABLE t ${cols%,}" 2>/tmp/pg.err2; ~/src/postgresql.install/bin/pg_ctl -swD ~/src/postgres.dat stop; done'

..and log_statment_stats with a variation on the getrusage patch here
https://www.postgresql.org/message-id/20170615145824.GC15684%40telsasoft.com

Justin

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2017-07-06 19:53:33 Re: CREATE AGGREGATE on jsonb concat
Previous Message Adrian Klaver 2017-07-06 17:27:13 Re: Renaming Raster Table

Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Meskes 2017-07-06 18:26:29 Re: Rust bindings to pgtypes lib
Previous Message Greg Stark 2017-07-06 16:37:56 Re: Challenges preventing us moving to 64 bit transaction id (XID)?