Add contrib module pg_stat_log: cumulative statistics about server log messages
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t253465psql -h localhost -U postgresBuilt from patchset v1 (message #1), August 18, 2026 at 11:34 AM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t253465_1 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t253465_1 && git checkout t253465_1Patchset v1 (message #1) is on t253465_1
Hi hackers,
I'd like to propose a new contrib module, pg_stat_log, to track
cumulative statistics about server log messages. Patch attached.
Motivation
----------
Today the only way to answer questions like "how many errors of which
SQLSTATE is this instance producing, per database, per user?" is to
parse the server log. That is fragile, expensive at high log
volumes, usually requires external tooling, and the answer is gone if
logs have been rotated away. Meanwhile the server already sees every
message as structured data (ErrorData) at emit time.
pg_stat_log hooks into emit_log_hook and maintains cumulative counters
of emitted log messages grouped by (backend type, database, user,
severity level, SQLSTATE). This gives a cheap, always-on, SQL-queryable
signal for monitoring and alerting, e.g. spotting a spike in
serialization failures, deadlocks, or authentication errors without
touching the log files:
SELECT * FROM pg_stat_log ORDER BY count DESC;
backend_type | database_name | user_name | elevel | sqlerrcode |
sqlerrcode_name | count
----------------+---------------+-----------+--------+------------+
--------------------------+-------
client backend | app | web | ERROR | 40P01 |
deadlock_detected | 137
client backend | app | web | ERROR | 40001 |
serialization_failure | 89
client backend | | | FATAL | 28P01 |
invalid_password | 12
History and prior art
---------------------
The idea of turning log traffic into cumulative statistics is not new:
Joe Conway proposed pg_stat_logmsg back in 2023 [1]/messages/by-id/89742024-d51a-c66b-90b9-67f837072cd2@joeconway.com, a
pg_stat_statements-like contrib module keying messages by source
location (filename, lineno, elevel) with the format string for context.
That proposal was an explicit inspiration for this one. pg_stat_log
approaches the same problem from the operational side rather than the
source side: it keys by workload dimensions (backend type, database,
user, severity, SQLSTATE), which is the grouping a DBA typically wants
for dashboards and alerting, and it is built on the custom cumulative
statistics API rather than a pg_stat_statements-style shared hash plus
custom persistence. I see the two as complementary — keying by source
location answers "which code path fired", keying by workload dimensions
answers "who is affected by what" — and I'd be glad to hear from people
who followed that thread.
pg_stat_log itself started as an out-of-tree extension [2]https://github.com/fabriziomello/pg_stat_log, where it
went through several rounds of review, adversarial testing, and feature
work by Nikolay Samokhvalov (n_dropped accounting, slot reclamation on
reset, pg_stat_log_info(), among others). The attached patch is the
in-tree adaptation: SGML documentation, meson + make build integration,
and regression/TAP tests included.
Design
------
The module is built on the custom cumulative statistics API added in
PostgreSQL 18 (7949d9594582), using a fixed-amount stats kind. That
means the counters get all the standard cumulative-stats behavior for
free: snapshot consistency within a transaction, persistence across
clean shutdowns, discard on crash recovery, and pg_stat_reset-style
reset via pgstat_reset_of_kind().
A few design points worth calling out:
* Memory is a fixed-size shared block, capped by the
pg_stat_log.max_entries GUC (default 1024, PGC_POSTMASTER). Once all
entries are taken, already-tracked combinations keep counting and new
distinct combinations are dropped and counted in a n_dropped counter
exposed by pg_stat_log_info(), so capacity pressure is observable.
* Entries are kept in a separate-chaining hash table whose links are
array indices rather than pointers, laid out inside the stats block
itself. The fixed-amount stats API snapshots the block with a raw
memcpy and persists/restores it verbatim, so everything in it must be
position-independent and self-contained; that constraint is also why
none of dynahash/simplehash/dshash fit here (rationale in a comment
in pg_stat_log.c). Lookups, inserts, and drops are O(1) expected even
with the table full.
* The hot path is deliberately minimal: one LWLock acquire, a bucket
chain walk over preallocated memory, no allocation. emit_log_hook
runs in every process type (including postmaster and auxiliary
processes), which the module handles.
* Read access follows pg_read_all_stats; pg_stat_log_reset() is
superuser-only. Severity threshold is controlled by
pg_stat_log.min_error_level (SUSET, default warning), and collection
can be toggled at runtime with pg_stat_log.enabled.
The module requires shared_preload_libraries. Note that emit_log_hook
only fires for messages that actually reach the server log, so
log_min_messages acts as a floor on what is counted.
Performance
-----------
The interesting case is a pathological "log storm" (pgbench clients
emitting warnings with many distinct SQLSTATEs at full table
saturation): overhead there was in the low single digits percent in my
benchmarks. For regular workloads that don't log, there is no
measurable overhead, since the hook simply isn't reached. I can post
the benchmark harness and numbers if there's interest.
Open questions
--------------
* Is contrib the right home, or would people rather see this as a
builtin stats kind with a core pg_stat_* view? Contrib felt right for
a first step since it exercises the custom stats API and is entirely
optional, but I'm happy to go either way.
* The module currently uses custom stats kind ID 28, registered in the
wiki's custom kind registry [3]https://wiki.postgresql.org/wiki/CustomCumulativeStats. If it ships in contrib, should it
keep a registered custom ID, or is there an appetite for reserving
IDs for in-tree modules?
* The per-entry key is (backend_type, database, user, elevel,
sqlerrcode). Suggestions on additional dimensions (or fewer) are
welcome — each one multiplies cardinality against max_entries.
Thoughts?
[1]: /messages/by-id/89742024-d51a-c66b-90b9-67f837072cd2@joeconway.com
[2]: https://github.com/fabriziomello/pg_stat_log
[3]: https://wiki.postgresql.org/wiki/CustomCumulativeStats
--
Fabrízio Mello
PlanetScale Postgres Core Team