Measuring Query Latency the Hard Way: An Adventure in Impractical Postgres Monitoring by Simon Notley

Sampling the session state (as exposed by pg_stat_activity) is a surprisingly powerful way to understand how your Postgres instance spends its time. It is something I can wholeheartedly recommend to any Postgres DBA that needs a lightweight way to monitor query performance in production. However, it's a terrible way to measure query latency, fraught with complexity and weird statistical biases that could be avoided by simply using an extension built for the job, or even log analysis. But pursuing terrible ideas can be fun, so in this talk, I dive into my adventures in measuring query latency from session sampling, generate some extremely funky charts, and end up unexpectedly performing a vector similarity search.

1. A ScyllaDB Community
Measuring Query Latency the
Hard Way: An Adventure in
Impractical Postgres Monitoring
Simon Notley
Observability and Optimization

2. Simon Notley (he/him)

Observability and Optimization PM at EDB
■Something cool: Gained the freedom of Tryfan
■Perspective on P99s: The time 1 in every 100 of
your users is waiting and wondering if it’s broken
■Another thing: I used to race bicycles
■Away from work: dad stuff

3. Pursuing terrible ideas can be fun

Take a good idea
Understand it’s strengths and
weaknesses
Undeterred, try to apply it to something
you know it’s no good at
Have fun
Session sampling
Good at proportions, bad at details
Query latency
“fun”

4. Time-domain sampling, huh! What is it good for?


23%
20%

5. Time-domain sampling, huh! What is it good for?


Details
Proportions
…and I will now use
it for details…

6. Everything’s not lost



datid | 16385
datname | simon
pid | 45479
leader_pid |
usesysid | 16384
usename | simon
application_name | psql
client_addr |
client_hostname |
client_port | -1
backend_start | 2025-07-04 19:54:58.321751+01
xact_start | 2025-07-04 19:55:22.23828+01
query_start | 2025-07-04 19:55:22.23828+01
state_change | 2025-07-04 19:55:22.23828+01
wait_event_type |
wait_event |
state | active
backend_xid |
backend_xmin | 778
query_id |
query | select * from pg_stat_activity;
backend_type | client backend

7. Even an end has a start

time
We know the query started here
It was still running here
It wasn’t running here
a b

8. Even an end has a start
time
a bc
estimated_duration = 2a + c

9. Unbiased!

For a 1000 ms query, for a range of sample periods, calculate our estimated duration for all possible
relative positions of the query and the samples
Box: 25th - 75th percentile

Whiskers: 5th - 95th percentile

Central mark: median (and mean)

10. Biased?

Query IDTrue Mean Latency / ms
1 56 ± 3
2 300 ± 10
3 550 ± 20
4 800 ± 20
5 1050 ± 20

11. Biased?

Query IDTrue Mean Latency / msEstimated Mean Latency / ms
1 56 ± 3 520 ± 70
2 300 ± 10 520 ± 40
3 550 ± 20 680 ± 40
4 800 ± 20 870 ± 40
5 1050 ± 20 1080 ± 40

12. Biased?

Query IDTrue Mean Latency / msEstimated Mean Latency / msTrue Mean Latency (sampled
queries only) / ms
1 56 ± 3 520 ± 70 520 ± 60
2 300 ± 10 520 ± 40 520 ± 30
3 550 ± 20 680 ± 40 680 ± 30
4 800 ± 20 870 ± 40 870 ± 20
5 1050 ± 20 1080 ± 40 1080 ± 20

13. Stop! Weight a minute!

Query IDTrue Mean Latency / msEstimated Mean Latency / ms
1 56 ± 3 19 ± 4
2 300 ± 10 80 ± 20
3 550 ± 20 150 ± 50
4 800 ± 20 270 ± 100
5 1050 ± 20 500 ± 200

14. Funky charts!

true
true_obs
elapsed
estimate
1 2 3 4 5

15. Funky charts!

true
true_obs
elapsed
estimate
1 2 3 4 5

16. Not so funky chart

17. Funkier…

18. Now we’re talking!

19. Nobody expects vector search!


Query IDTrue Mean Latency /
ms
Vector Search Mean
Latency / ms
Percentage error at
run-level
1 56 ± 3 60 ± 20 10 ± 30
2 300 ± 10 300 ± 40 0 ± 10
3 550 ± 20 550 ± 50 0 ± 9
4 800 ± 20 800 ± 60 0 ± 7
5 1050 ± 20 1060 ± 60 1 ± 5

20. Thank you! Let’s connect.
Simon Notley
[email protected]
sonotley.uk