Things I hate about PostgreSQL (2020)

Post by redis_mlc » Tue, Apr 06, 2021, 11:44 AM UTC

doctor_eval wrote: post #48

I_am_tiberius wrote: post #40

Earlier quoted context omitted.

I would only have thought of MySQL.

MySQL isn't a general solution to problems of scale, because you don't know what problems you're going to have until you have them. So for example if your scaling problem is ACID compliant database updates - say you're the next fintech - then I was under the impression that MySQL would be the last database you'd want to be using. Have I missed something?

Post by SigmundA » Tue, Apr 06, 2021, 11:52 AM UTC

barrkel wrote: post #54

jeltz wrote: post #16

Earlier quoted context omitted.

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 specif…

Post by zaarn » Tue, Apr 06, 2021, 11:58 AM UTC

yen223 wrote: post #43

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…

I am partial to the "don't solve problems you don't have" argument which holds true in a lot of cases. That said, the database is the one part of the system that is very tricky to evolve after the fact. Data migrations are hard. It's worth investing a little bit of time upfront to get it right.

Post by SigmundA » Tue, Apr 06, 2021, 11:59 AM UTC

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…

Post by cafard » Tue, Apr 06, 2021, 12:02 PM UTC

chrisandchris wrote: post #49

I_am_tiberius wrote: post #34

Earlier quoted context omitted.

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.

I would rather use Postgres and have a RDBMS that is quite strict and migrate data later instead of having a RDBMS that just does what it likes sometimes. For example, query your table „picture“ with a first column „uuid“ (varchar) with the following query: SELECT * FROM picture WHERE uuid = 123; I don‘t know what you expect, I expect the query to fail because a number is not a string. MySQL thinks otherwise.