Code snippets — Postgresql Performance Tuning

snippet-2.ini

# checkpoint_completion_target: spread checkpoint writes
checkpoint_completion_target = 0.9

# wal_buffers: WAL write buffer (usually auto)
wal_buffers = 16MB

# synchronous_commit: set to off for async writes (risk: lose ~100ms of data on crash)
# Use only if you can tolerate this trade-off
synchronous_commit = off

# max_wal_size: allow more WAL before checkpoint
max_wal_size = 4GB

snippet-2.sql

-- Bad: fetches all columns, more data transferred
SELECT * FROM orders WHERE user_id = 42;

-- Good: only fetch what you need
SELECT id, total, status, created_at FROM orders WHERE user_id = 42;

snippet-3.ini

# max_connections: lower than you think you need — use a connection pooler!
max_connections = 100

# Use PgBouncer or pgpool-II for connection pooling

snippet-3.sql

-- PG 12+: this is fine and inlines automatically
WITH recent_orders AS (
    SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '7 days'
)
SELECT user_id, COUNT(*) FROM recent_orders GROUP BY user_id;

-- Force materialization (barrier) when needed
WITH MATERIALIZED expensive_query AS (
    SELECT ...
)

snippet-4.ini

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

[pgbouncer]
listen_addr = *
listen_port = 6432
auth_type = scram-sha-256
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

snippet-4.sql

-- Bad: N individual inserts
INSERT INTO events (type, data) VALUES ('click', '{}');
-- ... repeated 10,000 times

-- Good: single batch insert
INSERT INTO events (type, data)
VALUES ('click', '{}'), ('view', '{}'), ('purchase', '{}')
-- ... up to thousands of rows at once
;

-- Even better for large batches: COPY
COPY events (type, data) FROM STDIN WITH (FORMAT CSV);

snippet-5.sql

-- Bad: OFFSET pagination is slow on large tables
-- PostgreSQL must scan and discard 100,000 rows
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 100000;

-- Good: Cursor-based pagination
SELECT * FROM posts
WHERE created_at < '2026-01-15 10:30:00'  -- cursor from previous page
ORDER BY created_at DESC
LIMIT 20;

snippet-6.sql

-- Check table bloat
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(quote_ident(tablename))) AS total_size,
    n_dead_tup,
    n_live_tup,
    round(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS dead_ratio
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

-- Manual vacuum on a hot table
VACUUM (ANALYZE, VERBOSE) orders;

-- Reclaim disk space (locks table!)
VACUUM FULL orders;

-- Better for production: use pg_repack
pg_repack -t orders -d mydb

snippet-7.sql

ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.01,   -- Vacuum at 1% dead tuples
    autovacuum_analyze_scale_factor = 0.005  -- Analyze at 0.5%
);

snippet-8.sql

-- Install pg_stat_statements extension
CREATE EXTENSION pg_stat_statements;

-- Top 10 slowest queries
SELECT
    LEFT(query, 100) as query,
    calls,
    round(mean_exec_time::numeric, 2) AS avg_ms,
    round(total_exec_time::numeric, 2) AS total_ms,
    round(stddev_exec_time::numeric, 2) AS stddev_ms
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Queries with highest total time (most impactful to optimize)
SELECT
    LEFT(query, 100) as query,
    calls,
    round(total_exec_time::numeric / 1000, 2) AS total_seconds
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

snippet.ini

# shared_buffers: 25% of total RAM
shared_buffers = 4GB

# effective_cache_size: 75% of total RAM
# Tells the query planner how much OS cache is available
effective_cache_size = 12GB

# work_mem: RAM per sort/hash operation per query
# Be careful — this is per operation, not per connection
# Formula: (RAM * 0.25) / (max_connections * 3)
work_mem = 64MB

# maintenance_work_mem: for VACUUM, CREATE INDEX, etc.
maintenance_work_mem = 1GB

snippet.sql

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 u.created_at > '2026-01-01'
GROUP BY u.id, u.name
ORDER BY order_count DESC
LIMIT 10;