"PostgreSQL supports most of the major features of SQL:2016. Out of 177 mandatory features required for full Core conformance, PostgreSQL conforms to at least 170. In addition, there is a long list of supported optional features."[1]
Ask HN: It's 2023, how do you choose between MySQL and Postgres?
331–340 of 366 posts
Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?
#332Earlier quoted context omitted.
You're not considering the timeline of events. I'm unfamiliar with Faire but the rest were already using MySQL at the time of Oracle's acquisition in 2010. Switching backends would have been rough for those companies and this was... 2010, meaning Postgres was not nearly as performant or full featured as it is today. As mentioned in other comments, FB's investment in customizing MySQL has been extensive. They've poure…
(Full disclosure, I work for Oracle) Anyone who says no investment has been into MySQL I suspect never took the time to read the features/release notes for MySQL 8 https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html
Well, you got me there. It stagnated for so long relative to others that I didn't realize the pace had picked up in a big way.
Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?
#333Earlier quoted context omitted.
Sorry, ARCHIVE is a MySQL storage engine. It supports only non destructive transactions: INSERT, REPLACE, and SELECT, but not DELETE and UPDATE. It’s an excellent alternative to use a WORM drive when you’re trying to preserve everything (say, a list of financial transaction). I’ve looked for something like this in Postgres (which I love!), but sadly it doesn’t seem supported. https://dev.mysql.com/doc/refman/8.0/en/a…
Why is revoking permissions to run deletes/updates not an option?
[deleted]
Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?
#334Earlier quoted context omitted.
The problem is that when you you need to tell the planner what to do, you can do it in MySQL, but not postgres. Imagine you've got a production database with lots of traffic which suddenly can't handle anything because it inserted an extra row which tipped the balance and now takes seconds to process a common query. Do you know how to fix the table statistics quickly? Do you know how to change that query to force the…
There is the pg_hint_plan extension that gives most of what you would want in a hinting system. I think a better fix lies in the direction of making queries more of a first class object with options to nail down plans, add custom logic to pick plans dependent on parameter values, etc.
I am on GCP/AWS, its not possible to use extensions. They allow only a few whitelisted extensions. If it is built into the DB, then I can use without any hassles.
Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?
#335There is almost no good reason to choose MySQL over PostgreSQL for any operational reason, I did a deep dive many moons ago (before major improvements in performance to postgres) and people were saying that MySQL was faster. I found that not to be true and the differences have only gained even more favour towards postgres. also, I assume you mean MariaDB as MySQL is owned by Oracle and I would greatly implore anyone…
> There is almost no good reason to choose MySQL over PostgreSQL for any operational reason Galera is the main one I can think of: * https://galeracluster.com/library/documentation/tech-desc-in... * https://mariadb.com/kb/en/what-is-mariadb-galera-cluster/ * https://packages.debian.org/search?keywords=galera I'm not aware of any multi-master, active-active(-active) replication system that is open source for PostgreSQ…
how do multi master MySQL handles conflicts?
Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?
#336Earlier quoted context omitted.
> It's less featureful, and I'd consider that a strong virtue in the YAGNI camp - less to go wrong, less mental overhead. Imagine when you actually need any of the features that PostgreSQL provides like pub/sub, logical replication, JSONB etc. With MySQL you might have to hack a solution that is much more complex or you have to set up an entirely separate tool. What I find nice with PostgreSQL is that for simple case…
> features that PostgreSQL provides like [...] logical replication MySQL has offered logical replication for considerably longer than Postgres, and it's a substantially more configurable and mature implementation. MySQL's built-in replication has always been logical replication. It's a feature MySQL has had for 23 years -- built-in logical replication is literally one of the top reasons why all the biggest MySQL user…
[deleted]
Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?
#337Earlier quoted context omitted.
Didn't 8 ship 5 years ago?
Their release model changed with MySQL 8 -- they do rolling point releases every quarter with new features sprinkled in as they're ready. Quite a few new major features have been released that way, including INSTANT alters, parallel index builds, CLONE plugin, major changes to how UNDO logs are sized... it's more like Windows 10's release model. Very recently they've mentioned they'll be changing this again to have s…
Really wish they bump up the version number or something. It makes discussions with MySQL a lot easier.
Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?
#338Earlier quoted context omitted.
What's the ratio of solving DB perf issues by optimizing it and letting the planner do its work, to telling it what to do? For me it's like 1000:1. And that one case I remember was perfectly solvable the regular way, with a little more time.
The problem is that when you you need to tell the planner what to do, you can do it in MySQL, but not postgres. Imagine you've got a production database with lots of traffic which suddenly can't handle anything because it inserted an extra row which tipped the balance and now takes seconds to process a common query. Do you know how to fix the table statistics quickly? Do you know how to change that query to force the…
I googled a bit and had no idea how many questions about Postgres query planner going nuts are out there. I just imagined this is a problem that creeps over time (giving you time to notice and act in advance, assuming you have monitoring/alerts set up) rather than suddenly tipping the scale - though it probably can happen suddenly after large data import.
Personally never ran into this with Postgres nor had anyone I know worry about it - the query planner was reliable for me in 99.99% of cases but yeah, I admit that it's a black box for me that I expect to take care of internals - hopefully it continues to do so, but I got to give it to MySQL for allowing to override it then.
Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?
#339Earlier quoted context omitted.
What's the ratio of solving DB perf issues by optimizing it and letting the planner do its work, to telling it what to do? For me it's like 1000:1. And that one case I remember was perfectly solvable the regular way, with a little more time.
Postgres query planner suddenly deciding to do something silly in the middle of the night has taken Notion down a few times. It's quite annoying, and it's very frustrating to have no recourse.
Interesting - how do you approach it when it happens and you're under time pressure to bring it back online - assuming you can't just fix query plan? I'd probably start by tweaking stats options and resetting them for problematic tables but don't have further ideas from the top of my head.
Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?
#340I worked for many years with MySQL and then started working in a company that used PostgreSQL. Things I liked from PostgreSQL:
- Better SQL standard support (modernish?).
- JSONB type, also with indexes! Very useful to have JSON fields to allow dynamic fields or avoid sub-querying some tables (i.e. I mean caching some computed results in a JSON field).