- Notifications
You must be signed in to change notification settings - Fork5
Distributed Tracing for PostgreSQL
License
DataDog/pg_tracing
Folders and files
| Name | Name | Last commit message | Last commit date | |
|---|---|---|---|---|
Repository files navigation
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_spansandpg_tracing_peek_spansviews output spans as a set of recordspg_tracing_json_spansfunction outputs spans as a OTLP json
The utility functionspg_tracing_reset andpg_tracing_info provide ways to read and reset extension's statistics. These are not available globally but can be enabled for a specific database withCREATE EXTENSION pg_tracing.
There are currently two mechanisms to propagate trace context:
- As a SQL comment usingSQLCommenter
- As a GUC parameter
pg_tracing.trace_context
SeeTrace Propagation for more details.
Warning
This extension is still in early development and may be unstable.
pg_tracing only supports PostgreSQL 15 and 16 for the moment.
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
The following list of files is found in thedoc folder of the pg_tracing github repository. Forinstallation instructions, please see the next section of this README.
| File | Description |
|---|---|
| pg_tracing.md | Main reference documentation for pg_tracing. |
pg_tracing can be compiled against an installed copy of PostgreSQL with development packages usingPGXS.
To compile and install the extension, run:
git clone https://github.com/DataDog/pg_tracing.gitcd pg_tracingmake install# To compile and install with debug symbols:PG_CFLAGS="-g" make install
The extension must be loaded by adding pg_tracing to theshared_preload_libraries inpostgresql.conf.A server restart is needed to add or remove the extension.
# postgresql.confshared_preload_libraries = 'pg_tracing'compute_query_id = onpg_tracing.max_span = 10000pg_tracing.track = all# Send spans every 2 seconds to an otel collectorpg_tracing.otel_endpoint = http://127.0.0.1:4318/v1/tracespg_tracing.otel_naptime = 2000The extension requires additional shared memory proportional topg_tracing.max_span. Note that this memory is consumed whenever the extension is loaded, even if no spans are generated.
Trace context can be propagated throughSQLCommenter. 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'*/SELECT1;-- Check the generated spansselect trace_id, parent_id, span_id, span_start, span_end, span_type, span_operationfrom pg_tracing_consume_spansorder by span_start; trace_id | parent_id | span_id | span_start | span_end | span_type | span_operation----------------------------------+------------------+------------------+-------------------------------+-------------------------------+--------------+----------------00000000000000000000000000000123 |0000000000000123 | 4268a4281c5316dd |2024-03-1913:46:43.97958+00 |2024-03-1913:46:43.980121+00 |Select query |SELECT $1;00000000000000000000000000000123 | 4268a4281c5316dd | 87cb96b6459880a0 |2024-03-1913:46:43.979642+00 |2024-03-1913:46:43.979978+00 | Planner | Planner00000000000000000000000000000123 | 4268a4281c5316dd | f5994f9159d8e80d |2024-03-1913:46:43.980081+00 |2024-03-1913:46:43.980111+00 | Executor | ExecutorRun
The GUC variablepg_tracing.trace_context can also be used to propagate trace context.
BEGIN;SET LOCALpg_tracing.trace_context='traceparent=''00-00000000000000000000000000000005-0000000000000005-01''';UPDATE pgbench_accountsSET abalance=1where aid=1;COMMIT;-- Check generated spanselect trace_id, span_start, span_end, span_type, span_operationfrom pg_tracing_consume_spansorder by span_start; trace_id | span_start | span_end | span_type | span_operation----------------------------------+-------------------------------+-------------------------------+-------------------+-----------------------------------------------------------00000000000000000000000000000005 |2024-07-0514:24:55.305234+00 |2024-07-0514:24:55.305988+00 |Update query |UPDATE pgbench_accountsSET abalance=$1where aid=$2;00000000000000000000000000000005 |2024-07-0514:24:55.305266+00 |2024-07-0514:24:55.30552+00 | Planner | Planner00000000000000000000000000000005 |2024-07-0514:24:55.305586+00 |2024-07-0514:24:55.305906+00 | ExecutorRun | ExecutorRun00000000000000000000000000000005 |2024-07-0514:24:55.305591+00 |2024-07-0514:24:55.305903+00 |Update |Updateon pgbench_accounts00000000000000000000000000000005 |2024-07-0514:24:55.305593+00 |2024-07-0514:24:55.305806+00 | IndexScan | IndexScan using pgbench_accounts_pkeyon pgbench_accounts00000000000000000000000000000005 |2024-07-0514:24:55.649757+00 |2024-07-0514:24:55.649792+00 | Utility query |COMMIT;00000000000000000000000000000005 |2024-07-0514:24:55.649787+00 |2024-07-0514:24:55.649792+00 | ProcessUtility | ProcessUtility00000000000000000000000000000005 |2024-07-0514:24:55.649816+00 |2024-07-0514:24:55.650613+00 | TransactionCommit | TransactionCommit
Queries can also be sampled randomly through thepg_tracing.sample_rate parameter. Setting this to 1 will trace all queries.
-- Enable tracing for all queriesSETpg_tracing.sample_rate=1.0;-- Execute a query that will be tracedSELECT1;-- Check generated spansselect trace_id, parent_id, span_id, span_start, span_end, span_type, span_operationfrom pg_tracing_consume_spansorder by span_start; trace_id | parent_id | span_id | span_start | span_end | span_type | span_operation----------------------------------+------------------+------------------+-------------------------------+-------------------------------+--------------+---------------- 458fbefd7034e670eb3d9c930862c378 | eb3d9c930862c378 | bdecb6e35d429f3d |2024-01-1009:54:16.321253+00 |2024-01-1009:54:16.321587+00 |Select query |SELECT $1; 458fbefd7034e670eb3d9c930862c378 | bdecb6e35d429f3d | ad49f27543b0175d |2024-01-1009:54:16.3213+00 |2024-01-1009:54:16.321412+00 | Planner | Planner 458fbefd7034e670eb3d9c930862c378 | bdecb6e35d429f3d | 8805f7749249536b |2024-01-1009:54:16.321485+00 |2024-01-1009:54:16.321529+00 | Executor | ExecutorRun
Spans can be automatically sent to an otel collector by setting thepg_tracing.otel_endpoint parameter:
# postgresql.confpg_tracing.otel_endpoint = http://127.0.0.1:4318/v1/tracespg_tracing.otel_naptime = 2000If 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 valuerequires a restart.
About
Distributed Tracing for PostgreSQL