Re: POC: Extension for adding distributed tracing - pg_tracing

Hi Anthonin,

I'm only now catching up on this thread. Very exciting feature!

My first observation is that you were able to implement this purely as 
an extension, without any core changes. That's very cool, because:

- You don't need buy-in or blessing from anyone else. You can just put 
this on github and people can immediately start using it.
- As an extension, it's not tied to the PostgreSQL release cycle. Users 
can immediately start using it with existing PostgreSQL versions.

There are benefits to being in contrib/ vs. an extension outside the 
PostgreSQL source tree, but there are significant downsides too. 
Out-of-tree, you get more flexibility, and you can develop much faster. 
I think this should live out-of-tree, at least until it's relatively 
stable. By stable, I mean "not changing much", not that it's bug-free.

# Core changes

That said, there are a lot of things that would make sense to integrate 
in PostgreSQL itself. For example:

- You're relying on various existing hooks, but it might be better to 
have the 'begin_span'/'end_span' calls directly in PostgreSQL source 
code in the right places. There are more places where spans might be 
nice, like when performing a large sort in tuplesort.c to name one 
example. Or even across every I/O; not sure how much overhead the spans 
incur and how much we'd be willing to accept. 'begin_span'/'end_span' 
could be new hooks that normally do nothing, but your extension would 
implement them.

- Other extensions could include begin_span/end_span calls too. (It's 
complicated for an extension to call functions in another extension.)

- Extensions like postgres_fdw could get the tracing context and 
propagate it to the remote system too.

- How to pass the tracing context from the client? You went with 
SQLCommenter and others proposed a GUC. A protocol extension would make 
sense too. I can see merits to all of those. It probably would be good 
to support multiple mechanisms, but some might need core changes. It 
might be good to implement the mechanism for accepting trace context in 
core. Without the extension, we could still include the trace ID in the 
logs, for example.

# Further ideas

Some ideas on cool extra features on top of this:

- SQL functions to begin/end a span. Could be handy if you have 
complicated PL/pgSQL functions, for example.
- Have spans over subtransactions.
- Include elog() messages in the traces. You might want to have a lower 
elevel for what's included in traces; something like the 
client_min_messages and log_min_messages settings.
- Include EXPLAIN plans in the traces.

# Comments on the implementation

There was discussion already on push vs pull model. Currently, you 
collect the traces in memory / files, and require a client to poll them. 
A push model is more common in applications that support tracing. If 
Postgres could connect directly to the OTEL collector, you'd need one 
fewer running component. You could keep the traces in backend-private 
memory, no need to deal with shared memory and spilling to files.

Admittedly supporting the OTEL wire protocol is complicated unless you 
use an existing library. Nevertheless, it might be a better tradeoff. 
OTEL/HTTP with JSON format seems just about feasible to implement from 
scratch. Or bite the bullet and use some external library. If this lives 
as an extension, you have more freedom to rely on 3rd party libraries.

(If you publish this as an out-of-tree extension, then this is up to 
you, of course, and doesn't need consensus here on pgsql-hackers)

# Suggested next steps with this

Here's how I'd suggest to proceed with this:

1. Publish this as an extension on github, as it is. I think you'll get 
a lot of immediate adoption.

2. Start a new pgsql-hackers thread on in-core changes that would 
benefit the extension. Write patches for them. I'm thinking of the 
things I listed in the Core changes section above, but maybe there are 
others.


PS. Does any other DBMS implement this? Any lessons to be learned from them?

-- 
Heikki Linnakangas
Neon (https://neon.tech)