Question on PostgreSQL Table Partitioning – Performance of Queries 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 table size is still causing performance issues.
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. 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?
– Will it remain the same as before partitioning?
– Is there any chance it will improve?
Additional details: we plan to create only 16 partitions, so the partition 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.
Thank you in advance.
