PostgreSQL Performance Tuning That Survives Production

    A PostgreSQL performance issue rarely starts with one bad setting.

In production, it usually looks like this: the application team says the database is slow, CPU is not always high, storage graphs look confusing, and nobody changed anything “major”. Then we check deeper and find long transactions, dead tuples, stale statistics, unused indexes, chatty application queries, or checkpoint pressure.

That is why PostgreSQL tuning should not begin with random parameter changes. It should begin with evidence.

This post covers practical areas that DBAs should check before touching memory or kernel settings: SQL patterns, indexing, VACUUM, ANALYZE, connection pressure, bulk loading, and storage behavior. I will also call out a few old tuning tips that should not be blindly used on modern PostgreSQL versions.

The goal is simple: tune PostgreSQL like a production system, not like a copied checklist.

1. First Check the Workload, Not the Parameter File

Before tuning PostgreSQL, understand what kind of workload is hitting it.

Many systems become slow because OLTP traffic, reporting queries, batch jobs, exports, and queue processing all run on the same primary database. PostgreSQL can handle heavy workloads, but it does not like uncontrolled mixed pressure.

A few patterns I always check early:

• Long-running transactions
• Idle sessions inside transactions
• Too many active connections
• Reporting queries on the primary
• Frequently updated singleton rows
• Application-side loops firing thousands of small SQLs
• Large loads done with INSERT instead of COPY

Check long transactions & Idle transactions as well :

// Long running
SELECT pid,
       usename,
       application_name,
       state,
       now() - xact_start AS transaction_age,
       wait_event_type,
       wait_event,
       left(query, 120) AS query_text
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY transaction_age DESC;
Check idle transactions:

// idle
SELECT pid,
       usename,
       application_name,
       now() - state_change AS idle_time,
       left(query, 120) AS query_text
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY idle_time DESC;

This is important because old transactions can stop VACUUM from cleaning dead rows. In Oracle, DBAs think heavily about undo. In PostgreSQL, DBAs must think about old row versions sitting inside the table until VACUUM can remove them.

Production perspective

If table size is growing, autovacuum is running, and dead tuples are not reducing, do not immediately increase autovacuum workers. First check whether an old transaction is holding the cleanup back.

2. Bad SQL Can Waste Good Hardware

One bad SQL is visible. One small bad SQL executed 5 million times is more dangerous.

For PostgreSQL, pg_stat_statements is one of the first extensions I want enabled in production.

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

After enabling it in shared_preload_libraries, check top SQL by total time:

SELECT calls,
       round(total_exec_time::numeric, 2) AS total_ms,
       round(mean_exec_time::numeric, 2) AS avg_ms,
       rows,
       left(query, 150) AS sample_query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
Also check high-frequency SQL:

SELECT calls,
       round(mean_exec_time::numeric, 2) AS avg_ms,
       left(query, 150) AS sample_query
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 10;

Some SQL patterns deserve suspicion like below:

Unanchored LIKE

WHERE remarks LIKE '%failed payment%'

For text search, use full text search or pg_trgm depending on the requirement.

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX CONCURRENTLY idx_customer_notes_trgm

ON customer_notes

USING gin(note_text gin_trgm_ops);

Huge IN lists

Instead of sending thousands of values in an IN clause, load them into a temporary table and join.

CREATE TEMP TABLE temp_ids(id bigint) ON COMMIT DROP;

-- load IDs using COPY or application batch insert

SELECT o.*

FROM orders o

JOIN temp_ids t ON t.id = o.order_id;

SELECT * everywhere

This looks harmless until the table becomes wide or the query runs thousands of times per minute.

Better:

SELECT order_id, status, created_at
FROM orders
WHERE customer_id = $1
ORDER BY created_at DESC
LIMIT 20;

// Use EXPLAIN with buffers when needed:

EXPLAIN (ANALYZE, BUFFERS)
SELECT order_id, status, created_at
FROM orders
WHERE customer_id = 1001
ORDER BY created_at DESC
LIMIT 20;

Be careful with EXPLAIN ANALYZE in production. It executes the query.

3. Indexes Are Not Free

Indexes can save a system. They can also quietly slow it down.

Every extra index has a cost during INSERT, UPDATE, DELETE, VACUUM, backup, restore, and replication. So the question is not “can we create an index?” The question is “does this index support a real workload?”

Find tables with heavy sequential reads & indexes which are unused.

# SELECT schemaname,
       relname,
       seq_scan,
       seq_tup_read,
       idx_scan,
       n_live_tup,
       n_dead_tup
FROM pg_stat_user_tables
ORDER BY seq_tup_read DESC
LIMIT 20;
Find indexes that look unused:

# 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
LIMIT 20;

Do not drop an index only because idx_scan = 0.
Check uptime, reporting schedules, constraints, and whether the index is used on a standby.

Create production indexes with less blocking:

CREATE INDEX CONCURRENTLY idx_orders_customer_created

ON orders (customer_id, created_at DESC);

For partial workloads, partial indexes are often cleaner:

CREATE INDEX CONCURRENTLY idx_orders_failed_created

ON orders (created_at DESC)

WHERE status = 'FAILED';

This is useful when only a small subset of the table is queried often.

Caveat

CREATE INDEX CONCURRENTLY takes longer and cannot run inside a transaction block. If it fails, check for invalid indexes.

SELECT c.relname AS index_name,
       i.indisvalid,
       i.indisready
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE NOT i.indisvalid OR NOT i.indisready;

4. VACUUM Is Core Maintenance, Not Housekeeping

PostgreSQL uses MVCC. When rows are updated or deleted, old row versions remain in the table until VACUUM can clean them.

This is one of the biggest mindset changes for Oracle DBAs. Oracle depends heavily on undo for read consistency. PostgreSQL keeps old row versions in the heap, so table cleanup becomes a daily operational topic.

Check dead tuples and its percentage as well :

SELECT schemaname,
       relname,
       n_live_tup,
       n_dead_tup,
       last_autovacuum,
       last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;
Check dead tuple percentage:

SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(
         n_dead_tup::numeric /
         nullif(n_live_tup + n_dead_tup, 0) * 100, 2
       ) AS dead_pct
FROM pg_stat_user_tables
WHERE n_live_tup + n_dead_tup > 0
ORDER BY dead_pct DESC
LIMIT 20;

Manual cleanup after heavy change:

VACUUM (ANALYZE, VERBOSE) public.orders;

Do not jump to VACUUM FULL casually. Regular VACUUM makes space reusable inside the table. VACUUM FULL rewrites the table and takes a strong lock.

For large busy tables, tune autovacuum per table:

ALTER TABLE public.orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 5000,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_analyze_threshold = 5000
);

Production view

For a table with hundreds of millions of rows, default autovacuum thresholds may react too late. Per-table tuning is often better than changing global settings aggressively.

5. ANALYZE: The Planner Needs Fresh Statistics

Many PostgreSQL problems after migration, purge, or bulk load are not caused by missing indexes. They are caused by stale statistics.

Run ANALYZE after major data movement:

ANALYZE public.orders;

Or with cleanup:

VACUUM (ANALYZE) public.orders;

When estimated rows and actual rows differ heavily in an execution plan, statistics may be weak.

For skewed columns, increase statistics target only where needed:

ALTER TABLE public.orders

ALTER COLUMN status SET STATISTICS 1000;

ANALYZE public.orders;

For related columns, extended statistics can help:

CREATE STATISTICS st_orders_status_created

ON status, created_at

FROM public.orders;

ANALYZE public.orders;

 

Production perspective here ;

After a large archival job, always run ANALYZE on affected tables. Otherwise the planner may still think old data distribution exists.

6. Use COPY for Bulk Loads

For large data loads, row-by-row INSERT is usually a bad idea.

Use server-side COPY:

COPY staging_orders(order_id, customer_id, status, created_at)
FROM '/data/load/orders.csv'
WITH (FORMAT csv, HEADER true);

If the file is on the client machine, use psql:

psql -d appdb -c "\copy staging_orders FROM '/tmp/orders.csv' WITH (FORMAT csv, HEADER true)"

A safer production pattern:

1. Load into staging table
2. Validate row count and duplicates
3. Create needed indexes
4. Move data into target table
5. Run ANALYZE

Example:

INSERT INTO orders(order_id, customer_id, status, created_at)
SELECT order_id, customer_id, status, created_at
FROM staging_orders;

ANALYZE orders;

Caveat

UNLOGGED tables can speed up temporary loads, but they are not crash-safe. Use them only when the data can be recreated.

CREATE UNLOGGED TABLE staging_orders
AS
SELECT *
FROM orders
WITH NO DATA;

7. Connections and Memory Need Practical Control

PostgreSQL creates a backend process per connection. Too many connections can create memory pressure and context switching overhead.

Check connection usage by application :

SELECT application_name,
       usename,
       client_addr,
       state,
       count(*) AS sessions
FROM pg_stat_activity
GROUP BY application_name, usename, client_addr, state
ORDER BY sessions DESC;

Do not blindly increase max_connections. For many  OLTP systems, PgBouncer is a better fix.

Also be careful with work_mem. It is not a single global memory bucket. Multiple sessions and multiple plan nodes can consume it.

SHOW max_connections;

SHOW work_mem;

SHOW shared_buffers;

SHOW maintenance_work_mem;

Production view

If the host is under memory pressure, do not say “disable the OOM killer” and move on. Fix the cause: too many connections, bad SQL, oversized memory settings, or other processes on the database server.

PostgreSQL should ideally run on a dedicated host or dedicated VM with predictable memory and I/O.

8. Storage and OS Tuning: Avoid Copy-Paste Settings

Old tuning notes often mention noop, deadline, and cfq. On modern Linux systems, especially RHEL 8 and similar platforms, you may see schedulers such as mq-deadline, kyber, bfq, or none.

Check the active scheduler:

cat /sys/block/sdb/queue/scheduler

Example:

[mq-deadline] kyber bfq none

Do not change the scheduler because a random benchmark says so. Test it with your storage type, workload, and platform team.

For PostgreSQL, also watch WAL and checkpoint behavior.

SHOW checkpoint_timeout;

SHOW max_wal_size;

SHOW synchronous_commit;

Check checkpoint stats:

SELECT checkpoints_timed,
       checkpoints_req,
       checkpoint_write_time,
       checkpoint_sync_time,
       buffers_checkpoint
FROM pg_stat_bgwriter;

If checkpoints_req is increasing frequently, PostgreSQL may be checkpointing because WAL size is getting exhausted before time-based checkpoints.

For OS-level visibility:

iostat -x 2

vmstat 2

Point to note :  Commit slowness is often storage-related. WAL flush latency, synchronous replication, and checkpoint pressure can all show up as application latency.

9. Old Tuning Tips to Recheck

Some PostgreSQL tuning advice is version-specific.

The best example is stats_temp_directory. Older versions allowed moving statistics temporary files to a RAM-based filesystem. From PostgreSQL 15 onward, that parameter is removed.

So do not carry this into modern builds:

stats_temp_directory = '/dev/shm/pg_stat_tmp'

Also avoid copied kernel settings like:

vm.dirty_background_ratio = 0

vm.dirty_ratio = 0

These are not generic PostgreSQL best practices. Kernel tuning should be tested and agreed with the Linux/platform team.

During PostgreSQL upgrades, review old postgresql.conf files. A parameter that helped years ago may be obsolete today.

Production Incident : Autovacuum Was Not the Real Problem

Problem

An order table kept growing. Queries became slower. Monitoring showed many dead tuples. The first suggestion was to increase autovacuum settings.

Investigation

The DBA checked dead tuples:

SELECT relname,
       n_live_tup,
       n_dead_tup,
       last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'orders';

Then checked old transactions:

One application session was idle in transaction for hours.

Root Cause The old transaction was holding a snapshot. VACUUM was running, but it could not clean everything.

Fix After confirmation with the application team:

SELECT pg_terminate_backend(<pid>);

Then:

VACUUM (ANALYZE, VERBOSE) public.orders;

A guardrail was added:

ALTER SYSTEM SET idle_in_transaction_session_timeout = '10min';

SELECT pg_reload_conf();

Lesson Learnt Do not tune autovacuum before checking what is blocking it.

Quick Takeaways

• PostgreSQL tuning starts with workload behavior.
• Long transactions can block cleanup and create bloat.
• High-frequency small SQL can hurt more than one large query.
• Indexes improve reads but add write and maintenance cost.
• VACUUM and ANALYZE are production essentials.
• COPY is the right tool for large loads.
• Old tuning advice must be checked against PostgreSQL version and Linux platform.

Conclusion

PostgreSQL performance tuning is not about changing ten parameters and hoping for the best. It is about following the evidence.

Start with active sessions, long transactions, query frequency, execution plans, dead tuples, statistics, indexes, connections, WAL, checkpoints, and storage latency. Most production issues become clearer when these areas are checked in the right order.

For Oracle DBAs, the biggest shift is MVCC cleanup. PostgreSQL keeps old row versions in the table until VACUUM can remove them. That makes long transactions, autovacuum health, and table bloat daily operational topics.

Also, do not trust old tuning notes blindly. Some advice was valid for older PostgreSQL versions or older Linux kernels, but not for current production builds. stats_temp_directory is a good example. Old I/O scheduler recommendations are another.

A well-tuned PostgreSQL system is not the one with the most customized settings. It is the one where every change has a reason, every query has been measured, and every maintenance task is monitored.

Review your top SQL. Check dead tuples. Validate indexes. Watch checkpoints. Test bulk loads. And most importantly, tune from production evidence, not from memory.

FAQs

1. Should I disable autovacuum?

No. Tune it if needed, but do not disable it. Without VACUUM, dead tuples and transaction ID risk will become serious problems.

2. When should I run ANALYZE manually?

Run it after bulk loads, large deletes, migrations, archival jobs, or major data distribution changes.

3. Is a sequential scan always bad?

No. Sequential scans are normal for small tables or queries reading a large percentage of data. Check row estimates and buffer usage before blaming the scan.

4. Should I increase max_connections?

Not always. More connections can increase memory pressure. For OLTP systems, connection pooling is often better.

5. Is VACUUM FULL a normal maintenance command?

No. Use it carefully. It rewrites the table and requires a strong lock. Regular VACUUM is the normal cleanup operation.

Finally a quick comparison with Oracle and how PostgreSQL handles the MVCC

What was the most misleading PostgreSQL performance issue you handled?

Was it SQL, autovacuum, stale stats, storage, connection pressure, or an application transaction left open?

Share your experience. These real stories are more useful than any tuning checklist.