pg.ddx.io pgsql-committers@postgresql.org mailing list archivehelp / color / mirror / Atom feed
pgsql: Improve performance of dumpSequence(). 2+ messages / 1 participants [nested] [flat]
* pgsql: Improve performance of dumpSequence(). @ 2024-07-31 15:13 Nathan Bossart <nathan@postgresql.org> 0 siblings, 0 replies; 2+ messages in thread From: Nathan Bossart @ 2024-07-31 15:13 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Improve performance of dumpSequence(). This function dumps the sequence definitions. It is called once per sequence, and each such call executes a query to retrieve the metadata for a single sequence. This can cause pg_dump to take significantly longer, especially when there are many sequences. This commit improves the performance of this function by gathering all the sequence metadata with a single query at the beginning of pg_dump. This information is stored in a sorted array that dumpSequence() can bsearch() for what it needs. This follows a similar approach as commits d5e8930f50 and 2329cad1b9, which introduced sorted arrays for role information and pg_class information, respectively. As with those commits, this patch will cause pg_dump to use more memory, but that isn't expected to be too egregious. Note that before version 10, the sequence metadata was stored in the sequence relation itself, which makes it difficult to gather all the sequence metadata with a single query. For those older versions, we continue to use the preexisting query-per-sequence approach. Reviewed-by: Euler Taveira Discussion: https://postgr.es/m/20240503025140.GA1227404%40nathanxps13 Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/68e9629985981ce8f8f04b5a9f8b3781eacaafd6 Modified Files -------------- src/bin/pg_dump/pg_dump.c | 175 ++++++++++++++++++++++++++++----------- src/tools/pgindent/typedefs.list | 1 + 2 files changed, 129 insertions(+), 47 deletions(-) ^ permalink raw reply [nested|flat] 2+ messages in thread
* pgsql: Improve performance of dumpSequenceData(). @ 2024-07-31 15:13 Nathan Bossart <nathan@postgresql.org> 0 siblings, 0 replies; 2+ messages in thread From: Nathan Bossart @ 2024-07-31 15:13 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Improve performance of dumpSequenceData(). As one might guess, this function dumps the sequence data. It is called once per sequence, and each such call executes a query to retrieve the relevant data for a single sequence. This can cause pg_dump to take significantly longer, especially when there are many sequences. This commit improves the performance of this function by gathering all the sequence data with a single query at the beginning of pg_dump. This information is stored in a sorted array that dumpSequenceData() can bsearch() for what it needs. This follows a similar approach as previous commits that introduced sorted arrays for role information, pg_class information, and sequence metadata. As with those commits, this patch will cause pg_dump to use more memory, but that isn't expected to be too egregious. Note that we use the brand new function pg_sequence_read_tuple() in the query that gathers all sequence data, so we must continue to use the preexisting query-per-sequence approach for versions older than 18. Reviewed-by: Euler Taveira, Michael Paquier, Tom Lane Discussion: https://postgr.es/m/20240503025140.GA1227404%40nathanxps13 Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/bd15b7db489deadb2d9af7f21d16a6ed4a09465b Modified Files -------------- src/bin/pg_dump/pg_dump.c | 81 ++++++++++++++++++++++++++++++++++++----------- 1 file changed, 63 insertions(+), 18 deletions(-) ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-07-31 15:13 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-07-31 15:13 pgsql: Improve performance of dumpSequence(). Nathan Bossart <nathan@postgresql.org> 2024-07-31 15:13 pgsql: Improve performance of dumpSequenceData(). Nathan Bossart <nathan@postgresql.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox