Downloads

Blog

Bringing pt-query-digest-Style Slow Query Analysis to PostgreSQL with pg_enhanced_query_logging

May 8, 2026

Author

Agustín

Uncategorized

Share this Post:

In this blog post, we are going to briefly discuss pg_enhanced_query_logging (PEQL for short), a PostgreSQL extension that produces slow query logs in the same format MySQL and Percona Server users have been feeding into pt-query-digest for years. The idea is simple: reuse the tried-and-true tools and concepts we have been using for performing full query audits with low performance hits. This tool was conceived and developed for the recent Percona Build with AI Competition.

A quick word of caution before we begin: PEQL is under active development and has not been validated for production use. We will use it in a development environment here, and you should do the same.

Why a new slow log for PostgreSQL?

Out of the box, PostgreSQL gives us log_min_duration_statement and a handful of related GUCs that print slow queries to the server log. That is useful, but the format is line-oriented and mixed in with everything else PostgreSQL writes there. On the MySQL side, the Percona Server extended slow query log goes much further: per-query counters, lock and I/O times, plan-quality flags, and a structured format that pt-query-digest can group by query fingerprint and rank by total time, average time, lock time, etc. This introduces the more powerful concept of performance of a family of queries, and not just individual query executions.

PEQL ports that same workflow to PostgreSQL. It hooks into the executor and planner, captures timing, buffer I/O, WAL, JIT and row-count metrics for every query slower than a configurable threshold, and writes them to a dedicated log file using a pt-query-digest-compatible format.

Motivation and benefits

The original idea behind this extension is doing query audits with minimal impact on the running server. We want to be able to ask “what queries will we benefit more from tuning?” without paying for it in latency, I/O or in a flood of unrelated log lines.

That goal drives most of the design decisions:

Statistically accurate sampling with low overhead. We don’t need to log every single query to draw useful conclusions. PEQL can sample 1 out of every N queries (or 1 out of every N sessions), and doing this for enough time will mean that we can have a sample that represents the overall workload for that time period. The cost on the producer side stays low even on busy servers.
pt-query-digest compatibility out of the box. The output format mirrors the MySQL/Percona Server slow log, so the same toolchain we already use for MySQL audits works for PostgreSQL with no extra steps.
Logging to a separate file. All entries go to a dedicated file (default peql-slow.log), not to PostgreSQL’s main error log. That keeps the error log clean for actual errors and lets us point the slow log at a separate mountpoint if we want to isolate its I/O from the rest of the server.
Rate limiting by both queries and bytes per second. On top of the per-session/per-query 1-in-N sampling, peql.rate_limit_auto_max_queries and peql.rate_limit_auto_max_bytes give us a cluster-wide cap on logged queries per second and on bytes written per second. Useful for guaranteeing that the slow log itself never becomes a performance issue.
Always-log override for slow outliers. Even when sampling is on, peql.rate_limit_always_log_duration lets us say “but always log anything that takes longer than X ms”. The common queries get randomly sampled; the long-running ones always get logged.
Extended resource usage metrics. Each entry includes buffer hit/read/dirtied/written counts (shared, local and temp), block I/O timings, WAL records/bytes/full-page images, JIT compilation timings, planning time, optional memory context allocations and an optional wait-event histogram.
Execution plans embedded in the entry. With peql.log_query_plan = on, the full EXPLAIN ANALYZE output (text or JSON) is appended to each entry, so the plan that produced the metrics is right there next to them when we are reviewing the log later.
Automatic pause when disk space is low. If the log mountpoint drops below a configurable free-space threshold, PEQL pauses logging on its own (with optional auto-purge of old rotated files) and resumes once there is room again. The database keeps serving traffic; the slow log gets out of the way.

Installing the extension

PEQL is a regular PGXS extension, to build it we can execute the following steps:

git clone https://github.com/guriandoro/pg_enhanced_query_logging.git
cd pg_enhanced_query_logging
make USE_PGXS=1
sudo make install USE_PGXS=1

git clone https://github.com/guriandoro/pg_enhanced_query_logging.git

cd pg_enhanced_query_logging

make USE_PGXS=1

sudo make install USE_PGXS=1

This installs the shared library into $(pg_config --pkglibdir) and the SQL/control files into $(pg_config --sharedir)/extension/. The hooks live in the shared library, so we need to preload it. Add the following line to postgresql.conf (or edit your current value to include it):

shared_preload_libraries = 'pg_enhanced_query_logging'

1	shared_preload_libraries = 'pg_enhanced_query_logging'

Restart PostgreSQL, and then create the extension in any database where we want the SQL helper functions:

CREATE EXTENSION pg_enhanced_query_logging;

1	CREATE EXTENSION pg_enhanced_query_logging;

To easily test it, the repository ships a Docker-based quick start that builds Rocky Linux 9 + PostgreSQL 18 with the extension preloaded:

./test/deploy_docker_pg18_rhel.sh

1	./test/deploy_docker_pg18_rhel.sh

A minimal configuration

For a first look, the easiest thing to do is to log every query at full verbosity:

shared_preload_libraries = 'pg_enhanced_query_logging'
peql.log_min_duration = 0 # log every query
peql.log_verbosity = 'full' # emit all metric lines

shared_preload_libraries = 'pg_enhanced_query_logging'

peql.log_min_duration = 0 # log every query

peql.log_verbosity = 'full' # emit all metric lines

While we are at it, we can also silence PostgreSQL’s native query logging so we have a single place to look:

log_statement = 'none'
log_min_duration_statement = -1
log_duration = off

log_statement = 'none'

log_min_duration_statement = -1

log_duration = off

By default, PEQL writes to peql-slow.log inside PostgreSQL’s log_directory. The location and filename are configurable via peql.log_directory and peql.log_filename.

What an entry looks like

After running a few queries, opening peql-slow.log shows entries like this one (trimmed for brevity):

# Time: 2026-03-11T09:15:32.847291
# User@Host: app_user[app_user] @ 10.0.1.42 []
# Thread_id: 48712 Schema: mydb.public
# Query_id: -6432758210044805760
# Query_time: 1.285034 Lock_time: 0.000000 Rows_sent: 256 Rows_examined: 87500
# Shared_blks_hit: 4096 Shared_blks_read: 312 Shared_blks_dirtied: 0 Shared_blks_written: 0
# Temp_blks_read: 0 Temp_blks_written: 48
# Shared_blk_read_time: 0.024310 Shared_blk_write_time: 0.000000
# WAL_records: 0 WAL_bytes: 0 WAL_fpi: 0
# Plan_time: 0.003210
# Full_scan: Yes Temp_table: No Temp_table_on_disk: Yes Filesort: Yes Filesort_on_disk: No
# JIT_functions: 4 JIT_generation_time: 0.001250 JIT_emission_time: 0.003100
SET timestamp=1741680931;
SELECT o.id, o.total, c.name FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending' ORDER BY o.total DESC LIMIT 256;

# Time: 2026-03-11T09:15:32.847291

# User@Host: app_user[app_user] @ 10.0.1.42 []

# Thread_id: 48712 Schema: mydb.public

# Query_id: -6432758210044805760

# Query_time: 1.285034 Lock_time: 0.000000 Rows_sent: 256 Rows_examined: 87500

# Shared_blks_hit: 4096 Shared_blks_read: 312 Shared_blks_dirtied: 0 Shared_blks_written: 0

# Temp_blks_read: 0 Temp_blks_written: 48

# Shared_blk_read_time: 0.024310 Shared_blk_write_time: 0.000000

# WAL_records: 0 WAL_bytes: 0 WAL_fpi: 0

# Plan_time: 0.003210

# Full_scan: Yes Temp_table: No Temp_table_on_disk: Yes Filesort: Yes Filesort_on_disk: No

# JIT_functions: 4 JIT_generation_time: 0.001250 JIT_emission_time: 0.003100

SET timestamp=1741680931;

SELECT o.id, o.total, c.name FROM orders o JOIN customers c ON c.id = o.customer_id

WHERE o.status = 'pending' ORDER BY o.total DESC LIMIT 256;

The full breakdown of every field, with the GUCs that produce it, lives in doc/annotated-sample.md. This is a great place to start reading the documentation.

Feeding it to pt-query-digest

Because the format mirrors the MySQL slow log, we can point pt-query-digest at it directly:

pt-query-digest --type slowlog $(pg_config --logdir)/peql-slow.log

1	pt-query-digest --type slowlog $(pg_config --logdir)/peql-slow.log

We get the familiar profile at the top (queries grouped by fingerprint, ranked by total time), followed by the per-query detail blocks. The plan-quality flags above can also be used as filters, for example to look only at queries that did a sequential scan:

pt-query-digest --type slowlog \
--filter '$event->{Full_scan} eq "Yes"' \
$(pg_config --logdir)/peql-slow.log

pt-query-digest --type slowlog \

--filter '$event->{Full_scan} eq "Yes"' \

$(pg_config --logdir)/peql-slow.log

If you do not have pt-query-digest installed, the standalone script can be downloaded directly:

curl -LO https://percona.com/get/pt-query-digest
chmod +x pt-query-digest

1 2	curl -LO https://percona.com/get/pt-query-digest chmod +x pt-query-digest

Example pt-query-digest outputs will look like the following images.

Queries grouped by fingerprint, ranked by total time.

A few useful knobs

Once we move beyond logging everything, there are a handful of GUCs worth knowing about:

peql.rate_limit: 1-in-N sampling, either per session or per query, with a peql.rate_limit_always_log_duration override so that very slow queries are always captured even when sampling is on.
peql.log_parameter_values: include actual bind parameter values for prepared statements alongside the placeholder query text.
peql.log_query_plan: embed the full EXPLAIN ANALYZE output (text or JSON) inside the log entry, so the plan that produced the metrics is right there next to them. This can be expensive in terms of I/O, so use sparingly and only if needed.

The full list, with default values and contexts, is documented in doc/configuration.md.

Future work

The pt-query-digest compatibility is a feature, but it’s also a constraint: the MySQL slow log format was designed to be human readable, which means it’s way too verbose. For instance, the plan-quality flags line only has 5 bits of actual information, but uses around 100 bytes to encode them:

# Full_scan: Yes Temp_table: No Temp_table_on_disk: No Filesort: Yes Filesort_on_disk: No

1	# Full_scan: Yes Temp_table: No Temp_table_on_disk: No Filesort: Yes Filesort_on_disk: No

We can do this better by simply logging YNNYN or 10010 (hence the 5 bits of information mentioned above), and have the position within the query log entry make it self-explanatory as to what this information is.

This is a 20x amplification factor! And other lines suffer of similar issues… Multiply that by every query on a busy server and the overhead adds up quickly, both in disk space and in the I/O the backend has to do to write the entries out.

There are two pieces of follow-up work we have in mind to address this:

A PEQL-native log format. A more compact, structured format (think key-value pairs with short keys, bitfields for the boolean flags, or a binary framing for the numeric metrics) that drops the bytes-per-query cost without losing any of the information we currently emit. The verbose pt-query-digest-compatible format could still be available for users that want it; the native format would be the recommended option for high-throughput workloads.
Tooling for the new format. Once the native format exists, we will either contribute a parser to pt-query-digest so that it can ingest it natively (--type peql or similar), or ship a small companion tool that either post-processes them or produces the same kind of profile reports pt-query-digest does today. Either way, the goal is to keep the analysis workflow we are used to while removing the format-imposed overhead from the producer side.

If any of this sounds interesting and you would like to help shape it, the repository’s doc/contributing.md is the right place to start.

Conclusion

PostgreSQL has had rich per-query metrics available for a while now, but stitching them together into the kind of “show me the worst-performing family of queries from the last hour” workflow MySQL users have enjoyed for years has taken more effort. PEQL closes that gap by emitting a single, pt-query-digest-compatible log file with timing, buffer, WAL, JIT and plan-quality data attached to every query.

If you want to dig deeper, the doc/ directory in the repository has detailed pages on the output format, the architecture of the hooks, the rate limiter and the disk-space protection logic. And if you have not used pt-query-digest before, this is a great time to do it!