Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rX52G-008g1O-0G for pgsql-general@arkaria.postgresql.org; Mon, 05 Feb 2024 19:53:45 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rX52E-00FSA6-Cc for pgsql-general@arkaria.postgresql.org; Mon, 05 Feb 2024 19:53:42 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rX52C-00FS9q-GJ for pgsql-general@lists.postgresql.org; Mon, 05 Feb 2024 19:53:41 +0000 Received: from out2-smtp.messagingengine.com ([66.111.4.26]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rX524-005Ex5-Vz for pgsql-general@lists.postgresql.org; Mon, 05 Feb 2024 19:53:39 +0000 Received: from compute5.internal (compute5.nyi.internal [10.202.2.45]) by mailout.nyi.internal (Postfix) with ESMTP id 9CD175C00F9; Mon, 5 Feb 2024 14:53:30 -0500 (EST) Received: from mailfrontend1 ([10.202.2.162]) by compute5.internal (MEProxy); Mon, 05 Feb 2024 14:53:30 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:content-type:content-type:date:date:from:from:in-reply-to :in-reply-to:message-id:mime-version:references:reply-to:subject :subject:to:to; s=fm2; t=1707162810; x=1707249210; bh=FTUpOD1eQe y+VBm7DSfOa+IxppJrdWsNO9AahUA3Xtw=; b=E38/hqOnlM0uFo8Qhk6+ou6n8D V9fU00fYTuCafq7TfF0H6H5txomXD9xOLwwENHWNC+bIhmLnxEnUgjJC+dKKacMF 4++YfnGYVDoSGxWUXk6+mM/7QArmbbC77N9bQsSbOFA+9lS+KMy279wxE5nZslxu W9kQ5s3q9QZysOwT5sBfD1w6buX/JRuIkI+WNjktechUqLmNZsXmkzxBXcvABQjM OZN+dTM3yOEzkc2rvyaQFRVgkh1mnVGOvEQVz7tRYYEaIzvmww1i6aaN0p0jr8LK fvPcU4qjXoWusee1irKRMH49Dkk/MRSZuJWDpHbXt1w0BoOJlzzoCP15Dtcw== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-type:content-type:date:date :feedback-id:feedback-id:from:from:in-reply-to:in-reply-to :message-id:mime-version:references:reply-to:subject:subject:to :to:x-me-proxy:x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s= fm3; t=1707162810; x=1707249210; bh=FTUpOD1eQey+VBm7DSfOa+IxppJr dWsNO9AahUA3Xtw=; b=WtkroYmVKz+5BU6IfHfw1JCJCdjFbZqob8I5Jl0fFQtU IWOoaFc6GcXI+ktezUu/7oKnFh/d38vmS8kna/FCFQJkqQqtvxapkBaV5F+YdO/y zTiPwmi47hpU8KXrsmtNRI2SjLfsdNm/Gi+jbKxt4zMyEqrFI8jt63g0alqJLY+p DvxgbMIlrj2Q0HIUOPPj5bGP5132M3JSfA9+R72xGKuVV+5hQDOnV6P9WTbCrpHK hiYkFZer5qPwIXILv2McenBy1X/kZmrFZKvsvuq4m+/Gg41WRzLrBoI7hPRwafI8 nZ4824vVKMBr//bpYmNjuQpBNXnzTkywFq8J4MQ3gg== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvkedrfedvuddgfeekucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne cujfgurheptgfkffggfgfuvfhfhfgjsegrtderredtvdejnecuhfhrohhmpeetughrihgr nhcumfhlrghvvghruceorggurhhirghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtghomh eqnecuggftrfgrthhtvghrnhepheevudfgfeeifefhheejleevgefglefgheeijeffueeg feejfedtieffhfffkeelnecuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehmrg hilhhfrhhomheprggurhhirghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtghomh X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Mon, 5 Feb 2024 14:53:29 -0500 (EST) Content-Type: multipart/alternative; boundary="------------V4u3B0yXUkRLvH4Mf1OB5pQR" Message-ID: <8f2cc3a4-79df-4e82-9a5f-85972b2ab56f@aklaver.com> Date: Mon, 5 Feb 2024 11:53:27 -0800 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Improving pg_dump performance when handling large numbers of LOBs To: Ron Johnson , "pgsql-generallists.postgresql.org" References: Content-Language: en-US From: Adrian Klaver In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------V4u3B0yXUkRLvH4Mf1OB5pQR Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 2/5/24 11:35 AM, Ron Johnson wrote: > On Mon, Feb 5, 2024 at 2:01 PM Wyatt Tellis > wrote: > > Hi, > > We've inherited a series of legacy PG 12 clusters that each > contain a database that we need to migrate to a PG 15 cluster. > Each database contains about 150 million large objects totaling > about 250GB. > > > 250*10^9 / (150*10^6) = 1667 bytes.  That's *tiny*. > > Am I misunderstanding you? I think it less about the size of the individual objects then the number (150 million) of them. AFAIK that can't be handled by COPY, therefore they have to be transferred individually. -- Adrian Klaver adrian.klaver@aklaver.com --------------V4u3B0yXUkRLvH4Mf1OB5pQR Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit


On 2/5/24 11:35 AM, Ron Johnson wrote:
On Mon, Feb 5, 2024 at 2:01 PM Wyatt Tellis <wyatt.tellis@gmail.com> wrote:
Hi,

We've inherited a series of legacy PG 12 clusters that each contain a database that we need to migrate to a PG 15 cluster. Each database contains about 150 million large objects totaling about 250GB.

250*10^9 / (150*10^6) = 1667 bytes.  That's tiny.

Am I misunderstanding you?


I think it less about the size of the individual objects then the number (150 million) of them.

AFAIK that can't be handled by COPY, therefore they have to be transferred individually.

-- 
Adrian Klaver
adrian.klaver@aklaver.com
--------------V4u3B0yXUkRLvH4Mf1OB5pQR--