logo

PostgreSQL Performance Optimization: Advanced Techniques

DM
Davis Mathew · Senior Software Architect
6 min read · January 12, 2024

PostgreSQL is reliable and full-featured, but getting good performance out of it requires understanding how it works internally. This guide covers configuration, indexing, query tuning, connection pooling and maintenance for production databases.

Understanding PostgreSQL architecture

Tuning makes more sense once you know how PostgreSQL is put together:

Key components

  1. Shared buffers: PostgreSQL's main data cache
  2. WAL (write-ahead log): the transaction log
  3. Background writer: writes dirty pages to disk
  4. Vacuum: cleans up dead tuples

Configuration tuning

Memory configuration

Memory settings often make the biggest difference:

-- postgresql.conf settings
shared_buffers = '256MB'                # 25% of RAM for dedicated servers
effective_cache_size = '1GB'           # Total available RAM for caching
work_mem = '4MB'                        # Per-operation memory
maintenance_work_mem = '64MB'           # Maintenance operations memory

Connection and query settings

max_connections = 100                   # Adjust based on your workload
random_page_cost = 1.1                  # For SSD storage
effective_io_concurrency = 200          # For SSD storage
checkpoint_completion_target = 0.9      # Spread checkpoint writes
wal_buffers = '16MB'                    # WAL buffer size

Query optimization

Understanding EXPLAIN

EXPLAIN shows how PostgreSQL runs a query:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2023-01-01'
GROUP BY u.id, u.name
ORDER BY order_count DESC;

Key metrics to watch

  • Execution time: total time to run the query
  • Shared hit: data found in shared buffers
  • Shared read: data read from disk
  • Planning time: time spent building the execution plan

Advanced indexing strategies

B-Tree indexes (default)

The default and most common index type:

CREATE INDEX idx_users_created_at ON users (created_at);
CREATE INDEX idx_orders_user_status ON orders (user_id, status);

Partial indexes

Index only part of a table to save space and speed up lookups:

CREATE INDEX idx_active_users 
ON users (email) 
WHERE status = 'active';

Expression indexes

Index a computed value:

CREATE INDEX idx_users_email_lower 
ON users (LOWER(email));

GIN and GiST indexes

For JSONB, full-text search and other complex data types:

-- For JSONB data
CREATE INDEX idx_user_preferences_gin 
ON users USING gin (preferences);

-- For full-text search
CREATE INDEX idx_articles_fts 
ON articles USING gin (to_tsvector('english', content));

Connection pooling

Busy applications need a connection pooler:

PgBouncer configuration

# pgbouncer.ini
[databases]
myapp = host=localhost port=5432 dbname=myapp

[pgbouncer]
pool_mode = transaction
listen_port = 6432
listen_addr = *
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
max_client_conn = 100
default_pool_size = 20

Connection pool sizing

A rough starting point:

  • CPU cores × 2 for CPU-intensive workloads
  • CPU cores × 5-10 for I/O intensive workloads

Query performance patterns

Efficient pagination

Avoid large OFFSET values; paginate on a key instead:

-- Bad: Using OFFSET
SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 10000;

-- Good: Using cursor-based pagination
SELECT * FROM orders 
WHERE id > $last_seen_id 
ORDER BY id LIMIT 10;

Batch processing

Process large datasets in chunks:

-- Process records in batches
DO $$
DECLARE
    batch_size INT := 1000;
    processed INT := 0;
BEGIN
    LOOP
        UPDATE orders 
        SET status = 'processed'
        WHERE status = 'pending'
        AND id IN (
            SELECT id FROM orders 
            WHERE status = 'pending'
            LIMIT batch_size
        );
        
        GET DIAGNOSTICS processed = ROW_COUNT;
        EXIT WHEN processed = 0;
        
        COMMIT;
        PERFORM pg_sleep(0.1); -- Brief pause
    END LOOP;
END $$;

Monitoring and maintenance

Essential monitoring queries

Current activity

SELECT pid, state, query, query_start
FROM pg_stat_activity
WHERE state = 'active';

Long-running queries

SELECT pid, now() - pg_stat_activity.query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'
AND (now() - pg_stat_activity.query_start) > interval '1 minute';

Index usage

SELECT schemaname, relname AS table_name, indexrelname AS index_name,
       idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC, pg_relation_size(indexrelid) DESC;

Regular maintenance

VACUUM and ANALYZE

-- Manual vacuum
VACUUM ANALYZE users;

-- Check for bloat
SELECT schemaname, relname AS table_name,
       pg_size_pretty(pg_total_relation_size(relid)) AS size,
       n_dead_tup, n_live_tup
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

Advanced performance techniques

Partitioning

For large tables, partitioning can significantly improve performance:

-- Range partitioning by date
CREATE TABLE orders (
    id SERIAL,
    order_date DATE NOT NULL,
    customer_id INT,
    amount DECIMAL(10,2)
) PARTITION BY RANGE (order_date);

-- Create partitions
CREATE TABLE orders_2024_01 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE orders_2024_02 PARTITION OF orders
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

Materialized views

For complex aggregations:

CREATE MATERIALIZED VIEW user_order_stats AS
SELECT 
    u.id,
    u.name,
    COUNT(o.id) as order_count,
    SUM(o.total) as total_spent,
    MAX(o.created_at) as last_order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- Refresh periodically
REFRESH MATERIALIZED VIEW user_order_stats;

Common performance anti-patterns

Avoid these mistakes

  1. N+1 queries: use a JOIN instead of one query per row
  2. SELECT *: select only the columns you need
  3. Unnecessary ORDER BY: remove it when the order does not matter
  4. Missing WHERE clauses: filter data as early as possible
  5. Inefficient EXISTS: use a JOIN where it fits better

Example optimization

-- Bad: N+1 query pattern
SELECT * FROM users;
-- Then for each user:
SELECT * FROM orders WHERE user_id = ?;

-- Good: Single query with JOIN
SELECT u.*, o.* 
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

Performance testing

Load testing tools

  1. pgbench: the benchmarking tool that ships with PostgreSQL
  2. Apache JMeter: application performance testing
  3. Artillery: a load testing toolkit

pgbench example

# Initialize test database
pgbench -i -s 10 mydb

# Run benchmark
pgbench -c 10 -j 2 -t 1000 mydb

Troubleshooting common issues

Slow queries

  1. Check execution plans with EXPLAIN ANALYZE
  2. Look for missing indexes
  3. Consider query rewriting
  4. Check statistics are up to date

High CPU usage

  1. Identify expensive queries
  2. Check for excessive sorting/grouping
  3. Look for sequential scans on large tables
  4. Consider adding appropriate indexes

Memory issues

  1. Monitor shared buffer hit ratio
  2. Check for excessive work_mem usage
  3. Look for memory-intensive operations
  4. Consider adjusting configuration

Performance monitoring dashboard

A useful monitoring setup shows:

  • Query performance metrics
  • Connection pool statistics
  • Index usage statistics
  • Vacuum and maintenance status

Conclusion

PostgreSQL tuning is never finished. Learn your workload and access patterns, configure for your hardware, index for the queries you actually run, and keep monitoring and maintaining the database as data grows.

Measure first, change one thing at a time, and measure again.

Performance checklist

  • Memory settings optimized for hardware
  • Appropriate indexes created and maintained
  • Connection pooling implemented
  • Regular VACUUM and ANALYZE scheduled
  • Query performance monitoring in place
  • Slow query log analysis automated
  • Backup and recovery tested

Need help with PostgreSQL performance? Get in touch for a database health check.

DM
Written by
Davis Mathew
Co-founder · Senior Software Architect

Davis leads software and data architecture: databases, search, data pipelines and the integrations that connect business systems such as Odoo. He leads delivery on our data and integration projects.

Want a second opinion on your setup?
Talk to the engineers who wrote this. 30 minutes, no sales pitch.
Book a call

Keep reading

Observability
Datadog Bill Too High? How to Cut It, and When to Move to Open Source
Where Datadog costs come from, the fixes that cut the bill inside Datadog, and when a hybrid or full move to Prometheus, Grafana and OpenTelemetry pays off.
Read article →
Nextcloud
The November 2026 Outlook Outage: How to Keep Working When Microsoft 365 Goes Down
What happened in the 12–14 November Outlook and Microsoft 365 outage, and how a Nextcloud setup you control keeps teams working and data private.
Read article →
Observability
Building an On-Prem Observability Stack with Prometheus, Grafana, Loki and OpenTelemetry
How to build a unified, open-source observability stack on your own servers: OpenTelemetry, Prometheus, Loki, Tempo and Grafana, with sizing and alerting.
Read article →

Next step

Talk to an engineer about PostgreSQL

Send a few details, and pick up to 3 times if you would like a call. We reply within one business day.

You talk to the expert who would do the work

Written scope and price after the call

No obligation

info@opensource.consulting
+1 514 577 8599