MIME-Version: 1.0
References: <CAA1Yw9-rOunXTb8DEPCZKkEts1gj3oThKidCSVn6egVe9W4-UQ@mail.gmail.com>
 <60a4dc2711441931d29df7a43ba7447bbedb402f.camel@cybertec.at>
In-Reply-To: <60a4dc2711441931d29df7a43ba7447bbedb402f.camel@cybertec.at>
From: atma ram <atmaramkp@gmail.com>
Date: Wed, 26 Nov 2025 21:59:22 +0530
Message-ID: <CAA1Yw986mfAV8MijYz+nGgCgcbKFiEm7baDazPo=sB=QvaO7Zg@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: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000c06510064481e610"
Archived-At: <https://www.postgresql.org/message-id/CAA1Yw986mfAV8MijYz%2BnGgCgcbKFiEm7baDazPo%3DsB%3DQvaO7Zg%40mail.gmail.com>
Precedence: bulk

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

Hi Laurenz,

Thank you for your reply.

The table size is 1.6TB and not GB. My bad.

The queries using primary key or partition key will be definitely improved.
My question is 20 queries that do not use partition key and use only index.
Since this is a critical OLTP system, even if there is a slight chance that
those 20 queries will degrade performance, then we may not go with
partition and find any alternative ways. Hence the question.

We can do a POC and find out. But wanted to check if there are any existing
benchmarking, explanations or details available before we do POC. Is it
worth it?

Thanks,
Atma

On Wed, Nov 26, 2025 at 9:05=E2=80=AFPM Laurenz Albe <laurenz.albe@cybertec=
.at>
wrote:

> On Wed, 2025-11-26 at 18:50 +0530, atma ram wrote:
> > 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 table size is still causin=
g
> performance issues.
>
> 1.6 GB is too small for partitioning.
>
> > We are planning to partition the table on the primary key. This is an
> OLTP system, and there
> > are around 100 queries that access this table. About 80 of these querie=
s
> use the primary key
> > and will therefore benefit directly from the partition key once we
> implement partitioning.
>
> If a table uses the primary key, I cannot see how it could cause
> performance issues.
>
> I expect that these queries will become slightly *slower* if you partitio=
n
> the table,
> because of the overhead of partition pruning.
>
> > However, the remaining 20 queries do not use the primary key; they rely
> on other indexed 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 tha=
t
> do not use the partition key?
> > =E2=80=93 Will their performance degrade?
>
> Very likely yes, though perhaps only slightly.
>
> > =E2=80=93 Will it remain the same as before partitioning?
> > =E2=80=93 Is there any chance it will improve?
>
> That is unlikely, but possible, for example if the query can choose a
> faster sequential
> scan on some partitions, instead of a slower index scan on the whole tabl=
e.
>
> > Is there any benchmarking, documentation, or reference material that ca=
n
> 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.
>
> The only good way to tell is to implement it on a test database and try i=
t.
>
> But as I said initially, with a 1.6 GB table patritioning is pointless.
>
> Examine the performance bottleneck with EXPLAIN (ANALYZE, BUFFERS) and tr=
y
> to improve
> your queries.
>
> Yours,
> Laurenz Albe
>

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

<div dir=3D"ltr">Hi=C2=A0Laurenz,<div><br></div><div>Thank you for your=C2=
=A0reply.</div><div><br></div><div>The table size is 1.6TB and not GB. My b=
ad.</div><div><br></div><div>The queries using primary key or partition key=
 will be definitely=C2=A0improved. My question is 20 queries that do not us=
e partition key and use only index. Since this is a critical OLTP system, e=
ven if there is a slight chance that those 20 queries will degrade performa=
nce, then we may not go with partition and find any alternative ways. Hence=
 the question.=C2=A0</div><div><br></div><div>We can do a POC and find out.=
 But wanted to check if there are any existing benchmarking, explanations o=
r details available before we do POC. Is it worth it?</div><div><br></div><=
div>Thanks,</div><div>Atma</div></div><br><div class=3D"gmail_quote gmail_q=
uote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Wed, Nov 26, 2025 =
at 9:05=E2=80=AFPM Laurenz Albe &lt;<a href=3D"mailto:laurenz.albe@cybertec=
.at">laurenz.albe@cybertec.at</a>&gt; wrote:<br></div><blockquote class=3D"=
gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(20=
4,204,204);padding-left:1ex">On Wed, 2025-11-26 at 18:50 +0530, atma ram wr=
ote:<br>
&gt; We have a table that is approximately 1.6 GB in size. Query performanc=
e has started to degrade.<br>
&gt; Although we have multiple indexes, the large table size is still causi=
ng performance issues.<br>
<br>
1.6 GB is too small for partitioning.<br>
<br>
&gt; We are planning to partition the table on the primary key. This is an =
OLTP system, and there<br>
&gt; are around 100 queries that access this table. About 80 of these queri=
es use the primary key<br>
&gt; and will therefore benefit directly from the partition key once we imp=
lement partitioning.<br>
<br>
If a table uses the primary key, I cannot see how it could cause performanc=
e issues.<br>
<br>
I expect that these queries will become slightly *slower* if you partition =
the table,<br>
because of the overhead of partition pruning.<br>
<br>
&gt; However, the remaining 20 queries do not use the primary key; they rel=
y on other indexed columns.<br>
&gt; Our question is: after partitioning the table, and after creating the =
necessary indexes on<br>
&gt; each partition, what happens to the performance of those 20 queries th=
at do not use the partition key?<br>
&gt; =E2=80=93 Will their performance degrade?<br>
<br>
Very likely yes, though perhaps only slightly.<br>
<br>
&gt; =E2=80=93 Will it remain the same as before partitioning?<br>
&gt; =E2=80=93 Is there any chance it will improve?<br>
<br>
That is unlikely, but possible, for example if the query can choose a faste=
r sequential<br>
scan on some partitions, instead of a slower index scan on the whole table.=
<br>
<br>
&gt; Is there any benchmarking, documentation, or reference material that c=
an help demonstrate<br>
&gt; how partitioning will affect the performance of the 20 queries that do=
 not use the partition key?<br>
&gt; This information is critical for us before proceeding with the partiti=
oning strategy.<br>
<br>
The only good way to tell is to implement it on a test database and try it.=
<br>
<br>
But as I said initially, with a 1.6 GB table patritioning is pointless.<br>
<br>
Examine the performance bottleneck with EXPLAIN (ANALYZE, BUFFERS) and try =
to improve<br>
your queries.<br>
<br>
Yours,<br>
Laurenz Albe<br>
</blockquote></div>

--000000000000c06510064481e610--