Re: Proposal: Extending the PostgreSQL Protocol with Command Metadata

Hi,

> I'd like to propose a backward-compatible extension to the PostgreSQL Frontend/Backend Protocol that would allow
serversto send command metadata back to clients alongside query results. 
>
> ## Background & Motivation
>
> Coming from a MySQL background, I've found PostgreSQL's lack of session state tracking capabilities to be a
significantgap. MySQL's session state tracking allows servers to piggyback metadata on query responses -- things like
CPU/IOtime consumed, server utilization metrics, tenant quotas, etc. 
>
> This capability enables powerful client-side patterns:
> - Immediate backoff based on server utilization metadata
> - Real-time tenant quota enforcement without separate polling systems
> - Query performance tracking without additional round-trips
> - Custom application metrics embedded in the protocol flow
>
> ## High-Level Proposal
>
> The extension consists of four key components:
>
> 1. Client opt-in via a new parameter (`_pq_.send_command_metadata='on'`) on StartupMessage
> 2. Server-side metadata injection through C API or a SQL function as a demo (`pg_add_command_metadata(key, value)`)
> 3. New protocol message (`CommandMetadata`, type 'm') sent before `CommandComplete`
> 4. Client API extensions in libpq to consume the metadata
>
> The goal is to keep backward compatibility while allowing flexibility of the interface, and zero additional cost if
youhave not opted in. 
>
> ## Unlocking Greater Extensions
>
> Extensions leveraging this metadata protocol could allow to build things with very little overhead that are not
possibletoday: 
>
> - Multi-tenant applications tracking per-query resource consumption (buffer hits/misses, CPU/IO time)
> - Connection poolers making routing decisions based on server load
> - Applications implementing custom rate limiting based on DB metrics
> - Performance monitoring without separate instrumentation queries
> - Distributed tracing integration at the protocol level
>
> ## Why not session variables?
>
> You could argue this could be achieved by reading session variables after each query, but that quickly becomes not
practical:leveraging session variables breaks multiplexing (e.g. PgBouncer) and hitting the server with yet another
querywhen it's already busy just increases the number of incoming queries that it needs to handle. 
>
> ## Asking for feedback
>
> Is there interest in this type of protocol extension?
> What concerns do you have about protocol extensions in general?
> Would you prefer a different approach (e.g., separate protocol messages vs. extending existing ones)?
>
> I have a complete technical specification with detailed protocol message formats and API designs on Github [0].
>
> Looking forward to your feedback and suggestions. Thanks!

Here are my two cents.

I'm neither for nor against the idea. However I can imagine how it
will unnecessarily increase our code complexity while the benefits
seem questionable, to me at least. On top of that it's probably worth
noting that the PostgreSQL protocol itself is an industry standard
de-facto used not only by PostgreSQL. If we could avoid changing it
(much) we better do so.

IMO our typical approach would be to make the core extendable enough
to make it possible to implement what you want as an extension and
also document the corresponding API and cover it with tests properly.
Perhaps we could include the corresponding extension to /contrib/, as
a reference implementation.

The advantage of this approach is that it decomposes the task into
separate steps each of which can be discussed, implemented, reviewed
and tested separately.

--
Best regards,
Aleksander Alekseev