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 1tsq3U-005zwj-4M for pgsql-performance@arkaria.postgresql.org; Thu, 13 Mar 2025 21:25:28 +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 1tsq3S-0062ma-Dq for pgsql-performance@arkaria.postgresql.org; Thu, 13 Mar 2025 21:25:26 +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 1tsq3R-0062mL-Ut for pgsql-performance@lists.postgresql.org; Thu, 13 Mar 2025 21:25:26 +0000 Received: from mail-pj1-x1030.google.com ([2607:f8b0:4864:20::1030]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tsq3P-002gBm-2O for pgsql-performance@postgresql.org; Thu, 13 Mar 2025 21:25:25 +0000 Received: by mail-pj1-x1030.google.com with SMTP id 98e67ed59e1d1-2ff784dc055so2528284a91.1 for ; Thu, 13 Mar 2025 14:25:23 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1741901123; x=1742505923; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=wgrLqxzyAx0ju4Vz1EDVrUbrwt6D92qkaPtdfr+iFj4=; b=VuLz7QSQj3o4TDJSXDS63tVGVYcRJyCPdRLS7eYPhHc2EyXi/ihEa2GUwEfxg+d2JX T8FlhxfIRrJwXm/wu+lomb4RE8sGsffOzAiPvfkOwYMckMejYhKrzmdVivJ3ySfDJpNy QT67OOs6IZsqJ8e97to8bFwV8klQAYC1c4uHUwn3xQ1dBkoV2kGvEDK6lwRD0UCNvlxe YQlzAZoPZrTIsbhv5a0tUPIVBzXo0Q4TuFW+gqwxry8uoEkajscy+9Z7fN314bs51gpv v+Y73yJsAZbi6NA2gHtshXmktCzaQQh3AawRYTi3tiYaoNE7eC+g/15/fgwuYwyRPpvL ULCA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1741901123; x=1742505923; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=wgrLqxzyAx0ju4Vz1EDVrUbrwt6D92qkaPtdfr+iFj4=; b=StjSxeCZRg/D6tq1aC16FCXf1eOk0GzUkBfaqWWcSgRrGRmKQq6FEPw3iqtxoC653c /vxHOjPFUww3H/J84IDiK0YMI+YdhDikgh+gT7Le5Pn4NJ+xpgz1uiab3HCpcvRtR+YG zv7cBnxwg+6WDZklkUpA5Om2mrvjapvv0JXzPTtvVISgfeniv3Wg9tcpaQk49g8DsiIn H/n2+9fLjrfJPcNW1SIKrFA52mHBV/GkDKzEHTwvlB3r4ocFsfvkiMaPVJwmusGtkUxD 5qq1HOaTeiGDcwPsqFlWEoX2mV11xXhR2N+4DfPHBq5WtIx7hzwgUCkwsczmWD+N0pro 2+uA== X-Gm-Message-State: AOJu0YyZs4rncGYUKyLVhPgd0g6vBMa0/IVnP6QQke1rH4OdeTouqR3i 4VE+uI/iPjGcNGJ6g+U/dIggwaBcnwU8Po37P1LCZkzsG2eyIeGPcsCJFDMod8pTl8sm1Tc7KAW F4UQPo+mro+l/+jwVJzm089FI8lM= X-Gm-Gg: ASbGnct7e0/bvnY2JPheNCy9CeL2irvshOQWyJXTWTPirY3Q7spPcx2x7iTLk1NwcSb aTVG6g9Ulrd9pufgxoUYO9URDq4XjgsbqR14Sc6/Zas6p8kRjlXDwEhDUw5KAcwEcj/qULTMmdX nFlIvIGZTwzMSUf72RVf0yD4fvtg3D4jAVuwlUCyc= X-Google-Smtp-Source: AGHT+IFj2T8KH2x/Qlr0rwJW8a3T0zNIk82grxFVRyjzx0bDN/9dBCAS3e9eXXEzArnytYU8l7Du3syZ0rS89kkxISg= X-Received: by 2002:a17:90b:2f0b:b0:2ee:ee77:227c with SMTP id 98e67ed59e1d1-30151c9a3cfmr79947a91.3.1741901122592; Thu, 13 Mar 2025 14:25:22 -0700 (PDT) MIME-Version: 1.0 References: <008701db93cd$2eb404d0$8c1c0e70$.ref@ymail.com> <008701db93cd$2eb404d0$8c1c0e70$@ymail.com> In-Reply-To: <008701db93cd$2eb404d0$8c1c0e70$@ymail.com> From: Renan Alves Fonseca Date: Thu, 13 Mar 2025 22:25:10 +0100 X-Gm-Features: AQ5f1Jri-6ynd9daz90N9hhX-JdYyiMmiMt_hBldldwyP0nWP3tmeutQ1_aqL0c Message-ID: Subject: Re: Bulk DML performance To: bill.poole@ymail.com Cc: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary="000000000000a567df06303ff573" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000a567df06303ff573 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hello, Regarding the additional time for UPDATE, you can try the following: CREATE TABLE test3 ( id bigint PRIMARY KEY, text1 text ) WITH (fillfactor=3D30); 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... Regards, Renan Fonseca 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 second= s > 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). > > > > 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, SELE= CT > 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). > > > > CREATE TABLE test ( > > id bigint PRIMARY KEY, > > text1 text > > ); > > > > INSERT INTO test (id, text1) > > SELECT generate_series, 'x' > > FROM generate_series(1, 3000000) > > ON CONFLICT (id) DO UPDATE > > SET text1 =3D 'x'; > > > > 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. > > > > Furthermore, deleting the rows takes about 18 seconds to perform (about 4 > seconds longer than the time taken to update the rows): > > > > DELETE FROM test > > WHERE id in ( > > SELECT * FROM generate_series(1, 3000000) > > ) > > > > 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. > > > > Thanks! > > Bill > --000000000000a567df06303ff573 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hello,
Regarding the additional time for UPDATE, you c= an try the following:

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

My local tes= t gives me almost the same time for INSERT (first insert) and UPDATES (foll= owing upserts).

Regarding the overall problem, there is a= lways room for=C2=A0improvement. I did a quick test with partitions, and I = found out that Postgres will not parallelize the upserts for us. One soluti= on could be to partition the records at the application level, creating one= connection per partition. On the DB side, the partitions can be implemente= d as standard tables (using a union view on top of them) or actual partitio= ns of a main table. However, this solution does not strictly respect the &q= uot;one single=C2=A0transaction'"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 INSE= RT .. 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 (a= bout 4.5 GB) of data in about 5 seconds, but PostgreSQL takes about 35 seco= nds to insert that data and about 55 seconds to update that data. This is b= oth on my local dev machine as well as on a large AWS Aurora PostgreSQL ins= tance (db.r8g.16xlarge with 64 vCPUs, 512 GB RAM and 30 Gbps).

=C2=A0

= 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 w= ith 9 bytes per row); although after the first run, SELECT pg_size_pretty(p= g_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).

=C2=A0

CREATE TABLE test (

=C2=A0 id bigint PRIMARY KEY,

=C2= =A0 text1 text

);

=

=C2=A0

INSER= T INTO test (id, text1)

SELECT gene= rate_series, 'x'

FROM gener= ate_series(1, 3000000)

ON CONFLICT = (id) DO UPDATE

SET text1 =3D 'x= ';

=C2=A0

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 con= straint depends on the number of rows inserted more than the size of each r= ow.

=C2=A0

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

=C2=A0

DELETE FROM test

WHERE id i= n (

=C2=A0 SELECT * FROM generate_s= eries(1, 3000000)

)

=C2=A0

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 Postgre= SQL to know whether my instinct is correct on this, so I thought I=E2=80=99= d raise the question with the experts.

=C2=A0

Thanks!

Bill

--000000000000a567df06303ff573--