Horton

Re: Can we get rid of repeated queries from pg_dump?

Stephen Frost <sfrost@snowman.net>

View thread View on postgr.es
From: Stephen Frost <sfrost@snowman.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>, depesz@depesz.com, Laurenz Albe <laurenz.albe@cybertec.at>, pgsql-general <pgsql-general@lists.postgresql.org>
Date: 2021-08-29T19:47:11Z
Lists: pgsql-hackers, pgsql-general 
Greetings,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> > Another pointlessly repetitive query is in getTriggers, which we run
> > once per table to be dumped containing triggers.  We could reduce that
> > by running it in bulk for many relations at a time.  I suppose it's
> > normally not hurtful, but as we grow the number of partitions we allow
> > it's going to become a problem.
> 
> Perhaps.  In the regression database, only ~10% of the tables have
> triggers, so it's likely not going to yield any measurable gain there.
> But databases that make heavier use of foreign keys might see a win.

It sure seems like in just about all cases fewer queries is going to be
better.

> Another thing I've wondered about before is whether it could make sense
> to read pg_attribute once rather than once per table.  There might be
> a fair amount of wasted work if the dump is selective, and in big DBs
> the sheer size of that result could be a problem.  But those reads are
> definitely way up there on the number-of-queries scale.

Yeah, I've thought about this before too.  Would sure be nice if there
was a way that we could query the catalog selectively based on the
options the user has passed in but do so in as few queries as possible.

Thanks,

Stephen

Commits

  1. In pg_dump, avoid doing per-table queries for RLS policies.

  2. Cache the results of format_type() queries in pg_dump.