pg_tracing

pg_tracing is a PostgreSQL extension that generates server-side spans for distributed tracing.

When pg_tracing is active, it generates spans on sampled queries. To access these spans, the extension provides two ways:

• pg_tracing_consume_spans and pg_tracing_peek_spans views output spans as a set of records
• pg_tracing_json_spans function outputs spans as a OTLP json

The utility functions pg_tracing_reset and pg_tracing_info provide ways to read and reset extension's statistics. These are not available globally but can be enabled for a specific database with CREATE EXTENSION pg_tracing.

There are currently two mechanisms to propagate trace context:

• As a SQL comment using SQLCommenter
• As a GUC parameter pg_tracing.trace_context

See Trace Propagation for more details.

Warning

This extension is still in early development and may be unstable.

PostgreSQL Version Compatibility

pg_tracing only supports PostgreSQL 15 and 16 for the moment.

Generated Spans

pg_tracing generates spans for the following events:

• PostgreSQL internal functions: Planner, ProcessUtility, ExecutorRun, ExecutorFinish
• Statements: SELECT, INSERT, DELETE...
• Utility Statements: ALTER, SHOW, TRUNCATE, CALL...
• Execution Plan: A span is created for each node of the execution plan (SeqScan, NestedLoop, HashJoin...)
• Nested queries: Statements invoked within another statement (like a function)
• Triggers: Statements executed through BEFORE and AFTER trigger are tracked
• Parallel Workers: Processes created to handle queries like Parallel SeqScans are tracked
• Transaction Commit: Time spent fsync changes on the WAL

Documentation

The following list of files is found in the doc folder of the pg_tracing github repository. For installation instructions, please see the next section of this README.

File	Description
pg_tracing.md	Main reference documentation for pg_tracing.

Installation

From Source

pg_tracing can be compiled against an installed copy of PostgreSQL with development packages using PGXS.

To compile and install the extension, run:

git clone https://github.com/DataDog/pg_tracing.git
cd pg_tracing
make install
# To compile and install with debug symbols:
PG_CFLAGS="-g" make install

Setup

The extension must be loaded by adding pg_tracing to the shared_preload_libraries in postgresql.conf. A server restart is needed to add or remove the extension.

# postgresql.conf
shared_preload_libraries = 'pg_tracing'

compute_query_id = on
pg_tracing.max_span = 10000
pg_tracing.track = all

# Send spans every 2 seconds to an otel collector
pg_tracing.otel_endpoint = http://127.0.0.1:4318/v1/traces
pg_tracing.otel_naptime = 2000

The extension requires additional shared memory proportional to pg_tracing.max_span. Note that this memory is consumed whenever the extension is loaded, even if no spans are generated.

Trace Propagation

SQLCommenter

Trace context can be propagated through SQLCommenter. By default, all queries with a SQLCommenter with a sampled flag enabled will generate spans.

-- Query with trace context and sampled flag enable
/*traceparent='00-00000000000000000000000000000123-0000000000000123-01'*/ SELECT 1;

-- Check the generated spans
select trace_id, parent_id, span_id, span_start, span_end, span_type, span_operation from pg_tracing_consume_spans order by span_start;
             trace_id             |    parent_id     |     span_id      |          span_start           |           span_end            |  span_type   | span_operation
----------------------------------+------------------+------------------+-------------------------------+-------------------------------+--------------+----------------
 00000000000000000000000000000123 | 0000000000000123 | 4268a4281c5316dd | 2024-03-19 13:46:43.97958+00  | 2024-03-19 13:46:43.980121+00 | Select query | SELECT $1;
 00000000000000000000000000000123 | 4268a4281c5316dd | 87cb96b6459880a0 | 2024-03-19 13:46:43.979642+00 | 2024-03-19 13:46:43.979978+00 | Planner      | Planner
 00000000000000000000000000000123 | 4268a4281c5316dd | f5994f9159d8e80d | 2024-03-19 13:46:43.980081+00 | 2024-03-19 13:46:43.980111+00 | Executor     | ExecutorRun

trace_context GUC

The GUC variable pg_tracing.trace_context can also be used to propagate trace context.

BEGIN;
SET LOCAL pg_tracing.trace_context='traceparent=''00-00000000000000000000000000000005-0000000000000005-01''';
UPDATE pgbench_accounts SET abalance=1 where aid=1;
COMMIT;

-- Check generated span
select trace_id, span_start, span_end, span_type, span_operation from pg_tracing_consume_spans order by span_start;
             trace_id             |          span_start           |           span_end            |     span_type     |                      span_operation
----------------------------------+-------------------------------+-------------------------------+-------------------+-----------------------------------------------------------
 00000000000000000000000000000005 | 2024-07-05 14:24:55.305234+00 | 2024-07-05 14:24:55.305988+00 | Update query      | UPDATE pgbench_accounts SET abalance=$1 where aid=$2;
 00000000000000000000000000000005 | 2024-07-05 14:24:55.305266+00 | 2024-07-05 14:24:55.30552+00  | Planner           | Planner
 00000000000000000000000000000005 | 2024-07-05 14:24:55.305586+00 | 2024-07-05 14:24:55.305906+00 | ExecutorRun       | ExecutorRun
 00000000000000000000000000000005 | 2024-07-05 14:24:55.305591+00 | 2024-07-05 14:24:55.305903+00 | Update            | Update on pgbench_accounts
 00000000000000000000000000000005 | 2024-07-05 14:24:55.305593+00 | 2024-07-05 14:24:55.305806+00 | IndexScan         | IndexScan using pgbench_accounts_pkey on pgbench_accounts
 00000000000000000000000000000005 | 2024-07-05 14:24:55.649757+00 | 2024-07-05 14:24:55.649792+00 | Utility query     | COMMIT;
 00000000000000000000000000000005 | 2024-07-05 14:24:55.649787+00 | 2024-07-05 14:24:55.649792+00 | ProcessUtility    | ProcessUtility
 00000000000000000000000000000005 | 2024-07-05 14:24:55.649816+00 | 2024-07-05 14:24:55.650613+00 | TransactionCommit | TransactionCommit

Standalone Sampling

Queries can also be sampled randomly through the pg_tracing.sample_rate parameter. Setting this to 1 will trace all queries.

-- Enable tracing for all queries
SET pg_tracing.sample_rate = 1.0;

-- Execute a query that will be traced
SELECT 1;

-- Check generated spans
select trace_id, parent_id, span_id, span_start, span_end, span_type, span_operation from pg_tracing_consume_spans order by span_start;
             trace_id             |    parent_id     |     span_id      |          span_start           |           span_end            |  span_type   | span_operation
----------------------------------+------------------+------------------+-------------------------------+-------------------------------+--------------+----------------
 458fbefd7034e670eb3d9c930862c378 | eb3d9c930862c378 | bdecb6e35d429f3d | 2024-01-10 09:54:16.321253+00 | 2024-01-10 09:54:16.321587+00 | Select query | SELECT $1;
 458fbefd7034e670eb3d9c930862c378 | bdecb6e35d429f3d | ad49f27543b0175d | 2024-01-10 09:54:16.3213+00   | 2024-01-10 09:54:16.321412+00 | Planner      | Planner
 458fbefd7034e670eb3d9c930862c378 | bdecb6e35d429f3d | 8805f7749249536b | 2024-01-10 09:54:16.321485+00 | 2024-01-10 09:54:16.321529+00 | Executor     | ExecutorRun

Sending Spans

Spans can be automatically sent to an otel collector by setting the pg_tracing.otel_endpoint parameter:

# postgresql.conf
pg_tracing.otel_endpoint = http://127.0.0.1:4318/v1/traces
pg_tracing.otel_naptime = 2000

If an otel endpoint is defined, a background worker will be started and will send spans every $naptime using the OTLP HTTP/JSON protocol. The endpoint can be changed while the server is running, but if it was not set when the server was started, changing it to non-empty value requires a restart.