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

201–210 of 255 posts

Re: Things I hate about PostgreSQL (2020)

#201

Post by fdr »

rattray wrote: post #170
fdr wrote: post #147

I think this is a reasonable list of weaknesses, with a few quibbles. I guess since I've built parts of Heroku Postgres, and Citus Cloud, and now Crunchy Bridge...maybe I'd know. On the other hand...on the whole...maintaining Postgres is probably among the cheapest pieces of software on which I have to do so, which is why the cloud business model works. Something less stable (in all senses of the word) would chew up…

I'd be very curious to hear your quibbles!

I don't think the post informs on Physical and Logical replication that well.

Most database systems of adequate budget and maturity implement both, for various reasons.

Re: Things I hate about PostgreSQL (2020)

#202

Post by fdr »

briffle wrote: post #103
nezirus wrote: post #21

I think this is a good list, one needs to know potential pitfalls and plan accordingly. As for point #7, if your upgrade requires hours, you are holding it wrong, try pg_upgrade --link: https://www.endpoint.com/blog/2015/07/01/how-fast-is-pgupgra... (as usual, before letting pg_upgrade mess with on disk data, make proper backups with pg_basebackup based tools such as barman).

my only complaint with the pg_upgrade (with or without the --link) is for some reason, it does not move the statistics over, and you have to rebuild them, or have horrible performance for a while, until each page hits it auto-analyze thresholds. I'm doing some testing now for my DB, and the rebuilding all stats takes far, far longer than the upgrade. The upgrade takes seconds, and it takes a while to analyze multi-TB…

Yeah, there's some kludgey workaround for this that is definitely 80/20 kind of material...pg_upgrade will generate a script that does progressively more accurate re-ANALYZE so you're not flying your early queries totally blind. Maybe look into running that.

Re: Things I hate about PostgreSQL (2020)

#203

Post by aidos »

I_am_tiberius wrote: post #142
doctor_eval wrote: post #48

Earlier quoted context omitted.

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?

I'm no expert and can't answer that. It was just my impression, and I might be wrong, that for scaling purposes MySQL is better suited. Currently I'm working on a Saas product and the test instance that runs on Digital Ocean sometimes causes connection limit issues (with connection pool) sometimes. Sure my code is maybe not perfectly utilizing connections but I'm really afraid that this happens in production and I do…

The default limit on Postgres is 100, so you need to ask yourself why you’re exhausting all those connections. The issue isn’t the dB, it’s the code making the connections. Advice: don’t fret scaling issues, get your fundamentals right

Re: Things I hate about PostgreSQL (2020)

#204

Post by jrockway »

paulryanrogers wrote: post #95
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…

It's interesting how personal scars can entrench ones perspective. After MySQL 8's renaming-table-will-crash-server bug I'm reluctant to use it for new projects.

I'm personally guilty of this mindset, but it's something I'm working on. After you get burned by a system, you know of the bug, and you can fix it. But the instinct is to switch to a new system, or to rewrite the system. That does get rid of all the bugs in the old system! But, in the process you've replaced them with brand new bugs that nobody has seen or heard from, until they decide to crawl into your mouth while you're asleep and you wake up in a panic. It's bugs all the way down, folks... this is software we're dealing with!

I think sendmail is the classic example of a program with so many bugs it had to be rewritten from scratch, and many people did. All of those alternatives, even qmail (widely debated: https://www.qualys.com/2020/05/19/cve-2005-1513/remote-code-...), ended up with a bug or security problem too. And they seem to have even fixed sendmail. It's still around and it doesn't take down the whole Internet every three weeks anymore. Wow! Sometimes there are just a million bugs, and you fix all one million of them, and then there aren't any more bugs.

Re: Things I hate about PostgreSQL (2020)

#205

Post by lmarcos »

sethhochberg wrote: post #177
sdevonoes wrote: post #110

For the ones who know: is MySQL (without Percona) affected by the same issues the author is talking about?

Its a mixed bag, the author does make a few direct comparisons to MySQL. In my experience MySQL replication and cluster defaults are generally a lot more robust, whether you're using Galera or PXC or even just master-slave replication. Other pain points the author discussed like Postgres effectively being major-version incompatible between different replicas are less of an issue in MySQL, especially since it most typ…

> But if you're using MySQL 5.7ish or later, InnoDB, and some of the other Percona tooling for things like online DDLs you've got an extremely robust DBMS to work with.

Would you say that percona is a must when using MySQL in production? I have some experience with Postgres and the fact that pgbouncer is needed in production environments makes me think "why postgress doesn't come with batteries included?".

Re: Things I hate about PostgreSQL (2020)

#206

Post by rattray »

fdr wrote: post #201
rattray wrote: post #170

Earlier quoted context omitted.

I'd be very curious to hear your quibbles!

I don't think the post informs on Physical and Logical replication that well. Most database systems of adequate budget and maturity implement both, for various reasons.

Interesting, thanks. Yeah I was surprised to hear his skepticism of logical replication, but I've never operated it in production before. Curious for resources on that.

Re: Things I hate about PostgreSQL (2020)

#207

Post by sethhochberg »

lmarcos wrote: post #205
sethhochberg wrote: post #177

Earlier quoted context omitted.

Its a mixed bag, the author does make a few direct comparisons to MySQL. In my experience MySQL replication and cluster defaults are generally a lot more robust, whether you're using Galera or PXC or even just master-slave replication. Other pain points the author discussed like Postgres effectively being major-version incompatible between different replicas are less of an issue in MySQL, especially since it most typ…

> But if you're using MySQL 5.7ish or later, InnoDB, and some of the other Percona tooling for things like online DDLs you've got an extremely robust DBMS to work with. Would you say that percona is a must when using MySQL in production? I have some experience with Postgres and the fact that pgbouncer is needed in production environments makes me think "why postgress doesn't come with batteries included?".

The landscape is already a bit weird in terms of "out of the box" features for MySQL because there are at least a few major distributions between Oracle MySQL, Percona XtraDB, MariaDB, etc. They're generally all compatible. They have some different defaults, and very rarely different features. But particularly for XtraDB you tend to get access to better diagnostic info and some better defaults for InnoDB (or at least you used to, my info may be a year or two out of date at this point).

While it is true that MySQL support for online DDLs has gotten much better over the years, I think the tools like pt-online-schema-change are still extremely valuable - there are still certain kinds of changes that you can't make with an online DDL in MySQL, or sometimes you specifically don't want to take that approach. But I'd think of the Percona Toolkit stuff as more a nice set of tools to have in your DBA toolkit, rather than an essential part of your DBMS for anyone running it in production. Its not like the pgbouncer situation. Everybody wants to avoid process-per-connection, but plenty of people can get by without complex online schema migrations.

Re: Things I hate about PostgreSQL (2020)

#208

Post by twunde »

rattray wrote: post #194
eor wrote: post #189

Earlier quoted context omitted.

It's not a book, but Christophe Pettus' blog ( https://thebuild.com/blog ) has a lot of really good information. In particular, his talk "Breaking PostgreSQL at Scale" goes through the problems you run into as you hit different levels of scale ( https://thebuild.com/presentations/2019-fosdem-broken.pdf )

Thanks! Very helpful, that talk looks like a (very) condensed version of what I was looking for. It looks like the video for that talk is here: https://www.youtube.com/watch?v=XUkTUMZRBE8

FYI, for anyone interested Pettus does consulting work as the founder of https://pgexperts.com/. Highly recommend working with him if you need a postgres DBA.

Re: Things I hate about PostgreSQL (2020)

#209

Post by psanford »

jrockway wrote: post #204
paulryanrogers wrote: post #95

Earlier quoted context omitted.

It's interesting how personal scars can entrench ones perspective. After MySQL 8's renaming-table-will-crash-server bug I'm reluctant to use it for new projects.

I'm personally guilty of this mindset, but it's something I'm working on. After you get burned by a system, you know of the bug, and you can fix it. But the instinct is to switch to a new system, or to rewrite the system. That does get rid of all the bugs in the old system! But, in the process you've replaced them with brand new bugs that nobody has seen or heard from, until they decide to crawl into your mouth while…

> It's still around and it doesn't take down the whole Internet every three weeks anymore.

Maybe that's because the vast majority of email no longer goes through sendmail.

Re: Things I hate about PostgreSQL (2020)

#210

Post by kleebeesh »

Does anyone know if there is a way to monitor sudden changes in planning behavior for a given query? For example, I'd like to monitor/alert on this kind of situation: I'm executing the same query for many weeks/months, some table involved in the query is slowly growing over time, at some point the query planner responds to the growth by changing the plan.
Post reply on HN