ringerc/postgres_otel_tracing.md Secret

## postgres_otel_tracing.md

      
    Raw
  

              postgres_otel_tracing.md
            
          
    I've been doing some work on improving distributed tracing capabilities for PostgreSQL. As a result, I have a collection of demos that together provide:

A central otel_api extension  to decouple trace/metric producers from exporters, and provide shared services like trace context propagation and log/trace correlation
Optional patches to:

Get rid of the reliance on the SQL comment hack (and its problems) and the round-trips added by  using SET / SET LOCAL in favour of a backwards-compatible protocol-level mechanism for propagating trace context;
Enable key/value structured logging in postgres for native log/trace correlation


A demo OTLP exporter extension that wraps the Rust OpenTelemetry SDK to send telemetry collected by the API service
A demo OpenTelemetry tracing instrumentation extension for postgres that uses hooks to emit trace spans.
A proof of concept for intercepting subsets of TRACE_POSTGRESQL_ SDT points and emit them as trace spans
A proof-of-concept pgx (Go PostgreSQL client) trace context propagation patch for easy, near-zero-overhead trace support

Sounds cool, right? But ... why is any of this needed?
The problem

In the Postgres/OpenTelemetry world, everyone currently goes their own way. Each OpenTelemetry component in the postgres ecosystem currently does its own thing independently from (and often incompatibly with) any others. Each one has its own context propagation, log capture, trace emitting hooks, trace exporters to trace stores etc.
This means that PostgreSQL extensions that are not directly focused on OpenTelemetry (such as FDWs) have no way to integrate with opentelemetry services. They cannot benefit from OpenTelemetry observability by emitting traces and metrics. Neither can the core server, except to the extent that any particular otel tracing/metrics extension can use existing hooks to instrument it.

  
      flowchart TD   
  otel_endpoint["OpenTelemetry (OTLP) endpoint"]
  postgres_hooks["postgres hooks"]

  subgraph "pg_stat_ch_extension"

    pg_stat_ch["pg_stat_ch instrumentation"]
    pg_stat_ch_context_propagation["context propagation\n(pg_stat_ch only)"]
    pg_stat_ch_opentelemetry_cpp_api["opentelemetry_cpp::api"]
    pg_stat_ch_opentelemetry_cpp_sdk["opentelemetry_cpp::sdk"]
 
    pg_stat_ch --> pg_stat_ch_opentelemetry_cpp_api --> pg_stat_ch_opentelemetry_cpp_sdk
    pg_stat_ch_context_propagation --> pg_stat_ch

  end

  postgres_hooks --> pg_stat_ch
  postgres_hooks --> pg_tracing

  subgraph "pg_tracing extension"

    pg_tracing["pg_tracing extension instrumentation"]
    pg_tracing_context_propagation["context propagation\n(pg_tracing only)"]
    custom_otel_exporter["pg_tracing custom otel exporter"]
    pg_tracing_libcurl["libcurl"]
    
    pg_tracing --> custom_otel_exporter --> pg_tracing_libcurl
    pg_tracing_context_propagation --> pg_tracing
    
  end

  pg_stat_ch_opentelemetry_cpp_sdk -->|OTLP/HTTP protocol| otel_endpoint
  pg_tracing_libcurl -->|OTLP/HTTP protocol| otel_endpoint

  uninstrumented ~~~ postgres_hooks

  subgraph uninstrumented

    postgres_fdw["postgres_fdw extension"]
    citus_fdw["citus_fdw extension"]
    timescaledb["timescaledb extension"]
    others["..."]
    postgres_fdw ~~~ citus_fdw ~~~ timescaledb ~~~ others
  end
  style uninstrumented fill:darkred,color:white

    
      Loading

  
Each opentelemetry extension has to carry its own exporter and exporter configuration, generally restricting it to one specific export protocol and data store. For example pg_tracing uses libcurl to custom-build OTLP/HTTP and pg_stat_ch embeds a private opentelemetry_cpp SDK. Each extension must provide its own services for queuing and batching up telemetry for delivery, making sampling decisions, adding resource attributes, and more. Using multiple extensions in one postgres instance requires multiple independent exporters to run, increasing overheads - if it even works at all.
Different OpenTelemetry extensions also have different and incompatible interfaces for configuring trace/metric destinations, propagating trace context (via SET statements, sqlcommenter-style injection) and more, so deployments and applications must target one specific telemetry extension.
There are also some issues all extensions face around limitations in how trace context can be propagated from apps to PostgreSQL and limitations in postgres's logging that would ideally be addressed centrally for the benefit of all OpenTelemetry services.
Proposal

I'd like to address these issues by decoupling OpenTelemetry services in the PostgreSQL ecosystem into separate telemetry-emitter and telemetry-exporter services connected by a common API and infrastructure layer.
Here's what I hope to enable - not what is currently supported (new pieces in green, grey for optional/retire-able components):

  
      flowchart TD   

  classDef optional fill:lightgrey,color:black
  classDef new fill:lightgreen,stroke:black,color:black

  subgraph otel_api_extension["otel_api extension"]
    producer_api["Producer API\n(Create metrics and spans)"]
    exporter_api["Exporter API\n(Collect and export telemetry)"]
    context_propagation["Context propagation"]
    log_enrichment["Log enrichment\n(Log/trace correlation)"]

    context_propagation --> log_enrichment
    producer_api --> exporter_api
    producer_api <--> context_propagation
  end
  class otel_api_extension new

  otel_endpoint["OpenTelemetry (OTLP) endpoint"]
  postgres_hooks["postgres hooks"]
  
  subgraph "pg_stat_ch_extension (PROTOTYPED)"

    pg_stat_ch["pg_stat_ch instrumentation"]
    pg_stat_ch_opentelemetry_cpp_api["opentelemetry_cpp::api"]
    pg_stat_ch_opentelemetry_cpp_sdk["opentelemetry_cpp::sdk"]
 
    pg_stat_ch -->|traces,metrics| producer_api
    exporter_api -.->|optional| pg_stat_ch_opentelemetry_cpp_api --> pg_stat_ch_opentelemetry_cpp_sdk
    class pg_stat_ch_opentelemetry_cpp_api,pg_stat_ch_opentelemetry_cpp_sdk optional
  end

  subgraph "pg_tracing extension (proposed)"

    pg_tracing["pg_tracing extension instrumentation"]
    custom_otel_exporter["pg_tracing custom otel exporter"]
    pg_tracing_libcurl["libcurl"]
 
    pg_tracing -->|traces,metrics| producer_api
    exporter_api -.->|optional| custom_otel_exporter --> pg_tracing_libcurl
    class custom_otel_exporter,pg_tracing_libcurl optional
  end

  subgraph otel_exporter_extension["otel exporter extension"]
    exporter_plugin["OTLP exporter extension"]
    exporter_plugin -.->|registers to| exporter_api
    exporter_api --->|sends telemetry via| exporter_plugin
    exporter_plugin_opentelemetry_api["Rust opentelemetry_api crate"]
    exporter_plugin_opentelemetry_sdk["Rust opentelemetry_sdk crate"]
    exporter_plugin --> exporter_plugin_opentelemetry_api --> exporter_plugin_opentelemetry_sdk
  end
  class otel_exporter_extension new

  postgres_hooks --> pg_stat_ch
  postgres_hooks --> pg_tracing
  exporter_plugin_opentelemetry_sdk -->|OTLP/HTTP protocol| otel_endpoint
  pg_stat_ch_opentelemetry_cpp_sdk -.->|OTLP/HTTP protocol| otel_endpoint
  pg_tracing_libcurl -.->|OTLP/HTTP protocol| otel_endpoint

  subgraph "other extensions (proposed)"

    postgres_fdw["postgres_fdw extension"]
    citus_fdw["citus_fdw extension"]
    timescaledb["timescaledb extension"]
    others["..."]
    postgres_fdw --->|traces,metrics| producer_api
    citus_fdw --->|traces,metrics| producer_api
    timescaledb --->|traces,metrics| producer_api
    others --->|traces,metrics| producer_api
  end

    
      Loading

  
Extensions will consume a simple OpenTelemetry API to expose their own telemetry at a low cost both in complexity and runtime overhead. The API short-circuits at low cost if there is no registered sink for the signals. So it should be possible to instrument in-tree extensions like postgres_fdw, and out-of-tree extensions like citus_fdw or timescaledb to emit OpenTelemetry metric and trace signals natively when a destination for them is available.
An API is exposed for telemetry exporter extensions to implement, providing a place to send logs and metrics generated by other postgres components. To limit dependencies, no in-core OTLP exporter sink will be provided just a minimal json exporter. It's expected that the community will settle on a single widely-used out-of-tree OTLP exporter extension, but specific telemetry vendors are free to also provide their own native exporters.
These producers and exporters will be connected via a producer/consumer API and a OpenTelemetry infrastructure service providing trace context propagation, log enrichment services, etc.
It is hoped that all-in-one extensions like pg_tracing or pg_stat_ch may adopt the producer-side API, so their instrumentation can be delivered via a common channel. They should use the shared services for trace context propagation and log/trace enrichment, with compatibility shims for backward compatibility. These extensions could also offer their existing exporter as an optional exporter API for those who want an all-in-one solution - giving them the ability to capture telemetry from other instrumented postgres extensions "for free" into their existing export sinks.
Implementation

To enable this, I have prototyped a central OpenTelemetry API and infrastructure services component otel_api that provides a point of rendezvous to connect telemetry producers to exporters via APIs that producers consume and exporters implement.
The otel_api extension also provides common infrastructure services that most OpenTelemetry extensions require, such as:

Trace context propagation into and within postgres
Log/trace correlation
Sampling services
... so they don't need to be duplicated.

Using this central API (currently implemented as an extension), existing telemetry signal emitters like pg_tracing or pg_stat_ch could emit their existing traces and metrics to any OpenTelemetry-compatible exporter that the DB operator requires. Typically this will be an OTLP-over-HTTP exporter using an OTLP SDK. Multiple trace- or metric-producing extensions can share the same exporter, reducing memory overhead and incompatibility risks.
I've also identified a few pain-points for integration that'd benefit from targeted core improvements:

Client->Pg trace context propagation
Trace context propagation into parallel query bgworkers
Log/trace correlation via structured logging

... none of which are strictly necessary to enable a shared OpenTelemetry integration service (as demonstrated below), but all of which improve the results such a service can deliver.
Streams of work

You can find the work to date in the form of PRs (against my own tree) just for convenience of sharing/viewing.
First there's the main otel extension:


https://github.com/ringerc/postgres_otel_api - provides an otel_api extension supplying an extension-facing API for PostgreSQL OpenTelemetry instrumentation (tracing focused) + demo extensions showing how to produce traces, and how to export them. Handles common scaffolding like trace context propagation and tracking, log/trace correlation. Can build out-of-tree or in-tree as contrib/otel_api. Builds against patched or unpatched postgres versions.
This extension is intended as the "glue" to connect existing and new trace producers (think pg_tracing, tracing-instrumenting postgres_fdw, etc) to trace sink exporters, enabling tracing-aware postgres extensions to co-exist and collaborate in a single postgres install.


Then there are a couple of optional postgres patches that are not strictly required for the otel_api extension but greatly improve performance/functionality:

ringerc/postgres#5 - extend postgres error log with structured key/value fields, to support otel log/trace correlation into postgres logs (or ringerc/postgres#2 for a more targeted approach).
ringerc/postgres#3 - a zero-added-RTT "query headers" mechanism that could be used for both trace headers and other tasks. Used to propagate trace context from client->server with zero added RTs while avoiding SQL-commenting hacks (and the issues those cause with pooling, prepared statements, etc). Also working on narrower-scoped TraceContext protocol message alternative.
ringerc/postgres#4  - a hook extensions can use for end-of-statement state clearing/resetting (for statement-level tracing). Optional.
otel_enablement ringerc/postgres branch - extends auto_explain, postgres_fdw for OpenTelemetry tracing integration.

An integration-testing tree combines the otel_api extension and the postgres patches:

ringerc/postgres#1 - Integrates contrib/otel_api scaffolding and supporting extensions along with the upstream postgres patches (via merge commits and subtrees) for convenience combined testing and evaluation. No meaningful local changes.

There's a somewhat-more-realistic demo extension showing how to wire the API exposed by otel_api up to a real OTLP trace exporter as https://github.com/ringerc/postgres_otel_tracing_demo
A modified pg_stat_ch (branch otel-api-producer) gains OpenTelemetry trace context propagation via otel_api, and emits its signals via otel_api rather than the embedded clickhouse writer or OpenTelemetry C++ SDK, demonstrating that the producer API works.
Note that the actual tracing functionality implemented here does not try to duplicate existing extensions like pg_tracing or pg_stat_ch; the hope is that such extensions would pivot to use otel_api and pluggable exporters.
For testing/validation and benchmarking I have a tool https://github.com/ringerc/postgres_otel_tracing_bench containing:

a tracing-enabled postgres client utility for benchmark/test use, including header-message support where available; and
setup scripts and test harnesses to integrate and run everything

To support the test client, I've also prepared a patched pgx ringerc/pgx_patches#1 that the above demo/bench tool uses to demonstrate the improvements made with the new headers protocol message.
No results found