Problem:

After deleting 52 million rows from a table, the client performed a VACUUM FULL and REINDEX on the table. Following these maintenance operations, the performance of large joins involving this table has significantly deteriorated, with query execution times increasing from minutes to hours. The task is to identify the cause of this performance regression and recommend solutions to restore the original performance levels.

The expert team provided the initial investigation and scheduled the meeting with the client. At the meeting, the expert team discussed the following topics:

  1. The client has a billing system with millions of records, and each month they delete extra records.
  2. The client has upgraded from Patroni version 11.5-11.18 and after that, their performance decreased whenever the client ran a merge query then it took hours rather than minutes. The client was avoiding it but wanted it to be solved.
  3. The client shared the query, DB logs, and everything with the expert via emails for investigation.

Solution:

To improve query performance, experts suggested to the client the following optimizations:

  1. Index Optimization: Ensured that tables had appropriate indexes on columns used in filters and joins to speed up data retrieval.
  2. Partitioning: Aligned the partitioning scheme with query predicates to minimize partition scans.
  3. Limit Result Set: Added a LIMIT clause to reduce the data volume for sorting and joining.
  4. Rewrite the Query: Tried using Nested Loop Joins or Hash Joins instead of Merge Joins, depending on data distribution and join conditions.
  5. Aggregate Data: Used materialized views or summary tables for pre-aggregated data to reduce processing.
  6. Optimize Sort: Increased work_mem for sorting operations and optimized the Sort Key to reduce sorted columns.
  7. Analyze Query Plan: Reviewed the query plan to identify and address potential bottlenecks or costly operations. Also, the expert team provided the client with the updated query as an example to improve performance.

Conclusion:

The client faced significant performance degradation in their billing system after deleting 52M rows and running VACUUM FULL and REINDEX on a table. Queries that previously took minutes now took hours. Following the upgrade from Patroni 11.5 to 11.18, merge queries experienced increased runtimes. The expert team investigated the issue based on shared queries and DB logs. During the meeting, our experts discussed optimizations like index adjustments, partitioning alignment, limiting result sets, query rewriting, data aggregation, sort optimization, and query plan analysis to restore and enhance performance.