Design Records

• 1: Turning PostgreSQL CSV Logs into Durable Metrics
• 2: Caching pgBackRest Metrics Outside the Scrape Path
• 3: The PostgreSQL Observability Product We Chose Not to Build
• 4: One Endpoint, Several Sources: The Composite Exporter Contract
• 5: Snapshot Histograms Are Gauges, Not Counters

1 - Turning PostgreSQL CSV Logs into Durable Metrics

PostgreSQL logs record events that SQL snapshots can miss: deadlocks, authentication failures, canceled autovacuum workers, checkpoints, temporary-file removals, and client disconnects. They can also report statement duration, according to the server’s logging policy.

Status: Implemented but unreleased.
Decision date: 2026-08-24.
Last verified: 2026-10-13.
Scope: Default-off PostgreSQL 14+ CSV log collector at source commit c0ba58a; absent from stable v1.4.1.
Replaces: The separate observability-product proposal.
Delivery: Local tests passed. Not merged into main or tagged; packages, deployment, and production operation remain unverified.

This collector reads one local CSV log directory. It saves bounded aggregate state and exposes Counters and Histograms on /metrics. Raw logs remain in PostgreSQL’s log files.

Scope and activation

The collector runs in the existing PG Exporter process. A non-empty log directory enables it:

pg_exporter \
  --pg-log-dir=/var/log/postgresql \
  --pg-log-state-file=/var/lib/pg_exporter/pglog-state.json \
  --pg-log-poll-interval=1s

The corresponding environment variables are PG_EXPORTER_PG_LOG_DIR, PG_EXPORTER_PG_LOG_STATE_FILE, and PG_EXPORTER_PG_LOG_POLL_INTERVAL. Defaults are an empty directory, the state path shown above, and a one-second poll interval.

When disabled, the collector creates no worker, state lock, state file, or log metrics. It adds no file scans or scrape coordination. --dry-run and --explain do not start the worker.

The scope is local PostgreSQL CSV logs and aggregate metrics. Other log formats, log shipping, search interfaces, session timelines, arbitrary regular expressions, and automatic root-cause analysis are excluded. PostgreSQL logging settings remain server configuration; the collector does not create pg_log_setting_* metrics.

Read complete CSV records

PostgreSQL 14 through 18 document the same 26-column CSV layout. It includes timestamps, process and session identifiers, severity, SQLSTATE, messages, application, backend type, parallel leader PID, and query ID.

Fields can contain commas, quotes, carriage returns, and newlines. The reader therefore frames complete CSV records before decoding them. A physical-line scanner cannot handle this format correctly.

An unfinished record at the end of a file stays pending. Its offset is committed only after the record is complete. Records have a 16 MiB limit. Malformed or oversized input triggers bounded resynchronization and increments parse-error and input-gap counters. Repeated schema mismatches keep the component down until valid input resumes.

File identity and rotation

The worker polls and reconciles the directory. File notifications alone cannot recover missed events or reconstruct state after restart.

Each cursor holds a file identity, generation, hashed path, byte offset, size, content fingerprint, and reader status. The state file does not contain clear-text log paths.

Rename rotation follows file identity: the reader can finish the renamed file and start a cursor for the new one. A size below the committed offset means truncation. The reader starts a new generation at offset zero and records a rotation and an input gap. Copy-truncate can discard unread bytes before polling detects the change; those bytes cannot be recovered by this collector.

A missing unfinished file or changed fingerprint also records a gap. Completed, disappeared cursors can be removed. State tracks at most 256 file identities and retains cursors that still have unread work.

On first startup without state, the reader begins at the current end of every existing file. It increments the state-reset and input-gap counters to mark this new starting point. Historical backfill is outside this design.

Commit state before metrics

Log Counters and Histograms must survive an exporter restart. Cursor offsets and aggregates are committed together: advancing one without the other would lose or duplicate observations.

A background cycle prepares a candidate state and then:

read complete records and update candidate state
    -> validate counters, histograms, labels, and limits
    -> construct and validate metric families
    -> write a 0600 temporary state file
    -> fsync the file
    -> atomically rename it to the state path
    -> fsync the parent directory where supported
    -> publish the matching immutable metric snapshot

The state file is limited to 4 MiB. On Unix, it must be a regular file with mode 0600; symlinks and unsafe permissions are rejected. A non-blocking state lock prevents two processes from using the same state. The saved directory identity must match the configured source.

A crash before rename leaves the previous committed state. A crash after rename allows restart to recover the new state and reconstruct its metrics. /metrics only reads the published snapshot; it performs no file scan, CSV parsing, or state write.

A pass processes at most 100,000 records or 64 MiB. If input remains, the next pass starts immediately.

Metrics and series limits

Core families are:

pg_log_records_total{severity}
pg_log_errors_total{severity,sqlstate_class}
pg_log_query_duration_seconds{kind}
pg_log_events_total{category,event}

Additional metrics cover exact SQLSTATE codes, checkpoints and restartpoints, autovacuum, lock waits, temporary files, connections, and session duration.

Log Histograms are cumulative observations persisted with the cursor. Use ordinary counter-Histogram queries such as rate(). An explicit state reset begins a new counter epoch. This differs from SQL snapshot Histograms, which describe the current population and use Gauge series.

Labels use fixed values. Query duration uses five kind values: statement, execute, parse, bind, and other. Exact SQLSTATE metrics admit official codes up to 128 observed code series; custom and unknown codes use other. The total output has a 1,200-series limit.

Raw SQL, messages, database and user names, application names, relations, query IDs, addresses, process and session IDs, transaction IDs, and file paths are excluded from labels and saved state. One record can update several aggregates but produces at most one primary classified event. The metrics identify an increase in deadlocks, for example, without retaining the SQL text.

Reader health

The reader exposes:

pg_exporter_log_bytes_read_total
pg_exporter_log_parse_errors_total{reason}
pg_exporter_log_files_watched
pg_exporter_log_last_record_timestamp_seconds
pg_exporter_log_state_persist_timestamp_seconds
pg_exporter_log_rotations_total{type}
pg_exporter_log_input_gaps_total{reason}
pg_exporter_log_state_resets_total

It also uses pg_exporter_component_*{component="postgres_log"}. Input, permission, parse, state, limit, or family-conflict errors mark only this component down. The last committed snapshot remains available. Use health, last-success time, and gap counters to assess its freshness and completeness.

--disable-intro removes exporter, component, and reader self-metrics. It keeps the pg_log_* business metrics.

Logging policy determines coverage

Metrics cover the records PostgreSQL emits. Query-duration distributions depend on log_duration, log_min_duration_statement, sampling, and protocol behavior. For example, a slow-statement threshold produces a distribution of logged slow statements, not all queries.

Checkpoint, autovacuum, connection, disconnection, lock-wait, and temporary-file metrics also require the corresponding server logging settings. Message-based event parsing depends on the supported message language and format; SQLSTATE-based error classification does not depend on localized wording.

Give the service user read access to the selected logs and write access to its state directory. Keep log access restricted, because source files can contain SQL and client data.

Alternatives and non-goals

• Reading logs during /metrics adds parsing and persistence latency to database scrapes. A background worker keeps that work separate.
• File notifications can speed up discovery but cannot replace polling and reconciliation for correctness.
• Replaying all retained files on first start makes the initial counters depend on log retention. Starting at EOF gives an explicit baseline.
• Publishing before persistence can count records again after a crash. State is committed first.
• Dynamic labels and user patterns would make series counts and the saved schema depend on input. The metric catalog is fixed.
• A separate endpoint or executable would add a deployment unit for the same bounded data source. The existing process and endpoint are reused.

Verification and remaining work

The implementation is in exporter/postgres_log*.go. Tests use PostgreSQL 14, 16, and 18 fixtures. They cover quoted and multiline fields, partial records, rename, truncation, restart, locking, permissions, and crash points. They also cover event classification, series limits, conflicts, overlapping scrapes, and shutdown.

External CSV integration remains unverified. Set PG_EXPORTER_TEST_PG_LOG_DIR and validate real log rotation and restart recovery under the intended service user.

See Composite Exporter for coordination. CSV definitions and logging settings are documented by PostgreSQL 14 and PostgreSQL 18.

2 - Caching pgBackRest Metrics Outside the Scrape Path

pgbackrest info --output=json reports backup state. The command can contact remote repositories and wait for storage. Running it during a Prometheus request would add that delay to database monitoring.

Status: Implemented but unreleased.
Decision date: 2026-08-23.
Last verified: 2026-10-13.
Scope: Optional --pgbackrest component at source commit c0ba58a; absent from stable v1.4.1.
Delivery: Local tests passed. Not merged into main or tagged; packages, deployment, and production operation remain unverified.

PG Exporter therefore runs pgBackRest in a background worker. The worker validates the result and publishes an immutable snapshot. /metrics reads the latest snapshot without waiting for a command.

background worker
    -> detect pgBackRest version
    -> run info --output=json
    -> validate and build metrics
    -> publish snapshot

/metrics request
    -> read snapshot
    -> merge with other sources

Execution limits

The worker executes the configured program directly, without a shell.

The worker first runs version --output=num. For older versions, it falls back to version. It then runs info --output=json. Cancellation stops the direct child process; a one-second wait delay bounds cleanup of output pipes.

Validate before publication

A zero command exit status is not sufficient. The output must have the expected JSON structure, valid numeric fields, and consistent stanza and repository references. Validation also limits the input to 256 stanzas and 4,096 backups, and the output to 20,000 samples and 4 MiB of Prometheus text.

Only a complete valid result replaces the snapshot. Metric families are validated before publication, so the scrape path only needs to check their headers and ownership.

pgBackRest follows PostgreSQL, process self-metrics, Patroni, and PgBouncer in the Composite ownership order. A conflicting family is omitted and marks the component down. Other valid families remain available.

Retain the last successful result

After the first successful refresh, a command, timeout, parse, or limit error preserves the previous business metrics. The component reports up=0, increments its error counter, and keeps the previous last-success timestamp.

Backup-age gauges are recalculated from the retained JSON during failed background refreshes. They continue to reflect elapsed time at the refresh interval. /metrics does not parse JSON or execute pgBackRest.

Before the first success, no backup business metrics are available. PostgreSQL and the other valid sources continue to work.

Health and concurrent requests

Component health uses component="pgbackrest". up describes the latest collection result; the last-success timestamp shows how old the backup data is. Command failures use exec, output limits use limit for standard output, and parse or merge errors use their corresponding reasons. Standard-error overflow is classified as an execution error.

Overlapping scrapes can read the same snapshot. They do not start another command or update the stored component-health history. Metric conflicts still follow the Composite ownership rule.

Alternatives and non-goals

Running the command on every scrape would couple Prometheus timeouts and concurrent requests to repository work. A background worker gives command execution a separate timeout and refresh interval.

A separate exporter offers process isolation but adds a service, port, package, and target. The integrated component offers another deployment option. It does not migrate existing deployments automatically.

A generic shell-command plugin was rejected because pgBackRest requires specific JSON validation, version handling, metric definitions, and recovery rules. This component accepts a pgBackRest executable, not arbitrary command templates.

Verification and operation

exporter/pgbackrest.go owns command execution and snapshot publication; exporter/pgbackrest_metrics.go owns JSON validation and metrics. Tests cover version fallback, malformed and oversized output, timeout, last-success retention, family conflicts, and shutdown.

The service user needs permission to run pgBackRest and read its configuration and repository credentials. The PG Exporter package does not bundle pgBackRest. Use the host’s backup installation.

Alert on component health and last-success age. Existing backup metrics can remain visible after collection fails. Before replacing a standalone exporter, compare both outputs against the same repository under the intended service user.

3 - The PostgreSQL Observability Product We Chose Not to Build

We first considered a local PostgreSQL incident workbench. It would read structured logs, reconstruct session timelines, provide interactive filters and a terminal interface, and later run as an agent.

Status: Superseded on 2026-08-24 by PostgreSQL log metrics.
Decision date: Research began on 2026-08-13; scope changed on 2026-08-24.
Last verified: 2026-10-13.
Scope: The separate PostgreSQL incident workbench and agent proposal.
Delivery: Stopped at proposal stage; never implemented, released, or deployed.

The immediate need was smaller: derive reliable operational metrics from PostgreSQL logs. We chose to add a background collector to PG Exporter. This record explains which findings survived and why the separate product was dropped.

The original proposal

During an incident, a DBA may have local logs without a central log service. A tool that understands PostgreSQL CSV, SQLSTATE, sessions, and rotation could build a timeline without uploading SQL text.

The proposed workbench included:

• PostgreSQL 14+ CSV parsing, including multiline fields;
• filters for time, severity, SQLSTATE, database, user, application, and session;
• context across renamed log files;
• an interactive terminal interface;
• local processing and low-cardinality Prometheus metrics;
• a future daemon with durable cursors and multiple output destinations.

Findings retained

PostgreSQL 14 through 18 document the same 26-column CSV layout. The sample import schema uses (session_id, session_line_num) as a primary key. CSV fields can contain commas, quotes, and newlines, so the parser must read complete records.

SQLSTATE is more stable than localized message text. It is the primary error classifier. Checkpoint, autovacuum, lock-wait, temporary-file, and connection messages need tested message patterns for the supported versions and language.

Raw SQL, user names, database names, client addresses, and session identifiers are unsuitable as default metric labels. They expose source data and make the number of series depend on workload. Fixed event categories and bounded distributions suit Prometheus.

Rotation, partial writes, restart recovery, and cursor persistence determine whether counts are reliable. They belong in the feature contract from the start.

Why the scope changed

A workbench needs its own commands, output schema, interface, packages, documentation, and release cycle. An agent also needs queues, retries, retention rules, and service management. These responsibilities exceed those of a metrics exporter.

PG Exporter already provides a scrape target, /metrics, packaging, and Pigsty integration. Its development coordinator also provides component health. A bounded log collector can reuse those facilities and meet the immediate need.

We therefore stopped the separate product, repository, and branding work. Interactive investigation and log delivery were removed from the scope.

Replacement

The decision on 2026-08-24 was:

Add a default-off background PostgreSQL CSV log collector to PG Exporter. Publish a bounded set of operational metrics on the existing /metrics endpoint.

The replacement keeps complete CSV parsing, file identity, rotation and truncation handling, durable cursors and aggregates, atomic persistence, immutable snapshots, fixed labels, and explicit input-gap metrics.

Log shipping, interactive search, session timelines, arbitrary regular expressions, and automatic root-cause analysis remain outside its scope. The implementation contract is in PostgreSQL log metrics.

When to reconsider

A separate workbench would need evidence of a local investigation workflow that existing log tools do not serve. It would also need users to validate the event model and maintainers to own its release and support cycle. Until those needs are established, PG Exporter will expose aggregate metrics.

Sources

The CSV requirements come from the official logging documentation for PostgreSQL 14 and PostgreSQL 18. The replacement’s source boundary and test evidence are recorded in its linked design article.

4 - One Endpoint, Several Sources: The Composite Exporter Contract

Composite mode combines PostgreSQL, PgBouncer, Patroni, pgBackRest, and PostgreSQL log metrics in one /metrics endpoint. It reduces the number of processes and scrape targets for a database node. PostgreSQL remains the primary source; each optional source has its own failure and health state.

Status: Implemented but unreleased.
Decision date: 2026-08-11; cached components added on 2026-08-23 and 2026-08-24.
Last verified: 2026-10-13.
Scope: Composite coordinator at source commit c0ba58a; absent from stable v1.4.1.
Delivery: Local tests passed. Not merged into main or tagged; packages, deployment, and production operation remain unverified.

PostgreSQL remains authoritative

Only a PostgreSQL gather error can make the Composite gather fail. A failure in an optional component affects that component’s metrics and health. A valid PostgreSQL gather still produces an HTTP 200 response.

All optional components are disabled by default. With none enabled, startup creates no optional workers, clients, or file readers, and the scrape path uses no Composite coordination lock.

Live collection and cached snapshots

Prometheus /metrics
        |
        v
Composite coordinator
  |-- PostgreSQL SQL       live, authoritative
  |-- PgBouncer SQL        live, optional
  |-- Patroni HTTP         live, optional
  |-- pgBackRest snapshot  cached, optional
  `-- PostgreSQL log       cached, optional

PostgreSQL and PgBouncer use the existing SQL collectors and their query caches. Patroni is fetched on each full scrape because role and leader state can change quickly.

pgBackRest and PostgreSQL logs use background workers. Each worker publishes an immutable snapshot. The HTTP request reads that snapshot, so command execution, log parsing, and state persistence do not delay database collection. See cached pgBackRest metrics and durable PostgreSQL log metrics for their refresh and recovery rules.

Timeout and concurrent requests

PgBouncer and Patroni run concurrently under --scrape-timeout, which defaults to 10 seconds. PostgreSQL runs in parallel under its existing query timeouts. The optional deadline does not cancel PostgreSQL collection.

If a full Composite scrape is already running, an overlapping request collects PostgreSQL, process metrics, and cached snapshots. It omits live PgBouncer and Patroni collection and reports their component up values as zero for that response. It does not update their stored error counters or last-success timestamps. This prevents optional collection from building a request queue.

Metric-family ownership

The ownership order for business metrics is:

PostgreSQL > Patroni > PgBouncer > pgBackRest > PostgreSQL Log

Process and HTTP self-metrics are merged after PostgreSQL and before the optional sources. Component-health metrics are merged last.

The first source to own a metric-family name owns the whole family. A lower-priority source cannot append samples, even when the type, help text, and labels match. Histogram and Summary suffixes are also reserved: a higher-priority foo Histogram blocks a lower-priority foo_count family.

On a conflict, the conflicting family is omitted and the optional component reports a gather error. Its other valid families remain available.

Patroni ingestion

PG Exporter fetches, parses, validates, and re-encodes Patroni’s classic Prometheus text. It preserves family names, help text, types, labels, and sample values. It does not add a component label to business metrics.

The response limits are 1 MiB, 10,000 sample lines, and 256 metric families. HTTPS uses system trust roots and an optional --http-ca-file. Certificate errors mark Patroni down; there is no certificate-verification bypass flag.

Component health

Composite mode exposes these self-metrics unless --disable-intro is set:

pg_exporter_component_enabled{component="..."}
pg_exporter_component_up{component="..."}
pg_exporter_component_scrape_duration_seconds{component="..."}
pg_exporter_component_scrape_errors_total{component="...",reason="..."}
pg_exporter_component_last_success_timestamp_seconds{component="..."}

enabled identifies configured components, including zero for disabled ones. up reports the collection outcome. The duration and last-success time describe a scrape for live sources and a background cycle for cached sources. Error reasons come from a fixed list, such as connect, timeout, tls, parse, and gather.

The existing /up, /health, /primary, and /replica routes continue to describe PostgreSQL. They are used for database health checks and routing, so optional components do not change their meaning. --disable-intro removes self-metrics and retains business metrics.

Alternatives and non-goals

• Separate exporters offer process isolation but require separate targets, packages, and service configuration. Composite mode is an optional deployment choice.
• Sequential collection makes the total latency depend on the order of sources. Concurrent live collection shares one optional timeout.
• Raw Patroni passthrough cannot enforce metric ownership or response limits. Parsing provides both checks.
• A generic plugin registry would expose lifecycle and precedence rules before the integrations were stable. This design uses named components and fixed precedence.
• An “all sources must succeed” policy would make database monitoring depend on optional services. The coordinator keeps failures local.

The design does not change metric namespaces or migrate Pigsty targets, dashboards, alerts, or existing standalone exporters.

Verification and consequences

The contract is implemented in exporter/composite.go, exporter/patroni.go, and the startup and reload paths. Tests cover disabled behavior, timeouts, TLS and format errors, family conflicts, concurrent requests, reload, and shutdown.

One process now needs the permissions for every enabled source. Grant only those permissions. Before replacing a standalone exporter, validate the released package, target labels, dashboards, and alerts in the destination environment.

5 - Snapshot Histograms Are Gauges, Not Counters

A scalar metric cannot describe the distribution of a changing population. One 30-minute transaction and one hundred 18-second transactions have the same total age, but different counts, maxima, and operational effects.

Status: Shipped in v1.4.0.
Decision date: 2026-07-11; revised on 2026-07-17.
Last verified: 2026-10-13.
Scope: HISTOGRAM columns and the pg_xact_age collector in stable v1.4.1, checked against development commit c0ba58a.
Delivery: Implementation, merge, release tag, and package publication completed. Focused local tests passed. Deployment and production operation remain unverified.

A PG Exporter snapshot Histogram groups the observations returned by one SQL execution. Each execution starts a new distribution. The exporter exposes its buckets, count, and sum as Gauge series because their values can decrease.

Time semantics

Open transactions appear when they start, move between age buckets, and disappear when they finish. This differs from a cumulative Histogram that counts events over time.

The output has the familiar bucket layout:

pg_xact_age_seconds_bucket{datname="app",le="10"} 5
pg_xact_age_seconds_bucket{datname="app",le="30"} 9
pg_xact_age_seconds_bucket{datname="app",le="+Inf"} 11
pg_xact_age_seconds_count{datname="app"} 11
pg_xact_age_seconds_sum{datname="app"} 214

All three families are Gauges. Buckets are cumulative across their upper bounds within this snapshot, not across scrapes. Use histogram_quantile() directly:

histogram_quantile(
  0.95,
  sum by (datname, le) (pg_xact_age_seconds_bucket)
)

For the current mean age, divide sum by count:

sum by (datname) (pg_xact_age_seconds_sum)
/
sum by (datname) (pg_xact_age_seconds_count)

Do not use rate(), irate(), or increase() on these snapshot series. Those functions interpret decreases as counter resets.

Configuration and SQL

Declare an observation column with usage: HISTOGRAM and explicit bucket bounds:

- seconds:
    usage: HISTOGRAM
    bucket: [1, 3, 10, 30, 100, 300, 1000, 3000]
    description: Open transaction age snapshot in seconds

Each non-NULL value is one observation. Rows with the same complete label tuple belong to one distribution. Multiple Histogram columns aggregate independently.

Bucket upper bounds are inclusive, finite, and strictly increasing. Configuration loading rejects empty lists, duplicates, NaN, and infinities. PG Exporter adds +Inf automatically. The le label and generated _bucket, _count, and _sum names are reserved; conflicting configuration is rejected.

scale applies before bucket assignment and summation. Timestamp and Boolean values use the scalar conversion rules and are not scaled. An explicit default turns NULL into an observation; otherwise NULL is ignored.

Query, cache, and failure behavior

Each SQL execution starts with empty accumulators. PG Exporter groups observations, calculates cumulative buckets, and publishes metrics after reading the complete result.

A query error, missing required column, or non-finite observation rejects the whole query result, including its scalar metrics. A failed refresh replaces the cached result with an empty result; it does not retain the previous distribution.

A cache hit returns the previous result without adding observations. The next SQL execution starts from zero. Reload discards the old collector and cache, including the old bucket layout.

Alternatives and non-goals

• prometheus.NewConstHistogram labels the data as a counter Histogram. Its time semantics do not match a changing SQL population.
• OpenMetrics GaugeHistogram describes the semantics directly, but would require a different exposition contract for this feature. Explicit Gauges work with the existing classic text endpoint.
• Native Histograms address storage and resolution. They do not change whether observations describe events or a current population.
• SQL could produce pre-aggregated buckets, but each collector would need to repeat the grouping logic.
• Retaining observations between scrapes would require event identity, restart handling, and duplicate prevention. Persistent event collection is outside this design; log Histograms have a separate contract.

Reference collector and validation

pg_xact_age groups open transaction age and idle-in-transaction age by database. It selects client backends and uses a 10-second TTL. Its bucket grids support current quantiles, counts above a threshold, and mean age.

This pgbench workload creates several query and idle-in-transaction duration bands. Use it on a test database to observe transactions entering and leaving the buckets:

\set query_band random(1, 4)
\if :query_band = 1
  \set query_ms 300
\elif :query_band = 2
  \set query_ms 3000
\elif :query_band = 3
  \set query_ms 10000
\else
  \set query_ms 30000
\endif

\set hold_band random(1, 4)
\if :hold_band = 1
  \set hold_ms 300
\elif :hold_band = 2
  \set hold_ms 3000
\elif :hold_band = 3
  \set hold_ms 10000
\else
  \set hold_ms 30000
\endif

BEGIN;
SELECT pg_current_xact_id(), pg_sleep(:query_ms / 1000.0);
\sleep :hold_ms ms
COMMIT;

Tests cover independent Histogram columns, NULL handling, scaling, bucket boundaries, and query failures. They also cover cache reuse, deterministic output, concurrent collection, and reload.

Consequences

The exporter provides aggregatable current distributions without keeping event history. Dashboard queries must use snapshot semantics even though the series use familiar Histogram suffixes.

See Collector Configuration for syntax and v1.4.0 for the release. Prometheus explains ordinary cumulative Histogram queries in Histograms and summaries.