DEV Community

Deep Fix
Deep Fix

Posted on

Boost PostgreSQL Query Performance: Proven Tips for Developers & DevOps

#postgres #performance #queries #devops

Introduction

If you’re a software developer, engineer, or DevOps professional, you’ve probably hit a wall where a PostgreSQL query suddenly becomes a bottleneck. This guide walks you through concrete steps to optimize PostgreSQL query performance, from understanding the planner to applying real‑world fixes.

1. Let PostgreSQL Explain Itself

The first step is to see what the optimizer thinks:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 
SELECT order_id, total
FROM orders
WHERE created_at >= '2024-01-01' AND status = 'completed'
ORDER BY created_at DESC
LIMIT 100;
Enter fullscreen mode Exit fullscreen mode

The output tells you:

  • Seq Scan vs Index Scan
  • Rows Removed by Filter
  • I/O cost (shared buffers, temp files)

If you see a Seq Scan on a large table where an index could help, that’s a red flag.

2. Indexing Strategies

a) Simple B‑Tree Index 

CREATE INDEX idx_orders_created_status ON orders (created_at, status);
Enter fullscreen mode Exit fullscreen mode

A multi‑column B‑tree works best for equality + range predicates.

b) Covering Index (INCLUDE) 

CREATE INDEX idx_orders_cover ON orders (created_at, status) INCLUDE (order_id, total);
Enter fullscreen mode Exit fullscreen mode

The INCLUDE clause lets PostgreSQL retrieve the needed columns directly from the index, avoiding a heap fetch.

c) Partial Index for Hot Data 

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

This keeps the index small and fast for recent queries.

3. Query Rewrites

Sometimes a small rewrite changes the plan dramatically.

Use JOIN instead of IN 

-- Bad
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

-- Good
SELECT u.*
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.total > 1000;
Enter fullscreen mode Exit fullscreen mode

The planner can better estimate row counts with a join.

4. Configuration Tweaks (PostgreSQL Settings)

Parameter Typical Adjustment Why
shared_buffers 25‑30% of RAM Improves cache hit rate
work_mem 64‑128MB (per sort) Allows in‑memory sorts, avoids temp files
effective_cache_size 50‑75% of RAM Guides planner’s cost model
max_parallel_workers_per_gather 2‑4 Enables parallel query execution

Remember to SELECT pg_reload_conf(); after changing postgresql.conf.

5. Step‑by‑Step Troubleshooting Checklist

  1. Run EXPLAIN ANALYZE – Identify scans, joins, and cost spikes.
  2. Check Index Usage – Ensure indexes exist and are used.
  3. Look for Mis‑estimated Rows – Run ANALYZE or increase default_statistics_target.
  4. Inspect Locks – SELECT * FROM pg_locks; to see blocking transactions.
  5. Review Server Stats – SELECT * FROM pg_stat_activity; and pg_stat_user_tables.
  6. Test Configuration Changes – Use a staging replica to measure impact.
  7. Profile with pgBadger or pg_stat_statements – Spot the top‑cost queries.

6. Real‑World Script Example

Below is a ready‑to‑run script that creates the recommended indexes and updates statistics. Grab it from the repository:

#!/usr/bin/env psql
-- Optimize orders table
CREATE INDEX IF NOT EXISTS idx_orders_created_status ON orders (created_at, status);
CREATE INDEX IF NOT EXISTS idx_orders_cover ON orders (created_at, status) INCLUDE (order_id, total);
ANALYZE orders;
Enter fullscreen mode Exit fullscreen mode

Run it on a non‑production clone first!

Conclusion

Optimizing PostgreSQL query performance is a blend of visibility (EXPLAIN), proper indexing, smart query design, and tuned server settings. Follow the checklist, apply the snippets, and you’ll see response times drop dramatically.

Happy querying!

Top comments (0)