An automatic indexing system for Postgres
pganalyze.com
pganalyze.com
https://www.youtube.com/watch?v=SlNQTtfjlnI
Compared to the initial version, this updated version is both more configurable, as well as has better handling of competing objectives in index selection (write overhead vs query performance).
If you want to give this a try, and have pganalyze set up / want to try it out, feel free to send me a message (email in profile).
If rewriting query and remodelling data are out of question, the options are much more limited.
Second, not only queries have rps, they have hourly, weekly and seasonal distributions. They evolve, become deprecated, and have different SLAs, tables have different write to read ratios. There was an instance recently when I slowed down a query, quite intentionally by deleting a very good index for the query and substituted it with worse, but smaller BRIN index.
The thing is, this particular query did not matter as much and SLA permitted slowdown, write path was far more important.
I often wonder why, in addition to sql, we don't have a low level language allowing you to specify exactly how you want your query executed?
What I would like, and I assume GP as well, is the ability to write the low-level querying, as in bypass SQL and the planner, and give postgres the post-planner bytecode itself.
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.
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.
Planners are mysterious black boxes, which we can at best influence.
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-...
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.
However, after a few months the ROI crept down until it wasn't worth it anymore (access patterns stabilized). I'm tempted to bring it back once and a while but the price tag keeps me from having it always on.
Generally I (Founder and CEO) feel the pricing is fair for the value provided for production databases, and it allows us to run the business as an independent company without external investors, whilst continuing to invest in product improvements. That said, it may not be a good fit if you have a small production database, or only make database-related changes infrequently.
> This is powered by a modified version of the Postgres planner that runs as part of the pganalyze app. This modified planner can generate EXPLAIN-like data from just a query and schema information - and most importantly, that means we can take query statistics data from pg_stat_statements and generate a generic query plan from it. You can read more about that in our blog post "How we deconstructed the Postgres planner".
My gut feeling tells me that the chances of having an upstream library that contains the parser/parse analysis/planner are slim. Mainly from there being a lot of entanglement with reading files on disk, memory management, etc - I suspect one of the pushbacks would be it would complicate development work for Postgres itself, to the point its not worth the benefits.
For the pg_query library on the other hand I have hopes that we can upstream this eventually - there are enough third-party users out there to clearly show the need, and its much more contained (i.e. raw parser + AST structs). Hopefully something we can spend a bit of time on next year.
Yea. The parser alone would be doable and not even that hard. But once you get to parse analysis and planning, you need to access the catalogs (for parse analysis to look up object names and do permission checks, for planning to access operator definitions, statistics etc). Which in turn needs a lot of the catalog / relation cache infrastructure. By that point you've pulled in a lot of postgres.
Of course you could try to introduce a "data provider" layer between parse analysis and catalogs, but that'd be a lot of work. And it'd be quite hard to get right in places - e.g. doing name lookups without acquiring heavyweight locks on objects, before having done permission checks, in a concurrency safe way, relies on a bunch of subsystems working together.
We need better tooling around indexes but it needs to take into account all the queries and run them against the real dataset IMO. I think AWS Aurora plan manager is on the right track if it would be combined it with your tech.
It's so nice being able to sleep at night and not have to that an ultra important won't randomly shit the bed.
One of my buddies that works at a PostgreSQL powered place once bitched about how they had a maintenance job that truncated a big table, then another job refreshed statistics, then the table rapidly filled up again and ask the query plans were awful because the table is usually massive.
I get the religion behind the open source stuff: with SQL, you shouldn't have to tell the thing how to build the query plan, you should just tell it what you want and let it figure out how to do it.
But if humans are expected to perform maintenance on the database, setting up scheduled jobs, refreshing statistics, identifying missing indexes, etc., then those same humans should be able to specify a query plan and/or specific indexes to use in a query.
Eventually you get a vague idea of how to coax Postgres to make the plans you want it to, but the fact that you can't at least lock it in and the plan might change with any number of factors at any time... That's just bad design.
Sequential scan == Full table scan: https://en.wikipedia.org/wiki/Full_table_scan