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
