MIME-Version: 1.0
References: <CACgMzfxO7h+Bj4T8rjTV=trM3+Uw=jOAcUE+0Kimv4DSAVU1JA@mail.gmail.com>
 <CAHyXU0ynqbCHrw3XvofEzidHow4tx1FFGNB2FUR5-wj-EhLcNQ@mail.gmail.com>
 <CACgMzfwyMxq+qVFBMXzsMFFdBKJ5aRf_REX01KHL1mcVpRKwyA@mail.gmail.com> <9db5f4a38ad248accaba926ba75e10ed50e5ef1d.camel@cybertec.at>
In-Reply-To: <9db5f4a38ad248accaba926ba75e10ed50e5ef1d.camel@cybertec.at>
From: Merlin Moncure <mmoncure@gmail.com>
Date: Tue, 30 Sep 2025 09:13:06 -0600
Message-ID: <CAHyXU0ynctRCs73d6tk-=jMgfE1jrz=KK5LurvrGmMUHPQPDTw@mail.gmail.com>
Subject: Re: Downgrade pgsql 17 to pgsql 12 question
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: Ashish Mukherjee <ashish.mukherjee@gmail.com>, pgsql-general@postgresql.org
Content-Type: multipart/alternative; boundary="00000000000043a2c40640063137"
Archived-At: <https://www.postgresql.org/message-id/CAHyXU0ynctRCs73d6tk-%3DjMgfE1jrz%3DKK5LurvrGmMUHPQPDTw%40mail.gmail.com>
Precedence: bulk

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

On Tue, Sep 30, 2025 at 3:47=E2=80=AFAM Laurenz Albe <laurenz.albe@cybertec=
.at>
wrote:

> On Tue, 2025-09-30 at 13:53 +0530, Ashish Mukherjee wrote:
> > Now the consideration is to use some other encryption option for the
> > database which will work fine on pgsql 17. Cybertec's technology is
> > one route, the other is EDB. I am happy to hear experiences of folks
> > here with pgsql encryption options for v17 on large databases
> > (2.5T in our case).
>
> I will refrain from making a recommendation, but you should know that
> data-at-rest encryption will always incur a certain performance penalty.
>
> Whatever solution you choose, you should run performance tests.
>

Yeah.  This applies to database upgrades in general.

The lists have quite a bit of, I upgraded, and "query X that drives my
platform now is 100x slower".  I don't think this suggests that postgres is
getting worse; foundationally, it's mostly getting faster, but simply that
the planner changes how it responds to certain conditions.  Over time, I've
learned some painful lessons and try to write SQL that is less risky from a
performance standpoint.

Developers tend to optimize into fragile planner behaviors chasing
performance, sometimes without realizing it.  These kinds of issues are
almost always very fixable assuming you can modify the SQL or the thing
writing the SQL. The takeaway here is: test/verify before making major
upgrades, and bake in some recalibration time.  Adding encryption or other
major features strongly reinforces that need.

merlin

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

<div dir=3D"ltr"><div dir=3D"ltr">On Tue, Sep 30, 2025 at 3:47=E2=80=AFAM L=
aurenz Albe &lt;<a href=3D"mailto:laurenz.albe@cybertec.at">laurenz.albe@cy=
bertec.at</a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_contain=
er"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bor=
der-left:1px solid rgb(204,204,204);padding-left:1ex">On Tue, 2025-09-30 at=
 13:53 +0530, Ashish Mukherjee wrote:<br>
&gt; Now the consideration is to use some other encryption option for the<b=
r>
&gt; database which will work fine on pgsql 17. Cybertec&#39;s technology i=
s<br>
&gt; one route, the other is EDB. I am happy to hear experiences of folks<b=
r>
&gt; here with pgsql encryption options for v17 on large databases<br>
&gt; (2.5T in our case).<br>
<br>
I will refrain from making a recommendation, but you should know that<br>
data-at-rest encryption will always incur a certain performance penalty.<br=
>
<br>
Whatever solution you choose, you should run performance tests.<br></blockq=
uote><div><br></div><div>Yeah.=C2=A0 This applies to database upgrades in g=
eneral.=C2=A0=C2=A0</div><div><br></div><div>The lists have quite a bit of,=
 I upgraded, and &quot;query X that drives my platform now is 100x slower&q=
uot;.=C2=A0 I don&#39;t think this suggests that postgres is getting worse;=
 foundationally, it&#39;s mostly getting faster, but simply that the planne=
r changes how it responds=C2=A0to certain=C2=A0conditions.=C2=A0 Over time,=
 I&#39;ve learned some painful lessons and try to write SQL that is less ri=
sky from=C2=A0a performance standpoint.</div><div><br></div><div>Developers=
 tend to optimize into fragile planner behaviors chasing performance, somet=
imes without realizing it.=C2=A0 These kinds of issues are almost always ve=
ry fixable assuming you can modify the SQL or the thing writing the SQL. Th=
e takeaway here is: test/verify before making major upgrades, and bake in s=
ome recalibration time.=C2=A0 Adding encryption or other major features str=
ongly reinforces that need.</div><div><br></div><div>merlin</div></div></di=
v>

--00000000000043a2c40640063137--