DEV Community

Deep Fix
Deep Fix

Posted on

Optimizing PostgreSQL Query Performance: Indexing, Partitioning, and Tuning Tips for Faster Apps

#postgres #performance #optimization #devops

Introduction

Performance‑critical applications often hit bottlenecks in their PostgreSQL queries. This guide walks software developers, engineers, and DevOps teams through practical steps to diagnose slow queries and apply proven optimizations.

1. Read the Execution Plan

The first step is to understand what PostgreSQL is doing.

EXPLAIN (ANALYZE, BUFFERS) SELECT *
FROM orders
WHERE customer_id = 12345
  AND order_date >= '2023-01-01';
Enter fullscreen mode Exit fullscreen mode
  • Look for Seq Scan on large tables – a strong signal to add an index.
  • Check cost, rows, and actual time columns; large discrepancies hint at misestimated statistics.

2. Indexing Strategies

2.1 Single‑Column Index 

CREATE INDEX idx_orders_customer ON orders(customer_id);
Enter fullscreen mode Exit fullscreen mode

2.2 Multi‑Column (Composite) Index

When the query filters on multiple columns, a composite index can cover both predicates:

CREATE INDEX idx_orders_cust_date ON orders(customer_id, order_date);
Enter fullscreen mode Exit fullscreen mode

2.3 Partial Indexes

If only recent data is queried frequently:

CREATE INDEX idx_orders_recent ON orders(customer_id)
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days';
Enter fullscreen mode Exit fullscreen mode

2.4 Covering Index (Include)

PostgreSQL 12+ supports INCLUDE to keep non‑key columns in the index leaf pages, eliminating the need for a heap fetch:

CREATE INDEX idx_orders_cover ON orders(customer_id) INCLUDE (order_total, status);
Enter fullscreen mode Exit fullscreen mode

3. Partitioning Large Tables

Partitioning reduces the amount of data scanned.

CREATE TABLE orders_y2023 PARTITION OF orders
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
Enter fullscreen mode Exit fullscreen mode

After partitioning, repeat the EXPLAIN ANALYZE step – you should see Partition Scan instead of a full table scan.

4. Configuration Tuning

Adjust these settings in postgresql.conf or via SET for session‑level testing.

SET work_mem = '64MB';               -- larger sort buffers
SET maintenance_work_mem = '512MB';  -- faster index creation
SET effective_cache_size = '4GB';    -- informs planner about OS cache
SET random_page_cost = 1.1;          -- lower if using SSDs
Enter fullscreen mode Exit fullscreen mode

Remember to reload the config after permanent changes: SELECT pg_reload_conf();

5. Common Pitfalls & Step‑by‑Step Troubleshooting

Symptom Likely Cause Quick Fix
Query runs >5 s, Seq Scan appears Missing index Create appropriate index (see Section 2)
High Buffers: shared hit=0 count Insufficient shared_buffers Increase shared_buffers to 25‑30 % of RAM
EXPLAIN shows estimated rows far from actual rows Stale statistics ANALYZE the table or enable autoanalyze
Frequent Lock wait events Long‑running transactions Commit/rollback early; use shorter transaction scopes

6. Monitoring & Continuous Improvement

Automate plan checks with a cron job:

#!/bin/bash
psql -d mydb -c "EXPLAIN (ANALYZE, BUFFERS) SELECT ..." > /var/log/pg_query.log
Enter fullscreen mode Exit fullscreen mode

Integrate the log with a dashboard (e.g., Grafana + pg_stat_statements) to spot regressions early.

Conclusion

Optimizing PostgreSQL queries is an iterative process: read the plan, apply the right index or partition, tune server parameters, and monitor continuously. When you’re ready to apply these changes at scale, Download the pre-configured script here, or Get the complete patch tool to automate index creation across environments. For a deeper dive, Access the full repository fix and explore the sample configurations.

Top comments (0)