Labs ICT
⭐ Pro Login

Performance Tuning

As your data grows and your application gets more users, PostgreSQL performance becomes critical. Tuning is not just about making things faster — it is about using your hardware efficiently and keeping response times consistent under load.

Configuration Tuning

The main configuration file is postgresql.conf. Here are the most impactful settings: 

-- Memory settings
shared_buffers = '256MB'           -- 25% of total RAM
effective_cache_size = '1GB'       -- 75% of total RAM
work_mem = '16MB'                  -- Per-operation sort hash memory
maintenance_work_mem = '256MB'     -- Memory for VACUUM, CREATE INDEX

-- Write performance
wal_buffers = '16MB'
checkpoint_completion_target = 0.9
max_wal_size = '1GB'

-- Query planner
random_page_cost = 1.1             -- Lower for SSDs
effective_io_concurrency = 200     -- Higher for SSDs

These are starting points. Adjust based on your specific workload and available hardware. Never change settings you do not understand.

Connection Pooling

Each PostgreSQL connection uses memory. For applications with many connections, use a connection pooler:

  • PgBouncer — Lightweight, widely used connection pooler
  • Pgpool-II — More features including replication and load balancing

A typical setup allows the application to open many connections to the pooler, which maintains a smaller number of actual PostgreSQL connections. This dramatically reduces memory usage.

VACUUM and ANALYZE

PostgreSQL uses MVCC, which means old versions of rows accumulate over time. VACUUM cleans them up: 

-- Manual vacuum
VACUUM students;

-- Full vacuum (reclaims space but locks the table)
VACUUM FULL students;

-- Analyze table statistics for query planner
ANALYZE students;

-- Vacuum and analyze together
VACUUM ANALYZE students;

Autovacuum is enabled by default and handles most cases. But for heavy write workloads, you may need to tune autovacuum settings or run manual vacuums.

Query Optimization

Use EXPLAIN ANALYZE to find slow queries: 

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT s.first_name, c.name
FROM students s
JOIN enrollments e ON s.id = e.student_id
JOIN courses c ON e.course_id = c.id
WHERE c.name = 'Database Systems';

Look for:

  • Sequential scans on large tables — add an index
  • High cost operations — optimize the query or add indexes
  • Nested loops with high row estimates — check statistics

Table Partitioning

For very large tables, partitioning splits data into smaller, more manageable pieces: 

CREATE TABLE orders (
  id SERIAL,
  order_date DATE,
  amount NUMERIC(10,2)
) PARTITION BY RANGE (order_date);

CREATE TABLE orders_2024 PARTITION OF orders
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

CREATE TABLE orders_2025 PARTITION OF orders
  FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

Queries that filter by order_date only scan the relevant partition, which is much faster than scanning the entire table.

Monitoring Queries

Keep an eye on these system views: 

-- Slow queries
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Table bloat
SELECT relname, n_dead_tup, n_live_tup
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

-- Index usage
SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

Regularly monitoring these views helps you catch performance issues before they become problems.

← Previous Roles & Permissions