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 <pgsql-general-owner+archive@lists.postgresql.org>)
	id 1snLzq-002tfE-AC
	for pgsql-general@arkaria.postgresql.org; Sun, 08 Sep 2024 17:46:47 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.94.2)
	(envelope-from <pgsql-general-owner+archive@lists.postgresql.org>)
	id 1snLyr-00GyOw-2z
	for pgsql-general@arkaria.postgresql.org; Sun, 08 Sep 2024 17:45:45 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtps  (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <hjp-pgsql@hjp.at>)
	id 1snLyq-00GyOo-OV
	for pgsql-general@lists.postgresql.org; Sun, 08 Sep 2024 17:45:44 +0000
Received: from mail.hjp.at ([212.17.106.138] helo=rorschach.hjp.at)
	by magus.postgresql.org with esmtps  (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <hjp-pgsql@hjp.at>)
	id 1snLyl-000ArU-SM
	for pgsql-general@lists.postgresql.org; Sun, 08 Sep 2024 17:45:44 +0000
Received: by rorschach.hjp.at (Postfix, from userid 1000)
	id 5047095ED; Sun,  8 Sep 2024 19:45:39 +0200 (CEST)
Date: Sun, 8 Sep 2024 19:45:39 +0200
From: "Peter J. Holzer" <hjp-pgsql@hjp.at>
To: pgsql-general@lists.postgresql.org
Subject: Re: Faster data load
Message-ID: <20240908174539.teeanjwrthkjm4ti@hjp.at>
Mail-Followup-To: pgsql-general@lists.postgresql.org
References: <CAKna9VaVsDzfOfOGu1+grStp9BBHFMKrH5DCEbbtGcQUWJ74KQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="3qztsrp2qleffoxv"
Content-Disposition: inline
In-Reply-To: <CAKna9VaVsDzfOfOGu1+grStp9BBHFMKrH5DCEbbtGcQUWJ74KQ@mail.gmail.com>
List-Id: <pgsql-general.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-general@lists.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-general>
Archived-At: <https://www.postgresql.org/message-id/20240908174539.teeanjwrthkjm4ti%40hjp.at>
Precedence: bulk


--3qztsrp2qleffoxv
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On 2024-09-06 01:44:00 +0530, Lok P wrote:
> We are having a requirement to create approx 50 billion rows in a partiti=
on
> table(~1 billion rows per partition, 200+gb size daily partitions) for a
> performance test. We are currently using ' insert into <target table_part=
ition>
> select.. From <source_table_partition> or <some transformed query>;' meth=
od .
> We have dropped all indexes and constraints First and then doing the load.
> Still it's taking 2-3 hours to populate one partition.

That seems quite slow. Is the table very wide or does it have a large
number of indexes?

> Is there a faster way to achieve this?=A0
>=20
> Few teammate suggesting to use copy command and use file load instead, wh=
ich
> will be faster.

I doubt that.

I benchmarked several strategies for populating tables 5 years ago and
(for my test data and on our hardware at the time - YMMV) s simple
INSERT ... SELECT was more than twice as fast as 8 parallel COPY
operations (and about 8 times as fast as a single COPY).

Details will have changed since then (I should rerun that benchmark on
a current system), but I'd be surprised if COPY became that much faster
relative to INSERT ... SELECT.

        hp

--=20
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

--3qztsrp2qleffoxv
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmbd4rwACgkQ8g5IURL+
KF1uRg/+JYVAMbQw50UO39Z6E5QE6V3y3aXxSt2RHPpUisekAVLWfd9lkSPdldBc
rF/Cy5fx5IFWlONsTDv9uCtxIGmN2zhvYyaFbBaEBmqPgzACLjSOtY51tUMz9zb5
atc/e28mUDwgZuwUwF6813EAoQOozHcxMYlbIGHkSTqHbxuNClQcOZA00rWhJbaQ
DWCIe0/02OvFKCkVd1pMeoKkW8TW7ctx5zPkGVf22f13uKy9YWpMXiwmZs4W1wtQ
7OR8eA03J/p/YWNFgLcLzekNy1UN2EzHuXjR/ZW/1dUS59wDSW8HT+EQwc23gT1e
qNzisyUeyaSIKfz1zvXgE+R0uZNKuhpJtn0AJPRxLX25ocfe7LVIPbCtivyygkm0
AIPftuXWounzl59ocxUnbq/R+wWWjg7UfG6Ro6jdthgiEwpupecrQUkjJSVwAacB
2wgytB+zf5LwFmLMd4ZApsUfU3duvp4GEmv0/OkpvHcvhs3Z2UGKABBXURiCVWUK
PgHEwLXc+leEcUYDyTvmMu2Z4ErgoNZsvInuotWklg61K/1YyvFCQnUpvYSe5zgI
lH09AKjxAO/tHuoH7AKBjiLIKpZRNmIVX54D79ZCmLabTYgeq72F/zksrckUZGi1
MDVb5S7iIH59kV/j8o+eb/hpXdQHcRepfW/i8W7X19Z/g0kEtnw=
=GVWC
-----END PGP SIGNATURE-----

--3qztsrp2qleffoxv--