From: <bill.poole@ymail.com>
To: <pgsql-performance@postgresql.org>
Subject: Bulk DML performance
Date: Thu, 13 Mar 2025 12:05:34 +0800
Message-ID: <008701db93cd$2eb404d0$8c1c0e70$@ymail.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0088_01DB9410.3CD744D0"
Thread-Index: AduTwtnms/JVVmIRR2uvT32rK3gn8w==
Content-Language: en-au
References: <008701db93cd$2eb404d0$8c1c0e70$.ref@ymail.com>
Content-Length: 7157
Archived-At: 
 <https://www.postgresql.org/message-id/008701db93cd%242eb404d0%248c1c0e70%24%40ymail.com>
Precedence: bulk

This is a multipart message in MIME format.

------=_NextPart_000_0088_01DB9410.3CD744D0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

Hello! I'm building a system that needs to insert/update batches of millions
of rows (using INSERT .. ON CONFLICT (.) 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).

 
The following INSERT .. ON CONFLICT (.) 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).

 
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 = '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't have enough knowledge of the inner workings of
PostgreSQL to know whether my instinct is correct on this, so I thought I'd
raise the question with the experts.

 
Thanks!

Bill


------=_NextPart_000_0088_01DB9410.3CD744D0
Content-Type: text/html;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" =
xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" =
xmlns=3D"http://www.w3.org/TR/REC-html40"><head><meta =
http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dus-ascii"><meta name=3DGenerator content=3D"Microsoft Word 15 =
(filtered medium)"><style><!--
/* Font Definitions */
@font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
	{font-family:Aptos;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0cm;
	font-size:11.0pt;
	font-family:"Aptos",sans-serif;
	mso-ligatures:standardcontextual;
	mso-fareast-language:EN-US;}
span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:"Aptos",sans-serif;
	color:windowtext;}
.MsoChpDefault
	{mso-style-type:export-only;
	font-size:11.0pt;
	mso-fareast-language:EN-US;}
@page WordSection1
	{size:612.0pt 792.0pt;
	margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
	{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]--></head><body lang=3DEN-AU =
link=3D"#467886" vlink=3D"#96607D" style=3D'word-wrap:break-word'><div =
class=3DWordSection1><p class=3DMsoNormal>Hello! I&#8217;m building a =
system that needs to insert/update batches of millions of rows (using =
INSERT .. ON CONFLICT (&#8230;) 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></o:p></p><p =
class=3DMsoNormal><o:p>&nbsp;</o:p></p><p class=3DMsoNormal>The =
following INSERT .. ON CONFLICT (&#8230;) 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></o:p></p><p =
class=3DMsoNormal><o:p>&nbsp;</o:p></p><p class=3DMsoNormal>CREATE TABLE =
test (<o:p></o:p></p><p class=3DMsoNormal>&nbsp; id bigint PRIMARY =
KEY,<o:p></o:p></p><p class=3DMsoNormal>&nbsp; text1 =
text<o:p></o:p></p><p class=3DMsoNormal>);<o:p></o:p></p><p =
class=3DMsoNormal><o:p>&nbsp;</o:p></p><p class=3DMsoNormal>INSERT INTO =
test (id, text1)<o:p></o:p></p><p class=3DMsoNormal>SELECT =
generate_series, 'x'<o:p></o:p></p><p class=3DMsoNormal>FROM =
generate_series(1, 3000000)<o:p></o:p></p><p class=3DMsoNormal>ON =
CONFLICT (id) DO UPDATE<o:p></o:p></p><p class=3DMsoNormal>SET text1 =3D =
'x';<o:p></o:p></p><p class=3DMsoNormal><o:p>&nbsp;</o:p></p><p =
class=3DMsoNormal>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></o:p></p><p =
class=3DMsoNormal><o:p>&nbsp;</o:p></p><p class=3DMsoNormal>Furthermore, =
deleting the rows takes about 18 seconds to perform (about 4 seconds =
longer than the time taken to update the rows):<o:p></o:p></p><p =
class=3DMsoNormal><o:p>&nbsp;</o:p></p><p class=3DMsoNormal>DELETE FROM =
test<o:p></o:p></p><p class=3DMsoNormal>WHERE id in (<o:p></o:p></p><p =
class=3DMsoNormal>&nbsp; SELECT * FROM generate_series(1, =
3000000)<o:p></o:p></p><p class=3DMsoNormal>)<o:p></o:p></p><p =
class=3DMsoNormal><o:p>&nbsp;</o:p></p><p class=3DMsoNormal>It seems =
like it should be possible to do better than this on modern hardware, =
but I don&#8217;t have enough knowledge of the inner workings of =
PostgreSQL to know whether my instinct is correct on this, so I thought =
I&#8217;d raise the question with the experts.<o:p></o:p></p><p =
class=3DMsoNormal><o:p>&nbsp;</o:p></p><p =
class=3DMsoNormal>Thanks!<o:p></o:p></p><p =
class=3DMsoNormal>Bill<o:p></o:p></p></div></body></html>
------=_NextPart_000_0088_01DB9410.3CD744D0--