MIME-Version: 1.0
References: <CAA1Yw9-rOunXTb8DEPCZKkEts1gj3oThKidCSVn6egVe9W4-UQ@mail.gmail.com>
In-Reply-To: <CAA1Yw9-rOunXTb8DEPCZKkEts1gj3oThKidCSVn6egVe9W4-UQ@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Wed, 26 Nov 2025 09:16:23 -0500
Message-ID: <CANzqJaDC4NbGbMBmHXJKTc7HW4HYDy+Hu+_mR72wYeMN5YL=Eg@mail.gmail.com>
Subject: =?UTF-8?Q?Re=3A_Question_on_PostgreSQL_Table_Partitioning_=E2=80=93_Pe?=
	=?UTF-8?Q?rformance_of_Queries_That_Do_Not_Use_the_Partition_Key?=
To: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000002e6fcd0644800bb0"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaDC4NbGbMBmHXJKTc7HW4HYDy%2BHu%2B_mR72wYeMN5YL%3DEg%40mail.gmail.com>
Precedence: bulk

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

On Wed, Nov 26, 2025 at 8:32=E2=80=AFAM atma ram <atmaramkp@gmail.com> wrot=
e:

> Hi,
>
> Question on PostgreSQL Table Partitioning =E2=80=93 Performance of Querie=
s That Do
> Not Use the Partition Key
>
> We have a table that is approximately 1.6 GB in size. Query performance
> has started to degrade. Although we have multiple indexes, the large tabl=
e
> size
>
1.6GB is pretty darned tiny.  Did you mean TB?

When was the last time the table was vacuumed and analyzed?  Tuning
autovacuum parameters is important, but you might have to also create a
cron job to regularly manually analyze and vacuum them.

is still causing performance issues.
>
> We are planning to partition the table on the primary key. This is an OLT=
P
> system, and there are around 100 queries that access this table. About 80
> of these queries use the primary key and will therefore benefit directly
> from the partition key once we implement partitioning. However, the
> remaining 20 queries do not use the primary key; they rely on other index=
ed
> columns.
>
> Our question is: after partitioning the table, and after creating the
> necessary indexes on each partition, what happens to the performance of
> those 20 queries that do *not* use the partition key?
> =E2=80=93 Will their performance degrade?
> =E2=80=93 Will it remain the same as before partitioning?
> =E2=80=93 Is there any chance it will improve?
>
> Additional details: we plan to create only 16 partitions, so the partitio=
n
> count will not be very high.
>
> *Is there any benchmarking, documentation, or reference material that can
> help demonstrate how partitioning will affect the performance of the 20
> queries that do not use the partition key?*
>
> This information is critical for us before proceeding with the
> partitioning strategy.
>
Every circumstance is different. You're going to have to test it.

--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

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

<div dir=3D"ltr"><div dir=3D"ltr">On Wed, Nov 26, 2025 at 8:32=E2=80=AFAM a=
tma ram &lt;<a href=3D"mailto:atmaramkp@gmail.com">atmaramkp@gmail.com</a>&=
gt; wrote:</div><div class=3D"gmail_quote gmail_quote_container"><blockquot=
e class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px s=
olid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr">Hi,<div><br></div>=
<div><div>Question on PostgreSQL Table Partitioning =E2=80=93 Performance o=
f Queries That Do Not Use the Partition Key=C2=A0=C2=A0</div><div><br></div=
><div><p>We have a table that is approximately 1.6 GB in size. Query perfor=
mance has started to degrade. Although we have multiple indexes, the large =
table size</p></div></div></div></blockquote><div>1.6GB is pretty darned ti=
ny.=C2=A0 Did you mean TB?</div><div><br></div><div>When was the last time =
the table was vacuumed and analyzed?=C2=A0 Tuning autovacuum parameters is =
important, but you might have to also create a cron job to regularly manual=
ly analyze and vacuum them.</div><div><br></div><blockquote class=3D"gmail_=
quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,=
204);padding-left:1ex"><div dir=3D"ltr"><div><div><p>is still causing perfo=
rmance issues.</p>
<p>We are planning to partition the table on the primary key. This is an OL=
TP system, and there are around 100 queries that access this table. About 8=
0 of these queries use the primary key and will therefore benefit directly =
from the partition key once we implement partitioning. However, the remaini=
ng 20 queries do not use the primary key; they rely on other indexed column=
s.</p>
<p>Our question is: after partitioning the table, and after creating the ne=
cessary indexes on each partition, what happens to the performance of those=
 20 queries that do <strong>not</strong> use the partition key?<br>
=E2=80=93 Will their performance degrade?<br>
=E2=80=93 Will it remain the same as before partitioning?<br>
=E2=80=93 Is there any chance it will improve?</p>
<p>Additional details: we plan to create only 16 partitions, so the partiti=
on count will not be very high.</p><p><strong>Is there any benchmarking, do=
cumentation, or reference material that can help demonstrate how partitioni=
ng will affect the performance of the 20 queries that do not use the partit=
ion key?</strong></p><div><div><div></div><div><div></div></div></div></div=
>
<p>This information is critical for us before proceeding with the partition=
ing strategy.</p></div></div></div></blockquote></div><div>Every circumstan=
ce is different. You&#39;re going to have to test it.</div><div><br></div><=
span class=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=
=3D"gmail_signature"><div dir=3D"ltr">Death to &lt;Redacted&gt;, and butter=
 sauce.<div>Don&#39;t boil me, I&#39;m still alive.<br><div><div>&lt;Redact=
ed&gt; lobster!</div></div></div></div></div></div>

--0000000000002e6fcd0644800bb0--