Date: Sun, 16 Mar 2025 15:33:49 -0500
From: Nathan Bossart <nathandbossart@gmail.com>
To: Corey Huinker <corey.huinker@gmail.com>
Cc: Jeff Davis <pgsql@j-davis.com>, Robert Treat <rob@xzilla.net>,
	Robert Haas <robertmhaas@gmail.com>,
	Andres Freund <andres@anarazel.de>, Tom Lane <tgl@sss.pgh.pa.us>,
	Michael Paquier <michael@paquier.xyz>,
	jian he <jian.universality@gmail.com>,
	Bruce Momjian <bruce@momjian.us>,
	Matthias van de Meent <boekewurm+postgres@gmail.com>,
	Magnus Hagander <magnus@hagander.net>,
	Stephen Frost <sfrost@snowman.net>,
	Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>,
	Peter Smith <smithpb2250@gmail.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
	alvherre@alvh.no-ip.org
Subject: Re: Statistics Import and Export
Message-ID: <Z9c1rbzZegYQTOQE@nathan>
References: <pbjbvxnq36xo2ooufcnlfqsqnxuvggbqxbx3myjtswokshfjbl@hfihx57z4j4t>
 <CA+TgmoYGuEC=E4TbomRyqO8+Uav=90G0dYXw=rA1d5UmRB76Yg@mail.gmail.com>
 <bd0513e4b1ea2b2f2d06f02720c6579711cb62a6.camel@j-davis.com>
 <CABV9wwO5v8Nu8q+xWexMdL3Z+2xS=fFJMQetBSHy3tR64wNHOA@mail.gmail.com>
 <bec4c2feb4ef51c129ee6e1912fffa911a3c7229.camel@j-davis.com>
 <CABV9wwP_ajEbLhCzfCO2Sy0180AshYwQFViys=QhyZA7sW2_qw@mail.gmail.com>
 <CADkLM=dvG_F2giHEAGJ=DGVGNmujUt0vAaXNSDkDXzbzcGQbxQ@mail.gmail.com>
 <CAJSLCQ10++5GLeoRwNMQMKuaVrikEp2X6feBv=kYDtWmsVy-Pw@mail.gmail.com>
 <d8a3beed2682fde95936ee779e411b907df1148a.camel@j-davis.com>
 <CADkLM=c+r05srPy9w+-+nbmLEo15dKXYQ03Q_xyK+riJerigLQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CADkLM=c+r05srPy9w+-+nbmLEo15dKXYQ03Q_xyK+riJerigLQ@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/Z9c1rbzZegYQTOQE%40nathan>
Precedence: bulk

Thanks for working on this, Corey.

On Fri, Mar 14, 2025 at 04:03:16PM -0400, Corey Huinker wrote:
> 0003 -
> 
> Storing the restore function calls in the archive entry hogged a lot of
> memory and made people nervous. This introduces a new function pointer that
> generates those restore SQL calls right before they're written to disk,
> thus reducing the memory load from "stats for every object to be dumped" to
> just one object. Thanks to Nathan for diagnosing some weird quirks with
> various formats.
> 
> 0004 -
> 
> This replaces the query in the prepared statement with one that batches
> them 100 relations at a time, and then maintains that result set until it
> is consumed. It seems to have obvious speedups.

I've been doing a variety of tests with my toy database of 100K relations
[0], and I'm seeing around 20% less memory usage.  That's still 20% more
than without stats, but that's still a pretty nice improvement.

I'd propose two small changes to the design:

* I tested a variety of batch sizes, and to my suprise, I saw the best
  results with around 64 relations per batch.  I imagine the absolute best
  batch size will vary greatly depending on the workload.  It might also
  depend on work_mem and friends.

* The custom format actually does two WriteToc() calls, and since these
  patches move the queries to this part of pg_dump, it means we'll run all
  the queries twice.  The comments around this code suggest that the second
  pass isn't strictly necessary and that it is really only useful for
  data/parallel restore, so we could probably skip it for no-data dumps.

With those two changes, a pg_upgrade-style dump of my test database goes
from ~21.6 seconds without these patches to ~11.2 seconds with them.  For
reference, the same dump without stats takes ~7 seconds on HEAD.

[0] https://postgr.es/m/Z9R9-mFbxukqKmg4%40nathan

-- 
nathan