9.3 Server Logging Configuration & Autovacuum Tuning

9.3 Server Logging Configuration & Autovacuum Tuning

[!NOTE] Operational Synergy: Server logging and background autovacuum maintenance represent two sides of the same operational coin. Logging provides external visibility into database operations, capturing server errors, deadlocks, and slow query executions. Autovacuum provides internal health maintenance, operating quietly in the background to reclaim MVCC dead tuple space, update query optimizer statistics, and prevent transaction ID wraparound. Tuning both subsystems ensures cluster stability and high throughput.

Misconfigured logging can easily exhaust disk space with gigabytes of unneeded statements or obscure root causes due to missing session identifiers. Similarly, default autovacuum configurations calibrated for modest workloads can cause massive tables to accumulate millions of dead tuples before maintenance triggers.

---

Server Logging Architecture: The logging_collector

By default on Unix-like platforms, PostgreSQL emits diagnostic and error messages to stderr. In production environments, database administrators enable the background logging_collector (formerly known as redirect_stderr).

+-------------------------------------------------------------------------+
|                     PostgreSQL Logging Architecture                     |
+-------------------------------------------------------------------------+
|  Postmaster & Backend Processes                                         |
|  [Backend 1]  [Backend 2]  [Checkpointer]  [Autovacuum]                 |
|       │            │             │               │                      |
|       └────────────┴─────────────┴───────────────┘                      |
|                            │                                            |
|                            ▼ (stderr pipe capture)                      |
|               +───────────────────────────+                             |
|               |     logging_collector     |                             |
|               +───────────────────────────+                             |
|                            │                                            |
|                            ├──> Writes to log_directory/log_filename    |
|                            ├──> Enforces log_rotation_age (e.g. 1d)     |
|                            └──> Enforces log_rotation_size (e.g. 10MB)  |
+-------------------------------------------------------------------------+

Core Logging Parameters in postgresql.conf

• logging_collector = on: Enables the dedicated background collector process. This process intercepts stderr and safely writes messages into rotating log files. Modifying this setting requires a full database server restart (postmaster context).
• log_destination = 'stderr, csvlog': Determines the target format for log output. Common options include stderr (standard text logs), csvlog (Comma-Separated Values format, allowing log records to be loaded directly into a PostgreSQL table for SQL analysis), and syslog (routing to operating system system log facilities).
• log_directory = 'log': The directory where log files are stored. Can be specified as a relative path from $PGDATA or an absolute filesystem path.
• log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log': The filename template for generated log files, supporting strftime format codes.

Automated Log Rotation Settings

To prevent a single log file from consuming all available disk storage, PostgreSQL provides automated time-based and size-based rotation:

• log_rotation_age = 1d: Forces creation of a new log file when the current file reaches a specific age (e.g., 1d for daily rotation, 1h for hourly rotation). Setting to 0 disables time-based rotation.
• log_rotation_size = 10MB: Forces creation of a new log file when the current file reaches a specific size threshold (e.g., 10MB or 100MB). Setting to 0 disables size-based rotation.
• log_truncate_on_rotation = on: When enabled, PostgreSQL overwrites any existing log file of the same name rather than appending to it. Used in fixed 7-day cyclical rotation schemes (e.g., postgresql-%a.log for Sun–Sat).

---

Slow Query Detection and Statement Logging

Monitoring query performance requires capturing slow statements without overwhelming disk I/O.

log_min_duration_statement

This is the single most important parameter for query performance diagnostics. It specifies the minimum statement execution duration in milliseconds for a query to be logged:

-- Log all queries that take 250 milliseconds or longer
ALTER SYSTEM SET log_min_duration_statement = 250;
SELECT pg_reload_conf();

• log_min_duration_statement = 250: Logs the full SQL text and exact elapsed runtime for any statement taking $\ge 250\text{ms}$.
• log_min_duration_statement = 0: Logs every single executed statement and its duration. Useful for short debugging sessions in development, but causes catastrophic disk write contention and security risks in production!
• log_min_duration_statement = -1: Disables duration-based query logging entirely (the default).

log_statement: Structural Statement Logging

Controls which types of SQL statements are logged independently of execution duration:

• none (default): No statements logged by type.
• ddl: Logs data definition commands (CREATE, ALTER, DROP). Recommended in production for change tracking and audit compliance.
• mod: Logs all DDL statements plus data modification commands (INSERT, UPDATE, DELETE, TRUNCATE, COPY).
• all: Logs all statements unconditionally (high overhead).

---

Custom Log Formatting: log_line_prefix

The log_line_prefix parameter defines a printf-style formatting string that PostgreSQL prepends to the beginning of every single log entry. A well-designed prefix is vital for matching log lines to application transactions and debugging connection incidents.

-- Recommended production log line prefix
ALTER SYSTEM SET log_line_prefix = '%m [%p] %q%u@%d (%a, %r) ';
SELECT pg_reload_conf();

Standard Format Escape Codes

• %m: Time stamp with fractional milliseconds (e.g., 2026-09-06 14:32:01.402 UTC). Essential for sequencing concurrent transactions.
• %p: Process ID (PID) of the backend process handling the request. Matches the pid column in pg_stat_activity.
• %u: Authenticated database user name.
• %d: Database name.
• %r: Remote client host address and port (e.g., 192.168.1.50(49210)). Evaluates to empty for local socket connections.
• %a: Application name (configured by client connection string, e.g., web-frontend-pod-4).
• %q: Suppresses all characters that follow if the log message does not originate from a backend session (such as postmaster startup or checkpointer logs).

---

Autovacuum Recap and the Insert-Driven Trigger

Section 6.1 covers the autovacuum engine itself — the launcher, the workers, and how dead tuples are reclaimed. This section assumes that material and focuses only on tuning it. The one-line recap you need here:

An autovacuum worker fires on a table when n_dead_tup exceeds autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor × n_live_tup) — by default 50 + (0.20 × n_live_tup). Autoanalyze fires on the parallel formula using autovacuum_analyze_threshold (50) and autovacuum_analyze_scale_factor (0.10).

The Third Trigger: Insert-Only Tables

The dead-tuple formula has a blind spot that both older documentation and older exam material miss: a table that only ever receives INSERTs never accumulates dead tuples, so it never crosses the vacuum threshold. Before PostgreSQL 13, such a table went unvacuumed until anti-wraparound vacuum forced a pass, leaving its visibility map unset — which silently disabled index-only scans and made the eventual freeze scan enormous.

PostgreSQL 13 added a dedicated insert-driven trigger:

Insert Threshold = autovacuum_vacuum_insert_threshold + (autovacuum_vacuum_insert_scale_factor × n_live_tup)

• autovacuum_vacuum_insert_threshold: Tuples inserted since the last vacuum before a vacuum is triggered (default 1000). Setting it to -1 disables insert-driven autovacuum entirely.
• autovacuum_vacuum_insert_scale_factor: Proportional component (default 0.2).

This matters most for append-only audit, event, and time-series tables — exactly the tables where a BRIN index or an index-only scan plan depends on an up-to-date visibility map. If you are tuning an insert-heavy table, tune this trigger, not just the dead-tuple one.

---

The Large Table Autovacuum Dilemma & Per-Table Overrides

Under default cluster settings (scale_factor = 0.20), consider a high-throughput table with 50,000,000 live rows:

$\text{Threshold} = 50 + (0.20 \times 50,000,000) = 10,000,050 \text{ dead tuples}$

The table must accumulate over 10 million dead tuples before autovacuum wakes up! By the time autovacuum finally triggers, cleaning 10 million dead tuples from table pages and secondary B-Trees requires massive I/O, runs for hours, and generates severe table bloat.

Per-Table Storage Parameters

PostgreSQL allows administrators to override global autovacuum settings on individual tables using ALTER TABLE ... SET (...):

-- Customize autovacuum thresholds for a high-volume orders table
ALTER TABLE customer_orders SET (
    autovacuum_vacuum_scale_factor = 0.02,     -- Trigger at 2% dead rows instead of 20%
    autovacuum_vacuum_threshold = 5000,       -- Fixed base threshold
    autovacuum_vacuum_cost_limit = 1000,      -- Give this table higher I/O capacity
    autovacuum_vacuum_cost_delay = 2          -- Throttle delay in milliseconds
);

With a 2% scale factor, a 50M-row table triggers autovacuum after 1,005,000 dead tuples—cleaning the table ten times more frequently in smaller, faster, non-disruptive increments.

---

Cost-Based Vacuum Throttling

To prevent autovacuum workers from saturating disk I/O controllers and degrading active user transactions, PostgreSQL implements a cost-based throttling delay mechanism.

+-------------------------------------------------------------------------+
|                    Cost-Based Vacuum Throttling Cycle                   |
+-------------------------------------------------------------------------+
|  Worker scans 8KB pages and accumulates operational cost:               |
|  - Page found in shared_buffers:         vacuum_cost_page_hit   (cost 1)|
|  - Page read from OS cache or disk:      vacuum_cost_page_miss  (cost 2)|
|  - Page modified/dirtied by dead tuple:  vacuum_cost_page_dirty (cost 20)|
|                                                                         |
|  When Accumulated Cost reaches autovacuum_vacuum_cost_limit:            |
|       Worker sleeps for autovacuum_vacuum_cost_delay (e.g. 2ms)         |
|       Cost counter resets to 0 -> Worker resumes scanning               |
+-------------------------------------------------------------------------+

Throttling Configuration Parameters

• autovacuum_vacuum_cost_limit: The maximum accumulated cost points a worker can incur before it is forced to sleep. Default is -1, which means it falls back to vacuum_cost_limit (default 200).
• autovacuum_vacuum_cost_delay: The duration in milliseconds the worker sleeps when cost_limit is reached (default 2ms in modern PostgreSQL). Set to 0 to disable cost delays completely.
• Cost Weights: vacuum_cost_page_hit = 1, vacuum_cost_page_miss = 2, vacuum_cost_page_dirty = 20.

[!TIP] On modern high-speed NVMe solid-state storage arrays capable of 100,000+ IOPS, default cost settings (cost_limit = 200, cost_delay = 2ms) throttle vacuuming too severely, causing autovacuum to run sluggishly. Tuning autovacuum_vacuum_cost_limit = 1000 to 2000 enables autovacuum to complete much faster without causing detectable I/O contention.

---

Summary of Key Configuration Settings

---

Exam Tips and Common Pitfalls

• Exam Trap: Enabling logging_collector Context: Changing logging_collector from off to on requires a full PostgreSQL server restart (postmaster context). You cannot enable it dynamically with pg_reload_conf().
• Exam Trap: Difference Between log_min_duration_statement and log_statement: log_min_duration_statement logs queries based on execution duration (e.g. queries taking $> 250\text{ms}$). log_statement logs queries based on statement type (ddl, mod, all), regardless of how fast they run.
• Exam Trap: Autovacuum Threshold Formula: Be prepared to calculate autovacuum trigger points: threshold + (scale_factor * live_tuples). Remember that the scale factor is a percentage multiplied by live tuples, not dead tuples!