Does lowering autovacuum_vacuum_scale_factor hurt write performance?

It increases how often autovacuum runs, which adds background I/O. Tuning autovacuum_vacuum_cost_delay and autovacuum_vacuum_cost_limit throttles that I/O so OLTP workloads are not noticeably affected. The default cost delay of 20ms is usually too conservative; 2ms with a higher cost limit gives faster vacuums with similar per-operation overhead.

When should I use a partial index instead of a full-table index?

Use a partial index (with a WHERE clause) when a large fraction of rows are never queried — for example, indexing only active or non-deleted rows. The index is smaller, cheaper to maintain, and the planner will prefer it for matching queries.

Why does pg_stat_statements show the same query with different parameter values as one row?

pg_stat_statements normalizes queries by replacing literals with placeholders, so SELECT * FROM users WHERE id = 1 and id = 2 collapse into a single entry. This is intentional; it lets you see aggregate cost across all executions of the same query shape.

Can I enable parallel query for a specific table only?

Yes. Set the table's parallel_workers storage option with ALTER TABLE t SET (parallel_workers = 4). The planner treats this as a strong hint for the worker count for that table regardless of the global min_parallel_table_scan_size threshold.

How do I tell if table bloat is actually hurting performance versus just wasting disk?

Bloat hurts performance when it forces sequential scans to read many dead-tuple pages or when index scans traverse bloated index levels. Use EXPLAIN (ANALYZE, BUFFERS) and watch Buffers: shared hit/read counts. High read counts on a heavily-updated table after a restart usually point to bloat. The pgstattuple extension gives exact bloat percentages per relation.

PostgreSQL Performance Tuning

A default PostgreSQL installation ships with conservative settings designed to run on modest hardware without breaking anything. In production, those defaults quietly strangle throughput. This guide walks through four high-impact tuning areas: query-level visibility with pg_stat_statements, keeping the heap clean with autovacuum, choosing the right indexes, and unlocking parallelism for analytical queries. Commands target PostgreSQL 15/16 on current LTS releases, though most settings apply back to PG 13.

Enable pg_stat_statements

pg_stat_statements is a core contrib module that tracks execution statistics for every normalized query. You cannot tune what you cannot measure, so this comes first.

Load the module

Edit postgresql.conf (location varies by distro) and add the module to the preload list.

# Debian/Ubuntu
sudo -u postgres psql -c "SHOW config_file;"
# Typically: /etc/postgresql/16/main/postgresql.conf

# Fedora/RHEL/Rocky
# Typically: /var/lib/pgsql/data/postgresql.conf

sudo sed -i "s/#shared_preload_libraries = ''/shared_preload_libraries = 'pg_stat_statements'/" \
  /etc/postgresql/16/main/postgresql.conf

Then tune the module's own parameters in the same file:

cat >> /etc/postgresql/16/main/postgresql.conf << 'EOF'
pg_stat_statements.max = 10000
pg_stat_statements.track = all
pg_stat_statements.track_utility = off
EOF

Restart the server (a reload is not enough for shared_preload_libraries):

sudo systemctl restart postgresql   # Debian/Ubuntu
sudo systemctl restart postgresql-16  # Fedora/RHEL/Rocky
sudo systemctl restart postgresql   # Arch

Activate the extension and query it

sudo -u postgres psql -d mydb -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"

Find your top ten slowest queries by total execution time:

sudo -u postgres psql -d mydb << 'SQL'
SELECT
  round(total_exec_time::numeric, 2) AS total_ms,
  calls,
  round(mean_exec_time::numeric, 2)  AS mean_ms,
  round(stddev_exec_time::numeric,2) AS stddev_ms,
  left(query, 80)                    AS query_snippet
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
SQL

Reset statistics after a tuning round so you get a clean baseline:

sudo -u postgres psql -c "SELECT pg_stat_statements_reset();"

Tune Autovacuum

Autovacuum reclaims dead row versions (the MVCC bloat every UPDATE and DELETE creates) and updates the visibility map, which query planner and index-only scans depend on. The default thresholds are tuned for small databases; large tables get vacuumed far too infrequently.

Understand the trigger math

Vacuum fires when dead tuples exceed autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × reltuples. With the default scale factor of 0.2, a 50-million-row table needs 10 million dead tuples before vacuum kicks in. That is too many.

Cluster-wide baseline settings

Add these to postgresql.conf. Adjust autovacuum_max_workers based on available cores.

cat >> /etc/postgresql/16/main/postgresql.conf << 'EOF'
autovacuum_max_workers = 4
autovacuum_naptime = 30s
autovacuum_vacuum_scale_factor = 0.02
autovacuum_analyze_scale_factor = 0.01
autovacuum_vacuum_cost_delay = 2ms
autovacuum_vacuum_cost_limit = 400
EOF

Reload (no restart needed for these parameters):

sudo systemctl reload postgresql
# or inside psql:
-- SELECT pg_reload_conf();

Per-table overrides for high-churn tables

Storage-level settings on individual tables override the cluster defaults and are preserved across pg_dump restores.

sudo -u postgres psql -d mydb << 'SQL'
ALTER TABLE events
  SET (autovacuum_vacuum_scale_factor = 0.005,
       autovacuum_vacuum_cost_delay = 1);
SQL

Monitor vacuum activity

sudo -u postgres psql -d mydb << 'SQL'
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,
  last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 15;
SQL

Indexing Strategy

Indexes speed reads and slow writes. The goal is to have indexes that are actually used, sized correctly, and of the right type.

Find unused and missing indexes

sudo -u postgres psql -d mydb << 'SQL'
-- Unused indexes (no scans since last stats reset)
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexname NOT LIKE '%pkey%'
ORDER BY pg_relation_size(indexrelid) DESC;
SQL

sudo -u postgres psql -d mydb << 'SQL'
-- Tables doing sequential scans that might benefit from an index
SELECT relname, seq_scan, seq_tup_read, idx_scan
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 10;
SQL

Build indexes concurrently in production

CREATE INDEX CONCURRENTLY does not hold a table lock for the full build. It takes longer and cannot run inside a transaction block, but it is the only safe option on a live system.

sudo -u postgres psql -d mydb << 'SQL'
CREATE INDEX CONCURRENTLY idx_events_user_created
  ON events (user_id, created_at DESC)
  WHERE deleted_at IS NULL;
SQL

Choose the right index type

B-tree (default): equality, range, ORDER BY. Right choice 90% of the time.
BRIN: very large, naturally ordered tables (e.g., time-series with an append-only insert pattern). Tiny on disk, less precise.
GIN: JSONB, full-text search, array containment operators.
GiST / SP-GiST: geometric types, PostGIS, nearest-neighbour searches.
Hash: equality-only lookups; rarely worth it over B-tree since PG 10 made them crash-safe.

sudo -u postgres psql -d mydb << 'SQL'
-- BRIN example for a large append-only log table
CREATE INDEX CONCURRENTLY idx_logs_ts_brin
  ON logs USING BRIN (logged_at) WITH (pages_per_range = 64);

-- GIN for JSONB
CREATE INDEX CONCURRENTLY idx_events_payload
  ON events USING GIN (payload jsonb_path_ops);
SQL

Keep indexes healthy

B-tree indexes accumulate bloat after heavy deletes. Check bloat with the pgstattuple extension and rebuild when avg_leaf_density drops below ~60%.

sudo -u postgres psql -d mydb << 'SQL'
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstatindex('idx_events_user_created');
SQL

Parallel Query Execution

PostgreSQL can split sequential scans, joins, and aggregations across multiple worker processes. This helps analytic workloads but adds overhead for OLTP queries that already use indexes.

Core parallel settings

cat >> /etc/postgresql/16/main/postgresql.conf << 'EOF'
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_worker_processes = 16
parallel_tuple_cost = 0.1
parallel_setup_cost = 500
min_parallel_table_scan_size = 8MB
min_parallel_index_scan_size = 512kB
EOF

Reload for the cost parameters; max_worker_processes requires a restart.

sudo systemctl restart postgresql

Verify the planner uses parallel plans

sudo -u postgres psql -d mydb << 'SQL'
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT user_id, count(*) FROM events GROUP BY user_id;
SQL

Look for Gather or Gather Merge nodes in the plan output. If they are absent on a large table scan, the table may be below min_parallel_table_scan_size, or parallelism was disabled by a function in the query.

Force or disable parallel for testing

sudo -u postgres psql -d mydb << 'SQL'
-- Force parallel (session only, for testing)
SET max_parallel_workers_per_gather = 4;
SET force_parallel_mode = regress;  -- PG 15 and earlier; removed in PG 16

-- Disable parallel on a specific slow query
SET max_parallel_workers_per_gather = 0;
SQL

Shared Buffers and Memory Settings

These are not the focus of this guide, but parallel and autovacuum tuning interact with memory. A quick summary of the most impactful values:

shared_buffers: set to 25% of total RAM as a starting point.
effective_cache_size: set to 50–75% of RAM; this is a planner hint, not an allocation.
work_mem: memory per sort/hash operation per parallel worker. Start at 32–64 MB and raise only for sessions doing heavy analytics. Multiply by max connections × parallel workers before raising cluster-wide.
maintenance_work_mem: used by VACUUM, CREATE INDEX, and autovacuum workers. 256 MB–1 GB is reasonable on servers with 16+ GB RAM.

Verification

sudo -u postgres psql << 'SQL'
SELECT name, setting, unit, source
FROM pg_settings
WHERE name IN (
  'shared_preload_libraries',
  'autovacuum_vacuum_scale_factor',
  'max_parallel_workers_per_gather',
  'work_mem',
  'shared_buffers'
);
SQL

The source column confirms whether a value came from postgresql.conf, an ALTER SYSTEM call, or the compiled default. Any value still showing default after your edits means the config file change did not take effect — double-check the file path and that a reload or restart was performed.

Troubleshooting

pg_stat_statements shows no rows: the extension was not created in the target database, or shared_preload_libraries was not set before the last restart. Check SHOW shared_preload_libraries; inside psql.
Autovacuum still not running: check pg_stat_activity for autovacuum workers. If autovacuum = off in pg_settings, it was explicitly disabled. Also check per-table storage options with SELECT reloptions FROM pg_class WHERE relname = 'mytable';.
CONCURRENTLY index build fails: the index is left in an invalid state. Drop it with DROP INDEX CONCURRENTLY idx_name; and rebuild. Check pg_indexes for rows with the index marked invalid.
Parallel plans absent after tuning: some functions are marked PARALLEL UNSAFE by default. Check with SELECT proname, proparallel FROM pg_proc WHERE proname = 'yourfunction';. You can change this to SAFE only if the function genuinely has no side effects.
OOM killer targeting PostgreSQL after raising work_mem: work_mem is allocated per sort node per connection. Under load it multiplies fast. Lower it and use SET work_mem at the session level for heavy queries instead.