public inbox for [email protected]  

help / color / mirror / Atom feed

From: Laurenz Albe <[email protected]>
To: atma ram <[email protected]>
To: [email protected]
Subject: Re: Question on PostgreSQL Table Partitioning – Performance of Queries That Do Not Use the Partition Key
Date: Wed, 26 Nov 2025 16:35:19 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAA1Yw9-rOunXTb8DEPCZKkEts1gj3oThKidCSVn6egVe9W4-UQ@mail.gmail.com>
References: <CAA1Yw9-rOunXTb8DEPCZKkEts1gj3oThKidCSVn6egVe9W4-UQ@mail.gmail.com>

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 causing 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 queries 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 partition 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 that do not use the partition key?
> – Will their performance degrade?

Very likely yes, though perhaps only slightly.

> – Will it remain the same as before partitioning?
> – 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 table.

> 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.

The only good way to tell is to implement it on a test database and try it.

But as I said initially, with a 1.6 GB table patritioning is pointless.

Examine the performance bottleneck with EXPLAIN (ANALYZE, BUFFERS) and try to improve
your queries.

Yours,
Laurenz Albe

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Question on PostgreSQL Table Partitioning – Performance of Queries That Do Not Use the Partition Key
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox