MIME-Version: 1.0
References: <aZ3kYQnF9_u6sUQp@nathan>
 <CAN55FZ3+NYF1TkKyNtpRQuLiaauSYk9G5tA+fpruOA4-14Y_ZA@mail.gmail.com>
 <aaXrGSyq4u2d9qEC@nathan>
 <CAN55FZ2DNaKCK3Kf_kHizb2pAbQvULeDYtzaiz97B_xz7YbrkQ@mail.gmail.com>
 <91acb778-42c4-44ef-8888-f18ad9b12a5b@dunslane.net>
 <CAKWEB6r0CrN-a2P=2ey3EK7p1MxsbQx2C8=hpNGfxLxnRaX66Q@mail.gmail.com>
 <CAN55FZ1BYbdt97RLkd-vAVZ5EKiX=KLkhJ_S-4vgvNJRHvQZ6w@mail.gmail.com>
 <CAKWEB6pmXvv3sbQBNLAR_B=8wzX8rn4VFsJ4WwmNbuJ2etSwDQ@mail.gmail.com>
 <CAN55FZ25HV0FNziFR8xBpQpHkNPS48w8yEXCc91W+=0c0jr+KA@mail.gmail.com>
 <CAKWEB6rnMKYGSt=t9=pL2kKUefsAdzKfjtwdqW_acv1+vMTKVA@mail.gmail.com>
 <aatfiTMLvLok2cUc@nathan>
 <CA+K2RunT=1P7_E4jrZMogDiPunNCOGw9P_UHLaJRJk=5_odKmA@mail.gmail.com>
 <CAN55FZ1k2sOZn1qkDDVTX8Z1t99ZUkywBhdp_43euDHaAi5bRA@mail.gmail.com>
 <CAKWEB6pJ-5b7QUmVtG12hC0bQ82OvDv4XsidAcnngN36q28qTQ@mail.gmail.com>
 <CAN55FZ1pM3PgKfAuTT5YmG4enCoh93bOjxNi95nqKgwbHmh3hg@mail.gmail.com>
In-Reply-To: 
 <CAN55FZ1pM3PgKfAuTT5YmG4enCoh93bOjxNi95nqKgwbHmh3hg@mail.gmail.com>
From: Manni Wood <manni.wood@enterprisedb.com>
Date: Mon, 9 Mar 2026 08:31:39 -0500
Message-ID: 
 <CAKWEB6pWU+2mUa41t0Tb+3XmKyDuGSS=8XaDirumjSc9=d8WJQ@mail.gmail.com>
Subject: Re: Speed up COPY FROM text/CSV parsing using SIMD
To: Nazir Bilal Yavuz <byavuz81@gmail.com>
Cc: KAZAR Ayoub <ma_kazar@esi.dz>, Nathan Bossart <nathandbossart@gmail.com>,
	Andrew Dunstan <andrew@dunslane.net>, Neil Conway <neil.conway@gmail.com>,
	Shinya Kato <shinya11.kato@gmail.com>,
	PostgreSQL-development <pgsql-hackers@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000db38af064c976c04"
Archived-At: 
 <https://www.postgresql.org/message-id/CAKWEB6pWU%2B2mUa41t0Tb%2B3XmKyDuGSS%3D8XaDirumjSc9%3Dd8WJQ%40mail.gmail.com>
Precedence: bulk

--000000000000db38af064c976c04
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Mon, Mar 9, 2026 at 3:10=E2=80=AFAM Nazir Bilal Yavuz <byavuz81@gmail.co=
m> wrote:

> Hi,
>
> On Sun, 8 Mar 2026 at 22:45, Manni Wood <manni.wood@enterprisedb.com>
> wrote:
> >
> > As requested, here are some numbers based on the latest master but with
> the copy code inlining excised (`git revert
> dc592a41557b072178f1798700bf9c69cd8e4235`), compared to master with copy
> code inlining left in place and the v11 patch applied.
> > Both results have lz4 compression in place.
>
> Thank you for the benchmark!
>
> > I have not run numbers without lz4. I assume I could use the two
> postgres instances that I have compiled with lz4, but just set
> `default_toast_compression =3D pglz` in postgesql.conf for both instances=
.
> Let me know if that is a mistaken assumption on my part.
>
> I am a bit confused. Are you asking that for the current benchmark you
> shared or future benchmarks? I assume your current benchmark has
> 'default_toast_compression =3D lz4' because your benchmark results are
> very similar to my benchmark with 'default_toast_compression =3D lz4'
> but I just wanted to make sure.
>
> What you said about editing postgresql.conf is correct but you need to
> make this change before creating the Postgres instance with 'pg_ctl
> ... start' command, otherwise it won't have an effect and you need to
> restart the instance to see the effect. Also, If you want to benchmark
> without lz4 change, you can just use the "SET
> default_toast_compression to 'pglz';" command in psql, then you don't
> need to edit postgresql.conf. Please note that this will affect only
> the psql instance you typed the command. To make things easier, you
> can run the 'SHOW default_toast_compression;' command to see the
> current value of 'default_toast_compression'.
>
> --
> Regards,
> Nazir Bilal Yavuz
> Microsoft
>

Hello, Nazir!

I was being too brief.

The benchmarks I shared were absolutely with lz4 compiled in
and 'default_toast_compression =3D lz4' set in postgresql.conf for every
postgres instance I tested with. (Furthermore, I ran `show
default_toast_compression` via `psql` on each postgres instance to be
sure 'default_toast_compression =3D lz4' was really set!)

Also, all were compiled using meson using `debugoptimized` which results in
`-g -O2`.

So those are the benchmarks that I shared.

OK, so my final question, hopefully clarified: If I run additional
benchmarks where pglz is used for default_toast_compression, is it enough
to use the instances I have already compiled with lz4 in them, but
with 'default_toast_compression =3D pglz` explicitly set in postgresql.conf
in a brand new data dir created by initdb? (In other words, existing data
dir deleted, then initdb run to make a new data dir, then postgresql.conf
edited to ensure 'default_toast_compression =3D pglz` explicitly set, then
and only then starting up the cluster for the first time... and finally
verifying via `show default_toast_compression` for good measure.)

Or should I re-compile with the lz4-is-now-the-default commit completely
excised?

Thanks so much!

-Manni

--
-- Manni Wood EDB: https://www.enterprisedb.com

--000000000000db38af064c976c04
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote g=
mail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Mon, Mar 9, =
2026 at 3:10=E2=80=AFAM Nazir Bilal Yavuz &lt;<a href=3D"mailto:byavuz81@gm=
ail.com">byavuz81@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gm=
ail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,=
204,204);padding-left:1ex">Hi,<br>
<br>
On Sun, 8 Mar 2026 at 22:45, Manni Wood &lt;<a href=3D"mailto:manni.wood@en=
terprisedb.com" target=3D"_blank">manni.wood@enterprisedb.com</a>&gt; wrote=
:<br>
&gt;<br>
&gt; As requested, here are some numbers based on the latest master but wit=
h the copy code inlining excised (`git revert dc592a41557b072178f1798700bf9=
c69cd8e4235`), compared to master with copy code inlining left in place and=
 the v11 patch applied.<br>
&gt; Both results have lz4 compression in place.<br>
<br>
Thank you for the benchmark!<br>
<br>
&gt; I have not run numbers without lz4. I assume I could use the two postg=
res instances that I have compiled with lz4, but just set `default_toast_co=
mpression =3D pglz` in postgesql.conf for both instances. Let me know if th=
at is a mistaken assumption on my part.<br>
<br>
I am a bit confused. Are you asking that for the current benchmark you<br>
shared or future benchmarks? I assume your current benchmark has<br>
&#39;default_toast_compression =3D lz4&#39; because your benchmark results =
are<br>
very similar to my benchmark with &#39;default_toast_compression =3D lz4=
9;<br>
but I just wanted to make sure.<br>
<br>
What you said about editing postgresql.conf is correct but you need to<br>
make this change before creating the Postgres instance with &#39;pg_ctl<br>
... start&#39; command, otherwise it won&#39;t have an effect and you need =
to<br>
restart the instance to see the effect. Also, If you want to benchmark<br>
without lz4 change, you can just use the &quot;SET<br>
default_toast_compression to &#39;pglz&#39;;&quot; command in psql, then yo=
u don&#39;t<br>
need to edit postgresql.conf. Please note that this will affect only<br>
the psql instance you typed the command. To make things easier, you<br>
can run the &#39;SHOW default_toast_compression;&#39; command to see the<br=
>
current value of &#39;default_toast_compression&#39;.<br>
<br>
-- <br>
Regards,<br>
Nazir Bilal Yavuz<br>
Microsoft<br>
</blockquote></div><div><br clear=3D"all"></div><div>Hello, Nazir!</div><di=
v><br></div><div>I was being too brief.</div><div><br></div><div>The benchm=
arks I shared were absolutely with lz4 compiled in and=C2=A0&#39;default_to=
ast_compression =3D lz4&#39; set in postgresql.conf for every postgres inst=
ance I tested with. (Furthermore, I ran `show default_toast_compression` vi=
a `psql` on each postgres instance to be sure=C2=A0&#39;default_toast_compr=
ession =3D lz4&#39; was really set!)</div><div><br></div><div>Also, all wer=
e compiled using meson using `debugoptimized` which results in `-g -O2`.</d=
iv><div><br></div><div>So those are the benchmarks that I shared.</div><div=
><br></div><div>OK, so my final question, hopefully clarified: If I run add=
itional benchmarks where pglz is used for default_toast_compression, is it =
enough to use the instances I have already compiled with lz4 in them, but w=
ith=C2=A0&#39;default_toast_compression =3D pglz` explicitly set in postgre=
sql.conf in a brand new data dir created by initdb? (In other words, existi=
ng data dir deleted, then initdb run to make a new data dir, then postgresq=
l.conf edited to ensure &#39;default_toast_compression =3D pglz` explicitly=
 set, then and only then starting up the cluster for the first time... and =
finally verifying via `show default_toast_compression` for good measure.)</=
div><div><br></div><div>Or should I re-compile with the lz4-is-now-the-defa=
ult commit completely excised?</div><div><br></div><div>Thanks so much!</di=
v><div><br></div><div>-Manni</div><div><br></div><div>--</div><div dir=3D"l=
tr" class=3D"gmail_signature"><div dir=3D"ltr"><span style=3D"color:rgb(29,=
28,29);font-family:Monaco,Menlo,Consolas,&quot;Courier New&quot;,monospace;=
font-size:12px;background-color:rgba(29,28,29,0.04)">--
Manni Wood
EDB: </span><a href=3D"https://www.enterprisedb.com/" rel=3D"noopener noref=
errer" style=3D"font-family:Monaco,Menlo,Consolas,&quot;Courier New&quot;,m=
onospace;font-size:12px;background-color:rgba(29,28,29,0.04)" target=3D"_bl=
ank">https://www.enterprisedb.com</a></div></div></div>

--000000000000db38af064c976c04--