pg_session_trace - PostgreSQL Session Tracing Extension

pg_session_trace is a PostgreSQL extension that provides Oracle 10046-equivalent session tracing functionality with minimal performance overhead. It captures SQL statements, bind variables, execution plans, and timing metrics in either text or binary format.

Features

• Per-session trace control without server restart
• Two output formats:
  • Text format: Human-readable, direct file writes per session
  • Binary format: High-performance with ring buffers and background worker
• Comprehensive query lifecycle capture:
  • SQL statements with full text
  • Execution plans
  • Query execution timing
  • Bind variables (fully implemented)
  • Row counts
• Production-safe with < 3% overhead
• Oracle 10046 compatible trace levels:
  • Level 0: Disabled
  • Level 1: SQL statements only
  • Level 4: SQL + bind variables (full trace)

Installation

Prerequisites

• PostgreSQL 15+
• PostgreSQL development headers (postgresql-server-dev-XX)
• C compiler (gcc or clang)
• GNU make

Building

git clone https://github.com/yourusername/pg_session_trace.git
cd pg_session_trace
make
sudo make install

Configuration

Add to postgresql.conf:

shared_preload_libraries = 'pg_session_trace'
pg_session_trace.enabled = on
pg_session_trace.output_directory = 'pg_session_trace'
pg_session_trace.level = 4
pg_session_trace.max_sessions = 100
pg_session_trace.buffer_size = 1MB
pg_session_trace.binary_format = off  # or 'on' for binary format

Then restart PostgreSQL:

sudo systemctl restart postgresql

Usage

Enable tracing for current session

-- Enable tracing
SELECT pg_session_trace_enable_session(pg_backend_pid());

-- Set trace level (0, 1, or 4)
SELECT pg_session_trace_set_level(pg_backend_pid(), 4);

-- Your SQL queries here...

-- Disable tracing
SELECT pg_session_trace_disable_session(pg_backend_pid());

View active trace sessions

SELECT * FROM pg_session_trace_list_sessions();
-- Returns: session_pid, session_id, start_time, trace_level, events_captured, bytes_written, events_dropped

The events_dropped column indicates if the ring buffer filled up and events were lost (only in binary mode).

Reading trace files

Text format traces

Text format creates one file per session:

cat /var/lib/postgresql/15/main/pg_session_trace/pg_session_trace_<PID>_<SESSION_ID>.trc

Binary format traces

Binary format uses a background worker that consolidates all sessions. You MUST use the provided reader tool to parse these files.

# Use the included trace reader (usually installed to PostgreSQL bin directory)
pg_session_trace_reader /var/lib/postgresql/data/pg_session_trace/pg_session_trace_YYYYMMDD_HHMMSS.trc

# Options:
# -v            Verbose output
# -b            Hide bind variables
# -s SESSION    Show only specific session
# -q QUERY      Show only specific query
# -S            Summary only

Configuration Parameters

Parameter	Type	Default	Context	Description
pg_session_trace.enabled	bool	off	SIGHUP	Master enable/disable switch
pg_session_trace.level	int	4	SIGHUP	Default trace level for new sessions
pg_session_trace.output_directory	string	pg_session_trace	SIGHUP	Directory for trace files
pg_session_trace.max_sessions	int	100	POSTMASTER	Maximum concurrent trace sessions
pg_session_trace.buffer_size	int	1MB	SIGHUP	Buffer size per session
pg_session_trace.flush_interval	int	1000ms	SIGHUP	Buffer flush interval
pg_session_trace.binary_format	bool	on	SIGHUP	Use binary format (recommended)
pg_session_trace.bgworker_naptime	int	100ms	SIGHUP	Background worker poll interval
pg_session_trace.file_rotation_size	int	100MB	SIGHUP	Binary file rotation size
pg_session_trace.file_rotation_age	int	3600s	SIGHUP	Binary file rotation age

Architecture

Text Format Mode

• Direct file writes from each session
• One trace file per session
• Lower latency, higher I/O overhead
• Best for debugging individual sessions

Binary Format Mode (Recommended)

• Lock-free ring buffers in shared memory
• Background worker consolidates all sessions
• Minimal impact on query execution
• Best for production use

Memory Layout

Shared Memory
├── TraceSharedState (control structure)
├── SessionEntry array (session registry)
└── DSA (Dynamic Shared Area)
    └── Ring buffers (per session)

Bind Variable Capture

pg_session_trace captures bind variables for parameterized queries when trace level is set to 4. This includes:

• Parameter position and type
• Parameter value (with appropriate formatting based on type)
• Support for all common PostgreSQL data types
• NULL values are properly indicated
• Large values are truncated to prevent excessive trace file growth

Supported Types

• Numeric types: int2, int4, int8, float4, float8, numeric
• String types: text, varchar, char, bpchar
• Date/time types: date, time, timestamp, timestamptz, interval
• Boolean type
• UUID type
• Array types (shown as ARRAY[...])
• JSON/JSONB types
• And many more PostgreSQL built-in types

Example with Bind Variables

PREPARE stmt AS SELECT * FROM orders WHERE customer_id = $1 AND order_date >= $2;
EXECUTE stmt(123, '2025-01-01');

The trace will show:

• The prepared statement with parameter placeholders
• Each bind variable's position, type, and value
• The actual execution with resolved parameters

Functions

Function	Description
pg_session_trace_enable_session(pid)	Enable tracing for a session
pg_session_trace_disable_session(pid)	Disable tracing for a session
pg_session_trace_set_level(pid, level)	Set trace level (0,1,4)
pg_session_trace_list_sessions()	List all active trace sessions

Testing

Run regression tests:

make installcheck

Acknowledgments

Based on PostgreSQL's extensibility features and inspired by Oracle's 10046 trace events.