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 1tu5iK-003Vix-ME for pgsql-performance@arkaria.postgresql.org; Mon, 17 Mar 2025 08:20:49 +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 1tu5iJ-008XoM-EA for pgsql-performance@arkaria.postgresql.org; Mon, 17 Mar 2025 08:20:47 +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 ) id 1tstij-00AowW-GB for pgsql-performance@lists.postgresql.org; Fri, 14 Mar 2025 01:20:18 +0000 Received: from sonic312-22.consmr.mail.bf2.yahoo.com ([74.6.128.84]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tstie-002kZh-2o for pgsql-performance@postgresql.org; Fri, 14 Mar 2025 01:20:17 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ymail.com; s=s2048; t=1741915211; bh=61nMYFWyDVaEhnn+hbtkFAi1VY3InrKCf7yscEznV5o=; h=From:To:Cc:References:In-Reply-To:Subject:Date:From:Subject:Reply-To; b=Xd995x6N+FnC9DyoPhEgJFghbt/vQjqDsFooJKZbDGWBasSxsfe7Yz1mXTDU+ocLNt2xJgwsYcGxvqMtRYaX35Fa47N9g/M9xZyun1+q4WzdAM0ZshLzGtdxUdY4YXeA6iEMg4gpMFj+r67d16N5g0OQ8L0Nby3in5U/8VOrf5zcj9TD36e0qavD+11HWB9GZ6Q2xfCOkcQV14Jh/+kFi5Ay1fYkCHVOTpETrqQ28FDJehaoAwrxbfTD2g8iLw18wXzg7MESGNw+d/HmCW18Tnc1uYtOak+5ukyCslqev6sEZBfnUPSsjoVc20v0U1TmPs6wu9RN+U+Itd0ulo5qSw== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1741915211; bh=kDimKpnVEtNx/ydzmJRZA/Nv0fDp2jvEe/nPPO0KwmG=; h=X-Sonic-MF:From:To:Subject:Date:From:Subject; b=UEN6/C8u35ha3ZDd2sPitprodxv2Qs3SwEBFSu/Rc+ZIc9dXwFx1oh1Zdvnh2DhlN5Y3vlqa7+0OpGSMtnlcdP5hhG3D1if5YxzRpoS/LILuVNwF6Z+/dTBkf1c629y0UQrQyemSrm6OSjFQ/UBSj2II4LGyifQnkJgn3xbTf4S4iDSmC26a9nUHzGNkqZyJAGLDEKZpE7zwKbywyF2WSW8x/NKO/xR5hDN9VpuTptdMN9VtcUuBK861QvoPpHMh8JHQb+oUs39pR/z77vq2+G+Yx8/j53uCuZalc0VJgPaKvuTLk61tIBUr4rz38YWR5AwnNaS9Gar7Y46mwVrHhQ== X-YMail-OSG: _.wSiQUVM1lmRI4SzmZR7iQR8r.lNGoxRmuuFQOBt.7R.vd5iPgX7C5B9lt4Kyy Kr_K4wLYXwi8ADlYsHt.gUp96VUxC914Oqm9t1Zywlu6PBDqGi_8R.CIjUzGtE66L74L10v7G4pI 9s2UrXzZupFhLItSC1BW_ThptviySVw3l5TgMiIedZltRb78QEHMywgT3Jsscsnx7ajuH0mi1APe aNFl9wmMKvov6Rsi.0ken2ewvb1YK75lr20kYDopu4f0MYeX4rQabCMcDfAtojOl2qOZ0lIhbL_M ElhcccGPPvRC3BnpUd2R8GEJyH.oPWKGoStG7F7pCwThegxKs0HwqkKCKuj2cshYylNlVs_519R6 7.cEtY9lks3vxff_KOhgT0UFtbbJLhzYJIclUGg8kdj87Pl9emrgvFdhff5.eLMOZ_JZS5EL.wm6 F2GVlvSmtaLA605S4RdBa2N3kIKi2Fm9zc83sOw93PmnI8YyfxFHGinFCbiGddTBb3Jlb4cTBt0X Wz4AN0LLVGf_pEVyWcoL_9BVJQ60_6pmJmzl1lxJOvzTWERqh0WTGwNuEjo4odPpO6wd7wnf2REs w_IXqnAQRm8q.weKO15n8ZKyRJ7rS21sip64hjlatHDwAmRC1EoSqV4XxrdsJcEnuy_s4Eh9vSsb kKr_mduRIsT3OgkPtQkBSNBa5wC5v0XVxo6HyRAH8Rid36C3wczRXIQosdD9YmRBe3U9we7wkaKp 10qbSBjTosPpMocLT63f2H93VUmLy9yTxmB6RTDVLm3Ixibgep5jBRpLuxrvokuZADvfHJSWtV4b OTMDchsDhlLoZA9DszFLmaoFLqiLLwfBk1Vd1BpifQ_QIrZahJjhdyWaiTQMo.9Hyx7F0RXMF.aH odqRjhzzUfji8fgaxIuDf.q_.Tl3xoQ01u79pvRVfljEH4OrR58_62fvoCbN_jpP6HGvQIoBWZMi qKKTXoHr_nUHh1Oeb4QvOzOXrqeNjNtuMyaUWjIzVjYgAePlhMGCOhsqdFO5ecJLm4bu_kJ.IgL3 bKYtvm2R1VAjeNKHUD4DRcAQ3bSK9csJrKTruLhJhf43ZeOxJifdtrz_8ydyA_KUxuFR3wnohokT IzIHoNU0TdWc5dlQmfo.Z0yxYqwinmJoQyNJl1WdtLRTFPkgg9LvKlfXZE0bFbR7M6kaks817hX5 qbN3j4lW6NLZ3GgTf3kqJivhm_8WCey3I36iNcmx7XiEQbr03QrVcXKOmUhNPJCurbUuoag6BM_s QbiE3kkboj5uNIlJu9AG7Nf_QVzwRMU9DLTj6hj.yPlVACIxBNlt1Tm4mx8MOCiZKBRfTSsCloSV Jc_HBtQhtVKUmb6IJDAA0nVON.2kwhYHbAP48ndKeGK8WtejWpZH9FDwJV.Z5nUFHy6eMuydwkp8 f5g23hA_8kCPkLIaKw7_4B0qygfgjHQu4LHHAKEzroRCKcjdKSpvJQcEsdtjGidTkjTQmQ2VaQOS arJZc90ZIOurVRS.NBf7mdRb2stkStCkqq1hntDwgBI_lwnyrWffU2rQup.kdCgM6xqslPM5o49Y vwecD.oY4sJcyRxSRBeXLvVHK00KXeF.QoJ8pNyrdfxfpSpSJxe2SJ4QcGgaFUi7Lt7kHRiG.5m3 3dwARqErCDnxK5uniYHPY6yxf9wpaVjag1PuEIil6IuUPecSZMOh_OJ9SyBTfX6D6TooLALl1zIM 71LBjnUM14N5bmOl4Rn.L6ExlmR0UenOpouVSGLDI0oKne1d7.33RnepXQGJ6FSJHneYCE3J1Fa1 d33RzwAWhe5QH_ou4YnqkB9BO2A7htnx_3xkbXDubBNgKfQc2IDP.rNAcN_T2NuibQFSQLQfp2Jz xG3PXX3A3jU8HQxbw3iMrFqvbl3LY862SK5.kGHwC8chBruSFFRlby66_4sM7tybQWHWpZxR.cr. QsMPmx2vaV4ubAQsMbR1gAsJJDLB7bP7O4QNfxVeZdDMhkjf501FIsWyPzhzJ1BY7sYWS0N_E2y0 aDoz3fsP4Rk_j7quzPL3gbYwuJ3vqN1rz_DHZSNIKpipwVnclwAarZh6Qk1obrqg3RNN1UBO6lMy vvLQOGnYE_4mqGTPQVWJH6JE5TIKiOdxqCNl.6YaFMd4TxmiYiuSuFks12A6V9rN5N5_RnFBODtG UdlgP0.2VKmWWcHdH99A6ttKHWA5K0qMYHtrRSH.YVfYv5OYA8Ybo960VG9MTqU65yWRZmnGx5Cd Rej7g39vBcu_KnNICy3AQHZjWPHMY4DKlQ3m8ooLTFhHRAl__i18g3Gj3Lw-- X-Sonic-MF: X-Sonic-ID: 3054cf86-6691-4000-a7dc-3f50c405aad4 Received: from sonic.gate.mail.ne1.yahoo.com by sonic312.consmr.mail.bf2.yahoo.com with HTTP; Fri, 14 Mar 2025 01:20:11 +0000 Received: by hermes--production-gq1-7d5f4447dd-8btvg (Yahoo Inc. Hermes SMTP Server) with ESMTPA ID 6e1c7bd1a594ccd123c3504bb5caa1ef; Fri, 14 Mar 2025 01:20:08 +0000 (UTC) From: To: "'Renan Alves Fonseca'" Cc: References: <008701db93cd$2eb404d0$8c1c0e70$.ref@ymail.com> <008701db93cd$2eb404d0$8c1c0e70$@ymail.com> In-Reply-To: Subject: RE: Bulk DML performance Date: Fri, 14 Mar 2025 09:20:05 +0800 Message-ID: <00cb01db947f$39bc49c0$ad34dd40$@ymail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00CC_01DB94C2.47DF89C0" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQCh7jmKC8Qb7CsNFahmY0MRK48+bwLkLSPcAetuBc+1vqM4wA== Content-Language: en-au Content-Length: 14456 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_00CC_01DB94C2.47DF89C0 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Thanks Renan! Reducing the fill factor has improved my update = performance and I am now seeing the same time for updates as inserts. =20 I look forward to any advancements PostgreSQL may make in the future to = improve the performance of bulk DML operations. It would be amazing if = they could be parallelized in the future. =20 Best, Bill =20 From: Renan Alves Fonseca =20 Sent: Friday, 14 March 2025 5:25 AM To: bill.poole@ymail.com Cc: pgsql-performance@postgresql.org Subject: Re: Bulk DML performance =20 Hello, Regarding the additional time for UPDATE, you can try the following: =20 CREATE TABLE test3 ( id bigint PRIMARY KEY, =20 text1 text ) WITH (fillfactor=3D30); =20 See: https://www.postgresql.org/docs/17/storage-hot.html My local test gives me almost the same time for INSERT (first insert) = and UPDATES (following upserts). Regarding the overall problem, there is always room for improvement. I = did a quick test with partitions, and I found out that Postgres will not = parallelize the upserts for us. One solution could be to partition the = records at the application level, creating one connection per partition. = On the DB side, the partitions can be implemented as standard tables = (using a union view on top of them) or actual partitions of a main = table. However, this solution does not strictly respect the "one single = transaction'"constraint... =20 Regards, Renan Fonseca =20 Em qui., 13 de mar. de 2025 =C3=A0s 08:40, > escreveu: Hello! I=E2=80=99m building a system that needs to insert/update batches = of millions of rows (using INSERT .. ON CONFLICT (=E2=80=A6) DO UPDATE) = in a single database transaction, where each row is about 1.5 kB. The = system produces about 3 million rows (about 4.5 GB) of data in about 5 = seconds, but PostgreSQL takes about 35 seconds to insert that data and = about 55 seconds to update that data. This is both on my local dev = machine as well as on a large AWS Aurora PostgreSQL instance = (db.r8g.16xlarge with 64 vCPUs, 512 GB RAM and 30 Gbps). =20 The following INSERT .. ON CONFLICT (=E2=80=A6) DO UPDATE statement = inserts/updates 3 million rows with only 9 bytes per row and takes about = 8 seconds on first run (to insert the rows) and about 14 seconds on = subsequent runs (to update the rows), but is only inserting 27 MB of = data (3 million rows with 9 bytes per row); although after the first = run, SELECT pg_size_pretty(pg_total_relation_size('test')) reports the = table size as 191 MB and after the second run reports the table size as = 382 MB (adding another 191 MB). =20 CREATE TABLE test ( id bigint PRIMARY KEY, text1 text ); =20 INSERT INTO test (id, text1) SELECT generate_series, 'x' FROM generate_series(1, 3000000) ON CONFLICT (id) DO UPDATE SET text1 =3D 'x'; =20 If PostgreSQL is writing 191 MB on the first run and 382 MB on each = subsequent run, then PostgreSQL is only writing about 28 MB/s. Although = PostgreSQL is also able to write about 4.5 GB in about 35 seconds (as = stated above), which is about 128 MB/s, so it seems the performance = constraint depends on the number of rows inserted more than the size of = each row. =20 Furthermore, deleting the rows takes about 18 seconds to perform (about = 4 seconds longer than the time taken to update the rows): =20 DELETE FROM test WHERE id in ( SELECT * FROM generate_series(1, 3000000) ) =20 It seems like it should be possible to do better than this on modern = hardware, but I don=E2=80=99t have enough knowledge of the inner = workings of PostgreSQL to know whether my instinct is correct on this, = so I thought I=E2=80=99d raise the question with the experts. =20 Thanks! Bill ------=_NextPart_000_00CC_01DB94C2.47DF89C0 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

Thanks Renan! = Reducing the fill factor has improved my update performance and I am now = seeing the same time for updates as inserts.

 

I look forward to = any advancements PostgreSQL may make in the future to improve the = performance of bulk DML operations. It would be amazing if they could be = parallelized in the future.

 

Best,

Bill

 

From:<= /b> Renan Alves = Fonseca <renanfonseca@gmail.com>
Sent: Friday, 14 March = 2025 5:25 AM
To: bill.poole@ymail.com
Cc: = pgsql-performance@postgresql.org
Subject: Re: Bulk DML = performance

 

Hello,

Regarding the additional time for UPDATE, you can try = the following:

 

CREATE TABLE test3 (
  id bigint PRIMARY KEY, =            
  text1 text
) WITH = (fillfactor=3D30);

 

My local test gives me = almost the same time for INSERT (first insert) and UPDATES (following = upserts).

Regarding the = overall problem, there is always room for improvement. I did a = quick test with partitions, and I found out that Postgres will not = parallelize the upserts for us. One solution could be to partition the = records at the application level, creating one connection per partition. = On the DB side, the partitions can be implemented as standard tables = (using a union view on top of them) or actual partitions of a main = table. However, this solution does not strictly respect the "one = single transaction'"constraint...

 

Regards,

Renan Fonseca

 

Em = qui., 13 de mar. de 2025 =C3=A0s 08:40, <bill.poole@ymail.com> = escreveu:

Hello! = I=E2=80=99m building a system that needs to insert/update batches of = millions of rows (using INSERT .. ON CONFLICT (=E2=80=A6) DO UPDATE) in = a single database transaction, where each row is about 1.5 kB. The = system produces about 3 million rows (about 4.5 GB) of data in about 5 = seconds, but PostgreSQL takes about 35 seconds to insert that data and = about 55 seconds to update that data. This is both on my local dev = machine as well as on a large AWS Aurora PostgreSQL instance = (db.r8g.16xlarge with 64 vCPUs, 512 GB RAM and 30 = Gbps).

 <= /o:p>

The = following INSERT .. ON CONFLICT (=E2=80=A6) DO UPDATE statement = inserts/updates 3 million rows with only 9 bytes per row and takes about = 8 seconds on first run (to insert the rows) and about 14 seconds on = subsequent runs (to update the rows), but is only inserting 27 MB of = data (3 million rows with 9 bytes per row); although after the first = run, SELECT pg_size_pretty(pg_total_relation_size('test')) reports the = table size as 191 MB and after the second run reports the table size as = 382 MB (adding another 191 MB).

 <= /o:p>

CREATE = TABLE test (

  id = bigint PRIMARY KEY,

  = text1 text

);

 <= /o:p>

INSERT INTO = test (id, text1)

SELECT = generate_series, 'x'

FROM = generate_series(1, 3000000)

ON CONFLICT = (id) DO UPDATE

SET text1 = =3D 'x';

 <= /o:p>

If = PostgreSQL is writing 191 MB on the first run and 382 MB on each = subsequent run, then PostgreSQL is only writing about 28 MB/s. Although = PostgreSQL is also able to write about 4.5 GB in about 35 seconds (as = stated above), which is about 128 MB/s, so it seems the performance = constraint depends on the number of rows inserted more than the size of = each row.

 <= /o:p>

Furthermore,= deleting the rows takes about 18 seconds to perform (about 4 seconds = longer than the time taken to update the rows):

 <= /o:p>

DELETE FROM = test

WHERE id in = (

  = SELECT * FROM generate_series(1, 3000000)

)=

 <= /o:p>

It seems = like it should be possible to do better than this on modern hardware, = but I don=E2=80=99t have enough knowledge of the inner workings of = PostgreSQL to know whether my instinct is correct on this, so I thought = I=E2=80=99d raise the question with the experts.

 <= /o:p>

Thanks!=

Bill

------=_NextPart_000_00CC_01DB94C2.47DF89C0--