improve performance of pg_dump with many sequences

Nathan Bossart <nathandbossart@gmail.com>

From: Nathan Bossart <nathandbossart@gmail.com>

To: pgsql-hackers@postgresql.org

Date: 2024-05-03T02:51:40Z

Lists: pgsql-hackers

Attachments

v1-0001-parse-sequence-information.patch (text/x-diff)

Similar to 'pg_dump --binary-upgrade' [0], we can speed up pg_dump with
many sequences by gathering the required information in a single query
instead of two queries per sequence.  The attached patches are
works-in-progress, but here are the results I see on my machine for
'pg_dump --schema-only --binary-upgrade' with a million sequences:

          HEAD : 6m22.809s
           [0] : 1m54.701s
[0] + attached : 0m38.233s

I'm not sure I have all the details correct in 0003, and we might want to
separate the table into two tables which are only populated when the
relevant section is dumped.  Furthermore, the query in 0003 is a bit goofy
because it needs to dance around a bug reported elsewhere [1].

[0] https://postgr.es/m/20240418041712.GA3441570%40nathanxps13
[1] https://postgr.es/m/20240501005730.GA594666%40nathanxps13

-- 
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

Commits

pg_dump: Fix gathering of sequence information.
- 7a485bd641b7 19 (unreleased) landed
- 39d55557661f 18.2 landed
Improve performance of dumpSequence().
- 68e962998598 18.0 landed
Improve performance of dumpSequenceData().
- bd15b7db489d 18.0 landed
Introduce pg_sequence_read_tuple().
- c8b06bb969bf 18.0 landed
Parse sequence type and integer metadata in dumpSequence().
- 23687e925f94 18.0 landed