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 v5 (message #5), September 30, 2026 at 11:39 PM.
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_5 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_5 && git checkout t253465_5Patchset v5 (message #5) is on t253465_5
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
On Tue, Aug 18, 2026 at 7:23 AM Fabrizio Mello <fabrizio@planetscale.com>
wrote:
Hi hackers,
I'd like to propose a new contrib module, pg_stat_log, to track
cumulative statistics about server log messages. Patch attached.
+1
I like this idea.
I believe Nikolay Samokhvalov is going to run/check the performance impact.
The name... maybe it would be more clear as: pg_stat_log_elevel
(clarifies the abstract "log" a bit better).
Even pg_stat_server_log might be better. Just my opinion, "log" feels too
lonely/generic.
Kirk
On 18 Aug 2026, at 13:23, Fabrizio Mello <fabrizio@planetscale.com> wrote:
* 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.
IMHO, contrib is not the right home for anything new. Either we think it's
important for production and place it in core or it should live as an external
extension.
Your email didn't motivate why this extension should be in core instead of
maintained as an external extension?
--
Daniel Gustafsson
On Thu, Sep 3, 2026 at 00:58 Daniel Gustafsson <daniel@yesql.se> wrote:
On 18 Aug 2026, at 13:23, Fabrizio Mello <fabrizio@planetscale.com>
wrote:
* 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.IMHO, contrib is not the right home for anything new. Either we think it's
important for production and place it in core or it should live as an
external
extension.Your email didn't motivate why this extension should be in core instead of
maintained as an external extension?
I strongly support having this in core, exposed through a pg_stat_* view,
rather than in contrib or an external extension.
Reason is quite straightforward: error counters / error analysis is
fundamental database monitoring.
For example, I want to alert when Postgres reports an internal error
(XX000), data corruption (XX001), or index corruption (XX002). Today,
standard statistics don't provide general counters by SQLSTATE. We have
rollbacks, deadlocks, recovery conflicts, and some other specific counters,
but they don't tell us whether these errors occurred.
Getting that information from logs is now the only way, but operationally
much harder than collecting counters:
- Logs mix diagnostic messages with statements and potentially sensitive
data / PII, such as parameter values. Giving a monitoring system access to
all of that is different from letting it read error counts. I personally
feel this when working with users of managed Postgres platforms. It's
usually not a problem to get pg_monitor access, and it's a completely
different story to get access to logs.
- Exporting pg_stat_* counters to Prometheus is straightforward and common.
Processing logs requires a separate pipeline, with additional permissions,
storage, resource usage, and failure modes.
• In managed Postgres, users may have SQL monitoring access but limited
control over log collection or which extensions are available.
- SQL path of analysis is also great for smaller clusters that cannot
justify full fledged monitoring stacks -- having ability to just
self-analyze and see if we had errors of some kinds is good enough for
smaller cases.
Counters would not replace logs. I want monitoring to tell me that Postgres
reported a corruption error, then use logs to investigate it. Collecting
the full diagnostic details should not be a prerequisite for detecting the
event, in my opinion.
An external extension is useful for developing and testing the
implementation, but I don't see it as the right long-term home for this
capability, especially considering how long it might take for it to
propagate to managed Postgres platforms.
Nik
On Thu, Sep 3, 2026 at 4:58 AM Daniel Gustafsson <daniel@yesql.se> wrote:
On 18 Aug 2026, at 13:23, Fabrizio Mello <fabrizio@planetscale.com> wrote:
* 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.IMHO, contrib is not the right home for anything new. Either we think it's
important for production and place it in core or it should live as an external
extension.Your email didn't motivate why this extension should be in core instead of
maintained as an external extension?
Thanks Daniel, that's a fair point, and after Nikolay's strong case for
core [1]/messages/by-id/CAM527d9FX3FL7P63Ww9qVQfaqWoUDMMAp_GgSxWg+O2HWdigeQ@mail.gmail.com I'm convinced this belongs there: error counters by SQLSTATE
are fundamental monitoring, counters are far easier to expose to
monitoring systems than log access (which mixes diagnostics with
statements and potentially sensitive data), and external extensions
take a long time to propagate to managed platforms, if ever.
So here is v2, reworked from a contrib module into a builtin cumulative
statistics kind with a core view. Summary of what changed:
* New builtin stats kind PGSTAT_KIND_LOGMSG. Messages are now counted
directly in EmitErrorReport() when they are written to the server
log, instead of via emit_log_hook, so the previous hook-related
caveats (load order, preload requirement) are gone. As before,
log_min_messages acts as a floor on what can be tracked.
* The view is now pg_stat_log_messages, which hopefully also addresses
Kirk's point that "pg_stat_log" was too generic. Columns are
unchanged from v1: (backend_type, database, user, elevel, sqlerrcode,
sqlerrcode_name, count) plus stats_reset. Access requires
pg_read_all_stats.
* Counting is opt-in. The two previous GUCs were folded into a single
enum GUC, track_log_messages, which sets the minimum severity level
to count. The default is none, i.e. disabled.
* Reset is integrated into the usual machinery:
pg_stat_reset_shared('log_messages'). Counters persist across clean
restarts and are discarded after crash recovery, like other
cumulative statistics. Unlike v1, the dropped-entries counter and
stats_reset timestamp now persist too.
* Capacity: builtin fixed-amount stats kinds are sized at compile time
(their structs are embedded in PgStat_ShmemControl and
PgStat_Snapshot), so the previous max_entries GUC is gone. Instead
the table is sized proportionally to the number of SQLSTATE error
codes known to the server: 64 combinations per named errcode, about
16,800 entries in total. That is roughly 0.6 MB of shared memory
(allocated at startup regardless of the GUC) and the same increase in
the stats file at shutdown; a backend's snapshot only materializes
those pages if it actually reads the view. Once the table is full,
already-tracked combinations keep counting and new ones are counted
in pg_stat_get_log_messages_dropped(). I'm happy to adjust the
multiplier if people feel 64 is too generous or too tight.
* The SQLSTATE condition names (e.g. "deadlock_detected") come from a
small lookup table generated from errcodes.txt by a new
generate-errcodes-names.pl script, which also emits the errcode count
used to size the entry table.
* Documentation in monitoring.sgml and config.sgml, regression tests in
stats.sql, and persistence/crash coverage added to the existing
src/test/recovery/t/029_stats_restart.pl.
The entries are still kept in an index-based separate-chaining hash
table living entirely inside the stats block (chain links are array
indices, not pointers), since fixed-amount statistics are snapshotted
with a raw memcpy and persisted verbatim; the rationale, including why
dynahash/simplehash/dshash don't fit these constraints, is in a comment
in pgstat_logmsg.c.
Nikolay: since your alerting use case (XX000/XX001/XX002) motivated a
good part of this, I'd appreciate a look at whether the view shape and
the opt-in default work for you.
[1]: /messages/by-id/CAM527d9FX3FL7P63Ww9qVQfaqWoUDMMAp_GgSxWg+O2HWdigeQ@mail.gmail.com
--
Fabrízio de Royes Mello
PlanetScale Postgres Core Team
Hi Fabrizio,
On Tue, Sep 29, 2026 at 1:21 AM Fabrízio Mello <fabrizio@planetscale.com>
wrote:
On Thu, Sep 3, 2026 at 4:58 AM Daniel Gustafsson <daniel@yesql.se> wrote:
Your email didn't motivate why this extension should be in core instead
of
maintained as an external extension?
Thanks Daniel, that's a fair point, and after Nikolay's strong case for
core [1] I'm convinced this belongs there: error counters by SQLSTATE
are fundamental monitoring, counters are far easier to expose to
monitoring systems than log access (which mixes diagnostics with
statements and potentially sensitive data), and external extensions
take a long time to propagate to managed platforms, if ever.So here is v2, reworked from a contrib module into a builtin cumulative
statistics kind with a core view. Summary of what changed:
Thanks for the updated patch!
I did not look at it in details, just at what outcome it would provide and I
think it would also
make sense to provide this information on a per-backend basis. There is
ongoing work
to redesign the per-backend statistics infrastructure [1]/messages/by-id/anCjoFkGah/uB7lw@bdtpg, which, I think,
might be worth
considering for this feature.
[1]: /messages/by-id/anCjoFkGah/uB7lw@bdtpg
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com