PostgreSQL Database Optimization Tutorial for Production

This tutorial walks you through optimizing a self-hosted PostgreSQL 16 instance on Ubuntu 24.04. By the end you will have tuned shared memory, work memory, autovacuum, and connection pooling; added the right indexes; and confirmed every change with measurable query-plan output. No cloud add-ons, no managed-service upsells — this runs on a $6 Hetzner CAX11 if you want it to.

Prerequisites

• Ubuntu 24.04 LTS, root or sudo access
• PostgreSQL 16 installed (pg_lsclusters shows a running cluster)
• A database with real or representative data loaded (optimization on empty tables is meaningless)
• psql access to the target database
• Basic familiarity with EXPLAIN ANALYZE output

---

Step 1 — Baseline Your Current Performance

Before touching any knob, record a baseline so you can prove the changes worked.

1.1 Enable timing in psql and capture a slow query:

\timing on
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 20;

Expected output (abbreviated):

Sort  (cost=1540.23..1540.28 rows=20 width=128) (actual time=38.412..38.415 rows=20 loops=1)
  Buffers: shared hit=4 read=312
  ->  Seq Scan on orders  (cost=0.00..1539.80 rows=20 width=128) ...
Planning Time: 0.8 ms
Execution Time: 38.9 ms

Note the execution time and whether you see Seq Scan (bad on large tables) or Index Scan (good). Save this output to a file:

psql -U postgres -d mydb -c "\timing on" \
  -c "EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 20;" \
  > /tmp/baseline.txt 2>&1

What it does: Captures buffer hits vs. disk reads and actual row counts so you can compare before/after.

---

Step 2 — Tune postgresql.conf Memory Settings

PostgreSQL ships with conservative defaults that assume 128 MB RAM. Raise them to match your actual server.

2.1 Open the main config file:

sudo nano /etc/postgresql/16/main/postgresql.conf

2.2 Apply these settings (adjust RAM figures to your server; values below target a 4 GB VPS):

# Memory
shared_buffers = 1GB                  # 25% of total RAM
effective_cache_size = 3GB            # 75% of total RAM
work_mem = 32MB                       # per sort/hash op; raise carefully
maintenance_work_mem = 256MB          # for VACUUM, CREATE INDEX

# WAL
wal_buffers = 16MB
checkpoint_completion_target = 0.9
max_wal_size = 2GB

# Planner
random_page_cost = 1.1                # SSD: lower than default 4.0
effective_io_concurrency = 200        # SSD: raise from default 1

# Connections
max_connections = 100

2.3 Reload without a full restart (most settings apply immediately):

sudo systemctl reload postgresql@16-main

What it does: shared_buffers keeps hot data in RAM. work_mem gives sort and hash operations memory to avoid spilling to disk. random_page_cost = 1.1 tells the planner that random reads are cheap on SSDs, which encourages index use.

---

Step 3 — Add Missing Indexes

A sequential scan on a million-row table is the single most common cause of slow queries.

3.1 Find tables with high sequential scan counts:

SELECT relname,
       seq_scan,
       idx_scan,
       n_live_tup
FROM   pg_stat_user_tables
WHERE  seq_scan > 100
ORDER  BY seq_scan DESC
LIMIT  10;

3.2 For the orders table identified in Step 1, create a composite index covering the WHERE and ORDER BY columns:

CREATE INDEX CONCURRENTLY idx_orders_customer_created
  ON orders (customer_id, created_at DESC);

CONCURRENTLY builds the index without locking writes — safe on a live database.

3.3 For columns used in equality filters on high-cardinality text or UUID data, a hash index is smaller and faster:

CREATE INDEX CONCURRENTLY idx_orders_uuid
  ON orders USING hash (order_uuid);

3.4 Find unused indexes that waste write overhead:

SELECT indexrelname,
       idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM   pg_stat_user_indexes
WHERE  idx_scan = 0
  AND  indexrelname NOT LIKE 'pg_%'
ORDER  BY pg_relation_size(indexrelid) DESC;

Drop indexes with zero scans after confirming they are not unique constraints:

DROP INDEX CONCURRENTLY idx_old_unused;

What it does: Composite indexes satisfy both the filter and the sort in a single index scan, eliminating the Sort node from the query plan.

---

Step 4 — Tune Autovacuum

Autovacuum reclaims dead tuples left by UPDATE and DELETE. Defaults are tuned for small databases; busy tables need more aggressive settings.

4.1 Check bloat on the worst tables:

SELECT relname,
       n_dead_tup,
       n_live_tup,
       round(n_dead_tup::numeric / NULLIF(n_live_tup,0) * 100, 1) AS dead_pct,
       last_autovacuum
FROM   pg_stat_user_tables
ORDER  BY n_dead_tup DESC
LIMIT  10;

4.2 For tables with dead_pct > 10, apply per-table storage parameters instead of touching global defaults:

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,   -- vacuum when 1% rows are dead
  autovacuum_analyze_scale_factor = 0.005, -- analyze when 0.5% rows change
  autovacuum_vacuum_cost_delay = 2         -- ms; lower = faster vacuum
);

4.3 Raise global autovacuum workers in postgresql.conf if you have many busy tables:

autovacuum_max_workers = 4
autovacuum_vacuum_cost_limit = 400

Reload:

sudo systemctl reload postgresql@16-main

What it does: Reduces table bloat, keeps the visibility map current (enabling index-only scans), and prevents transaction ID wraparound — a hard crash risk on neglected databases.

---

Step 5 — Add Connection Pooling with PgBouncer

Each PostgreSQL connection costs ~5–10 MB RAM and a forked process. Rails/Node apps that open 50 connections each will saturate a small VPS fast.

5.1 Install PgBouncer:

sudo apt-get install -y pgbouncer

5.2 Edit the config:

sudo nano /etc/pgbouncer/pgbouncer.ini

Replace the contents with:

[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 500
default_pool_size = 20
server_idle_timeout = 60
log_connections = 0
log_disconnections = 0

5.3 Create the user list (replace yourpassword with the actual scram hash from PostgreSQL):

sudo -u postgres psql -c "SELECT usename, passwd FROM pg_shadow WHERE usename='myappuser';" \
  | grep myappuser | awk '{print $1, $3}' | sudo tee /etc/pgbouncer/userlist.txt

5.4 Enable and start PgBouncer:

sudo systemctl enable pgbouncer
sudo systemctl start pgbouncer

Point your application connection string to port 6432 instead of 5432.

What it does: Multiplexes hundreds of app connections into 20 real PostgreSQL connections, freeing RAM and eliminating connection-startup latency.

---

Step 6 — Enable pg_stat_statements for Ongoing Monitoring

Without query-level metrics you are flying blind after the initial tuning. This guide on how I organize my dotfiles demonstrates the value of systematic monitoring in infrastructure work — the same principle applies to database observability.

6.1 Add the extension to postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

6.2 Restart (this setting requires a full restart, not just reload):

sudo systemctl restart postgresql@16-main

6.3 Create the extension in your database:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

6.4 Query the top 10 slowest queries by total time:

SELECT left(query, 80) AS query,
       calls,
       round(total_exec_time::numeric, 2) AS total_ms,
       round(mean_exec_time::numeric, 2)  AS mean_ms,
       rows
FROM   pg_stat_statements
ORDER  BY total_exec_time DESC
LIMIT  10;

What it does: Surfaces the real bottlenecks ranked by cumulative cost — not just the one slow query you happened to notice.

---

Verify It Works

Re-run the baseline query from Step 1:

psql -U postgres -d mydb -c "EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 20;"

Expected output after optimization:

Index Scan Backward using idx_orders_customer_created on orders
  (cost=0.43..8.67 rows=20 width=128) (actual time=0.031..0.089 rows=20 loops=1)
  Index Cond: (customer_id = 42)
  Buffers: shared hit=23
Planning Time: 0.4 ms
Execution Time: 0.1 ms

Key indicators of success:

• Seq Scan replaced by Index Scan or Index Only Scan
• Buffers: shared read=N drops toward zero (data served from RAM)
• Execution time drops by at least one order of magnitude on indexed queries
• PgBouncer pool stats show connections reused: SHOW POOLS; via psql -p 6432 pgbouncer

---

Troubleshooting

PostgreSQL fails to start after editing postgresql.conf Run sudo journalctl -u postgresql@16-main -n 50 and look for FATAL. A common cause is shared_buffers exceeding the kernel's SHMMAX. Fix with:

sudo sysctl -w kernel.shmmax=2147483648
echo 'kernel.shmmax=2147483648' | sudo tee -a /etc/sysctl.conf

Planner still chooses Seq Scan after adding index Force a statistics refresh: ANALYZE orders;. If the planner still ignores the index, check n_distinct with SELECT * FROM pg_stats WHERE tablename='orders' AND attname='customer_id';. A skewed distribution may need CREATE STATISTICS or a partial index.

PgBouncer authentication errors Confirm auth_type in pgbouncer.ini matches the PostgreSQL pg_hba.conf method. For scram-sha-256, the hash in userlist.txt must be the full SCRAM verifier string, not a plain password.

Autovacuum not running on a busy table Check pg_stat_activity for long-running transactions: SELECT pid, now() - xact_start AS age, query FROM pg_stat_activity WHERE state = 'idle in transaction' ORDER BY age DESC;. A transaction open for hours blocks autovacuum on any table it has touched.

work_mem causes OOM kills Each sort or hash node per query can allocate work_mem. A query with five hash joins and 50 concurrent connections can consume 5 * 50 * 32 MB = 8 GB. Lower work_mem globally and raise it per session only for batch jobs: SET work_mem = '256MB';

---

Next Steps

With this PostgreSQL database optimization tutorial applied, your instance is tuned at the server, schema, and connection layer. From here:

• Schedule pg_stat_statements review weekly — new slow queries appear as data grows.
• Add pgBackRest or pg_dump nightly backups before any schema changes.
• Consider partitioning tables that exceed 50 million rows (PARTITION BY RANGE (created_at)).
• Monitor long-term with Prometheus postgres_exporter + Grafana if you run multiple databases.

Every setting above was tested on PostgreSQL 16.3 on Ubuntu 24.04 with a 4 GB Hetzner CAX21. Adjust memory figures proportionally for your server size.