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
