PostgreSQL performance tuning for web applications comes down to four recurring levers: indexing, query plan analysis, connection control, and maintenance strategy. Most teams do not need exotic configurations. They need consistent application of fundamentals, measured against real workload patterns. A well-tuned PostgreSQL instance on modest hardware routinely outperforms a poorly-tuned instance on hardware that costs 10x more.
---
Memory Configuration Baseline
PostgreSQL memory settings have the single largest impact on query performance. The defaults ship conservative because PostgreSQL does not know your workload, so you must tune them.
Critical Memory Parameters
| Parameter | Default | Web App Recommendation | Purpose |
| shared_buffers | 128MB | 25% of RAM (max 8GB) | Shared page cache |
| effective_cache_size | 4GB | 50-75% of RAM | Query planner hint |
| work_mem | 4MB | 16-64MB | Per-operation sort/hash memory |
| maintenance_work_mem | 64MB | 256MB-1GB | VACUUM, CREATE INDEX memory |
| wal_buffers | -1 (auto) | 64MB | WAL write buffer |
-- Apply memory tuning for a 16GB RAM server
ALTER SYSTEM SET shared_buffers = '4GB';
ALTER SYSTEM SET effective_cache_size = '12GB';
ALTER SYSTEM SET work_mem = '32MB';
ALTER SYSTEM SET maintenance_work_mem = '512MB';
ALTER SYSTEM SET wal_buffers = '64MB';
-- Checkpoint tuning to reduce I/O spikes
ALTER SYSTEM SET checkpoint_completion_target = '0.9';
ALTER SYSTEM SET max_wal_size = '2GB';
ALTER SYSTEM SET min_wal_size = '512MB';
-- Reload configuration (no restart needed for some)
SELECT pg_reload_conf();
Be cautious with `work_mem`. It is allocated per sort or hash operation, and complex queries can allocate it multiple times. Setting it to 256MB on a server handling 100 concurrent queries could consume 25GB of RAM on sort-heavy workloads.
Indexing Strategy
Indexes are the most common performance lever and the most commonly misapplied. Every index speeds up reads but slows down writes and consumes storage.
Index Types and When to Use Them
| Index Type | Best For | Example |
| B-tree (default) | Equality, range, sorting | `WHERE status = 'active' ORDER BY created_at` |
| GIN | Full-text search, JSONB, arrays | `WHERE tags @> '{postgresql}'` |
| GiST | Geometric, range types, nearest-neighbor | `WHERE location <-> point(40.7, -74.0) < 10` |
| BRIN | Large tables with natural ordering | `WHERE created_at > now() - interval '7 days'` |
| Hash | Equality only (rarely better than B-tree) | `WHERE session_id = 'abc123'` |
Composite Index Design
Order columns in composite indexes by selectivity and query pattern:
-- Query: WHERE tenant_id = $1 AND status = $2 ORDER BY created_at DESC
-- Good: matches WHERE clause order, covers ORDER BY
CREATE INDEX idx_services_tenant_status_created
ON services (tenant_id, status, created_at DESC);
-- Partial index for active records only (smaller, faster)
CREATE INDEX idx_services_active
ON services (tenant_id, created_at DESC)
WHERE status = 'active';
-- Covering index to avoid table lookups
CREATE INDEX idx_users_email_covering
ON users (email) INCLUDE (username, created_at);
Finding and Removing Unused Indexes
-- Find indexes that have never been scanned
SELECT
schemaname,
tablename,
indexname,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
idx_scan AS times_used
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexrelid NOT IN (
SELECT conindid FROM pg_constraint
WHERE contype IN ('p', 'u')
)
ORDER BY pg_relation_size(indexrelid) DESC;
Run this query after at least 2-4 weeks of production traffic to get representative usage data. Drop unused indexes to reduce write amplification and storage.
Query Plan Discipline
Use `EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)` on every slow query. The planner output tells you exactly what PostgreSQL is doing and why.
What to Look For
-- Analyze a slow query with full detail
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT s.name, s.status, u.email
FROM services s
JOIN users u ON u.id = s.user_id
WHERE s.tenant_id = 42
AND s.status = 'active'
ORDER BY s.created_at DESC
LIMIT 20;
Red flags in query plans:
- Seq Scan on large tables: Missing or unused index
- Rows estimated vs actual differ by 10x+: Stale statistics, run ANALYZE
- Sort with disk spill: `work_mem` too low or sort on non-indexed column
- Nested Loop with high loop count: Consider hash or merge join hints
- Bitmap Heap Scan with lossy blocks: Index returns too many rows
Statistics and Planner Accuracy
-- Increase statistics target for skewed columns
ALTER TABLE services ALTER COLUMN status SET STATISTICS 1000;
ALTER TABLE services ALTER COLUMN tenant_id SET STATISTICS 500;
ANALYZE services;
-- Check correlation (1.0 = perfectly ordered on disk)
SELECT attname, correlation
FROM pg_stats
WHERE tablename = 'services'
ORDER BY abs(correlation) DESC;
Columns with high correlation (close to 1.0 or -1.0) benefit from BRIN indexes. Columns with low correlation benefit from B-tree indexes.
Connection Pooling
PostgreSQL creates a new OS process for each connection. At 200+ connections, context switching and memory overhead degrade performance significantly. Connection pooling is mandatory for web applications.
PgBouncer Configuration
<h1>pgbouncer.ini</h1>
[databases]
app_db = host=127.0.0.1 port=5432 dbname=arxentra
[pgbouncer]
listen_port = 6432
listen_addr = 0.0.0.0
<h1>Pool mode: transaction is safest for web apps</h1>
pool_mode = transaction
<h1>Pool sizing</h1>
default_pool_size = 25
max_client_conn = 400
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
<h1>Timeouts</h1>
server_idle_timeout = 300
client_idle_timeout = 600
query_timeout = 30
<h1>Logging</h1>
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
stats_period = 60
Size your pool based on: `pool_size = (CPU cores * 2) + effective_spindle_count`. For a 4-core server with NVMe storage, start with 10-15 connections to PostgreSQL and measure from there. More connections does not mean more throughput; after saturation, it means more contention.
Monitoring Connection Health
-- Current connection state
SELECT state, count(*)
FROM pg_stat_activity
WHERE datname = 'arxentra'
GROUP BY state;
-- Long-running queries (potential lock holders)
SELECT pid, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'
AND now() - query_start > interval '5 seconds'
ORDER BY duration DESC;
Vacuum and Bloat Control
PostgreSQL MVCC creates dead tuples on every UPDATE and DELETE. Without regular vacuuming, tables bloat, indexes grow, and queries slow down. Autovacuum handles this automatically, but the defaults are tuned for small databases.
Autovacuum Tuning for High-Write Tables
-- Aggressive autovacuum for the services table
ALTER TABLE services SET (
autovacuum_vacuum_threshold = 50,
autovacuum_vacuum_scale_factor = 0.05,
autovacuum_analyze_threshold = 50,
autovacuum_analyze_scale_factor = 0.02,
autovacuum_vacuum_cost_delay = 10
);
-- Monitor bloat ratio
SELECT
relname,
n_live_tup,
n_dead_tup,
round(n_dead_tup::numeric / greatest(n_live_tup, 1) * 100, 1) AS dead_pct,
last_vacuum,
last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;
Watch for long-running transactions that prevent vacuum from reclaiming space. A single idle-in-transaction session can cause unbounded bloat across the entire database.
For platform context and deployment options, review the Cloud Computing page and Services Overview. For targeted tuning help, reach out via Contact.
Frequently Asked Questions
How often should I run ANALYZE on production tables?
Autovacuum runs ANALYZE automatically based on the `autovacuum_analyze_scale_factor` setting (default 10% of table rows changed). For tables with skewed distributions or frequent bulk inserts, lower this to 2-5% or run `ANALYZE table_name` after bulk operations. Stale statistics are the number one cause of bad query plans.
Should I use connection pooling even with a small number of users?
Yes. Even with 20 concurrent users, connection pooling improves performance by reusing established connections instead of creating and destroying OS processes. In transaction mode, PgBouncer can serve 200 application connections through 15 PostgreSQL backend connections with no perceptible latency increase.
What is the best index for a multi-tenant application?
Put `tenant_id` as the first column in composite indexes used for tenant-scoped queries. Partial indexes filtered by status or type are highly effective when most queries target a subset of rows. Monitor `pg_stat_user_indexes` to verify indexes are actually being used by the planner.
How do I detect table bloat before it causes problems?
Monitor the `n_dead_tup / n_live_tup` ratio from `pg_stat_user_tables`. A dead tuple percentage above 20% indicates vacuum is not keeping up. Also monitor table size growth relative to live row count. The `pgstattuple` extension provides exact bloat measurements.
When should I consider partitioning?
Partitioning helps when tables exceed 50-100 million rows and queries consistently filter on the partition key (typically time-based). Range partitioning by month or week is the most common pattern for web applications. Partition pruning eliminates entire partitions from query plans, reducing I/O dramatically.
Final Takeaway
Consistent measurement and iterative tuning beat one-time heroic optimization. Set up monitoring for query performance, connection usage, vacuum health, and bloat. Tune memory, add targeted indexes, pool connections, and ensure autovacuum keeps up with your write rate. Review quarterly as traffic patterns evolve.