pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Robins Tharakan <tharakan@gmail.com>
Cc: Kumar, Sachin <ssetiya@amazon.com>
Cc: Nathan Bossart <nathandbossart@gmail.com>
Cc: Jan Wieck <jan@wi3ck.info>
Cc: Bruce Momjian <bruce@momjian.us>
Cc: Zhihong Yu <zyu@yugabyte.com>
Cc: Andrew Dunstan <andrew@dunslane.net>
Cc: Magnus Hagander <magnus@hagander.net>
Cc: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
Cc: pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>
Subject: Re: pg_upgrade failing for 200+ million Large Objects
Date: Wed, 27 Dec 2023 10:18:21 -0500
Message-ID: <2495251.1703690301@sss.pgh.pa.us> (raw)
In-Reply-To: <CAEP4nAyyUcS2-saWxKkL9MwMCWbiZjNn3YxEKvM03mf+B7UF-Q@mail.gmail.com>
References: <c0f5fde5-5cad-65ed-dd30-cd852301d074@wi3ck.info>
	<986904.1616525964@sss.pgh.pa.us>
	<6cccaa33-c263-b8a2-b064-985605d33d25@wi3ck.info>
	<988415.1616528159@sss.pgh.pa.us>
	<872315a8-99fc-da4e-463d-784cfb5a025d@wi3ck.info>
	<1010642.1616532950@sss.pgh.pa.us>
	<802b96e9-f5e1-015c-dfb9-8756974b11fc@wi3ck.info>
	<0263bf35-05d6-02a1-519b-b7895a918314@wi3ck.info>
	<20220825003227.GA1456581@nathanxps13>
	<663393ca-b2ff-26f0-2e2d-adc942aff4fd@timescale.com>
	<20220908231807.GA2242918@nathanxps13>
	<CAAWbhmgUb8p7ff_ZX5jCvqM=ipPxbbDJTXMNVzH-Ho_CXVkRHA@mail.gmail.com>
	<0643CC11-223A-4039-AC34-94E127462796@amazon.com>
	<1152134.1699555261@sss.pgh.pa.us>
	<83D44BE5-0088-4D41-8AE6-20A05D026F46@amazon.com>
	<81D13E16-BA04-43CF-9B89-B8924300B211@amazon.com>
	<240D05EC-8B28-4112-BEAB-85ECBAF3F871@amazon.com>
	<2055911.1702258962@sss.pgh.pa.us>
	<534746.1703116064@sss.pgh.pa.us>
	<CAEP4nAyyUcS2-saWxKkL9MwMCWbiZjNn3YxEKvM03mf+B7UF-Q@mail.gmail.com>

Robins Tharakan <tharakan@gmail.com> writes:
> Applying all 4 patches, I also see good performance improvement.
> With more Large Objects, although pg_dump improved significantly,
> pg_restore is now comfortably an order of magnitude faster.

Yeah.  The key thing here is that pg_dump can only parallelize
the data transfer, while (with 0004) pg_restore can parallelize
large object creation and owner-setting as well as data transfer.
I don't see any simple way to improve that on the dump side,
but I'm not sure we need to.  Zillions of empty objects is not
really the use case to worry about.  I suspect that a more realistic
case with moderate amounts of data in the blobs would make pg_dump
look better.

			regards, tom lane





view thread (93+ messages)  latest in thread

Message-ID: <2495251.1703690301@sss.pgh.pa.us>
Permalink:  ../2495251.1703690301@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/2495251.1703690301@sss.pgh.pa.us

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-hackers@postgresql.org
  Cc: tgl@sss.pgh.pa.us, tharakan@gmail.com, ssetiya@amazon.com, nathandbossart@gmail.com, jan@wi3ck.info, bruce@momjian.us, zyu@yugabyte.com, andrew@dunslane.net, magnus@hagander.net, peter.eisentraut@enterprisedb.com
  Subject: Re: pg_upgrade failing for 200+ million Large Objects
  In-Reply-To: <2495251.1703690301@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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