agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedBUG #19688: pg_dump --schema scans all sequences in PostgreSQL 18, causing severe performance regression
2+ messages / 2 participants
[nested] [flat]
* BUG #19688: pg_dump --schema scans all sequences in PostgreSQL 18, causing severe performance regression
@ 2026-09-14 20:49 PG Bug reporting form <noreply@postgresql.org>
2026-09-26 19:01 ` Re: BUG #19688: pg_dump --schema scans all sequences in PostgreSQL 18, causing severe performance regression Andrew Krylosov <krylosov.andrew@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2026-09-14 20:49 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: cesarg9@gmail.com
The following bug has been logged on the website:
Bug reference: 19688
Logged by: César García Naranjo
Email address: cesarg9@gmail.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));
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #19688: pg_dump --schema scans all sequences in PostgreSQL 18, causing severe performance regression
2026-09-14 20:49 BUG #19688: pg_dump --schema scans all sequences in PostgreSQL 18, causing severe performance regression PG Bug reporting form <noreply@postgresql.org>
@ 2026-09-26 19:01 ` Andrew Krylosov <krylosov.andrew@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Andrew Krylosov @ 2026-09-26 19:01 UTC (permalink / raw)
To: cesarg9@gmail.com; pgsql-bugs@lists.postgresql.org
On Mon, 14 Sep 2026 at 20:49,, PG Bug reporting form <noreply@postgresql.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
From b73a2f979c0f5104c085cb8d52f9c0b441994e32 Mon Sep 17 00:00:00 2001
From: Andrew Krylosov <krylosov.andrew@gmail.com>
Date: Sat, 26 Sep 2026 21:57:45 +0300
Subject: [PATCH v1] pg_dump: Fetch sequence data only for sequences being
dumped
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Since commit bd15b7db48, collectSequences() reads the data of every
sequence in the database: its query calls pg_get_sequence_data() for
each row of pg_sequence, even when only a few sequences are going to be
dumped, e.g. with --schema or --table. That function opens, locks, and
reads each sequence, so in databases with many sequences a selective
dump became much slower than it was before v18. Such a dump could also
block on a lock held on a sequence it doesn't dump, for example one
being dropped by an uncommitted transaction.
To fix, pass the OIDs of the sequences whose data will be dumped to the
query and call pg_get_sequence_data() only for those.
Bug: #19688
Reported-by: César García Naranjo <cesarg9@gmail.com>
Author: Andrew Krylosov <krylosov.andrew@gmail.com>
Discussion: https://postgr.es/m/19688-e90025dc375a22a3@postgresql.org
Discussion: https://postgr.es/m/1862355.1767827628@sss.pgh.pa.us
Backpatch-through: 18
---
src/bin/pg_dump/pg_dump.c | 83 ++++++++++++++++++++++++++++-----------
1 file changed, 61 insertions(+), 22 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 618b0ea28a..9ad6d1f422 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -311,7 +311,7 @@ static void dumpTable(Archive *fout, const TableInfo *tbinfo);
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 @@ main(int argc, char **argv)
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,10 @@ SequenceItemCmp(const void *p1, const void *p2)
* speed in lookup.
*/
static void
-collectSequences(Archive *fout)
+collectSequences(Archive *fout, TableInfo *tblinfo, int numTables)
{
+ PQExpBuffer query;
PGresult *res;
- const char *query;
/*
* Before Postgres 10, sequence metadata is in the sequence itself. With
@@ -18743,26 +18743,64 @@ collectSequences(Archive *fout)
*/
if (fout->remoteVersion < 100000)
return;
- else if (fout->remoteVersion < 180000 ||
- (!fout->dopt->dumpData && !fout->dopt->sequence_data))
- query = "SELECT seqrelid, format_type(seqtypid, NULL), "
- "seqstart, seqincrement, "
- "seqmax, seqmin, "
- "seqcache, seqcycle, "
- "NULL, 'f' "
- "FROM pg_catalog.pg_sequence "
- "ORDER BY seqrelid";
+
+ query = createPQExpBuffer();
+
+ if (fout->remoteVersion < 180000 ||
+ (!fout->dopt->dumpData && !fout->dopt->sequence_data))
+ appendPQExpBufferStr(query,
+ "SELECT seqrelid, format_type(seqtypid, NULL), "
+ "seqstart, seqincrement, "
+ "seqmax, seqmin, "
+ "seqcache, seqcycle, "
+ "NULL, 'f' "
+ "FROM pg_catalog.pg_sequence "
+ "ORDER BY seqrelid");
else
- 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;";
+ {
+ PQExpBuffer seqoids = createPQExpBuffer();
- res = ExecuteSqlQuery(fout, query, PGRES_TUPLES_OK);
+ /*
+ * pg_get_sequence_data() has to open, lock, and read each sequence,
+ * so call it only for the sequences whose data will be dumped, i.e.,
+ * those that already have a TableDataInfo. Otherwise, dumping a few
+ * sequences from a database that has many would be slow, and it could
+ * block on locks held on sequences we're not dumping. We still
+ * collect the definitions of all sequences, which is cheap.
+ */
+ appendPQExpBufferChar(seqoids, '{');
+ for (int i = 0; i < numTables; i++)
+ {
+ TableInfo *tbinfo = &tblinfo[i];
+
+ if (tbinfo->relkind != RELKIND_SEQUENCE || tbinfo->dataObj == NULL)
+ continue;
+
+ if (seqoids->len > 1) /* do we have more than the '{'? */
+ appendPQExpBufferChar(seqoids, ',');
+ appendPQExpBuffer(seqoids, "%u", tbinfo->dobj.catId.oid);
+ }
+ appendPQExpBufferChar(seqoids, '}');
+
+ appendPQExpBuffer(query,
+ "SELECT s.seqrelid, format_type(s.seqtypid, NULL), "
+ "s.seqstart, s.seqincrement, "
+ "s.seqmax, s.seqmin, "
+ "s.seqcache, s.seqcycle, "
+ "d.last_value, d.is_called "
+ "FROM pg_catalog.pg_sequence s "
+ "LEFT JOIN (SELECT src.seqrelid, "
+ "sd.last_value, sd.is_called "
+ "FROM unnest('%s'::pg_catalog.oid[]) AS src(seqrelid), "
+ "pg_get_sequence_data(src.seqrelid) AS sd) d "
+ "ON d.seqrelid = s.seqrelid "
+ "ORDER BY s.seqrelid",
+ seqoids->data);
+
+ destroyPQExpBuffer(seqoids);
+ }
+
+ res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
nsequences = PQntuples(res);
sequences = (SequenceItem *) pg_malloc(nsequences * sizeof(SequenceItem));
@@ -18783,6 +18821,7 @@ collectSequences(Archive *fout)
}
PQclear(res);
+ destroyPQExpBuffer(query);
}
/*
base-commit: 2d748cfe337ec5afab3863f733caca43333c89cf
--
2.50.1 (Apple Git-155)
From 26278a2ee1faa6d02712442af4ad2ea25ee48bec Mon Sep 17 00:00:00 2001
From: Andrew Krylosov <krylosov.andrew@gmail.com>
Date: Sat, 26 Sep 2026 21:57:45 +0300
Subject: [PATCH v1] pg_dump: Fetch sequence data only for sequences being
dumped
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Since commit bd15b7db48, collectSequences() reads the data of every
sequence in the database: its query calls pg_get_sequence_data() for
each row of pg_sequence, even when only a few sequences are going to be
dumped, e.g. with --schema or --table. That function opens, locks, and
reads each sequence, so in databases with many sequences a selective
dump became much slower than it was before v18. Such a dump could also
block on a lock held on a sequence it doesn't dump, for example one
being dropped by an uncommitted transaction.
To fix, pass the OIDs of the sequences whose data will be dumped to the
query and call pg_get_sequence_data() only for those.
Bug: #19688
Reported-by: César García Naranjo <cesarg9@gmail.com>
Author: Andrew Krylosov <krylosov.andrew@gmail.com>
Discussion: https://postgr.es/m/19688-e90025dc375a22a3@postgresql.org
Discussion: https://postgr.es/m/1862355.1767827628@sss.pgh.pa.us
Backpatch-through: 18
---
src/bin/pg_dump/pg_dump.c | 83 ++++++++++++++++++++++++++++-----------
1 file changed, 61 insertions(+), 22 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 4bb69319b2..2204c24cdb 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -314,7 +314,7 @@ static void dumpTable(Archive *fout, const TableInfo *tbinfo);
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);
@@ -1168,7 +1168,7 @@ main(int argc, char **argv)
collectBinaryUpgradeClassOids(fout);
/* Collect sequence information. */
- collectSequences(fout);
+ collectSequences(fout, tblinfo, numTables);
/* Lastly, create dummy objects to represent the section boundaries */
boundaryObjs = createBoundaryObjects();
@@ -19351,10 +19351,10 @@ SequenceItemCmp(const void *p1, const void *p2)
* speed in lookup.
*/
static void
-collectSequences(Archive *fout)
+collectSequences(Archive *fout, TableInfo *tblinfo, int numTables)
{
+ PQExpBuffer query;
PGresult *res;
- const char *query;
/*
* Before Postgres 10, sequence metadata is in the sequence itself. With
@@ -19366,26 +19366,64 @@ collectSequences(Archive *fout)
*/
if (fout->remoteVersion < 100000)
return;
- else if (fout->remoteVersion < 180000 ||
- (!fout->dopt->dumpData && !fout->dopt->sequence_data))
- query = "SELECT seqrelid, format_type(seqtypid, NULL), "
- "seqstart, seqincrement, "
- "seqmax, seqmin, "
- "seqcache, seqcycle, "
- "NULL, 'f' "
- "FROM pg_catalog.pg_sequence "
- "ORDER BY seqrelid";
+
+ query = createPQExpBuffer();
+
+ if (fout->remoteVersion < 180000 ||
+ (!fout->dopt->dumpData && !fout->dopt->sequence_data))
+ appendPQExpBufferStr(query,
+ "SELECT seqrelid, format_type(seqtypid, NULL), "
+ "seqstart, seqincrement, "
+ "seqmax, seqmin, "
+ "seqcache, seqcycle, "
+ "NULL, 'f' "
+ "FROM pg_catalog.pg_sequence "
+ "ORDER BY seqrelid");
else
- 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;";
+ {
+ PQExpBuffer seqoids = createPQExpBuffer();
- res = ExecuteSqlQuery(fout, query, PGRES_TUPLES_OK);
+ /*
+ * pg_get_sequence_data() has to open, lock, and read each sequence,
+ * so call it only for the sequences whose data will be dumped, i.e.,
+ * those that already have a TableDataInfo. Otherwise, dumping a few
+ * sequences from a database that has many would be slow, and it could
+ * block on locks held on sequences we're not dumping. We still
+ * collect the definitions of all sequences, which is cheap.
+ */
+ appendPQExpBufferChar(seqoids, '{');
+ for (int i = 0; i < numTables; i++)
+ {
+ TableInfo *tbinfo = &tblinfo[i];
+
+ if (tbinfo->relkind != RELKIND_SEQUENCE || tbinfo->dataObj == NULL)
+ continue;
+
+ if (seqoids->len > 1) /* do we have more than the '{'? */
+ appendPQExpBufferChar(seqoids, ',');
+ appendPQExpBuffer(seqoids, "%u", tbinfo->dobj.catId.oid);
+ }
+ appendPQExpBufferChar(seqoids, '}');
+
+ appendPQExpBuffer(query,
+ "SELECT s.seqrelid, format_type(s.seqtypid, NULL), "
+ "s.seqstart, s.seqincrement, "
+ "s.seqmax, s.seqmin, "
+ "s.seqcache, s.seqcycle, "
+ "d.last_value, d.is_called "
+ "FROM pg_catalog.pg_sequence s "
+ "LEFT JOIN (SELECT src.seqrelid, "
+ "sd.last_value, sd.is_called "
+ "FROM unnest('%s'::pg_catalog.oid[]) AS src(seqrelid), "
+ "pg_get_sequence_data(src.seqrelid) AS sd) d "
+ "ON d.seqrelid = s.seqrelid "
+ "ORDER BY s.seqrelid",
+ seqoids->data);
+
+ destroyPQExpBuffer(seqoids);
+ }
+
+ res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
nsequences = PQntuples(res);
sequences = pg_malloc_array(SequenceItem, nsequences);
@@ -19406,6 +19444,7 @@ collectSequences(Archive *fout)
}
PQclear(res);
+ destroyPQExpBuffer(query);
}
/*
base-commit: 054eb938e51f4779f3fa4a9c2ed882a49a2cb941
--
2.50.1 (Apple Git-155)
Attachments:
[text/plain] v1_PG18-0001-pg_dump-Fetch-sequence-data-only-for-sequences-be.patch.txt (5.8K, ../../CA+nn4-qhKbHqXKtWc7asZAuHZE+6urDBxebxF1uCLz767khSAw@mail.gmail.com/2-v1_PG18-0001-pg_dump-Fetch-sequence-data-only-for-sequences-be.patch.txt)
download | inline diff:
From b73a2f979c0f5104c085cb8d52f9c0b441994e32 Mon Sep 17 00:00:00 2001
From: Andrew Krylosov <krylosov.andrew@gmail.com>
Date: Sat, 26 Sep 2026 21:57:45 +0300
Subject: [PATCH v1] pg_dump: Fetch sequence data only for sequences being
dumped
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Since commit bd15b7db48, collectSequences() reads the data of every
sequence in the database: its query calls pg_get_sequence_data() for
each row of pg_sequence, even when only a few sequences are going to be
dumped, e.g. with --schema or --table. That function opens, locks, and
reads each sequence, so in databases with many sequences a selective
dump became much slower than it was before v18. Such a dump could also
block on a lock held on a sequence it doesn't dump, for example one
being dropped by an uncommitted transaction.
To fix, pass the OIDs of the sequences whose data will be dumped to the
query and call pg_get_sequence_data() only for those.
Bug: #19688
Reported-by: César García Naranjo <cesarg9@gmail.com>
Author: Andrew Krylosov <krylosov.andrew@gmail.com>
Discussion: https://postgr.es/m/19688-e90025dc375a22a3@postgresql.org
Discussion: https://postgr.es/m/1862355.1767827628@sss.pgh.pa.us
Backpatch-through: 18
---
src/bin/pg_dump/pg_dump.c | 83 ++++++++++++++++++++++++++++-----------
1 file changed, 61 insertions(+), 22 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 618b0ea28a..9ad6d1f422 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -311,7 +311,7 @@ static void dumpTable(Archive *fout, const TableInfo *tbinfo);
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 @@ main(int argc, char **argv)
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,10 @@ SequenceItemCmp(const void *p1, const void *p2)
* speed in lookup.
*/
static void
-collectSequences(Archive *fout)
+collectSequences(Archive *fout, TableInfo *tblinfo, int numTables)
{
+ PQExpBuffer query;
PGresult *res;
- const char *query;
/*
* Before Postgres 10, sequence metadata is in the sequence itself. With
@@ -18743,26 +18743,64 @@ collectSequences(Archive *fout)
*/
if (fout->remoteVersion < 100000)
return;
- else if (fout->remoteVersion < 180000 ||
- (!fout->dopt->dumpData && !fout->dopt->sequence_data))
- query = "SELECT seqrelid, format_type(seqtypid, NULL), "
- "seqstart, seqincrement, "
- "seqmax, seqmin, "
- "seqcache, seqcycle, "
- "NULL, 'f' "
- "FROM pg_catalog.pg_sequence "
- "ORDER BY seqrelid";
+
+ query = createPQExpBuffer();
+
+ if (fout->remoteVersion < 180000 ||
+ (!fout->dopt->dumpData && !fout->dopt->sequence_data))
+ appendPQExpBufferStr(query,
+ "SELECT seqrelid, format_type(seqtypid, NULL), "
+ "seqstart, seqincrement, "
+ "seqmax, seqmin, "
+ "seqcache, seqcycle, "
+ "NULL, 'f' "
+ "FROM pg_catalog.pg_sequence "
+ "ORDER BY seqrelid");
else
- 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;";
+ {
+ PQExpBuffer seqoids = createPQExpBuffer();
- res = ExecuteSqlQuery(fout, query, PGRES_TUPLES_OK);
+ /*
+ * pg_get_sequence_data() has to open, lock, and read each sequence,
+ * so call it only for the sequences whose data will be dumped, i.e.,
+ * those that already have a TableDataInfo. Otherwise, dumping a few
+ * sequences from a database that has many would be slow, and it could
+ * block on locks held on sequences we're not dumping. We still
+ * collect the definitions of all sequences, which is cheap.
+ */
+ appendPQExpBufferChar(seqoids, '{');
+ for (int i = 0; i < numTables; i++)
+ {
+ TableInfo *tbinfo = &tblinfo[i];
+
+ if (tbinfo->relkind != RELKIND_SEQUENCE || tbinfo->dataObj == NULL)
+ continue;
+
+ if (seqoids->len > 1) /* do we have more than the '{'? */
+ appendPQExpBufferChar(seqoids, ',');
+ appendPQExpBuffer(seqoids, "%u", tbinfo->dobj.catId.oid);
+ }
+ appendPQExpBufferChar(seqoids, '}');
+
+ appendPQExpBuffer(query,
+ "SELECT s.seqrelid, format_type(s.seqtypid, NULL), "
+ "s.seqstart, s.seqincrement, "
+ "s.seqmax, s.seqmin, "
+ "s.seqcache, s.seqcycle, "
+ "d.last_value, d.is_called "
+ "FROM pg_catalog.pg_sequence s "
+ "LEFT JOIN (SELECT src.seqrelid, "
+ "sd.last_value, sd.is_called "
+ "FROM unnest('%s'::pg_catalog.oid[]) AS src(seqrelid), "
+ "pg_get_sequence_data(src.seqrelid) AS sd) d "
+ "ON d.seqrelid = s.seqrelid "
+ "ORDER BY s.seqrelid",
+ seqoids->data);
+
+ destroyPQExpBuffer(seqoids);
+ }
+
+ res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
nsequences = PQntuples(res);
sequences = (SequenceItem *) pg_malloc(nsequences * sizeof(SequenceItem));
@@ -18783,6 +18821,7 @@ collectSequences(Archive *fout)
}
PQclear(res);
+ destroyPQExpBuffer(query);
}
/*
base-commit: 2d748cfe337ec5afab3863f733caca43333c89cf
--
2.50.1 (Apple Git-155)
[application/octet-stream] v1-0001-pg_dump-Fetch-sequence-data-only-for-sequences-be.patch (5.6K, ../../CA+nn4-qhKbHqXKtWc7asZAuHZE+6urDBxebxF1uCLz767khSAw@mail.gmail.com/3-v1-0001-pg_dump-Fetch-sequence-data-only-for-sequences-be.patch)
download | inline diff:
From 688958595eb233b7610ba311687ee14916a27350 Mon Sep 17 00:00:00 2001
From: Andrew Krylosov <krylosov.andrew@gmail.com>
Date: Sat, 26 Sep 2026 21:57:45 +0300
Subject: [PATCH v1] pg_dump: Fetch sequence data only for sequences being
dumped
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Since commit bd15b7db48, collectSequences() reads the data of every
sequence in the database: its query calls pg_get_sequence_data() for
each row of pg_sequence, even when only a few sequences are going to be
dumped, e.g. with --schema or --table. That function opens, locks, and
reads each sequence, so in databases with many sequences a selective
dump became much slower than it was before v18. Such a dump could also
block on a lock held on a sequence it doesn't dump, for example one
being dropped by an uncommitted transaction.
To fix, pass the OIDs of the sequences whose data will be dumped to the
query and call pg_get_sequence_data() only for those.
Bug: #19688
Reported-by: César García Naranjo <cesarg9@gmail.com>
Author: Andrew Krylosov <krylosov.andrew@gmail.com>
Discussion: https://postgr.es/m/19688-e90025dc375a22a3@postgresql.org
Discussion: https://postgr.es/m/1862355.1767827628@sss.pgh.pa.us
Backpatch-through: 18
---
src/bin/pg_dump/pg_dump.c | 76 ++++++++++++++++++++++++++++-----------
1 file changed, 56 insertions(+), 20 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 388c3b9c34..0e48750581 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -315,7 +315,7 @@ static void dumpTable(Archive *fout, const TableInfo *tbinfo);
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);
@@ -1169,7 +1169,7 @@ main(int argc, char **argv)
collectBinaryUpgradeClassOids(fout);
/* Collect sequence information. */
- collectSequences(fout);
+ collectSequences(fout, tblinfo, numTables);
/* Lastly, create dummy objects to represent the section boundaries */
boundaryObjs = createBoundaryObjects();
@@ -19106,10 +19106,10 @@ SequenceItemCmp(const void *p1, const void *p2)
* speed in lookup.
*/
static void
-collectSequences(Archive *fout)
+collectSequences(Archive *fout, TableInfo *tblinfo, int numTables)
{
+ PQExpBuffer query = createPQExpBuffer();
PGresult *res;
- const char *query;
/*
* Since version 18, we can gather the sequence data in this query with
@@ -19117,24 +19117,59 @@ collectSequences(Archive *fout)
*/
if (fout->remoteVersion < 180000 ||
(!fout->dopt->dumpData && !fout->dopt->sequence_data))
- query = "SELECT seqrelid, format_type(seqtypid, NULL), "
- "seqstart, seqincrement, "
- "seqmax, seqmin, "
- "seqcache, seqcycle, "
- "NULL, 'f' "
- "FROM pg_catalog.pg_sequence "
- "ORDER BY seqrelid";
+ appendPQExpBufferStr(query,
+ "SELECT seqrelid, format_type(seqtypid, NULL), "
+ "seqstart, seqincrement, "
+ "seqmax, seqmin, "
+ "seqcache, seqcycle, "
+ "NULL, 'f' "
+ "FROM pg_catalog.pg_sequence "
+ "ORDER BY seqrelid");
else
- 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;";
+ {
+ PQExpBuffer seqoids = createPQExpBuffer();
- res = ExecuteSqlQuery(fout, query, PGRES_TUPLES_OK);
+ /*
+ * pg_get_sequence_data() has to open, lock, and read each sequence,
+ * so call it only for the sequences whose data will be dumped, i.e.,
+ * those that already have a TableDataInfo. Otherwise, dumping a few
+ * sequences from a database that has many would be slow, and it could
+ * block on locks held on sequences we're not dumping. We still
+ * collect the definitions of all sequences, which is cheap.
+ */
+ appendPQExpBufferChar(seqoids, '{');
+ for (int i = 0; i < numTables; i++)
+ {
+ TableInfo *tbinfo = &tblinfo[i];
+
+ if (tbinfo->relkind != RELKIND_SEQUENCE || tbinfo->dataObj == NULL)
+ continue;
+
+ if (seqoids->len > 1) /* do we have more than the '{'? */
+ appendPQExpBufferChar(seqoids, ',');
+ appendPQExpBuffer(seqoids, "%u", tbinfo->dobj.catId.oid);
+ }
+ appendPQExpBufferChar(seqoids, '}');
+
+ appendPQExpBuffer(query,
+ "SELECT s.seqrelid, format_type(s.seqtypid, NULL), "
+ "s.seqstart, s.seqincrement, "
+ "s.seqmax, s.seqmin, "
+ "s.seqcache, s.seqcycle, "
+ "d.last_value, d.is_called "
+ "FROM pg_catalog.pg_sequence s "
+ "LEFT JOIN (SELECT src.seqrelid, "
+ "sd.last_value, sd.is_called "
+ "FROM unnest('%s'::pg_catalog.oid[]) AS src(seqrelid), "
+ "pg_get_sequence_data(src.seqrelid) AS sd) d "
+ "ON d.seqrelid = s.seqrelid "
+ "ORDER BY s.seqrelid",
+ seqoids->data);
+
+ destroyPQExpBuffer(seqoids);
+ }
+
+ res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
nsequences = PQntuples(res);
sequences = pg_malloc_array(SequenceItem, nsequences);
@@ -19155,6 +19190,7 @@ collectSequences(Archive *fout)
}
PQclear(res);
+ destroyPQExpBuffer(query);
}
/*
base-commit: f197fdee8265050bfefb2f5d00829a814ff05ae4
--
2.50.1 (Apple Git-155)
[text/plain] v1_PG19-0001-pg_dump-Fetch-sequence-data-only-for-sequences-be.patch.txt (5.8K, ../../CA+nn4-qhKbHqXKtWc7asZAuHZE+6urDBxebxF1uCLz767khSAw@mail.gmail.com/4-v1_PG19-0001-pg_dump-Fetch-sequence-data-only-for-sequences-be.patch.txt)
download | inline diff:
From 26278a2ee1faa6d02712442af4ad2ea25ee48bec Mon Sep 17 00:00:00 2001
From: Andrew Krylosov <krylosov.andrew@gmail.com>
Date: Sat, 26 Sep 2026 21:57:45 +0300
Subject: [PATCH v1] pg_dump: Fetch sequence data only for sequences being
dumped
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Since commit bd15b7db48, collectSequences() reads the data of every
sequence in the database: its query calls pg_get_sequence_data() for
each row of pg_sequence, even when only a few sequences are going to be
dumped, e.g. with --schema or --table. That function opens, locks, and
reads each sequence, so in databases with many sequences a selective
dump became much slower than it was before v18. Such a dump could also
block on a lock held on a sequence it doesn't dump, for example one
being dropped by an uncommitted transaction.
To fix, pass the OIDs of the sequences whose data will be dumped to the
query and call pg_get_sequence_data() only for those.
Bug: #19688
Reported-by: César García Naranjo <cesarg9@gmail.com>
Author: Andrew Krylosov <krylosov.andrew@gmail.com>
Discussion: https://postgr.es/m/19688-e90025dc375a22a3@postgresql.org
Discussion: https://postgr.es/m/1862355.1767827628@sss.pgh.pa.us
Backpatch-through: 18
---
src/bin/pg_dump/pg_dump.c | 83 ++++++++++++++++++++++++++++-----------
1 file changed, 61 insertions(+), 22 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 4bb69319b2..2204c24cdb 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -314,7 +314,7 @@ static void dumpTable(Archive *fout, const TableInfo *tbinfo);
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);
@@ -1168,7 +1168,7 @@ main(int argc, char **argv)
collectBinaryUpgradeClassOids(fout);
/* Collect sequence information. */
- collectSequences(fout);
+ collectSequences(fout, tblinfo, numTables);
/* Lastly, create dummy objects to represent the section boundaries */
boundaryObjs = createBoundaryObjects();
@@ -19351,10 +19351,10 @@ SequenceItemCmp(const void *p1, const void *p2)
* speed in lookup.
*/
static void
-collectSequences(Archive *fout)
+collectSequences(Archive *fout, TableInfo *tblinfo, int numTables)
{
+ PQExpBuffer query;
PGresult *res;
- const char *query;
/*
* Before Postgres 10, sequence metadata is in the sequence itself. With
@@ -19366,26 +19366,64 @@ collectSequences(Archive *fout)
*/
if (fout->remoteVersion < 100000)
return;
- else if (fout->remoteVersion < 180000 ||
- (!fout->dopt->dumpData && !fout->dopt->sequence_data))
- query = "SELECT seqrelid, format_type(seqtypid, NULL), "
- "seqstart, seqincrement, "
- "seqmax, seqmin, "
- "seqcache, seqcycle, "
- "NULL, 'f' "
- "FROM pg_catalog.pg_sequence "
- "ORDER BY seqrelid";
+
+ query = createPQExpBuffer();
+
+ if (fout->remoteVersion < 180000 ||
+ (!fout->dopt->dumpData && !fout->dopt->sequence_data))
+ appendPQExpBufferStr(query,
+ "SELECT seqrelid, format_type(seqtypid, NULL), "
+ "seqstart, seqincrement, "
+ "seqmax, seqmin, "
+ "seqcache, seqcycle, "
+ "NULL, 'f' "
+ "FROM pg_catalog.pg_sequence "
+ "ORDER BY seqrelid");
else
- 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;";
+ {
+ PQExpBuffer seqoids = createPQExpBuffer();
- res = ExecuteSqlQuery(fout, query, PGRES_TUPLES_OK);
+ /*
+ * pg_get_sequence_data() has to open, lock, and read each sequence,
+ * so call it only for the sequences whose data will be dumped, i.e.,
+ * those that already have a TableDataInfo. Otherwise, dumping a few
+ * sequences from a database that has many would be slow, and it could
+ * block on locks held on sequences we're not dumping. We still
+ * collect the definitions of all sequences, which is cheap.
+ */
+ appendPQExpBufferChar(seqoids, '{');
+ for (int i = 0; i < numTables; i++)
+ {
+ TableInfo *tbinfo = &tblinfo[i];
+
+ if (tbinfo->relkind != RELKIND_SEQUENCE || tbinfo->dataObj == NULL)
+ continue;
+
+ if (seqoids->len > 1) /* do we have more than the '{'? */
+ appendPQExpBufferChar(seqoids, ',');
+ appendPQExpBuffer(seqoids, "%u", tbinfo->dobj.catId.oid);
+ }
+ appendPQExpBufferChar(seqoids, '}');
+
+ appendPQExpBuffer(query,
+ "SELECT s.seqrelid, format_type(s.seqtypid, NULL), "
+ "s.seqstart, s.seqincrement, "
+ "s.seqmax, s.seqmin, "
+ "s.seqcache, s.seqcycle, "
+ "d.last_value, d.is_called "
+ "FROM pg_catalog.pg_sequence s "
+ "LEFT JOIN (SELECT src.seqrelid, "
+ "sd.last_value, sd.is_called "
+ "FROM unnest('%s'::pg_catalog.oid[]) AS src(seqrelid), "
+ "pg_get_sequence_data(src.seqrelid) AS sd) d "
+ "ON d.seqrelid = s.seqrelid "
+ "ORDER BY s.seqrelid",
+ seqoids->data);
+
+ destroyPQExpBuffer(seqoids);
+ }
+
+ res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
nsequences = PQntuples(res);
sequences = pg_malloc_array(SequenceItem, nsequences);
@@ -19406,6 +19444,7 @@ collectSequences(Archive *fout)
}
PQclear(res);
+ destroyPQExpBuffer(query);
}
/*
base-commit: 054eb938e51f4779f3fa4a9c2ed882a49a2cb941
--
2.50.1 (Apple Git-155)
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-09-26 19:01 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-14 20:49 BUG #19688: pg_dump --schema scans all sequences in PostgreSQL 18, causing severe performance regression PG Bug reporting form <noreply@postgresql.org>
2026-09-26 19:01 ` Andrew Krylosov <krylosov.andrew@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox