Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Debugging the Postgres query planner (2018)

gocardless.com

Post reply on HN
Print view View original

11–20 of 26 posts

Re: Debugging the Postgres query planner (2018)

#11

Post by cube2222 »

hyperman1 wrote: post #2

In general I love what RDBMS and postgresql in particular can bring to you, but this is one corner of them that I hate: Planners are too smart for their own good. 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…

Afaik Aurora lets you pin plans and will at the same time ask you to approve new, better plans, if ones are discovered.

I haven't used it yet, but it sounds like that gives you the cake and lets you eat it too.

Re: Debugging the Postgres query planner (2018)

#12

Post by retcore »

lawrjone wrote: post #6
hyperman1 wrote: post #2

In general I love what RDBMS and postgresql in particular can bring to you, but this is one corner of them that I hate: Planners are too smart for their own good. 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…

Author of the article here, and thought it's worth noting on: > 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://gi…

Wow. Gocardless has over five years of engineering invested in scaling Postgres, and it was a on going development to enable them to "work with it safely"? At what point did operating safety concerns arise? I'm absolutely no fan of Oracle business practices, but this sounds like a story that sales can dine on for years, notwithstanding my private conviction that I could negotiate Exadata for less than the gross salaries excluding employer contributions.

Re: Debugging the Postgres query planner (2018)

#13

Post by lawrjone »

retcore wrote: post #12
lawrjone wrote: post #6

Earlier quoted context omitted.

Author of the article here, and thought it's worth noting on: > 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://gi…

Wow. Gocardless has over five years of engineering invested in scaling Postgres, and it was a on going development to enable them to "work with it safely"? At what point did operating safety concerns arise? I'm absolutely no fan of Oracle business practices, but this sounds like a story that sales can dine on for years, notwithstanding my private conviction that I could negotiate Exadata for less than the gross salar…

Of course GoCardless have to invest engineering effort into scaling the technology they use.

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!

Re: Debugging the Postgres query planner (2018)

#14

Post by lawrjone »

kroolik wrote: post #9
lawrjone wrote: post #4

Well this is funny: I'm the author of this post, and pleasantly surprised to see it coming around for a second time! 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 develope…

Any reason the article has sections appearing out of no where, that don't fit the surrounding parts? For example: > large, heterogenous datasets. When all the rows look similar there will be fewer candidate plans and less chance of a disasterous mis-step.

Awkwardly, I think this is probably because their blog has gone through several rewrites and it probably hasn't preserved the original text very well.

I'm no longer at the company so can't fix it, apologies!

Re: Debugging the Postgres query planner (2018)

#15

Post by paulryanrogers »

hyperman1 wrote: post #2

In general I love what RDBMS and postgresql in particular can bring to you, but this is one corner of them that I hate: Planners are too smart for their own good. 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…

There is also pg_hint_plan. MySQL too can suffer from this lurking thresholds problem. Though it has hints built in.

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.

Re: Debugging the Postgres query planner (2018)

#16

Post by stonemetal12 »

hyperman1 wrote: post #2

In general I love what RDBMS and postgresql in particular can bring to you, but this is one corner of them that I hate: Planners are too smart for their own good. 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…

Could be cool if the RDBMS A B tested their plans, if the new plan isn't better don't switch to it. Though that would certainly add to the Black Magic of it, maybe a command to show the dev the top 5 plans and allow them to pick and pin.

Re: Debugging the Postgres query planner (2018)

#17

Post by gurjeet »

hyperman1 wrote: post #2

In general I love what RDBMS and postgresql in particular can bring to you, but this is one corner of them that I hate: Planners are too smart for their own good. 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…

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

This is the precise problem I’m working on solving. See the pg_plan_guarantee extension.

https://github.com/DrPostgres/pg_plan_guarantee

Re: Debugging the Postgres query planner (2018)

#18

Post by darksaints »

hyperman1 wrote: post #2

In general I love what RDBMS and postgresql in particular can bring to you, but this is one corner of them that I hate: Planners are too smart for their own good. 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…

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

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.

Re: Debugging the Postgres query planner (2018)

#19

Post by darksaints »

stonemetal12 wrote: post #16
hyperman1 wrote: post #2

In general I love what RDBMS and postgresql in particular can bring to you, but this is one corner of them that I hate: Planners are too smart for their own good. 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…

Could be cool if the RDBMS A B tested their plans, if the new plan isn't better don't switch to it. Though that would certainly add to the Black Magic of it, maybe a command to show the dev the top 5 plans and allow them to pick and pin.

I don't think that's really necessary, and it could make the predictability problem much worse. What is really needed is a better cost model. Query planners fail only when their statistics and cost estimates do not reflect reality. What we need are statistical distributions for costs, with those distributions updating after every query. And any time you prepare a statement, it should be looking for patterns to create better column/row statistics and indexing schemes.

Re: Debugging the Postgres query planner (2018)

#20

Post by hyperman1 »

lawrjone wrote: post #13
retcore wrote: post #12

Earlier quoted context omitted.

Wow. Gocardless has over five years of engineering invested in scaling Postgres, and it was a on going development to enable them to "work with it safely"? At what point did operating safety concerns arise? I'm absolutely no fan of Oracle business practices, but this sounds like a story that sales can dine on for years, notwithstanding my private conviction that I could negotiate Exadata for less than the gross salar…

Of course GoCardless have to invest engineering effort into scaling the technology they use. 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!

As an escapee from Oracle land: I am not at all advocating purchasing it. Postgres in general is great, even if Oracle has some features that are better. The enterprise class insanity of the Oracle world is literally a cause of developer depressions in some cases. Run away.
Post reply on HN