Hacker News Reader

Comment by whalesalad | Hacker News Reader

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.

View on HN

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.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.

Lots of apps however do the same N kind of queries, but the data under them can change over time without anyone noticing

So? Just make sure you regularly refresh the statistics...

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.

Yes, a bit like machine learning models.

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-...

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.

> 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.

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

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.

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?

MaxScale does this for MariaDB

Reply on news.ycombinator.com