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

The following bug has been logged on the website:

Bug reference: 19688
Logged by: César García Naranjo
Email address: cesarg9(at)gmail(dot)com
PostgreSQL version: 18.6
Operating system: Ubuntu 24.04
Description:

I am seeing a severe performance regression in pg_dump --schema after
upgrading from PostgreSQL 16 to PostgreSQL 18.6.

Environment:

* PostgreSQL server: 18.6
* pg_dump: 18.6
* Database uses a schema-per-tenant design
* 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.

Using pg_stat_activity while the dump is running, almost all of the
additional time is spent in this query executed by pg_dump:

---------------
SELECT
seqrelid,
format_type(seqtypid, NULL),
seqstart,
seqincrement,
seqmax,
seqmin,
seqcache,
seqcycle,
last_value,
is_called
FROM pg_catalog.pg_sequence,
pg_get_sequence_data(seqrelid)
ORDER BY seqrelid;
---------------

During this query, the backend is typically waiting on:

wait_event_type = IO
wait_event = DataFileRead

I reproduced the sequence query independently.

Running it for all sequences in the database:

---------------
SELECT count(*)
FROM (
SELECT
seqrelid,
format_type(seqtypid, NULL),
seqstart,
seqincrement,
seqmax,
seqmin,
seqcache,
seqcycle,
last_value,
is_called
FROM pg_catalog.pg_sequence,
pg_get_sequence_data(seqrelid)
ORDER BY seqrelid
) s;

Result:

count: 142237
Time: 298715.267 ms (04:58.715)
---------------

The complete pg_dump --schema=myschema takes approximately:

---------------
326.7 seconds
---------------

so this sequence collection query accounts for almost all of the dump time,
however the selected schema only contains 329 sequences.

Running an equivalent query restricted to that schema:

---------------
SELECT count(*)
FROM (
SELECT
s.seqrelid,
d.last_value,
d.is_called
FROM pg_catalog.pg_sequence s
JOIN pg_catalog.pg_class c
ON c.oid = s.seqrelid
JOIN pg_catalog.pg_namespace n
ON n.oid = c.relnamespace
CROSS JOIN LATERAL pg_get_sequence_data(s.seqrelid) d
WHERE n.nspname = 'myschema'
) x;

Result:

count: 329
Time: 4984.888 ms (00:04.985)
---------------

The important part appears to be that PostgreSQL 18 pg_dump calls
pg_get_sequence_data() for every sequence in the database, even when pg_dump
is restricted to a single schema.

This is particularly expensive for schema-per-tenant databases. In this
database there are hundreds of schemas, each with approximately 329
sequences.

I prepared an experimental patch to pg_dump, with AI assistance. I am not
familiar with the PostgreSQL codebase, so please treat this patch as a proof
of concept rather than a proposed final fix.
The patch changes collectSequences() so that, for partial dumps, the
sequence query is restricted to the sequence OIDs that pg_dump has already
selected internally.

It keeps the existing PostgreSQL 18 behavior for full database dumps, while
for partial dumps it adds a condition equivalent to:

---------------
WHERE seqrelid = ANY ('{selected sequence OIDs}'::oid[])
---------------

With this patched pg_dump, dumping the same schema takes approximately ~50
seconds instead of ~5 minutes.

I also compared the output produced by the official and patched versions.

The archive TOCs were identical except for the archive creation timestamp.
After converting both archives to SQL using pg_restore, the generated SQL
was identical except for the random \restrict / \unrestrict token generated
by pg_dump.
I also restored both dumps and compared the sequence state.

This suggests that the performance regression is caused specifically by
collectSequences() reading sequence data for sequences that are not part of
the requested partial dump.

This behavior is especially problematic when backups are performed
separately per schema, because the cost of scanning all 142,237 sequences is
paid again for every pg_dump --schema invocation. In my case i take daily
backups for every schema, and adding ~5 minutes for every one would add 36
additional hours, thus making daily backups impossible.

Would it make sense for collectSequences() to restrict
pg_get_sequence_data() to the sequence objects already selected by pg_dump
for partial dumps, while retaining the current bulk query for full database
dumps?

Here is the patch that i am using right now as i cannot rollback the
database upgrade. I used the following to configure, compile and run the
current tests:

./configure --without-readline --enable-tap-tests
make -j4
make -C src/bin/pg_dump check

All the tests passed.

--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -311,7 +311,7 @@
static void dumpTableSchema(Archive *fout, const TableInfo *tbinfo);
static void dumpTableAttach(Archive *fout, const TableAttachInfo
*attachinfo);
static void dumpAttrDef(Archive *fout, const AttrDefInfo *adinfo);
-static void collectSequences(Archive *fout);
+static void collectSequences(Archive *fout, TableInfo tblinfo[], int
numTables);
static void dumpSequence(Archive *fout, const TableInfo *tbinfo);
static void dumpSequenceData(Archive *fout, const TableDataInfo *tdinfo);
static void dumpIndex(Archive *fout, const IndxInfo *indxinfo);
@@ -1141,7 +1141,7 @@
collectBinaryUpgradeClassOids(fout);

/* Collect sequence information. */
- collectSequences(fout);
+ collectSequences(fout, tblinfo, numTables);

/* Lastly, create dummy objects to represent the section boundaries
*/
boundaryObjs = createBoundaryObjects();
@@ -18728,10 +18728,15 @@
* speed in lookup.
*/
static void
-collectSequences(Archive *fout)
+collectSequences(Archive *fout, TableInfo tblinfo[], int numTables)
{
PGresult *res;
- const char *query;
+ PQExpBuffer query;
+ bool partial_dump = !fout->dopt->include_everything ||
+ schema_exclude_oids.head != NULL ||
+ table_exclude_oids.head != NULL ||
+ tabledata_exclude_oids.head != NULL ||
+ extension_exclude_oids.head != NULL;

/*
* Before Postgres 10, sequence metadata is in the sequence itself.
With
@@ -18743,26 +18748,49 @@
*/
if (fout->remoteVersion < 100000)
return;
- else if (fout->remoteVersion < 180000 ||
+ query = createPQExpBuffer();
+ if (fout->remoteVersion < 180000 ||
(!fout->dopt->dumpData &&
!fout->dopt->sequence_data))
- query = "SELECT seqrelid, format_type(seqtypid, NULL), "
+ appendPQExpBufferStr(query, "SELECT seqrelid,
format_type(seqtypid, NULL), "
"seqstart, seqincrement, "
"seqmax, seqmin, "
"seqcache, seqcycle, "
"NULL, 'f' "
- "FROM pg_catalog.pg_sequence "
- "ORDER BY seqrelid";
+ "FROM pg_catalog.pg_sequence ");
else
- query = "SELECT seqrelid, format_type(seqtypid, NULL), "
+ appendPQExpBufferStr(query, "SELECT seqrelid,
format_type(seqtypid, NULL), "
"seqstart, seqincrement, "
"seqmax, seqmin, "
"seqcache, seqcycle, "
"last_value, is_called "
"FROM pg_catalog.pg_sequence, "
- "pg_get_sequence_data(seqrelid) "
- "ORDER BY seqrelid;";
+ "pg_get_sequence_data(seqrelid) ");

- res = ExecuteSqlQuery(fout, query, PGRES_TUPLES_OK);
+ /* For PostgreSQL 18 and newer, restrict partial dumps to sequences
whose
+ * definition or data is emitted. Keep the upstream query for older
servers.
+ * getSchemaData() has already accounted for filters, ownership, and
+ * extension membership; getTableData() has created the data
objects.
+ */
+ if (fout->remoteVersion >= 180000 && partial_dump)
+ {
+ appendPQExpBufferStr(query, "WHERE seqrelid = ANY ('{");
+ for (int i = 0, n = 0; i < numTables; i++)
+ {
+ TableInfo *tbinfo = &tblinfo[i];
+
+ if (tbinfo->relkind != RELKIND_SEQUENCE ||
+ (!(tbinfo->dobj.dump &
DUMP_COMPONENT_DEFINITION) &&
+ tbinfo->dataObj == NULL))
+ continue;
+ appendPQExpBuffer(query, "%s%u", n++ ? "," : "",
+
tbinfo->dobj.catId.oid);
+ }
+ appendPQExpBufferStr(query, "}'::pg_catalog.oid[]) ");
+ }
+ appendPQExpBufferStr(query, "ORDER BY seqrelid");
+
+ res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
+ destroyPQExpBuffer(query);

nsequences = PQntuples(res);
sequences = (SequenceItem *) pg_malloc(nsequences *
sizeof(SequenceItem));