Re: BUG #19688: pg_dump --schema scans all sequences in PostgreSQL 18, causing severe performance regression

On Mon, 14 Sep 2026 at 20:49,, PG Bug reporting form <noreply(at)postgresql(dot)org>:
> * Total sequences in the database: 142237
> * Sequences in the schema being dumped: 329
>
> The command is approximately:
>
> ---------------
> pg_dump \
> -h 127.0.0.1 \
> -p 5432 \
> -U postgres \
> -F c \
> -n myschema \
> -b \
> mydatabase \
> -f output.dump
> ---------------
>
> With PostgreSQL 16, dumping this schema normally took aprox. ~50 seconds.
> After upgrading to PostgreSQL 18.6, the same dump takes approximately 5
> minutes and half.

Hi,

This comes from commit bd15b7db48 (v18): collectSequences() calls
pg_get_sequence_data() for every sequence in the database, even when
only a few of them are going to be dumped. Tom pointed this out while
discussing bug #19365 [1]. That function opens, locks, and reads each
sequence, so the cost depends on the number of sequences in the
database rather than in the dump.

I can reproduce it on HEAD with 20000 sequences in one schema and 4 in
another. "pg_dump -n small" takes about 1.3 s, of which the
collectSequences() query takes about 1 s; with the attached patch it
takes 0.33 s and the query 25 ms. The same dump also waits for an
AccessExclusiveLock held on an unrelated sequence: with an uncommitted
DROP SEQUENCE in the other schema, it waited until thattransaction
ended.

The attached patch passes the OIDs of the sequences whose data will be
dumped to the query and calls pg_get_sequence_data() only for them.
Definitions are still fetched for all sequences, since that part is
just a catalog scan.

Since this is a v18 regression, I have also attached versions for
REL_19_STABLE and REL_18_STABLE. They differ only in keeping the early
return for servers older than v10. The same tests and output
comparisons pass on both branches.

[1] https://postgr.es/m/1862355.1767827628@sss.pgh.pa.us

--
Andrew Krylosov