pg_enhanced_query_logging - PEQL

A PostgreSQL extension that produces pt-query-digest-compatible slow query logs with extended PostgreSQL-specific metrics. Modeled after Percona Server's improved slow query log, it gives PostgreSQL users the same rich query analysis workflow that MySQL/Percona Server users have enjoyed for years.

The extension hooks into the executor pipeline to capture timing, buffer I/O, WAL (Write-Ahead Log), JIT (Just-In-Time compilation), and row-count metrics for every query that exceeds a configurable duration threshold, then writes them to a dedicated log file that pt-query-digest can parse directly.

⚠️ Warning This extension is under active development and has not been validated for production use. Use it in development and testing environments only. APIs, configuration parameters, and log format may change in future releases without notice.

Want to see what the extension can do at a glance? Check the Annotated Sample Entry -- a fully commented log entry showing every metric and feature in action.

Features

• pt-query-digest compatibility -- output format matches the MySQL slow query log so pt-query-digest --type slowlog works out of the box
• Three verbosity levels -- from lightweight timing-only (minimal) to full buffer/WAL/JIT instrumentation (full)
• Plan quality indicators -- Full_scan, Filesort, Filesort_on_disk, Temp_table, Temp_table_on_disk derived from the actual plan tree
• Rate limiting -- per-session or per-query sampling with an always-log override for very slow queries
• Buffer, WAL, and I/O timing metrics -- shared/local/temp block counts, read/write times, WAL records and bytes
• JIT metrics -- function count, generation/optimization/inlining/emission times
• Planning time tracking -- separate planner hook measures planning wall-clock time
• Memory context tracking -- experimental per-query memory allocation measurement (why experimental?)
• Utility statement logging -- optional logging of DDL (Data Definition Language) and other utility statements
• Nested statement logging -- optional logging of statements inside PL/pgSQL functions
• Parameter value logging -- optional inclusion of bind parameter values from prepared statements and parameterized queries in log entries
• EXPLAIN plan inclusion -- optional EXPLAIN ANALYZE output embedded in the log entry
• Disk space protection -- automatic pause of logging when the log mountpoint runs low on disk, with optional old log purging

Requirements

• PostgreSQL 17 or later (tested on 18.x)
• C compiler and pg_config in your PATH (or specified explicitly)
• Optional: Percona Toolkit for pt-query-digest

Installing pt-query-digest

If you don't have pt-query-digest installed, you can grab the standalone script:

cd ~/.local/bin
curl -LO https://percona.com/get/pt-query-digest
chmod +x pt-query-digest
export PATH="$HOME/.local/bin:$PATH"

To make the PATH change permanent, add export PATH="$HOME/.local/bin:$PATH" to your shell profile (~/.bashrc, ~/.zshrc, etc.).

Installation

Building from Source

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

# Build (auto-detects pg_config in PATH)
make USE_PGXS=1

# Or specify pg_config explicitly
make USE_PGXS=1 PG_CONFIG=/path/to/pg_config

# Install into the PostgreSQL installation
sudo make install USE_PGXS=1

# Clean build artifacts
make clean USE_PGXS=1

# Full clean (includes files generated by pg_regress and prove)
make maintainer-clean USE_PGXS=1

This installs:

• pg_enhanced_query_logging.so (or .dylib) into $(pg_config --pkglibdir)
• pg_enhanced_query_logging.control and the SQL files (pg_enhanced_query_logging--1.0.sql, pg_enhanced_query_logging--1.1.sql, pg_enhanced_query_logging--1.2.sql, pg_enhanced_query_logging--1.3.sql, and upgrade scripts) into $(pg_config --sharedir)/extension/

Loading the Extension

Add to postgresql.conf:

shared_preload_libraries = 'pg_enhanced_query_logging'

Restart PostgreSQL, then create the extension in each database where you want the SQL helper functions:

CREATE EXTENSION pg_enhanced_query_logging;

Note: The extension's logging hooks are active for all databases as soon as the module is loaded via shared_preload_libraries. The CREATE EXTENSION step only installs the SQL-callable helper functions (pg_enhanced_query_logging_reset() and pg_enhanced_query_logging_stats()).

Quick Environment Setup (Docker)

The test/ directory includes scripts to spin up a fully configured Docker environment and run benchmarks with no manual installation required. This is the fastest way to try the extension.

Prerequisites: Docker must be installed and running.

# 1. Build and launch a Rocky Linux 9 + PostgreSQL 18 container
#    with the extension compiled, installed, and preloaded.
./test/deploy_docker_pg18_rhel.sh

# 2. Run a pgbench 5-phase benchmark (PEQL ON / OFF / 1% sampling /
#    native PG logging / no logging).
#    Defaults to a 600s run; override with PEQL_BENCH_DURATION.
PEQL_BENCH_DURATION=120 ./test/run_pgbench.sh

The deploy scripts also apply PostgreSQL performance tuning for the 50 GB container (memory, WAL/checkpoints, parallelism, connections, and background writer) so the server can handle read and write heavy workloads out of the box. They also start a PMM server and client so you can monitor PostgreSQL in real time. Once it finishes, it prints connection details:

Connect:      PGPASSWORD=peqltest psql -h localhost -p 15433 -U postgres
PMM UI:       https://localhost:8444  (admin / admin)
Teardown:     ./test/deploy_docker_pg18_rhel.sh teardown

To tear down all containers, the network, and the images when you are done:

# Remove the PostgreSQL, PMM client, and PMM server containers + network
./test/deploy_docker_pg18_rhel.sh teardown

# Optionally remove the built images to free disk space
docker rmi peql-pg18-rhel percona/pmm-server:3 percona/pmm-client:3

Both scripts accept environment variables for customization. Run ./test/run_pgbench.sh --help or see the script headers for the full list (port, password, clients, scale factor, workload mode, etc.).

Quick Start

Add these lines to postgresql.conf and restart:

shared_preload_libraries = 'pg_enhanced_query_logging'
# log all queries
peql.log_min_duration = 0
# include all extended metrics
peql.log_verbosity = 'full'

You can also disable PostgreSQL's native logging:

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

Run some queries, then analyze with pt-query-digest:

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

Documentation

Detailed documentation is available in the doc/ directory:

• Configuration Reference -- all peql.* GUC variables, context values, and runtime examples
• Output Format -- log entry examples at each verbosity level and a complete field reference
• Annotated Sample Entry -- a full log entry with every feature enabled, explained section by section
• Using with pt-query-digest -- filtering, time window analysis, comparing periods, and sampled data
• Architecture -- hook chain, rate limiting internals, file I/O, and plan tree analysis
• SQL Functions -- pg_enhanced_query_logging_reset() and pg_enhanced_query_logging_stats()
• Testing -- prerequisites, regression tests, TAP tests, Meson build, CI/CD, and manual testing
• Compatibility Notes -- differences from MySQL behavior and known limitations
• Disk Space Protection -- automatic pause, auto-purge, and monitoring under low disk conditions
• Contributing -- how to contribute and code style guidelines

License

This project is released under the PostgreSQL License, the same license used by PostgreSQL itself.

Acknowledgments

• Inspired by Percona Server's extended slow query log
• Built for use with Percona Toolkit's pt-query-digest