Support for sql statement metrics : nb rows returned, total bytes received

[gberche-orange]

Thanks for sharing this library with the community. As a new comer user of go compiled programs, I'd like to use otel to get sql-level metrics describing sql activity performed by the pgx driver such as: such as response latency, number of rows received in responses, size of response received in bytes

I understand that otelpgx is currently adding traces reflecting the sql statements.

Would the otel specification enable otelpgx to issue metrics in addition to traces ?

[costela]

I think #18 is what you're looking for?

Unfortunately we still haven't had the time to look at it :disappointed:

[costela]

On second thought, that seems to only be the foundation of what you're asking. It still doesn't include the rows/bytes info you'd like to have.

[KasonBraley]

18 uses the stats returned from pgxpool's Stat type. It doesn't look like any of the requested ones are supported by pgx. So my guess would be that those metrics need to be added to pgx first, or do something custom.

[costela]

@KasonBraley good point; missed that :+1:

[obitech]

The original aim of this library was to provide OTEL tracing support for pgx, as simple as possible. It implements pgx's .*Tracer interfaces so it can be plugged into pgx's Config.. I don't think these interfaces were ever really intended to capture the metrics you're looking for.

Also there are a lot of different metrics people might be interested in, as well as metrics adapters available (statsd, DataDog, Prometheus, etc). Supporting the Stat-type via the OTEL metrics API could be a good start here, we're currently looking into that (see #18).

There are ways on how to get some of the metrics you're looking for, like response times or rows returned. Just create a custom type and wrap the functions you need with your own custom metrics. For example:

type connectionPool struct {
    conn *pgxpool.Pool
}

func (p *connectionPool) Query(ctx context.Context, sql string, args ...interface{}) (pgx.Rows, error) {
    start := time.Now()

    rows, err := p.conn.Query(ctx, sql, args...)
    if err != nil {
        // track your error here

        return nil, err
    }

    // track your rows here via rows.CommandTag().RowsAffected() 

    // track your duration here via time.Since(start)

    return rows, nil
}

I don't think capturing bytes returned is possible with pgx right now, but I haven't dug too deep into the code.

[gberche-orange]

thanks @obitech for your detailed analysis and answer !

The go program I use, already publishes gosql connection metrics (using https://github.com/prometheus/client_golang/blob/main/prometheus/collectors/dbstats_collector.go see https://github.com/k3s-io/kine/blob/f7ae7ce70751a7eab4b40574b45fc0cfa7be15fc/pkg/drivers/generic/generic.go#L206). See sample metrics in https://gist.github.com/gberche-orange/32020e5fd00475d678eda04dec066955#file-sample-kine-v0-10-2-metrics-L111-L137 but they don't seem to relate to otel Stat-type metrics. This feel like duplication of effort.

[obitech]

@gberche-orange the package you're using relies on the database/sql, whereas this library uses jackc/pgx. I believe there is a database/sql adapter in pgx, but I haven't used that adapter yet. In any case, those metrics you're looking for are a bit out of scope for the project right now. We might change our mind in the future if we find a good and simple way to track additional things, but I'd like to see how #18 progresses first. I'm closing this issue for now.