I couldn’t find anything describing what each query was doing. I probably missed something important :) Any link to the details of the benchmarking setup?
To measure the impact on query duration, we started with 5,000 queries per second. This allows us to exclude side effects from the database side (long query execution times).
The query used in the experiment was the following:
select * from ( values (1, 'one'), (2, 'two'), (3, 'three') ) as t (num, letter);
This query is an extremely ideal scenario for a proxy. Because the SQL expression is constant, and doesn’t query data that would ever change, the proxy would only need to query the underlying DB on the initial request, and then basically never again: the initial response is valid forever. That’s definitely gonna give amazing performance results! It sidesteps not only DB execution times but also any costs related to updating the cache in each proxy.
I think it’d be much more interesting to see benchmarks for a query like SELECT SUM(count) FROM counts – where the counts table was being continuously updated by a separate process, and the benchmark included some kind of sanity check for the validity of the result.
That seems defensible as a test as an analog for serverless functions, which hold a connection (or many) and don’t necessarily do a lot of traffic each. My takeaway was they were testing that many simultaneous clients could all connect with minimal latency overheads, not as a Postgres benchmark. It’s clear that stuffing as many queries as you can at the database from each connection would lead to abject failure.
That’s definitely gonna give amazing performance results! It sidesteps not only DB execution times but also any costs related to updating the cache in each proxy.
Nowhere do the authors claim any kind of query caching for their results, just a promise of future work: “Supavisor will enable us to build some exciting new features for your Postgres cluster [..] query caching”.
I guess I don’t really understand what “latency” means in the context of a mostly-idle connection.
More broadly, I guess I also don’t understand the purpose of an application-layer performance test of mostly-idle connections. The application isn’t ever gonna be the limiting factor or the bottleneck for connection cardinality – or, at least, it shouldn’t be. This kind of stuff is constrained at the OS or network layer.
The goal would be to ensure that holding open a million connections doesn’t introduce large amounts of latency when queries are performed. You could write a similar proxy which didn’t scale properly, where connection management (pings and such) added unacceptable (100ms+) latency for even modest query throughput.
A highly concurrent proxy is only useful if it can respond in a timely manner. It doesn’t mean that all connections have to be non-idle. Though they could be, if such a proxy also balanced over replicas.
I couldn’t find anything describing what each query was doing. I probably missed something important :) Any link to the details of the benchmarking setup?
I saw this in the article:
select * from ( values (1, 'one'), (2, 'two'), (3, 'three') ) as t (num, letter);Thanks!
This query is an extremely ideal scenario for a proxy. Because the SQL expression is constant, and doesn’t query data that would ever change, the proxy would only need to query the underlying DB on the initial request, and then basically never again: the initial response is valid forever. That’s definitely gonna give amazing performance results! It sidesteps not only DB execution times but also any costs related to updating the cache in each proxy.
I think it’d be much more interesting to see benchmarks for a query like
SELECT SUM(count) FROM counts– where thecountstable was being continuously updated by a separate process, and the benchmark included some kind of sanity check for the validity of the result.That does assume the proxy caches, which doesn’t seem particularly desirable.
Yup, which is what I took away from the description of the tool in the post. Maybe I misunderstood?
While double-checking this detail I found something else that didn’t make a ton of sense to me:
Why not have each connection make as many requests as possible, one after the other?
I took that to mean the clients.
1,000,000/50 = 20kqps
That seems defensible as a test as an analog for serverless functions, which hold a connection (or many) and don’t necessarily do a lot of traffic each. My takeaway was they were testing that many simultaneous clients could all connect with minimal latency overheads, not as a Postgres benchmark. It’s clear that stuffing as many queries as you can at the database from each connection would lead to abject failure.
Nowhere do the authors claim any kind of query caching for their results, just a promise of future work: “Supavisor will enable us to build some exciting new features for your Postgres cluster [..] query caching”.
I guess I don’t really understand what “latency” means in the context of a mostly-idle connection.
More broadly, I guess I also don’t understand the purpose of an application-layer performance test of mostly-idle connections. The application isn’t ever gonna be the limiting factor or the bottleneck for connection cardinality – or, at least, it shouldn’t be. This kind of stuff is constrained at the OS or network layer.
I guess I don’t really understand what “latency” means in the context of a mostly-idle connection.
The goal would be to ensure that holding open a million connections doesn’t introduce large amounts of latency when queries are performed. You could write a similar proxy which didn’t scale properly, where connection management (pings and such) added unacceptable (100ms+) latency for even modest query throughput.
A highly concurrent proxy is only useful if it can respond in a timely manner. It doesn’t mean that all connections have to be non-idle. Though they could be, if such a proxy also balanced over replicas.
[Comment removed by author]
[Comment removed by author]