PostgreSQL Performance Tuning: 10 Tips for Faster Queries

Why PostgreSQL Performance Tuning Matters

A default PostgreSQL installation is deliberately conservative — it's tuned to run on a machine with 256 MB of RAM. Modern production servers have 8–64 GB. Without tuning, you're leaving enormous performance on the table.

These 10 tips move you from default settings to a database optimized for real-world workloads.

Tip 1: Use EXPLAIN ANALYZE Before Changing Anything

Never guess. Always profile first:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id) AS order_count
FROM   users u
JOIN   orders o ON o.user_id = u.id
WHERE  o.status = 'completed'
GROUP  BY u.name
ORDER  BY order_count DESC
LIMIT  10;

Look for: Seq Scan on large tables, high actual time, large rows removed by filter.

Tip 2: Add the Right Indexes

-- Index the JOIN column and the WHERE filter column
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status  ON orders(status);

-- Even better: a composite index for this query pattern
CREATE INDEX idx_orders_status_user ON orders(status, user_id);

Run EXPLAIN ANALYZE again — you should see Index Scan replacing Seq Scan.

Tip 3: Tune shared_buffers

shared_buffers is PostgreSQL's internal cache. Set it to 25% of total RAM:

# In postgresql.conf
shared_buffers = 4GB          # for a 16 GB RAM server

-- Verify current setting
SHOW shared_buffers;

Tip 4: Set effective_cache_size Correctly

This tells the planner how much total memory (OS + PostgreSQL) is available for caching. Set it to 75% of RAM:

effective_cache_size = 12GB   # for a 16 GB RAM server

A higher value encourages the planner to prefer index scans over sequential scans.

Tip 5: Increase work_mem for Complex Queries

work_mem controls memory per sort/hash operation. Increasing it speeds up ORDER BY, GROUP BY, and hash joins:

# Set globally (careful — this multiplies by connections × operations)
work_mem = 64MB

# Or set per session for an expensive report
SET work_mem = '256MB';
EXPLAIN ANALYZE SELECT ...;
RESET work_mem;

-- Check if sorts are spilling to disk
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM large_table ORDER BY created_at;
-- "Sort Method: external merge  Disk: 45678kB" means increase work_mem

Tip 6: Enable Parallel Query

# postgresql.conf
max_parallel_workers_per_gather = 4
max_parallel_workers = 8

-- Force parallel scan to verify it works
SET max_parallel_workers_per_gather = 4;
EXPLAIN ANALYZE SELECT COUNT(*) FROM orders;
-- Should show "Gather" and "Parallel Seq Scan"

Tip 7: Use Connection Pooling

As covered in our [Connection Pooling Guide](/blog/database-connection-pooling), each connection consumes ~5–10 MB. Keep max_connections modest and use PgBouncer in transaction mode:

# postgresql.conf
max_connections = 100   # lower than you think you need

# Then front it with PgBouncer
default_pool_size = 20
pool_mode = transaction

Tip 8: Avoid SELECT *

Fetching every column sends unnecessary data over the network and prevents index-only scans:

-- Bad
SELECT * FROM users WHERE id = 42;

-- Good — only fetch what you need
SELECT id, name, email FROM users WHERE id = 42;

If id, name, and email are all in the index, PostgreSQL can answer the query without touching the main table (index-only scan).

Tip 9: VACUUM and ANALYZE Regularly

PostgreSQL doesn't delete rows immediately on DELETE — it marks them dead. VACUUM reclaims that space:

-- Manual vacuum with stats update
VACUUM ANALYZE orders;

-- Check table bloat
SELECT relname, n_dead_tup, n_live_tup,
       round(100 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct
FROM   pg_stat_user_tables
ORDER  BY n_dead_tup DESC
LIMIT  10;

Autovacuum handles this automatically but tune its aggressiveness for write-heavy tables:

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,   -- vacuum after 1% dead rows
  autovacuum_analyze_scale_factor = 0.005
);

Tip 10: Cache Results with Redis

The fastest query is one you never run. Cache frequently read, rarely written results in Redis:

-- Identify the most-run slow queries
SELECT query, calls, total_exec_time, mean_exec_time
FROM   pg_stat_statements
ORDER  BY mean_exec_time DESC
LIMIT  10;

Any query in this list returning the same result repeatedly is a candidate for Redis caching. See our [Redis Caching Guide](/blog/redis-caching-guide) for implementation details.

Managed PostgreSQL on PandaStack

[PandaStack](https://dashboard.pandastack.io) runs managed PostgreSQL with tuned defaults, automated VACUUM scheduling, built-in connection pooling, and Redis available alongside — so you can focus on tips 1–2 (profiling + indexing) while the platform handles tips 3–10.

Documentation: [docs.pandastack.io](https://docs.pandastack.io).

Quick Reference Checklist

• [ ] Profile with EXPLAIN ANALYZE before changing anything
• [ ] Add indexes on WHERE, JOIN ON, and ORDER BY columns
• [ ] Set shared_buffers to 25% of RAM
• [ ] Set effective_cache_size to 75% of RAM
• [ ] Tune work_mem for sort-heavy workloads
• [ ] Enable parallel query workers
• [ ] Use connection pooling (PgBouncer)
• [ ] Avoid SELECT *
• [ ] Monitor and tune autovacuum
• [ ] Cache hot read paths in Redis