3 ms·

I’ve often wondered why we don’t put a thin tiny proxy in front of psql (or augment pgbouncer, other proxies) to collect telemetry on all real world queries aga

I’ve often wondered why we don’t put a thin tiny proxy in front of psql (or augment pgbouncer, other proxies) to collect telemetry on all real world queries against the db. Being a middle man there would give you lots of visibility into every single query being made as well as how long it takes. I guess the modern integrated stats tools help. I think the real problem is not indexing correctly but rather modeling your problem correctly. You can throw indexes at the problem but that is sometimes just a bandaid to a more integral issue which would be inventing a new schema, new access patterns etc.

phamilton 3y ago
We have this with APM monitoring, where every SQL query in the application becomes a span in a trace. It is very useful. We can quickly see the specific parameters that make an otherwise fast query slow.
ako 3y ago
Most databases will allow you to collect statistics on every query run by users, e.g., pg_stat_statements: https://www.postgresql.org/docs/current/pgstatstatements.html https://www.postgresql.org/docs/current/pgstatstatements.htm... A relational database really shines when you have all sorts of queries on tables, so it’s hard to optimize your model for every potentially future desired query. Precalculating these, caching the results in separate tables is not very efficient, and requires you to predetermine every query you want to answer. Indexing in this situation is often the better alternative.
- szundi 3y ago
  Lots of apps however do the same N kind of queries, but the data under them can change over time without anyone noticing
  - ako 3y ago
    So? Just make sure you regularly refresh the statistics...
    - sgarland 3y ago
      Ideally yes, but sometimes your tables get so monstrously large that the auto-analyze is sampling thousandths of a percent of rows, and fiddling with sampling targets can throw off seemingly unconnected queries. Planners are mysterious black boxes, which we can at best influence.
      - ako 3y ago
        Yes, a bit like machine learning models.
ellisv 3y ago
I definitely agree on problems not being modeled correctly, although I also frequently see application developers forego indexes. I think this is fundamentally a lack of understanding or inexperience.
jes5199 3y ago
you can get a long way just checking the slow query log every few days
bbminner 3y ago
Exactly, I have always been curious about the same thing - if we have live statistics on queries then what stops us from cloning data (to correctly capture statics such as skew, etc.) and running these live queries with a bunch of knobs tuned automatically (which indexes, on which columns, etc.), and reporting the cost-throughput paretto optimum to the user? The configuration space is too large to explore randomly? And it's structure is too complex to produce automatic suggestions?
ahachete 3y ago
Have a look at the Postgres filter for the Envoy proxy: [1] (blog announcement post). While it's not capturing as of today query performance, it collects notable telemetry for Postgres in exactly the way you mention: just because the traffic flows through it, making it a way to collect data "for free" and definitely without taking any resources from the upstream database. It can also offload SSL from Postgres. The filter could be extended for other use cases. Disclaimer: my company developed this plugin for Envoy with the help of Envoy's awesome community. [1]: https://www.cncf.io/blog/2020/08/13/envoy-1-15-introduces-a-new-postgres-extension-with-monitoring-support/ https://www.cncf.io/blog/2020/08/13/envoy-1-15-introduces-a-...
nijave 3y ago
MaxScale does this for MariaDB
magicalhippo 3y ago
> I think the real problem is not indexing correctly but rather modeling your problem correctly. There's also the issue that usage might change over time due to events outside of your control, that affects what the correct solution should look like. For example, there's always a tradeoff between development effort and performance. It doesn't make financial sense spending tons of engineering time on optimizing a process that's done once per day and for which the simple solution is more than fast enough even though it takes an hour. But if external circumstances change and that process suddenly needs to run every minute... perhaps not so easy to accommodate without drastic changes.

phamilton 3y ago

We have this with APM monitoring, where every SQL query in the application becomes a span in a trace. It is very useful. We can quickly see the specific parameters that make an otherwise fast query slow.

ako 3y ago

Most databases will allow you to collect statistics on every query run by users, e.g., pg_stat_statements: https://www.postgresql.org/docs/current/pgstatstatements.html https://www.postgresql.org/docs/current/pgstatstatements.htm... A relational database really shines when you have all sorts of queries on tables, so it’s hard to optimize your model for every potentially future desired query. Precalculating these, caching the results in separate tables is not very efficient, and requires you to predetermine every query you want to answer. Indexing in this situation is often the better alternative.

szundi 3y ago
Lots of apps however do the same N kind of queries, but the data under them can change over time without anyone noticing
- ako 3y ago
  So? Just make sure you regularly refresh the statistics...
  - sgarland 3y ago
    Ideally yes, but sometimes your tables get so monstrously large that the auto-analyze is sampling thousandths of a percent of rows, and fiddling with sampling targets can throw off seemingly unconnected queries. Planners are mysterious black boxes, which we can at best influence.
    - ako 3y ago
      Yes, a bit like machine learning models.

ellisv 3y ago

I definitely agree on problems not being modeled correctly, although I also frequently see application developers forego indexes. I think this is fundamentally a lack of understanding or inexperience.

jes5199 3y ago

you can get a long way just checking the slow query log every few days

bbminner 3y ago

Exactly, I have always been curious about the same thing - if we have live statistics on queries then what stops us from cloning data (to correctly capture statics such as skew, etc.) and running these live queries with a bunch of knobs tuned automatically (which indexes, on which columns, etc.), and reporting the cost-throughput paretto optimum to the user? The configuration space is too large to explore randomly? And it's structure is too complex to produce automatic suggestions?

ahachete 3y ago

Have a look at the Postgres filter for the Envoy proxy: [1] (blog announcement post). While it's not capturing as of today query performance, it collects notable telemetry for Postgres in exactly the way you mention: just because the traffic flows through it, making it a way to collect data "for free" and definitely without taking any resources from the upstream database. It can also offload SSL from Postgres. The filter could be extended for other use cases. Disclaimer: my company developed this plugin for Envoy with the help of Envoy's awesome community. [1]: https://www.cncf.io/blog/2020/08/13/envoy-1-15-introduces-a-new-postgres-extension-with-monitoring-support/ https://www.cncf.io/blog/2020/08/13/envoy-1-15-introduces-a-...

nijave 3y ago

MaxScale does this for MariaDB

magicalhippo 3y ago

> I think the real problem is not indexing correctly but rather modeling your problem correctly. There's also the issue that usage might change over time due to events outside of your control, that affects what the correct solution should look like. For example, there's always a tradeoff between development effort and performance. It doesn't make financial sense spending tons of engineering time on optimizing a process that's done once per day and for which the simple solution is more than fast enough even though it takes an hour. But if external circumstances change and that process suddenly needs to run every minute... perhaps not so easy to accommodate without drastic changes.