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

111–120 of 255 posts

Re: Things I hate about PostgreSQL (2020)

#111

Post by stepbeek »

oauea wrote: post #106
rattray wrote: post #82

Another recent Postgres-complaint post from one of the best engineers I've worked with: https://blog.nelhage.com/post/some-opinionated-sql-takes/ Quoting his conclusion: > As for Postgres, I have enormous respect for it and its engineering and capabilities, but, for me, it’s just too damn operationally scary. In my experience it’s much worse than MySQL for operational footguns and performance cliffs, where using it s…

As long as mysql can't run ddl statements in a transaction it's worthless as far as I'm concerned. Also the thing where they (used to?) silently truncate your data when it wouldn't fit a column is absolutely insane. I'll take operational footguns over losing half my data every damn time.

The lack of ddl in a transaction is what scared me away too. Having to manually clean up after a failed migration just felt like something I shouldn't be thinking about.

Re: Things I hate about PostgreSQL (2020)

#112

Post by carlos_rpn »

oauea wrote: post #106
rattray wrote: post #82

Another recent Postgres-complaint post from one of the best engineers I've worked with: https://blog.nelhage.com/post/some-opinionated-sql-takes/ Quoting his conclusion: > As for Postgres, I have enormous respect for it and its engineering and capabilities, but, for me, it’s just too damn operationally scary. In my experience it’s much worse than MySQL for operational footguns and performance cliffs, where using it s…

As long as mysql can't run ddl statements in a transaction it's worthless as far as I'm concerned. Also the thing where they (used to?) silently truncate your data when it wouldn't fit a column is absolutely insane. I'll take operational footguns over losing half my data every damn time.

They don't truncate data anymore, unless you enable it in the configuration (it's disabled by default). Invalid data (0000-00-00) is also not accepted anymore.

Re: Things I hate about PostgreSQL (2020)

#113

Post by jbluepolarbear »

rattray wrote: post #82

Another recent Postgres-complaint post from one of the best engineers I've worked with: https://blog.nelhage.com/post/some-opinionated-sql-takes/ Quoting his conclusion: > As for Postgres, I have enormous respect for it and its engineering and capabilities, but, for me, it’s just too damn operationally scary. In my experience it’s much worse than MySQL for operational footguns and performance cliffs, where using it s…

They’re so right about performance gotchas. I worked on a large Java project a few years back and they were transitioning from MySQL to Postgres, after the upgrade performance was abysmal. I then spent the next 5 months optimizing queries. A lot of the issues were inner joins and how MySQL and Postgres handled lookups in inner joins differently. I would still pick Postgres over MySQL because the tools and features around it are too very good.

Re: Things I hate about PostgreSQL (2020)

#114

Post by lokedhs »

oauea wrote: post #106
rattray wrote: post #82

Another recent Postgres-complaint post from one of the best engineers I've worked with: https://blog.nelhage.com/post/some-opinionated-sql-takes/ Quoting his conclusion: > As for Postgres, I have enormous respect for it and its engineering and capabilities, but, for me, it’s just too damn operationally scary. In my experience it’s much worse than MySQL for operational footguns and performance cliffs, where using it s…

As long as mysql can't run ddl statements in a transaction it's worthless as far as I'm concerned. Also the thing where they (used to?) silently truncate your data when it wouldn't fit a column is absolutely insane. I'll take operational footguns over losing half my data every damn time.

I'm definitely no fan of mysql. I have, like many others, been scarred by its misfeatures. However, not having DDL in transactions isn't really a barrier for being useful. Oracle doesn't have transactional DDL either, and say what you will about the company, the product itself has proven itself.

Re: Things I hate about PostgreSQL (2020)

#115

Post by darkstar_16 »

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 think a lot of issues that people complain about PostgreSQL come from the fact that the default config is not very good if one wants to run it in production, even for relatively small workloads. Things like process per connection can kick one in the foot if one is not aware of how PG works.

Re: Things I hate about PostgreSQL (2020)

#116

Post by drewbug01 »

> #1: Disastrous XID Wraparound

> Pretty much any non-trivial PostgreSQL install that isn’t staffed with a top expert will run into it eventually.

I agree that this landmine is particularly nasty - and I think it needs to be fixed upstream somehow. But I do think it is fairly well known at this point. Or at least, people outside of "top expert[s]" have heard of it and are at least aware of the problem by now.

Re: Things I hate about PostgreSQL (2020)

#117

Post by darksaints »

mikl wrote: post #102
john8903 wrote: post #30

Earlier quoted context omitted.

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

I guess that’s very dependent of what kind of framework you’re using. The only PostgreSQL-driver I’ve seen that does not have connection pooling built-in is the PHP one (since PHP’s runtime model does not work in a way where that would be easily possible).

It's still fairly easy to hit problems even when you're using an application level connection pool, simply because it's so damn easy to scale up application nodes.

Re: Things I hate about PostgreSQL (2020)

#118

Post by robertlagrant »

vbezhenar wrote: post #53

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.

This seems better done at the storage layer. Doing it in the database layer is a good idea if you're monetizing your database by CPU core, of course.

Re: Things I hate about PostgreSQL (2020)

#119

Post by robertlagrant »

I_am_tiberius wrote: post #89
taspeotis wrote: post #57

Earlier quoted context omitted.

Microsoft is working hard to fix a lot of the problems e.g. connection scalability [1]. [1] https://techcommunity.microsoft.com/t5/azure-database-for-po...

That's great. Connection scalability is my biggest issue with Postgres currently. It sounds like they work on it in by commiting directly to Postgres and not only to CitusData.

They're investing rather a lot into Postgres; it's great.

Re: Things I hate about PostgreSQL (2020)

#120

Post by slt2021 »

zaarn wrote: post #65
yen223 wrote: post #43

Earlier quoted context omitted.

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.

Don't do anything obviously complex with your RDBMS and migrations are free. If all you need is a few views, tables and FKs, then migration between RDBMS' should be low effort if you have a decent RSM or ORM to plug behind it. And even with more efforted things, I've written low-effort migrations from and to various RDBMS', it's not black magic. The little time upfront is "use pgsql unless there is a good reason not…

if you dont change schema dramatically, then it doesnt make much sense to migrate to another RDBMS, because most engines have pretty much similar query planner (if you not doing "anything obviously complex").

if you do migrate due to scaling issues, then the schema must evolve, for example: add in-memory db for caching, db sharding/partitioning, table partitioning, hot/cold data split, OLTP/OLAP split, etc.

Post reply on HN