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

Things I hate about PostgreSQL (2020)

rbranson.medium.com

Post reply on HN
Print view View original

51–60 of 255 posts

Re: Things I hate about PostgreSQL (2020)

#51

Post by barrkel »

My single biggest beef about PG is the lack of query planner hints.

Unplanned query plan changes as data distribution shifts can and does cause queries to perform orders of magnitude worse. Queries that used to execute in milliseconds can start taking minutes without warning.

Even the ability to freeze query plans would be useful, independent of query hints. In practice, I've used CTEs to force query evaluation order. I've considered implementing a query interceptor which converts comments into before/after per-connection settings tweaks, like turning off sequential scan (a big culprit for performance regressions, when PG decides to do a sequential scan of a big table rather than believe an inner join is actually sparse and will be a more effective filter).

Re: Things I hate about PostgreSQL (2020)

#52

Post by barrkel »

mikl wrote: post #8

I think it’s worth mentioning that most of these problems only occur at a scale that only top 1% of companies will reach. I’ve been using PostgreSQL for over a decade without reaching any of the mentioned scaling-related problems. PostgreSQL is still the best general purpose database in my opinion, and you can then consider using something else for parts of your application if you have special needs. I’ve used Cassan…

I've hit many query performance regression problems with ~10 million rows, which required rewriting with CTEs and other techniques to tweak the planner. This isn't a large scale at all.

Re: Things I hate about PostgreSQL (2020)

#53

Post by vbezhenar »

One thing that I miss from PostgreSQL is transparent encryption. Some information systems require encryption of personal data by law. It's trivially implemented with commercial databases, so you can enable it and check a mark. Not so much with Postres.

Re: Things I hate about PostgreSQL (2020)

#54

Post by barrkel »

jeltz wrote: post #16
junippor wrote: post #9

Earlier quoted context omitted.

Ok, what do you say about this one? > #9: Ridiculous No-Planner-Hints Dogma One of these "query shifts" that the author mentions happened with a production database where I work. It was down for two days. The query planner used to like using index X but at some point decided it didn't want to use that and decided it wanted to do a table scan inside a loop instead. Meaning: one day a certain query was working fine, th…

I feel that is the least fair of the complaints (I agree with several of them and have some of my own too). Not because query hints are not disreable but because who is going to pay for maintaing them? It is not really dogma (I, with dome help, managed to convince them to merge one very specific query hint: MATERIALIZED for CTEs) but that they do not want to expose more of the innards of the query planner than necess…

STRAIGHT_JOIN is probably my favourite feature of MySQL in terms of planner hints; but there's actually a deeper inconsistency behind the philosophy.

Usually, when you get a bad query plan, it's because the join order isn't right. Outside the start table and hash joins, indexes need to match up with both predicates and the join keys. Get the wrong join order and then your indexes aren't used.

Since you need to specify which indexes to build and maintain, and such indexes are generally predicated on the query plan, why not ensure that the query is using the expected indexes?

If one really wants to go down the route of no optimizer hints, then the planner should start making decisions about what indexes to build and update. Go all in.

Re: Things I hate about PostgreSQL (2020)

#55

Post by WJW »

I_am_tiberius wrote: post #26
theshrike79 wrote: post #19

Earlier quoted context omitted.

I think I've heard a saying about this, something about premature optimisation...

Sure you shouldn't care about scaling at the beginning. But why should you start using a system that you already know won't scale in the future?

Because the hyperscalable databases are much more difficult to set up, use and administer. It's not a "free" upgrade, it'll slow down everything else you do.

Re: Things I hate about PostgreSQL (2020)

#56

Post by barrkel »

lmm wrote: post #33
junippor wrote: post #17

Earlier quoted context omitted.

> i think you need to provide more details for a good reply. what changed between the time index was used and when it wasn’t? I also had to “convince” postgresql to use my index but that lead to a much better design I disagree: given that nothing changed, I don't think any details need to be provided. The question is NOT "Is postgresql's choice better than mine?" The question is "A certain design was working and sudd…

I guarantee you that something changed. Maybe the row count passed a certain threshold. Maybe you upgraded the database version. If you don't want the query planner to pull arbitrary execution behaviour out of its ass, why are you using an SQL database in the first place? The whole point of SQL is that you declare your queries and leave it up to the planner to decide, and for that to be at all workable the planner ne…

Thing is, MySQL, with judicious use of STRAIGHT_JOIN, won't do the same thing. And generally MySQL is much more predictable because it's much less sophisticated: it only has a couple of join strategies (pre 8.0, only nested loop join) and quite limited query rewriting, so you can - with practice - expect a query plan as you write the SQL. And in practice, there's usually only two or three really big tables involved in performance-sensitive queries, tables which you need to pay attention that you don't end up doing scans on. The rest of the tables you can leave up to the planner.

Re: Things I hate about PostgreSQL (2020)

#59

Post by marcus_holmes »

I_am_tiberius wrote: post #34
marcus_holmes wrote: post #25

Earlier quoted context omitted.

ffs, this attitude causes massively more problems than it solves. 1. You can always change later. Uber switched from Postgres to MySQL when they had already achieved massive scale. 2. You don't know what scaling problems you're going to get until you've scaled. 3. Systems designed to scale properly sacrifice other abilities in order to do that. You're actively hurting your velocity with this attitude. 4. Every single…

ffs, this attitude causes massively more problems than it solves. I don't think that it causes so many problems to just use MySQL instead of Postgres from the very beginning of a project. I like using Postgres and I understand that I shouldn't care about scaling but if a make a good decision from the very beginning it can't hurt.

Uber switched because of a very specific problem they had with the internals of Postgres, that was handled differently in MySQL (which I believe is now "solved" anyway).

It's not that MySQL scales better than Postgres, but that Uber hit a particular specific scaling problem that they could solve by switching to MySQL.

You could well use MySQL "because it scales better" and then hit a particular specific problem that would be solved by switching to Postgres.

Re: Things I hate about PostgreSQL (2020)

#60

Post by roenxi »

Rather than a query planner, an interesting approach would be to expose the more stable part of the internals with a new language and let people roll their own query plans. Then Postgres can be NoSQL too and we can all be happy.

I'm not hopeful that it would be technically feasible, but it isn't obvious that Postgres needs to only support SQL as an interface. The SQL language is so horrible I assume it is already translated it into some intermediate representation.

Post reply on HN