Design Records

• 1: Cluster Progress with Safe Relation Labels
• 2: Bounded SQL Scrapes and Client Cancellation
• 3: Container Trust Roots and Release Checks
• 4: Target Identity and Consistent Health Responses
• 5: Correcting Replication Collector Identity and Semantics
• 6: Turning PostgreSQL CSV Logs into Durable Metrics
• 7: Caching pgBackRest Metrics Outside the Scrape Path
• 8: The PostgreSQL Observability Product We Chose Not to Build
• 9: One Endpoint, Several Sources: The Composite Exporter Contract
• 10: Snapshot Histograms Are Gauges, Not Counters

1 - Cluster Progress with Safe Relation Labels

Status: Implemented in v1.6.0.
Decision / last verification: 2026-11-09.
Scope: v1.6.0 at source 6bee809 on main, #115; PostgreSQL 12+ vacuum, index creation, and cluster progress. Not shipped in v1.5.0.
Delivery: Source committed and pushed as 6bee809; local validation and Test Release passed. Versioned archives, RPM/DEB packages, and checksums are built for the draft. A public tag, public images, deployment, and production verification remain separate gates.

Decision

The progress views report work across the instance, but relid::regclass resolves names only within the connected database. An OID from another database can resolve to an unrelated local relation.

Keep pg_vacuuming, pg_indexing, and pg_clustering tagged cluster, primary. Resolve relname as relid::regclass::text when the row belongs to the connected database or the relation is a system catalog: OIDs below 16384 (FirstNormalObjectId) are assigned by initdb and name the same catalog in every database. For other relations in another database, emit the numeric OID as text, retaining datname and pid to identify the task. Do not filter out cross-database rows.

Keep metric names, labels, types, version ranges, TTLs, and three cluster-level queries per refresh. Cross-database coverage continues when auto-discovery is disabled, a database is excluded, or the monitoring role cannot connect to it, subject to the progress view’s existing visibility rules. No new connections or name lookups are required.

Tradeoff and rejected alternatives

A numeric OID is less readable than a relation name, but accurately reports the identifier available from the main connection. This fixes false names without losing catalog names, such as a VACUUM of pg_attribute in another database; it deliberately does not promise cross-database name resolution for user relations.

Per-database collection was rejected because it increases refresh queries from three to three per monitored database, including when no progress exists. It also loses progress from excluded or inaccessible databases. Adding lookup connections for active tasks duplicates discovery and permissions handling. Renaming labels would require a separate metric migration. Base-backup progress is unchanged.

Acceptance and upgrade

Execute the PostgreSQL 12–16, 17, and 18+ SQL variants with local and foreign rows sharing the same relation OID. Verify the local name, the foreign numeric OID, and the name of a foreign system catalog, with one sample per task. Check primary/replica planning, that discovered connections do not duplicate cluster collectors, and cross-database coverage with discovery disabled or excluded databases.

Update split YAML and generated configuration together. Existing installations must merge or replace these collector definitions; a binary-only upgrade does not apply this SQL correction. Local SQL tests do not establish production-load acceptance.

Local verification

PostgreSQL 12, 17, and 18 passed monitoring-role SQL integration tests covering all three query-version branches. Temporary progress views include a local and foreign task with the same relation OID; the local task receives its name and the foreign task its numeric OID. A real PostgreSQL 18 index build remained visible once per metric with discovery disabled, with the database excluded, and when the monitoring role lacked CONNECT on that database. Planning tests cover PostgreSQL 12–19 and prevent duplicate collection on discovered connections.

A review follow-up added a foreign pg_catalog.pg_attribute row to the fixture. On PostgreSQL 18.6 it reports pg_attribute; the earlier condition reported 1249. A live throttled VACUUM of pg_attribute in another database confirmed the same, while a user table there still reported its OID. PostgreSQL 12 and 17 were not rerun for this follow-up.

2 - Bounded SQL Scrapes and Client Cancellation

Status: Implemented in v1.6.0.
Decision / last verification: 2026-11-09.
Scope: v1.6.0 at source 6bee809 on main, addressing #116. Not shipped in v1.5.0.
Delivery: Source committed and pushed as 6bee809; local validation and Test Release passed. Versioned archives, RPM/DEB packages, and checksums are built for the draft. A public tag, public images, deployment, and production verification remain separate gates.

Decision

Queries previously derived their contexts from context.Background(), allowing work to continue after the metrics client disconnected. More HTTP requests could wait indefinitely for the exporter lock. Bound this work while preserving failure isolation and cache reuse.

Admit at most two metrics requests, including collection, waiting, and response writing. One request collects at a time; a second scraper can wait for the same collection lock within its remaining request budget. Further requests receive HTTP 503. Waiting is cancelable without starting another goroutine or executing SQL. This supports two Prometheus scrapers without an unbounded queue or shared cancellation ownership.

Derive the request budget from the existing 30-second HTTP write timeout, shortened by a valid positive finite X-Prometheus-Scrape-Timeout-Seconds header. Queueing consumes this same budget. Prechecks inherit the context; collection checks it before each database and collector and starts no further collector once it expires. A collector already running with a timeout finishes its predicates and main query, which share that one deadline, even if the request ends, and its result is cached normally for later scrapers. A negative query timeout removes the per-query deadline, so only such a query is canceled with the request.

Keep the existing failure-cache policy: a refresh that fails, including a canceled unbounded query, clears that collector’s result and records its attempt for the normal TTL, so the next request does not immediately repeat it. A departing client never turns a healthy bounded refresh into a failure: a fatal collector does not report pg_up 0 to other scrapers, and 60- or 300-second size and bloat collectors do not disappear for their TTL. A fatal collector with a negative timeout is the exception: canceling it caches a fatal failure for its TTL, so fatal collectors should keep a timeout. A pass ended by the client or the request budget has an INFO summary log and is not counted in pg_exporter_scrape_error_count or pg_exporter_server_scrape_error_count, which keep counting connection and fatal collector errors. Other collectors’ caches remain intact. No partial result from the interrupted collector is published, and no expired value is silently served as fresh. TTL zero and --disable-cache still explicitly request refreshes on every scrape. A long TTL therefore also delays recovery from a failed refresh; this is the existing tradeoff, not a new retry scheduler.

After gathering produces the response snapshot, release the collection lock before encoding and writing. The request still occupies an admission slot until its response finishes, bounding both waiting and slow responses. Keep Prometheus encoding, compression, Go/process metrics, and handler instrumentation. The normal collector interface remains usable without storing request context on the exporter.

Alternatives and boundaries

Immediate rejection of every overlapping request causes avoidable gaps for two scrapers. Clearing the retry timestamp on cancellation repeatedly runs expensive queries and churns connections. Both were rejected during review. Canceling bounded queries with the request was also rejected: one client’s disconnect became a failure cached for every scraper, and each lib/pq cancellation closes its connection. Letting the running collector finish normally costs up to its own timeout (2 seconds in the default configuration) of collection-lock time after the budget expires. Stopping between that collector’s predicates and main query was rejected: it needs a separate not-attempted state and rollback, while saving only work bounded by the same deadline. Serving old results indefinitely would conceal stale measurements and needs a separate freshness contract. Background refresh, shared gathers, a parallel scheduler, new flags, and driver migration are outside this correction.

The budget stops collection and cancels unbounded SQL. A running bounded collector can extend the response past the budget, normally by up to its own timeout. A stalled network can hold the driver longer, as in v1.5.0, so neither the budget nor a query timeout is a hard wall-clock limit, and no fixed encoding margin is reserved. The HTTP write deadline remains the transport limit. In particular, lib/pq connection handshakes need an appropriate DSN connect_timeout; a query context alone does not bound every network phase. See the target-state record for independent health probes.

Error Accounting

An interrupted pass does not increment pg_exporter_scrape_error_count or pg_exporter_server_scrape_error_count; its summary log is INFO interrupted. Query errors and fatal-query ERROR logs remain observable. A real failure coinciding with request cancellation can therefore be omitted from these two pass counters. Without request cancellation, connection/precheck failures and fatal query failures, including their own timeouts, retain their error accounting. Non-fatal failures remain query-level errors.

Acceptance

Verify repeated cancellation does not bypass TTL; healthy collectors and valid caches remain usable; retry resumes after TTL expiry. Exercise two overlapping scrapers, a canceled waiter, a full admission limit, cancellation of unbounded SQL, a client leaving during bounded SQL whose result must stay cached, an interrupted pass that must not increment error counters, and a slow response that must not hold the collection lock. Real PostgreSQL checks must confirm cancellation, bounded SQL attempts, and successful later scrapes. Validate PgBouncer separately. Local acceptance does not establish a release or production-load guarantee.

Local verification

The full race suite and ten repeated runs of the focused concurrency tests passed. On PostgreSQL 18.6, a canceled refresh executed once during its TTL, while six following requests returned healthy metrics without repeating the failed SQL; expiry allowed a successful retry. Five rounds of two overlapping scrapers both returned HTTP 200, with one SQL refresh per round. Tests also cover canceled waiters, admission exhaustion, partial rows, predicate/precheck cancellation, and releasing the collection lock before a slow response finishes. No background refresh or stale-value fallback was added.

These cancellation runs predate a review adjustment that changes only queries with a positive timeout. Afterward, on PostgreSQL 18.6, a client leaving or a 0.3-second budget during a 0.8-second bounded query let that query finish; the expired budget returned 503 after 0.8 seconds, and the next scrape reused the result without executing it again. A 3-second unbounded query was canceled by PostgreSQL (SQLSTATE 57014) when the client left at 1.5 seconds, left no active backend, and stayed absent within its TTL. Before the adjustment, the same bounded query was canceled and disappeared for its TTL. A regression test confirms that a client leaving during a fatal collector no longer gives the next scraper pg_up 0.

A second review found that the running collector’s later predicates and main query also continue after cancellation, and that interrupted passes incremented both error counters with an error log. The contract above now states the collector-level behavior, and interrupted passes are no longer counted. On PostgreSQL 18.6, a client leaving during a 0.8-second fatal bounded query previously left both counters at 1 with an error log; afterward both stayed at 0 and the pass was logged at info level, while the next scrape returned pg_up 1 and the cached metric. Regression tests now use explicit synchronization instead of sleeps, and a real connection failure is still counted; the full suite passed 20 race-detector runs.

3 - Container Trust Roots and Release Checks

Status: Implemented in v1.6.0.
Decision date / last verification: 2026-11-08 / 2026-11-09.
Scope: Container and release-validation changes based on v1.5.0 source 1891254, incorporating the packaging correction from 68c95ba. No exporter runtime or driver change.
Delivery: Source 370b9fa is included in v1.6.0 at 6bee809 and pushed. Both Docker build paths, amd64/arm64 GoReleaser images, three DEB architectures, Go tests, static checks, and the documentation build are validated. CI and draft artifacts are tracked by the v1.6.0 release gates; public tag, published images, deployment, and production verification remain separate.

Problem

Both v1.5.0 Docker build paths end at scratch without a system CA bundle. The ordinary Dockerfile also omits LICENSE; the GoReleaser Dockerfile already includes it. An OCI license label identifies the license but does not carry its text.

The current lib/pq driver uses Go’s system trust roots when sslrootcert is omitted. A container without roots cannot verify a publicly trusted server certificate in verify-ca or verify-full mode. The v1.5.0 target-state record correctly documents the previous need to mount a CA.

Decision and invariants

Keep the final image based on scratch. Copy the distribution CA bundle to /etc/ssl/certs/ca-certificates.crt, a standard Go trust-store location, and include /LICENSE in both build paths. The source build obtains roots from its Go builder; the release build installs ca-certificates in a separate Alpine stage and copies only the bundle into the final image. The Docker build-context allowlist must admit LICENSE.

This supplies public trust roots; it does not enable TLS automatically. Omitted sslmode still becomes disable. Use sslmode=verify-full to verify both the chain and hostname. Private CAs still require a mounted file selected by sslrootcert. No private key or organization-specific root belongs in the published image.

Roots are fixed at image build time, so CA updates require rebuilding and distributing the image. Runtime package installation, a shell, driver migration, mutual TLS changes, and changes to the default TLS mode are outside this decision.

Alternatives

Requiring every deployment to mount public roots retains the packaging gap and complicates ordinary verified connections. Switching the final image to a complete Linux distribution adds runtime files unnecessary for this static binary. Copying the CA bundle preserves the existing container shape while supplying the missing runtime data.

Acceptance and release boundaries

CI builds the source Dockerfile. The release test builds the actual GoReleaser snapshot, including amd64 and arm64 Docker images, without publishing it. Both paths run the same image checks:

• Compare the image’s license and default configuration with the source files, parse its CA bundle, and execute its binary.
• Connect to a disposable PostgreSQL TLS server using verify-ca and verify-full. An unknown private CA must fail; an explicit trusted CA must succeed.
• Append the test CA to the image’s bundle through a temporary mount and verify system-root lookup without sslrootcert. Reject a mismatched hostname under verify-full.

The TLS cases use a local server and generated certificates, without depending on a public TLS endpoint. They verify bundle parsing and trust-store lookup, not every public CA or the full mutual TLS matrix.

Release artifact checks retain RPM compatibility assertions and add DEB metadata, payload, configuration modes, conffiles, and installation checks. All DEB architectures are inspected; the container’s native architecture is installed and its binary runs as prometheus. Checksums must verify successfully.

These gates validate a build candidate. The v1.6.0 manuals and release copy describe this correction; deploy them with the public release, after its artifacts are available.

4 - Target Identity and Consistent Health Responses

Status: Original contract shipped in v1.5.0; the 2026-11-09 health amendment is implemented in v1.6.0.
Decision / last verification: Original decision 2026-10-23; health amendment and local verification 2026-11-09.
Scope: v1.5.0 at source 1891254 (fix baseline 9b378cb). This decision covers target URL handling and database identity, metadata-driven collector selection, HTTP health snapshots, YAML durations, and metrics-route validation.
Delivery: Source committed and pushed; tag v1.5.0 points to 18912540fe5edce0ed0ee81fac0d989738ecaa87. CI and the release workflow passed; the GitHub Release, RPM/DEB packages, archives, and Docker images are published. This does not establish production deployment or production-load acceptance.

Target identity

Malformed URL syntax and malformed query escaping must fail startup without printing the original URL or a parser error that contains it. The shared URL parser also rejects decoded query parameter names that are empty or contain Unicode whitespace or =. This prevents unsafe names from reaching driver parsing or bypassing password masking. Diagnostics use fixed text without the URL or offending name. Dropping an invalid query parameter can discard an explicit TLS setting. Omitted sslmode still defaults to disable; valid explicit settings remain intact.

Use the existing lib/pq parser to determine the database name for the supported URL forms, including paths, explicit query parameters, and database aliases. Reimplementing their precedence in exporter code can select PostgreSQL mode for a PgBouncer target or misidentify a discovered database. When replacing the database for discovery, remove both dbname and database query parameters and write one escaped path. Preserve all unrelated parameters and exact database-name characters. Service-file target resolution and PgBouncer identity inferred only from connection defaults remain outside this correction.

When PG_EXPORTER_URL_FILE is selected, empty or whitespace-only contents must fail startup. This is the file containing the connection URL; it is not a TLS private-key check.

Collector selection

Successful metadata prechecks invalidate the query plan when username, schema membership, or extension membership changes, as they already do for version, database, and recovery state. Compare sets, not array order. The next collection rebuilds eligible collectors and their caches and resets query statistics through the existing planning path. Stable metadata retains the current plan and caches. This avoids requiring a manual reload after installing an extension without adding a poller or another metadata query.

Health snapshots

Publish one atomic status enum: unknown, down, starting, primary, or replica. Derive Up() and Recovery() from that enum. Each health HTTP handler reads it once and derives both the response code and body from that value. Three separately atomic fields do not form one atomic snapshot, and repeated reads can combine different probe generations.

In v1.5.0, the existing routes, HTTP codes, background probe interval, and scrape-derived health updates remained. This guarantees internal response consistency; it does not make a cached probe a synchronous proof of database availability. A mutex is unnecessary because the entire state fits in one atomic integer.

Health probe independence (v1.6.0, 2026-11-09)

This amendment addresses #112 and #113, based on main at 9d9804f. It is included in v1.6.0 at 6bee809, after v1.5.0. Source is committed and pushed; CI and draft artifacts are separate release gates. A draft is not a public release or production deployment.

Before v1.6.0, the main server’s collection lock delayed background probes, and a scrape that finishes collecting child databases can overwrite a newer probe with an older main-target observation. Keep a separate short lock for database-handle initialization. Probes share the existing pool without waiting for the collection lock. The main PostgreSQL pool remains at three connections and discovered pools at one. Increase the main PgBouncer pool from one to two, allowing one serial collection and one serial probe without falsely reporting down because the exporter occupied its own connection. This costs at most one additional PgBouncer connection. Probes pass a connection-timeout context, but handshake phases still need an appropriate DSN connect_timeout; this is not a hard termination deadline.

After the constructor’s initial state, publish health only from serialized health probes. Scrape results continue to update pg_up and pg_in_recovery, but no longer write the HTTP health snapshot. Routes, response codes, and the one-second probe interval remain. Health describes the most recently completed reachability/role probe; a fatal collector failure is represented by scrape metrics rather than by a separate writer to health.

This avoids timestamp arbitration between two writers and a second connection pool. No new health flag, worker, or synchronous SQL in HTTP health handlers is needed. Regression tests must keep probes responsive during a blocked collector, preserve newer role/failure observations when a child scrape finishes, and cover initialization, concurrent explicit/background probes, and shutdown under the race detector.

Metrics route

Startup validation must verify that a request for the configured path selects the metrics handler itself. A matching pattern string is insufficient: a mux redirect can report the same pattern without reaching that handler. Compare the returned handler with the registered metrics handler, reject paths that select a redirect or another endpoint, and retain escaped paths that reach metrics under the active mux. This does not require a blanket ban on percent-encoded paths.

Validation and boundaries

Regression tests cover explicit database identity, special characters and conflicting parameters, malformed URLs, schema/extension/user eligibility changes, unchanged metadata ordering, concurrent health transitions, and reachable escaped paths and redirects under both HTTP mux implementations. New regression cases fail before the correction and pass afterward.

The final v1.5.0 pre-release checks add this evidence:

• Invalid TLS escaping is rejected before connecting; verify-full fails against a server without TLS. Logs omit connection passwords, and an empty selected URL file is rejected.
• Discovery with conflicting parameters and special characters returns the correct database samples. Adding and removing databases, schemas, and hstore changes the plan as metadata changes. Stable sets preserve caches.
• Invalid single-file reloads retain the previous definitions and caches. Health recovers after reconnecting. Physical standby promotion updates the role and tag selection. Concurrent scrape, health, and reload responses keep status codes and bodies consistent.
• Go race tests, both HTTP mux modes, go vet, Staticcheck, module and generated-config consistency, six target builds, RPM/DEB installation, and Docker builds pass. Short fuzz runs cover roughly 604,000 inputs, with valid UTF-8 URL identities and actual HTTP URL construction.
• Default primary collection was rerun as a non-superuser on PostgreSQL 9.1–9.6, 10–18, 19 RC1, plus PgBouncer 1.26.1. PostgreSQL 10–13 uses the documented subscription OID grant. This does not exhaust extension combinations, transient faults, or production workloads.

The full certificate-chain/hostname/mTLS matrix, a real SQLSTATE 57P03 startup transition, and production load are outside this verification scope. Docker certificate verification still requires a mounted CA. Directory loading can skip invalid files; empty YAML is a valid zero-collector configuration; SIGTERM does not wait for active HTTP requests. These existing behaviors are unchanged.

Related configuration validation rejects non-finite or positively overflowing YAML query TTL, query timeout, and predicate TTL seconds before conversion to time.Duration; zero/default and finite negative timeout semantics remain. No driver migration, optional-source integration, metric-name change, or general refactor is part of this decision.

The amendment passes deterministic race tests for blocked collectors, newer primary/replica/down observations, pool exhaustion, concurrent DB initialization, and stopping the health loop during collection. On PgBouncer 1.26.1, a deliberately delayed statistics response did not block version probes or cause false down responses; stopping and restarting the service was detected. These checks do not establish PostgreSQL failover or production-load acceptance.

5 - Correcting Replication Collector Identity and Semantics

Status: Shipped in v1.5.0.
Decision date / last verified: 2026-10-22 / 2026-10-23.
Scope: Collector corrections in pg_exporter main commit d97ba38fc8390984df994b2f9ea58b43ecfc8b89, based on 72f00a4fa4e6eb7524174f2122b3fe1e1d608db4; PostgreSQL REL_19_RC1 at 0b8bff042a997fe50fcd1c4c83b9ef1cc9ed8a89, with focused PostgreSQL 9.6–18 checks. These corrections ship in v1.5.0 and are absent from v1.4.1.
Delivery: Source committed and pushed; tag v1.5.0 points to 18912540fe5edce0ed0ee81fac0d989738ecaa87. CI and the release workflow passed; the GitHub Release, RPM/DEB packages, archives, and Docker images are published. This does not establish production deployment or production-load acceptance.

Decisions

Subscription identity

The pair (datname, subname) identifies a subscription within one PostgreSQL cluster. PostgreSQL enforces a unique (subdbid, subname) key, while database names are unique within the cluster. The query must resolve the owning database through the subscription OID:

JOIN pg_catalog.pg_subscription s ON s.oid = subscription_id
JOIN pg_catalog.pg_database d ON d.oid = s.subdbid

Here subscription_id means the existing source alias: s2.subid in the PG19 branch and the statistics-view subid in earlier branches. Add d.datname to the result and declare it as a LABEL. Do not use CURRENT_DATABASE(), join subscriptions by name alone, or add a constant connection-database label.

The existing query must still produce one row per subscription. Preserve the apply-leader filters and PG19’s exclusion of sequence synchronization workers. Database identity distinguishes different subscriptions; worker filtering prevents repeated rows for the same subscription. Both are required.

PostgreSQL 10–13 permission prerequisite

PostgreSQL 10–13 omitted public SELECT permission on pg_subscription.oid. The upstream correction was deliberately made for PostgreSQL 14, without changing older branches. Therefore this database-label design has an explicit upgrade prerequisite for those versions:

-- Execute as an administrator in the exporter's main connection database.
GRANT SELECT (oid) ON pg_catalog.pg_subscription TO monitor;

Use the actual monitoring role. The grant belongs in each database that may be used as the exporter’s main connection, not merely the databases owning subscriptions. It grants no access to subconninfo. PostgreSQL 14+ needs no additional grant for this join under the standard catalog ACLs; locally tightened ACLs still require local provisioning.

This is a deployment cost, not an automatic permission repair. On PG10–13, apply the grant before deploying the modified collector configuration. Without it, SQL fails and the subscription metrics are absent even if the HTTP response itself remains 200. Acceptance must check expected samples and query errors as well as HTTP status.

Alternatives and migration

An additional subid label would avoid both joins and the old-version permission prerequisite. Database identity was selected for readable, database-oriented filtering consistent with other collectors. Given that requirement, two catalog joins and one documented legacy grant are smaller than a new privilege wrapper or runtime fallback. Do not add both labels without a separate need.

Preserve the id GAUGE instead of converting it into a label and deleting pg_sub_id. Existing sample counts and family names remain unchanged, but the new label creates new time series. Check label-sensitive PromQL joins and aggregation. Database or subscription renames also change identity. Target labels continue to distinguish clusters.

Receiver state dictionary

The receiver source was compared at release boundaries from PostgreSQL 9.6 through 19 RC1. PostgreSQL 9.6–18 has six states; PG19 adds connecting.

Remove startup, catchup, and backup from the receiver CASE and HELP. Reserve their old codes 1–3 rather than assigning new meanings. Apply the same mapping to the three current branches and the legacy pg_recv_96 branch; no new version branch or Go state machinery is needed.

An internal state does not guarantee a visible statistics row. In RC1 the receiver function suppresses the row when the PID is zero or statistics are not ready. Do not fabricate a stopped or streaming sample when the row is absent. Preserve connecting behavior when sender host and port are NULL.

Adversarial testing also found that the legacy receiver query used regexp_match(), introduced in PostgreSQL 10, on PG9.6. Replacing its two first-capture expressions with scalar substring(... FROM ...) makes that query executable on PG9.6 without adding a branch, set-returning function, or conninfo parser. The regular expressions and resulting host/port labels stay the same. This narrow prerequisite is included with the dictionary correction.

PG9.6 itself reveals only the receiver PID to a non-superuser; the other statistics fields are NULL. The collector therefore reports state -1 under that role. Correcting SQL compatibility does not bypass this visibility rule or justify granting superuser to a monitoring account.

The final legacy query executed on PG9.6–12. A real PG9.6.24 standby returned streaming state 0 to its administrator, state -1 to the ordinary monitor, and 11 receiver samples with HTTP 200 to that monitor. Host/port fixtures covered missing fields, NULL, Unix paths, IPv6, repeated keys, and quoted input; PG10–12 produced the same results with the old and new extraction expressions. This preserves the existing regular-expression behavior, including its limitations on quoted values containing spaces.

Optional replication origins

The current and legacy configurations already set skip: true. Keep that setting. Explain that users must provision privileges before explicitly enabling the collector.

For PostgreSQL 14+ with standard catalog ACLs, an administrator can grant the actual monitoring role:

GRANT SELECT ON pg_catalog.pg_replication_origin_status TO monitor;
GRANT EXECUTE ON FUNCTION
  pg_catalog.pg_show_replication_origin_status() TO monitor;

Run grants in the exporter’s main connection database. PostgreSQL 13 and older retain an internal superuser check; GRANT alone does not enable direct non-superuser use. Keep those optional paths disabled unless the administrator separately chooses and owns an appropriate access arrangement. This correction adds no SECURITY DEFINER wrapper, automatic GRANT, retry under another identity, or synthetic zero values.

The origin catalog pg_replication_origin is already publicly readable under the standard ACLs, so an extra grant on it is redundant. Locally restricted catalog ACLs require the administrator to provision the query’s catalog access as well.

Publication and subscription counts

Document the exact existing behavior:

• pg_pubrel_count counts rows from pg_get_publication_tables(), not sequences. Its CROSS LATERAL query emits no row for empty or sequence-only publications; absence is not an explicit zero.
• In PG19, pg_subrel_count groups tables and sequences from pg_subscription_rel by synchronization state. The PG19 table states are i/d/f/s/r; sequence states are i/r.
• Counts on different sides are not a replication-health equality. Multiple publications, overlapping objects, partitions, refresh, and asynchronous synchronization can affect interpretation.

Change descriptions and manuals only. Keep DISTINCT subid, subname in the subscription relation query. Do not add pg_pubseq_count, a relkind label, zero-filling, or a different object-count definition in this patch.

Validation and remaining gates

The final configuration and a binary built from the stated main baseline were tested with a non-superuser monitoring role against PostgreSQL 10.21, 11.16, 12.22, 13.23, 14.23, 15.18, 16.14, 17.10, 18.4, and 19 RC1. Two databases with the same subscription name produced distinct labels and zero duplicate sample keys. PG10–13 failed before the column grant, still failed when it was granted only in a subscription database, and passed after the grant in the main connection database; subconninfo remained inaccessible. The PG10–14 branches emitted 14 samples, PG15–18 emitted 18, and PG19 emitted 42, reflecting their existing family counts.

RC1 full-configuration checks passed for disabled and active subscriptions, automatic discovery on and off, and a physical standby. Targeted HTTP checks captured table-sync and sequence-sync workers before and after the scrape alongside apply workers, still producing only 42 subscription samples. The default full plan did not select pg_origin. Receiver mapping fixtures covered PG9.6 and each PG10–19 major; actual RC1 streaming and connecting were also observed, including connecting with NULL sender fields and HTTP state 5. Fixtures do not prove that every transient state was observed live.

Origin tests with real origin entries confirmed that the view grant alone is insufficient, PG14+ succeeds with the documented function grant, and PG13 and older still reject non-superusers. Empty origin catalogs are not a sufficient permission test: the LEFT JOIN may avoid calling the restricted function. On RC1 a mixed publication returned one table, its subscription counted one table plus one sequence, and empty/sequence-only publications emitted no row.

Local Go tests with the race detector, the compatibility HTTP-mux mode, go vet, dependency consistency, schema/version coverage, generated-file parity, and label-conflict checks passed. No Go implementation or dependency changes were needed. Source validation and the paired documentation build are separate from release and production acceptance.

Implementation boundaries

The source change is limited to seven split collector files and their two generated bundles. The companion changes are the paired collector manuals and this paired Design record. The manuals distinguish existing permission/count semantics from the identity, dictionary, and PG9.6 SQL corrections in v1.5.0. Source review, commit, push, tagging, and formal artifacts are complete; production rollout remains a separate action.

Non-goals include a driver migration, Gatherer redesign, runtime privilege management, new collector branches, new metric families, changes to subscription relation aggregation, or unrelated legacy SQL repair.

Primary references

• RC1 subscription identity constraints
• PG14 correction to subscription column permissions
• RC1 receiver state and visibility implementation
• PG14 origin function permission change
• RC1 system views

6 - 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.

7 - 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.

8 - 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.

9 - 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.

10 - 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.