PostgreSQL Daily Operations: Connection Management, Slow Queries & VACUUM Maintenance

PostgreSQL issues in production are rarely due to database corruption but stem from three neglected daily tasks: connections creeping to the limit, a few slow queries dragging down the instance, and dead tuples bloating tables and indexes. These problems intertwine — full connections may be caused by slow queries holding them; VACUUM cannot clean dead tuples because a long‑running idle in transaction connection blocks the snapshot; table bloat then makes queries slower, which in turn holds connections longer. This article addresses each area with a focus on how to detect and what to do , targeting engineers who maintain self‑hosted PostgreSQL 13‑16 on Linux.

1. Connection Management: Understand Where Connections Go

Each Connection Is a Process

Unlike MySQL, every PostgreSQL client connection spawns a backend process forked from the postmaster. Each backend consumes several MB to tens of MB of memory (depending on shared_buffers, session caches, and query allocations). max_connections defaults to 100; raising it increases memory linearly and worsens lock contention under high concurrency, so “just increase it” is rarely the right answer. The parameter superuser_reserved_connections (default 3) reserves slots for superusers — setting it to 0 removes the last resort for admin access when the pool is full.

Classify Connections by State

First step: group connections by state rather than counting totals.

SELECT state, count(*)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state
ORDER BY 2 DESC;

Key states to watch:

idle : connection open but not executing. Harmless per se but consumes a max_connections slot, indicating oversized connection pools or clients not returning connections.

idle in transaction : transaction started but neither committed nor rolled back. Most dangerous — it blocks VACUUM from cleaning dead tuples. If also idle in transaction (aborted), an error occurred but the client didn’t handle it.

Find long‑lived idle connections:

SELECT pid, usename, application_name, client_addr, state,
       now() - state_change AS state_duration,
       now() - xact_start AS xact_duration,
       left(query, 80) AS last_query
FROM pg_stat_activity
WHERE backend_type = 'client backend'
  AND state <> 'active'
  AND state_change < now() - interval '5 minutes'
ORDER BY state_change;

application_name

is critical for identifying which service owns a connection; ensure every application sets it in the connection string (e.g., application_name=order-service). The backend_type filter (PG 10+) excludes background workers and walsenders.

Proactive Cleanup with Timeouts

PostgreSQL provides automatic termination parameters: idle_in_transaction_session_timeout (PG 9.6+): terminates sessions exceeding the threshold; default 0 (disabled). idle_session_timeout (PG 14+): terminates plain idle sessions; default disabled.

Set at session level first for testing:

SET idle_in_transaction_session_timeout = '5min';

Note: terminated sessions disconnect the client; the application must support reconnection. Verify client library behavior before enabling globally.

Choosing a Connection Pool

Two distinct roles:

Server‑side pool (PgBouncer / pgpool‑II) : sits between app and DB, multiplexing many app connections into few DB connections. PgBouncer’s transaction mode gives the highest reuse but historically had issues with prepared statements across transactions; newer versions support protocol‑level prepared statements — verify compatibility with your driver version.

Client‑side pool (HikariCP, psycopg pool, etc.) : reduces connection overhead within a single app process but does not reduce total connections across multiple replicas (e.g., 20 pods × 20 pool size = 400 DB connections).

Practical sizing: measure the stable concurrent query throughput at target latency, derive pool size from that, then set max_connections accordingly — not the reverse.

2. Slow Queries: From Statistics to Evidence

Enable pg_stat_statements , Not Full Logging

The pg_stat_statements extension aggregates normalized queries, far more efficient than parsing logs.

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = top

Changing shared_preload_libraries requires a restart. Then create the extension:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Query top consumers by total execution time (PG 13+ column names shown):

SELECT
  round(total_exec_time::numeric, 1) AS total_ms,
  calls,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  round(rows::numeric / NULLIF(calls, 0), 1) AS rows_per_call,
  left(query, 90) AS query
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat_statements%'
ORDER BY total_exec_time DESC
LIMIT 20;

Note: PG 13 renamed total_time / mean_time to total_exec_time / mean_exec_time and added total_plan_time. Verify your version’s column names.

Two common misinterpretations:

Sorting only by total time misses high‑frequency fast queries (e.g., 2 ms × 500/sec > 3 s × 1/min). Also sort by calls.

High total_plan_time but low total_exec_time indicates planning overhead, common when connection pools force re‑planning for every simple query.

Supplement with Targeted Logging

pg_stat_statements

lacks parameter values and wait context. Add selective logging:

log_min_duration_statement = '1s'          # log statements > 1s
log_lock_waits = on                        # log lock waits exceeding deadlock_timeout
log_temp_files = 0                         # log all temporary file usage
log_autovacuum_min_duration = '250ms'      # log slow autovacuum runs

log_temp_files = 0

is a direct indicator that sorts/hashes spilled to disk because work_mem was insufficient. Avoid log_min_duration_statement = 0 in production (fills disk); PG 13+ offers sampling via log_min_duration_sample and log_statement_sample_rate.

work_mem Is Per Operation Node, Not Per Connection

A frequent misconception: work_mem is allocated per sort/hash/aggregate node per query. A single query with multiple such nodes can consume work_mem × node_count × concurrency. Raising it globally from 4 MB to 256 MB is dangerous. Keep instance‑level conservative (16‑64 MB) and increase per session for specific reporting queries:

SET LOCAL work_mem = '128MB';

SET LOCAL

scopes to the current transaction, reverting automatically.

Capture Actual Execution Plans

Use EXPLAIN (ANALYZE, BUFFERS) on the identified query. BUFFERS shows buffer hits vs. reads — large reads with few hits indicate disk‑bound nodes. Analyze plans in this order:

Row estimate accuracy : rows=1 vs. actual rows=100000 → run ANALYZE <table> first.

Sequential scans : not always bad; evaluate retained‑row ratio.

Join method : nested loops on large tables with high inner iterations usually signal statistics or index issues.

External sort : Sort Method: external merge Disk means work_mem too small.

Rows Removed by Filter : large numbers indicate unused indexes or function/type mismatches on indexed columns.

The auto_explain extension can log plans for statements exceeding a threshold without manual execution:

shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '3s'
auto_explain.log_analyze = on

Enable log_analyze only during troubleshooting windows due to overhead.

Common Root Causes of Slow Queries

Missing or ineffective indexes : implicit casts, leading wildcards ( LIKE '%x'), functions on indexed columns.

Stale statistics : bulk loads without ANALYZE; multi‑column correlation may require CREATE STATISTICS.

Excessive result sets : missing pagination or large OFFSET; use keyset pagination.

Lock waits : query appears fast but total time high; check pg_stat_activity.wait_event_type = 'Lock' and use pg_blocking_pids(pid).

Connection pool queuing : application‑side wait time inflates latency; compare app wait metrics with DB execution time.

3. VACUUM: Why Dead Tuples Persist

Origin of Dead Tuples

PostgreSQL’s MVCC writes new tuple versions on update; old versions remain until marked reusable by VACUUM. Space is not returned to the OS unless VACUUM FULL runs. Crucially, VACUUM can only remove tuples invisible to all active snapshots . Any long‑running transaction, idle‑in‑transaction connection, replication slot, or prepared transaction holding an old snapshot blocks cleanup.

Check If Autovacuum Is Running

SELECT relname,
       n_live_tup, n_dead_tup,
       round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 1) AS dead_pct,
       last_vacuum, last_autovacuum,
       last_analyze, last_autoanalyze,
       autovacuum_count
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC
LIMIT 20;

If autovacuum_count = 0 but dead tuples are high, autovacuum never triggered. If count > 0 but dead tuples remain high, something blocks cleanup — investigate snapshots.

Four Common Blockers (by frequency)

Long transactions : a transaction open for 30 minutes prevents recycling of all dead tuples generated since its start.

SELECT pid, usename, application_name, state,
       now() - xact_start AS xact_age,
       left(query, 60) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 10;

idle in transaction connections : same effect as long transactions but less obvious because the query field shows the last statement, which looks harmless.

Unused replication slots : slots retain xmin; if a standby or logical consumer is down but the slot remains, VACUUM is blocked indefinitely.

SELECT slot_name, slot_type, active, restart_lsn,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal,
       xmin, catalog_xmin
FROM pg_replication_slots;

active = false with non‑null xmin is a suspect. Deleting a slot is irreversible — it forces a full resync — so confirm the consumer is truly gone. Uncommitted two‑phase transactions : prepared transactions also hold snapshots. <code>SELECT gid, prepared, owner, database FROM pg_prepared_xacts;</code> Resolve via COMMIT PREPARED / ROLLBACK PREPARED ; cannot just kill the connection.

Autovacuum Trigger Conditions and Tuning

Default trigger:

dead_tuples > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × reltuples

(i.e., 50 + 0.2 × row count). For a 100 M row table, that’s 20 M dead tuples — too lax. Adjust per table:

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 1000,
  autovacuum_vacuum_cost_delay = 0
);

autovacuum_vacuum_cost_delay

controls throttling (default 2 ms in PG 12+). Setting to 0 removes the limit but increases I/O pressure; apply only to specific lagging tables, not globally. Other relevant parameters: autovacuum_max_workers (default 3), autovacuum_naptime (default 1 min), maintenance_work_mem (default 64 MB, affects VACUUM and index build speed). Enable log_autovacuum_min_duration = '250ms' to see actual autovacuum activity in logs.

Detecting Table and Index Bloat

High dead‑tuple ratio doesn’t always mean bloat; space may be reused by new inserts. True bloat is “space retained but unused”. Accurate measurement requires pgstattuple or check_postgres. A lightweight heuristic:

SELECT relname,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
       pg_size_pretty(pg_relation_size(relid)) AS table_size,
       n_dead_tup, n_live_tup,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 15;

If actual disk size far exceeds n_live_tup × average_row_width and last_autovacuum is old, bloat is likely.

Reclaiming Space: Options and Trade‑offs

Regular VACUUM : no exclusive lock, runs concurrently with writes, marks space reusable but does not return it to OS. Daily maintenance.

VACUUM FULL : rewrites entire table, returns space to OS. Requires ACCESS EXCLUSIVE lock — table completely unavailable for reads/writes. Minutes to hours for large tables. Must be approved change with rollback plan.

pg_repack : online reorganization via triggers and a temporary table; minimal disruption but needs extra disk space (~table size) and a primary/unique key. Suitable when writes cannot stop but a long maintenance window exists.

Decision rule: if you can stop writes in a maintenance window, use VACUUM FULL; if writes must continue, use pg_repack; if not urgent and dead‑tuple ratio moderate, tune autovacuum to catch up gradually. Always verify free disk space before VACUUM FULL (it temporarily doubles table size).

Transaction ID Wraparound Prevention

VACUUM also freezes old transaction IDs to prevent 32‑bit wraparound (≈2.1 B transactions). If the oldest XID isn’t frozen, the database eventually rejects new writes, requiring single‑user mode VACUUM — an unplanned outage. Monitor:

SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY xid_age DESC;

Alert at 200 M and 500 M (default autovacuum_freeze_max_age = 200 M triggers anti‑wraparound autovacuum, which runs even if autovacuum = off). Replication slots and prepared transactions also block freezing.

4. Putting It Together: A Practical Cadence

Daily: check connection distribution and long transactions (state counts + transactions >10 min). Weekly: review pg_stat_statements top 20 by total time and by calls; compare with previous week. Weekly: examine dead‑tuple trends (rising n_dead_tup with stagnant autovacuum_count) and age(datfrozenxid) growth. Change window: combined check — table bloat, unused indexes ( idx_scan = 0 in pg_stat_user_indexes), and whether pg_stat_statements sampling needs reset. Irreversible/high‑risk actions (require change process & rollback): dropping replication slots, VACUUM FULL, terminating backends ( pg_terminate_backend), modifying shared_preload_libraries. All other reads and parameter tweaks are low risk.

5. Guiding Principles Checklist

When connections max out, classify first — don’t just raise max_connections. idle → pool sizing; idle in transaction → immediate hunt. idle in transaction is the top priority — it consumes both a connection slot and a snapshot. Enable idle_in_transaction_session_timeout as a safety net, then fix the client.

Slow queries: rank via pg_stat_statements , then prove with EXPLAIN (ANALYZE, BUFFERS) . Don’t guess; high‑frequency fast queries are often the real load. work_mem is per operation node, not per connection. Calculate worst‑case memory before raising globally.

Dead tuples not cleaning? Check snapshots first : long transactions, idle in transaction, idle replication slots, leftover 2PC — in that frequency order.

Bloat remedies have vastly different costs: VACUUM FULL locks the table; pg_repack needs extra space. Confirm write‑pause tolerance before choosing.

Transaction ID wraparound is the only issue that halts the database. Others are performance; this is availability. Set dedicated alerts.

Ready‑to‑use checklist:

[ ] application_name set in all connection strings

[ ] idle_in_transaction_session_timeout enabled, clients can reconnect

[ ] pg_stat_statements enabled, shared_preload_libraries change documented

[ ] log_min_duration_statement threshold matches SLA, not zero

[ ] log_temp_files enabled to catch work_mem pressure

[ ] No inactive replication slots holding xmin [ ] No stale prepared transactions in pg_prepared_xacts [ ] Large tables have custom autovacuum_vacuum_scale_factor (not default 0.2)

[ ] Two‑level alerts on age(datfrozenxid) [ ] Slot drops, VACUUM FULL, etc. governed by change management