DEV Community

Deep Fix
Deep Fix

Posted on

Boost PostgreSQL Query Performance: Proven Optimization Tips & Tricks

#postgres #performance #query #optimization

Introduction

PostgreSQL is a powerful, open‑source relational database, but even the best‑written queries can become bottlenecks as data grows. In this article we’ll walk through practical steps to optimize query performance, from indexing to configuration tuning, with real‑world code snippets and troubleshooting tips.

1. Profile the Query with EXPLAIN ANALYZE 

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE created_at > now() - interval '7 days';
Enter fullscreen mode Exit fullscreen mode

The output shows the actual execution plan, row counts, and timing. Look for Seq Scan (full table scan) or Nested Loop with large inner tables—these are typical performance red flags.

2. Indexing Strategies

a. Simple B‑tree index 

CREATE INDEX idx_orders_created_at ON orders(created_at);
Enter fullscreen mode Exit fullscreen mode

A B‑tree index speeds up range queries on created_at.

b. Composite index for multi‑column filters 

CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);
Enter fullscreen mode Exit fullscreen mode

Use the same column order as the query predicates.

c. Covering index with INCLUDE 

CREATE INDEX idx_orders_customer_id ON orders(customer_id) INCLUDE (status, total);
Enter fullscreen mode Exit fullscreen mode

The index now stores status and total so PostgreSQL can satisfy the query without touching the heap.

3. Partitioning Large Tables

Partitioning lets PostgreSQL prune whole chunks of data.

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

Steps:

  1. Choose a partition key (date, tenant_id, etc.).
  2. Create a master table with PARTITION BY.
  3. Add child partitions.
  4. Adjust your queries to target the partition key for automatic pruning.

4. Tune PostgreSQL Configuration

Parameter Typical Range Why it matters
shared_buffers 15‑25% of RAM Cache for frequently accessed data.
work_mem 4‑64 MB per operation Memory for sorts, hash joins, etc.
effective_cache_size 50‑75% of RAM Planner’s estimate of OS cache.
max_parallel_workers_per_gather 2‑4 Enables parallel query execution.

Update postgresql.conf and reload:

pg_ctl reload
Enter fullscreen mode Exit fullscreen mode

5. Common Pitfalls & Step‑by‑Step Troubleshooting

Scenario: Slow query due to missing index

  1. Run EXPLAIN ANALYZE and note a Seq Scan on orders.
  2. Identify the filter columns (customer_id, status).
  3. Create a composite index matching those columns.
  4. Re‑run EXPLAIN ANALYZE to confirm the plan now shows an Index Scan and reduced total time.

Scenario: Unexpected high cpu_time in pg_stat_statements

  1. Query pg_stat_statements: 
SELECT query, total_time, calls FROM pg_stat_statements
ORDER BY total_time DESC LIMIT 5;
Enter fullscreen mode Exit fullscreen mode
  1. Look for functions that can be rewritten (e.g., ILIKE → LIKE with lowercasing).
  2. Apply a functional index if needed: 
CREATE INDEX idx_lower_email ON users((lower(email)));
Enter fullscreen mode Exit fullscreen mode
  1. Verify the improvement with another EXPLAIN ANALYZE.

6. Monitoring & Observability

Enable the pg_stat_statements extension:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
Enter fullscreen mode Exit fullscreen mode

Then query it regularly or pipe the data to a dashboard (Grafana, Prometheus). This gives you a live view of the top‑cost queries.

7. Ready‑to‑Use Script

For a quick start, we’ve packaged a set of best‑practice index and configuration commands. You can grab it from our repository:

Conclusion

Optimizing PostgreSQL queries is an iterative process: measure → adjust → re‑measure. By profiling with EXPLAIN ANALYZE, applying the right indexes, leveraging partitioning, and fine‑tuning server parameters, you can dramatically reduce latency and improve throughput for your applications.

Happy querying!

Top comments (0)