Add view to Npgsql duration metrics

[JamesNK]

Histogram buckets aren't well configured in OpenTelemetry. The minimum bucket is 5. That's fine if you're measuring in milliseconds, not so good when you're measuring in seconds. There is special hardcoding for known .NET histograms, but 3rd party histograms aren't helped.

Need to add views to PostgresSQL component.

Example:

        builder.AddView("http.server.request.duration",
            new ExplicitBucketHistogramConfiguration
            {
                Boundaries = new double[] { 0, 0.005, 0.01, 0.025, 0.05, 0.075, 0.1, 0.25, 0.5, 0.75, 1, 2.5, 5, 7.5, 10 }
            });

[eerhardt]

This isn't an Aspire specific issue. Why isn't this being handled in OpenTelemetry? The instrument declares that it's units are seconds. This should just work without every instrument consumer needing to make a custom view.

[eerhardt]

Here's the instrument creation: https://github.com/npgsql/npgsql/blob/5c106023a1a192a2fab0726fc7df9ca51e39cb22/src/Npgsql/MetricsReporter.cs#L62-L66

        CommandDuration
            = Meter.CreateHistogram<double>(
                "db.client.commands.duration",
                unit: "s",
                description: "The duration of database commands, in seconds.");

[JamesNK]

Three decisions have put us in this situation:

• OTEL recommends seconds for durations. They changed from ms to s this year to align for Prometheus, who uses seconds everywhere.
• However, OTEL didn't change their histogram bucket bounds, which are still optimized for milliseconds. They won't change them because it's a breaking change. It's very dumb and means that the smallest bucket is 5 seconds.
• There isn't a way to configure bounds when creating a histogram in .NET. The feature in OTEL is experimental. I'm going to push for it to happen for .NET 9 - Add "hints" in Metric API to provide things like histogram bounds runtime#63650

A fix is hard coded for known .NET built-in counters in OTEL. Automatically changing all histograms with a unit of seconds might not be what someone wants.

The temporary solution for 3rd parties in .NET 8 is to add custom views.

[JamesNK]

Duration metric reporting 5 seconds:

The graph is unlikely to report any other value because everything will fall into the 5-second bucket.

[eerhardt]

https://github.com/open-telemetry/semantic-conventions/blob/1eb70c4d74aa904a8c81c5ed10302af8bb3c1b70/docs/database/database-metrics.md?plain=1#L138-L146
says

Name	Instrument Type	Unit (UCUM)	Description
db.client.connections.create_time	Histogram	ms	The time it took to create a new connection

So maybe Npgsql should be updated to ms?

[JamesNK]

I think DB conventions just hasn't been updated yet for seconds. They're focusing on stabilizing HTTP.

[eerhardt]

Reopening to backport to preview1.

[roji]

So maybe Npgsql should be updated to ms?

FWIW I implemented seconds for command duration because of the explicit OTel conventions around this, as @JamesNK wrote above:

When instruments are measuring durations, seconds (i.e. s) SHOULD be used.

[eerhardt]

Fixed with #660.