Module 9 · Operations

Monitoring, Connection Pooling, and Routine Maintenance

Lesson 27 of 30 11 min read Updated

Monitoring, connection pooling, and routine maintenance keep PostgreSQL usable after the schema is correct and the queries are written. The outcome is practical: know what the server is doing, keep client connection pressure under control, and perform cleanup before bloat, stale statistics, or replication lag become incidents.

In this operations section, the focus shifts from writing SQL to running PostgreSQL as a long-lived service. PostgreSQL exposes much of its internal state through system views, background workers, write-ahead logging, and maintenance commands. Those mechanisms are not separate chores. They determine whether transactions finish quickly, whether the planner chooses the right plan, whether disk usage grows predictably, and whether applications can connect during traffic spikes.

How PostgreSQL Exposes Work

Every client connection is served by a backend process. Each backend may be active, idle, waiting on a lock, waiting on I/O, or sitting in an open transaction. PostgreSQL reports these states through views such as pg_stat_activity, pg_locks, pg_stat_database, pg_stat_user_tables, and pg_stat_bgwriter. These views are counters and snapshots maintained by the server, so they should be sampled regularly instead of treated as a permanent event log.

The most important distinction is between symptoms and causes. A high query count is not automatically bad. Many idle sessions are not automatically bad. The operational question is whether useful work is blocked by a finite resource: connections, CPU, disk bandwidth, shared buffers, locks, transaction ID age, or autovacuum capacity. Good monitoring starts with server-level saturation, then narrows to database, relation, query, and transaction behavior.

Routine maintenance is tied to PostgreSQL’s multi-version concurrency control. When a row is updated or deleted, old row versions remain until no active transaction can still see them. VACUUM marks dead tuples reusable and prevents transaction ID wraparound. ANALYZE refreshes planner statistics so row-count estimates stay realistic. REINDEX rebuilds damaged or badly bloated indexes. Autovacuum runs these jobs in the background, but busy systems still need thresholds, visibility, and manual procedures for exceptional cases.

Configuration Anatomy

Connection settings control the number and behavior of backend processes. max_connections is a hard upper bound, but raising it is not free because each backend consumes memory and scheduler attention. Applications usually need a smaller, steadier number of server connections than their web request count suggests. A pooler such as PgBouncer accepts many client connections and reuses fewer PostgreSQL server connections.

Monitoring and maintenance settings shape what the operator can see and what PostgreSQL can clean automatically. log_min_duration_statement records slow statements. track_io_timing adds I/O timing to statistics where enabled. shared_preload_libraries can load extensions such as pg_stat_statements, which aggregates normalized query fingerprints. Autovacuum settings such as autovacuum_vacuum_scale_factor, autovacuum_analyze_scale_factor, and per-table storage parameters determine when cleanup starts.

SELECT datname, numbackends, xact_commit, xact_rollback, deadlocks
FROM pg_stat_database
WHERE datname = current_database();

This query reads database-level counters. numbackends is current connection pressure. Commit and rollback counters show transaction volume. deadlocks should normally remain zero or change rarely; an increasing value means application transaction ordering needs investigation.

Example 1: Find Connection Pressure

The first example separates active work from idle sessions. This is more useful than counting connections alone because an application may hold many idle sessions while only a few statements run.

SELECT state, wait_event_type, wait_event, count(*) AS sessions
FROM pg_stat_activity
WHERE datname = current_database()
GROUP BY state, wait_event_type, wait_event
ORDER BY sessions DESC;

Expected behavior is a small result set grouped by state. Rows with active and a wait event such as Lock point toward contention. Many idle in transaction sessions are more dangerous than plain idle sessions because they can hold snapshots, delay vacuum cleanup, and keep old row versions visible.

If the symptom is remaining connection slots are reserved, first check pg_stat_activity, then compare the application pool size, worker count, and number of application instances. The correction is usually to reduce per-instance pool sizes or introduce a transaction pooler, not to blindly raise max_connections.

Example 2: Add Transaction Pooling

PgBouncer has several pooling modes. Session pooling assigns a server connection to a client for the life of the client session. Transaction pooling assigns a server connection only while a transaction is running. Transaction pooling gives better reuse, but it changes behavior for session-local features such as temporary tables, prepared statements, advisory locks held at session scope, and per-session settings.

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

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
default_pool_size = 25
reserve_pool_size = 5
server_reset_query = DISCARD ALL

With this configuration, clients connect to port 6432 while PgBouncer maintains a bounded set of PostgreSQL connections. If 200 web workers mostly wait between short transactions, the database may only need 25 to 30 active server connections for that database. The expected operational result is lower backend count in PostgreSQL while request latency remains acceptable.

The trade-off is correctness for session-dependent applications. If the application uses temporary tables across transactions, transaction pooling will break it because the next transaction may run on a different backend. In that case use session pooling, remove the session dependency, or route that workload around the pooler.

Example 3: Inspect Table Maintenance Health

Table statistics reveal whether autovacuum and analyze are keeping up. The following query lists user tables with dead tuple counts and the last maintenance times.

SELECT relname,
       n_live_tup,
       n_dead_tup,
       last_autovacuum,
       last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

The output is deterministic in shape: one row per relation, with estimated live and dead tuple counts. Values are estimates, so use them to rank likely problems rather than to count exact row versions. A table with high n_dead_tup and no recent autovacuum may have thresholds set too high, autovacuum workers saturated, or a long-running transaction preventing cleanup.

A common correction for a frequently updated table is a per-table autovacuum setting. This avoids making the entire cluster more aggressive just because one table churns heavily.

ALTER TABLE job_queue SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01
);

After applying this change, verify that future updates trigger autovacuum and autoanalyze sooner for job_queue. The expected behavior is more frequent maintenance on that table, lower dead-tuple accumulation, and fresher planner statistics for queue-processing queries.

Example 4: Run Targeted Manual Maintenance

Manual maintenance should be targeted and observable. VACUUM (ANALYZE) is usually safe for an online table because it does not take the exclusive lock required by VACUUM FULL. It marks space reusable and updates statistics, but it normally does not shrink the table file on disk.

VACUUM (ANALYZE, VERBOSE) job_queue;

The expected output includes verbose details about pages scanned, tuples removed, and statistics updates. Use it when a table has accumulated dead tuples after a batch delete or when stale statistics are causing poor plans. If disk space must be returned to the operating system, consider VACUUM FULL, CLUSTER, or table rewrite approaches, but plan for heavier locking and test the procedure on a copy first.

Design Choices and Trade-offs

Monitoring frequency trades detail against overhead and storage. Sampling pg_stat_activity every few seconds is useful during incidents, while long-term dashboards often store lower-resolution aggregates. Statement logging catches exact SQL text but can expose sensitive values and create heavy log volume. Aggregated extensions reduce noise but hide individual parameter values.

Connection pooling trades backend stability against session semantics. A small pool protects PostgreSQL from connection storms, but a pool that is too small creates queueing in the pooler. Size pools from measured concurrency: active database time, transaction duration, CPU saturation, and acceptable wait time. For OLTP workloads, a moderate number of busy connections is usually better than hundreds of competing backends.

Maintenance tuning trades write overhead against future query performance and storage reuse. Aggressive vacuum settings consume I/O earlier but prevent large cleanup spikes. Lazy settings reduce background activity but risk bloat, wraparound pressure, and poor planner estimates. The right choice depends on table churn, table size, index count, and latency sensitivity.

Failure Modes and Troubleshooting

Symptom: new requests fail with connection errors. Cause: application pools across all instances exceed PostgreSQL capacity or leak sessions. Diagnose: inspect pg_stat_activity by application name, client address, state, and age. Compare the total with pool settings and deployment replica count. Correct: lower application pool sizes, add PgBouncer, terminate abandoned sessions carefully, and reserve superuser access for emergency diagnosis.

Symptom: table size grows after updates and deletes. Cause: MVCC dead tuples are not being cleaned or reusable space is not being reused by the workload. Diagnose: check n_dead_tup, last vacuum time, long-running transactions, and autovacuum logs. Correct: end idle transactions, tune per-table autovacuum, run targeted VACUUM (ANALYZE), and schedule rewrite operations only when reusable-space cleanup is not enough.

Symptom: a previously fast query suddenly uses a bad plan. Cause: stale statistics, changed data distribution, or index bloat. Diagnose: compare EXPLAIN (ANALYZE, BUFFERS) row estimates with actual rows and check last analyze time. Correct: run ANALYZE, increase statistics targets for skewed columns where justified, or rebuild a bloated index with a low-lock method such as REINDEX CONCURRENTLY where supported by the deployment.

Security, Performance, and Reliability

Monitoring roles should have the least privilege needed to read operational views. Avoid logging full parameter values when they may contain secrets or personal data. Pooler credentials should use strong authentication and separate administrative access from application access. Reliability improves when alerts are attached to causes: connection saturation, lock waits, replication lag, autovacuum wraparound age, disk growth, and error-rate changes.

Performance work should start with baselines. Track transaction rate, latency percentiles, active connections, lock waits, cache hit ratios, temporary file creation, checkpoint behavior, and table maintenance age. A dashboard that shows these together makes it easier to distinguish database saturation from an application rollout, a slow external dependency, or one abusive query.

Hands-on Lab

Prerequisites: a disposable PostgreSQL database, permission to create a table, and a SQL client such as psql. Do not run this lab against production data.

  1. Create a churn table: CREATE TABLE maintenance_lab (id bigserial PRIMARY KEY, status text NOT NULL, payload text NOT NULL);
  2. Insert test rows: INSERT INTO maintenance_lab (status, payload) SELECT 'ready', repeat('x', 100) FROM generate_series(1, 10000);
  3. Update half the table: UPDATE maintenance_lab SET status = 'done' WHERE id % 2 = 0;
  4. Inspect statistics with the pg_stat_user_tables query from Example 3 and note n_live_tup, n_dead_tup, and the last analyze fields.
  5. Run VACUUM (ANALYZE, VERBOSE) maintenance_lab; and then inspect the statistics again.
  6. Verify planner behavior with EXPLAIN SELECT count(*) FROM maintenance_lab WHERE status = 'done';. The plan should have current row estimates after analyze.
  7. Cleanup with DROP TABLE maintenance_lab;. If you changed per-table autovacuum settings on another table, reset them with ALTER TABLE table_name RESET (autovacuum_vacuum_scale_factor, autovacuum_analyze_scale_factor);.

Verification is complete when the table exists only during the lab, vacuum verbose output reports work on the lab table, statistics timestamps update, and the cleanup query removes the test object.

Assessment Exercises

  1. An application has 12 instances, each configured with a pool of 30 connections, and PostgreSQL has max_connections set to 200. Explain the failure mode and propose a safer pool strategy.
  2. A table receives constant updates, n_dead_tup rises quickly, and last_autovacuum is old. List three diagnostic checks before changing cluster-wide autovacuum settings.
  3. Why can transaction pooling break an application that works with direct PostgreSQL connections? Give two specific PostgreSQL session features that are affected.
  4. A query plan estimates 100 rows but returns 500000 rows. Explain how maintenance could be involved and what command you would run first.
  5. Design one alert for connection saturation that includes the metric, threshold idea, owner action, and verification after remediation.

Summary

PostgreSQL operations depend on seeing backend state, limiting connection pressure, and keeping MVCC cleanup and planner statistics current. Use system views to distinguish active work from idle sessions, use poolers to protect server backends while respecting session semantics, and tune vacuum or analyze where table behavior proves the default is not enough. The practical goal is a database whose resource use, cleanup behavior, and failure signals are visible before users experience an outage.

Progress is saved in this browser.