Debugging the Postgres query planner (2018)
gocardless.com
gocardless.com
This is a standard story: A query ran for a long time without issue, and then, one day, some minor shift in your statistics happens, and now you have a major performance issue in your hands, without any real change on prod. No non-productive environment can help you: They don't have the same data or they do have the same data but the statistic sampling was slightly different.
If it happens, you have to find an incantation that makes the planner comprehend what's going wrong. Postgresql has things like the statistic object and in this case the statistic column property, but finding the right incantation can be black magic, and adding indexes/statistics/... can take hours so trial and error is slow.
Dumber databases have an edge here: Their performance is probably lower, but it is predictable.
Some RDBMS have mitigations, e.g. Oracle's optimizer plan stability allows you to make the plan unchangeable. It's a 2-sided knife of course: It won't get better if the data has a chance for it, but it won't get worse either.
Every change to our prod DB requires running EXPLAIN and EXPLAIN ANALYZE on some data to make sure the queries are doing the right thing (we use GCP Query Insights to watch for regressions [2]).
The cast majority of our queries are single index scans. I wish there was a database that we could fix the plan when our app is deployed. For the most part our schema is fairly denormalized so we don't need very complex queries. The flexibility/power of SQL is really for debugging, analytics and other one off queries.
Hot take: I wish there was a DB that didn't force SQL. At least for the application, instead you just told it what scan you wanted to do (basically the embed plan directly in the query you send). There could be a reporting mechanism if the DB detected a more efficient plan for the query or something. You could still have a SQL layer for your debug and one off sessions.
I would vastly prefer the predictability over occasional performance spikes or in our case a spike of transaction failures due to a predicate lock being grabbed for a whole table.
[1]: the default here is 32 rows (https://www.postgresql.org/docs/current/runtime-config-locks...)
[2]: https://cloud.google.com/sql/docs/postgres/using-query-insig...
[1]: https://dev.mysql.com/doc/refman/8.0/en/index-hints.html [2]: https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion
> No non-productive environment can help you: They don't have the same data or they do have the same data but the statistic sampling was slightly different.
GoCardless still has a massive Postgres database (10TB or there-abouts) and only managed to scale it by investing heavily in tooling that helps developers work with it safely.
One example is https://github.com/gocardless/draupnir, a tool to create instances of production datasets very quickly (just `eval $(draupnir new); psql` and you have a mini production in ~3s) so you could try things like adding indexes, tweaking the plan settings (`set enable_seq_scan='off'`) and reshaping the data to see how your planner behaved.
I think it's very doable, though the planner still has blindspots. I had a side project to add linear correlation statistics to the planner that I abandoned when I stopped working with big Postgres databases, but that's an example of statistics that Postgres just doesn't track but lead to these pathological edge cases.
I'd rather have the clever planner than not, though. I've a healthy appreciation for the heavy lifting Postgres can do for you.
But it's not all roses and rainbows. Draupnir seem cool, but it can't help you avoid the problem, only fix it faster.
At the core, there is a trade off here: Performance for predictability. You see the same thing in compilers, JITs and sometimes even processor cores. There is an optimizer in these things, that works 99% of the time, and makes things clearly better than human effort alone allows. But once in a while it guesses wrong, and you fall off a performance cliff.
Meanwhile, predictability is valuable in production, even to the point you might want to trade a serious dent in your performance for it.
The company doubles in size every year, with totally different products and use patterns.
If you're under the impression that purchasing an Oracle database would mean GC can let go of their engineers then I have a bridge to sell you!
However do you make it that I have claimed that a Oracle license would make your team redundant?
I certainly didn't suggest anything like that.
But please forgive me for being reflexive, has your response accidentally been more revealing than intended about duplicating technologies that isn't AS clear as it might be from the context?
Nobody gets to let go any good database team this side of sanity for whatever reason.
Nevertheless I didn't phrase my comment as thoroughly as I probably should have:
"Working safely with" any asset class data store just shouldn't ever be a question without immediate answers. On going development for the same pirates, doesn't provide executive management with solid answers to "define safety issues present future and potentially retrospectively debugging any failure".
10TB primary dataset isn't considerable amount of production in valuable chain scale of store. Default not a large database on any Oracle installation.
DBMSs require administrative rigors and procedures as well as ideally in depth theory of operations and definitely codebase development skills with the engineering and management system itself is extremely desirable.
However, I can't help thinking that here is a potential case of taking those undoubted talents to directly create proprietary variants of Postgres, which is only going to develop technical debts and future increase in nominally normative support costs.
In other words, I think that your talents have been inappropriately unleashed. You're brain surgeons and everything looks like a brain to you?
Unfortunately and obviously the mere mention of Oracle is liable to create greater difficulty in the creation of openly equitable technical discussions. Oracle management managed to perfect this awful disassociation effect I'm convinced purely for stress testing potential customers often in a gaighting style / hazing sales process . Check out the lady Oracle sales executive who files suit in California every year just to try and negotiate payments of some approximate order to her contractual commission deal. Not many smaller companies get much beyond that not inconsiderable corporate culture clash. I used to joke that Oracle sales cycles were a super proving ground for whether you have a growth business model or not. Because account growth is the keys to sales commissions and simultaneously your easiest leverage for getting big discounts. (Start from 50% before anyone says anything, kept our business afloat)
Ultimately I am unable to understand the point you're making, because you claim inseparable effects from tangential issues and not the smallest misrepresentation of my argument.
Being pedantic, neither of us ought to have used the word "purchase" in relation to licensing nigh inseparable from support and other fat margins. You can still buy a per socket license for RDB , however, and obtain the x64 40% socket discount if you can get VMS X64 running. Sometime soon I'm going to risk indicating that we've renewed interest in such a installation.
The problem I have with the engineering path you took is merely that for the scale and growth outlined, off the shelf solutions absolutely exist and some are thoroughly honed for optimal low administration and even lights out running. We're all potentially caught up in the limitations of early start up scaling of essential computer services, when ad hoc OSS wrangling definitely can sound more attractive than months failing to even understand the small talk spoken by the whole freaking teams turning up to sell you big company shrink wrap software. I'm going round this once again, and have cut myself the budget for dumping all non novel problems onto the most tried solutions. In other end, being ruthless to only spend engineering resources on strategic advantage absolutely can encompass a large license deal or two, but ironically whilst wanting to get rid of Oracle (together with historical deployment and tuning sins) is just that much more attractive a fictional moral campaign than holy war against teaching development teams about transactions on payroll. I'm cynical indeed, but I hope the circumstances are more clear now?
I haven't used it yet, but it sounds like that gives you the cake and lets you eat it too.
Upgrading major versions and huge surges of writes are often the catalyst of crossing performance cliffs, IME. The first can be planned for and since load testing can help get ahead of the second.
This is the precise problem I’m working on solving. See the pg_plan_guarantee extension.
I don't like the interface, however. As you have to wrap the query with custom markers $pgpg$, you can't use it on anything that programatically generates the query, like an ORM.
I'd prefer an interface where you have a table that maps the query (hash?) to a plan. Then create a stored procedure e.g. nail_plan('SELECT blah blah blah') that inserts a record in that table. You can then backup and restore plans, easily query what plans are guaranteed, maybe even migrate plans between dev and prod. Table could also mark which plans are now invalid.
That's simply not true, it's just less noticeable. Because even if your query plan is not changing, your data is. There will always be some point where your data grows and a reasonable planner (whether you or your database) has to adapt to that as it grows. For example, if a small lookup table grows enough, it stops being faster to do a full table scan on that table, and it becomes reasonable to do an index lookup. If your plan never changes, your performance gets worse. You may argue that fixed query plans are more predictable, but they are not objectively better.
I know what you mean, but I don't think that that quite captures it. It's more like this: planners are built on a set of assumptions that are often pretty far from robust, but nevertheless work adequately well in almost all cases. Including many cases where the assumptions haven't been met!
The best example is the standard assumption that multiple conditions/columns are independent of each other -- all optimizers make this assumption (some can be coxed into recognizing specific exceptions). This is obviously not true much of the time, even with a well normalized schema. Because: why would it be?
All kinds of correlations naturally appear in real data. It's just that it mostly doesn't cause huge problems most of the time, for messy reasons that can't quite be pinned down. You have to get unlucky; the correlations usually have to be very high, and the planner makes completely the wrong inference for the actual query that you ran (not some hypothetical other query). The planner only has to have approximately the right idea to discover the cheapest plan. And the planner doesn't have to discover the cheapest plan in many cases -- there may be quite a few adequate plans (it's really hard to generalize, but that's often true).
Overall, the fact that cost-based optimizers work as well as they do seems quite surprising to me.
If you like this content, there's a bunch more on my blog that you'll probably like too:
https://blog.lawrencejones.dev/
While I continue to work with Postgres, I'm enjoying the life of an early start-up where your default is "not much data", though it does make you unlearn a lot of the habits developed when dealing with multi-terabyte databases!
> large, heterogenous datasets. When all the rows look similar there will be fewer candidate plans and less chance of a disasterous mis-step.
I'm no longer at the company so can't fix it, apologies!