Things I hate about PostgreSQL (2020) • Hacker News Board

Re: Things I hate about PostgreSQL (2020)

#71

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

otikik wrote: post #2

I will add one minor point to this list: The name. To this day I am convinced that the Hazapard UpperCASE usage is what has granted us: - A database called PostgreSQL - A library called libpostgres - An app folder called postgres - An executable called psql - A host of client libraries which chose to call themselves Pg or a variation.

Lets not forget the column casing issue, you either get columns to match your apps casing and have to quote them everywhere or live with them being lower cased automatically. https://dev.to/lefebvre/dont-get-bit-by-postgresql-case-sens...

Re: Things I hate about PostgreSQL (2020)

#72

Post by fuy » Tue, Apr 06, 2021, 12:33 PM UTC

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…

I wouldn't paint all hints with the same brush - it's not like the very fact of having a hint exposes query planner internals for arbitrary usage. Some hints may be more useful than others, and some may be less complicated to maintain - why not try to investigate if there's an intersection of these two sets that would be a valuable addition to Postgres?

Re: Things I hate about PostgreSQL (2020)

#73

Post by ganomi » Tue, Apr 06, 2021, 12:35 PM UTC

barrkel wrote: post #51

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…

Take a look at this Postgres Extension: http://pghintplan.osdn.jp/pg_hint_plan.html

I am even using this with AWS RDS since it comes in the set of default extensions that can be activated.

Re: Things I hate about PostgreSQL (2020)

#74

Post by dkarl » Tue, Apr 06, 2021, 12:36 PM UTC

o_p wrote: post #22

I_am_tiberius wrote: post #18

Earlier quoted context omitted.

The problem is that you want to build something that can scale in the future.

Its better to work on getting all those users before planning what color the ferrari will be..

While the executives are dreaming of exotic cars, the engineers are dreaming of exotic architectures. The difference is that when the CEO says, "It's crucial that I have this Ferrari BEFORE the business takes off," nobody takes them seriously.

Re: Things I hate about PostgreSQL (2020)

#75

Post by joana035 » Tue, Apr 06, 2021, 12:41 PM UTC

My only complain about PostgreSQL is COUNT() being quite slow compared with MySQL.

Everything else is pretty good, MySQL has compressed tables, but in PostgreSQL the same amount of data already takes less space by default.

Pghero/pg_stat_statements are also very handy.

But "hate"? No, no hate here :)

Re: Things I hate about PostgreSQL (2020)

#76

Post by junippor » Tue, Apr 06, 2021, 12:45 PM UTC

sgift wrote: post #66

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 disagree: given that nothing changed, I don't think any details need to be provided. You sound like a typical enterprise customer. "The whole system stopped working!!!!" "What did you change?" "Nothing!!!" "Are you sure?" "Yes!!!" .. searching around, looking into logs, and so on .. "Could it be that someone did x? The logs say x has happened and had to be done manually." "Oh yes. x was done by me." But, obviousl…

You sound like you have to deal with idiots all the time and resent that.

You also sound like you don't know much about PostgreSQL if you can't immediately see what happened.

Re: Things I hate about PostgreSQL (2020)

#77

Post by rattray » Tue, Apr 06, 2021, 12:49 PM UTC

ganomi wrote: post #73

barrkel wrote: post #51

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…

Take a look at this Postgres Extension: http://pghintplan.osdn.jp/pg_hint_plan.html I am even using this with AWS RDS since it comes in the set of default extensions that can be activated.

The pg_hint_plan is now being developed on github: https://github.com/ossc-db/pg_hint_plan

I recently put up a PR for a README, you can read it here: https://github.com/ossc-db/pg_hint_plan/blob/8a00e70c387fc07...

Re: Things I hate about PostgreSQL (2020)

#78

Post by dijit » Tue, Apr 06, 2021, 12:49 PM UTC

joana035 wrote: post #75

My only complain about PostgreSQL is COUNT() being quite slow compared with MySQL. Everything else is pretty good, MySQL has compressed tables, but in PostgreSQL the same amount of data already takes less space by default. Pghero/pg_stat_statements are also very handy. But "hate"? No, no hate here :)

just so you're aware, COUNT() on mysql can lie.

Basically it's fetching metadata on the table, which can in some cases not be updated (yet), where as in pg it actually counts entries in the index.

Re: Things I hate about PostgreSQL (2020)

#79

Post by hattar » Tue, Apr 06, 2021, 12:51 PM UTC

john8903 wrote: post #30

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…

Not true - I work at a company of 400 people, and we ran into the Process-Per-Connection / pgbouncer issue.

Company of 20 here. Same.

Re: Things I hate about PostgreSQL (2020)

#80

Post by datavirtue » Tue, Apr 06, 2021, 12:54 PM UTC

This blog post answered a lot of questions related to the internals, allowing me to make a better (real) comparison between SQL Server and PostgreS.

For all of these issues he pointed out it is simply done differently in SQL Server and suffers none of the stated pitfalls. Well, you can't get the source code, and it is not free.