pages/8-16-24-just-use-postgres

[utterances-bot]

Just use Postgres

https://mccue.dev/pages/8-16-24-just-use-postgres

[kmann3]

I once had a manager that wanted to migrate from SQL Server 2008 (Express, so it was free) to....... (wait for it) .... Microsoft Access.

[svanoort]

I can tell you why not MySQL (as someone who's had the dubious "pleasure" of using it as the main DB for the last 3+ years (due to application lock-in):

• MySQL is a dated database with not-great design, which has been in barebones maintenance mode for years. It's not really improving -- Oracle has little to no incentive. MariaDB has advanced some ahead of it but is also well behind Postgres.
• MySQL has a badly outdated query optimizer, meaning that with moderately complex queries you can get shockingly bad performance in some cases. I've seen queries go from seconds to minutes, and minutes to hours. Postgres on the other hand has a quite advanced query optimiser, going as far as using genetic algorithms to find the most efficient query plan.
  • Lived experience in production: if PG gives bad performance it's because you didn't index something that needed it, or your query CAN'T be executed efficiently as written. MySQL will require you to provide query hints to avoid prod-crippling outages unless your queries stay rather simple.
• MySQL administration, management, and monitoring tooling are very bare-bones and hacky. Postgres has quite advanced admin interfaces, but in MySQL things like querying the information schema, sys tables, or performance schema can come with quite painful performance hits or limited information
• MySQL does not adhere to SQL standards as closely as Postgres does; it doesn't take much effort to find nonstandard behaviors -- or cases where MySQL implicitly does something
• MySQL is less trustworthy in how it behaves; we've found quite a few undocumented or questionable behaviors, including cases where what are supposed to be lockless schema changes totally lock up production. Table statistics updates in MySQL sometimes don't happen when they should.
• Lots of trivial things in MySQL are more painful than they should be -- missing IF EXISTS/IF NOT EXISTS for idempotent schema changes (some objects support it, some do not), CREATE OR REPLACE for functions/sprocs, etc

[OllieJones]

Pros of MariaDB (the fork of MySQL).

1) it's on all the budget hosting services, so it is everywhere.

2) It's got the same slick indexing scheme as MSSQL -- clustered indexes -- and so can be made very performant.

3) Its team is adding objects, like SEQUENCEs, to help people escape Oracle's clutches. And other active development is happening.

Pros of MySQL over MariaDB? Let me think ... owned by Oracle? No, that's not it.

Should a funded greenfield project that might scale up still choose PostgreSQL? I think so. But MariaDB's ubiquity counts for a lot.

[alxmrtn]

no graph database slander?!

[zmaril]

There's a not insignificant number of companies who are holding off on upgrading from 5.7 to MySQL 8 because of the horror stories they've heard from others about the experience. Many companies are holding onto just using MySQL 5.7 as long as they can, so much so that Percona is offering 5.7 EOL support officially and publicly. https://www.percona.com/post-mysql-5-7-eol-support Oracle has not been great stewards of MySQL the last decade while Postgres has just kept steadily advancing and surpassing MySQL.

[idontusenumbers]

Another MYSQL problem: Schema changes and many seemingly arbitrary queries run outside of transactions, even if currently in one. These don't cause the transaction to fail or produce any sort of error, they just cause mysterious data inconsistencies, which are exceptionally hard to detect during a test since it depends on race conditions.

[Malix-Labs]

Graph databases also have their own use-cases

Multi-model databases are something I'm personally really interested about the future

Related

• https://youtu.be/W2Z7fbCLSTw

[0xpr03]

Mariadb is what you want for MySQL. So the license is not the problem.

[kleenkanteen]

related article: https://www.amazingcto.com/postgres-for-everything/

[svanoort]

Mariadb is what you want for MySQL. So the license is not the problem.

Yeah, it's better than MySQL in general, with the one exception that MariaDB is not available on AWS Aurora, where MySQL is. Otherwise if you have to choose between the two, MariaDB is generally better and avoids licensing pain.

But MariaDB still suffers from the terrible design and implementation decisions it inherited from MySQL. Postgres is much easier to manage and substantially less prone to problematic & prod-destabilizing behaviors. The main exception is that you need to be aware of how to manage autovacuum correctly, and that's well-known.

[adasari-sf]

MySQL has much better and stable replication features than Postgres. That's one area MySQL kicks Postgres out of the park.

[Dalkeith]

I actually use three regularly MariaDB / SQL Server / Postgres

Maria DB availability on shared hosting is just great for small personal projects to the extent that additional small cloud managed databases are free for me now. (Beyond the initial subscription) SQL Server is great on Azure because I find backup and recovery super easy Postgres kicks ass for Geography based applications

[Malix-Labs]

@adasari-sf :

MySQL has much better and stable replication features than Postgres. That's one area MySQL kicks Postgres out of the park.

Can you provide documentation backing that up, please ?

[atomkirk]

We had our app (zipbooks.com) using MySQL for 3 years and then converted it to postgres. Here's a few reasons:

• MySQL did not have cascading deletes. It would delete the fk record, but then any records pointing to that child were orphaned leaving the db in an inconsistent state.
• It was really painful to live without a bunch of the cool features Postgres has, like jsonb, json querying,
• I copied all the production data of our app from mysql to postgres and then pasted the top 10 slowest queries from our app verbatim and ran them with pg. I ran them on a load-less mysql staging server, then on a load-less pg copy. Postgres performed them 2x faster at worst and 10x faster at best. IMO it was the query planner. MySQL planner would constantly leave indexes on the floor where pg consistently figured out the best use of the structure and indexes.

There were a lot more reasons, but those are the ones I can remember off the top of my head.

[fzamperin]

Few years ago I had a big problem with MySQL, which was the lack of support for transactions on DDL queries, this made me very upset, I saw that Postgres supported this for a while already.

[sentenzadorval]

Few years ago I had a big problem with MySQL, which was the lack of support for transactions on DDL queries, this made me very upset, I saw that Postgres supported this for a while already.

This is also true for Oracle: DDLs are non-transactional and can’t be executed in the context of a transaction, ie: you must first commit or rollback.

Massive advantage for Postgres!

[na-ji]

Another reason against MySQL: its support is locked behind enterprise plans. It's so bad that even reporting a bug like a Database crash would get you an answer of the type: "you should pay our 10k enterprise plan to report this bug". I mean, this is ridiculous, a DB crash because of a query shouldn't be ignored. Migrating to MariaDB instantly solved the issue, since the crash wasn't even happening.

[lluki]

What about column store databases?

[Malix-Labs]

@lluki :

What about column store databases?

Do you mean wide-column data stores?

[ahnyounghoe]

I would like to request your permission to translate and publish this great post, on a Korean media platform. I believe your insights would be valuable to a Korean audience.

[bowbahdoe]

@ahnyounghoe I can't think of a strong reason to say no - just link back and have my name listed i guess.

[MichaelBrenden]

All above, plus one more reason RE Why Never MySQL Again (Nor Ever MariaDB) -- Mainly trustworthiness -- Many years ago the dev literally sold-out to Oracle; nothing prevents the likely repeat undercut. PG has been the sole stalwart, always 100% true to claimed intent. Mongo next. Redis next. The rest; why bother?

[MichaelBrenden]

All above, plus one more reason RE Why Never MySQL Again (Nor Ever MariaDB) -- Mainly trustworthiness -- Many years ago the dev literally sold-out to Sun, which then sold-out to Oracle, so the 'trick' inadvertently (or perhaps purposely) harmed all to benefit one; nothing prevents the likely repeat undercut. PG has been the sole stalwart, always 100% true to claimed intent. Mongo next. Redis next. The rest; why bother?

[bowbahdoe]

@ahnyounghoe - also link back here when you post it; i'm curious to see what the discourse is

[neerajx86]

What about ClickHouse?

[riking]

@adasari-sf @Malix-Labs :

The existence and success of Vitess https://vitess.io/ is enough evidence that MySQL replication is extremely solid and tested, while Postgres replication has several outage horror stories from Joyent. Check the Oxide & Friends episode "The Saga of Sagas". Several factors there: single threaded WAL recovery implementation, no metrics for replication lag, and missing documentation for how you monitor for issues.

CockroachDB is the comparable product to Vitess for the pg wire protocol and it's a reimplementation. In summary: expect to be patching Postgres if you want good replication.

[kublermdk]

@riking said what I wanted to say regarding the horror stories I heard about Postgres and the Oxide and friends episode.

But I also wanted to say that as a MongoDB fan, I can see that you are mostly post-rationalising your commitment to Postgres. It's an emotional thing that you are pretending is intellectual.

You are missing major parts of NoSQL databases and how they can actually scale to be massively large and support a lot more capacity than say a standard master and 2 read replica Postgres setup.

Granted in MongoDB land that means using sharding. Or doing the same thing you'd do in Postgres and instead of having the whole everything in the database cluster you'd spin off different tables into their own database, or split based on something like company account or whatever makes sense.

Dynamo DB or something more key->value like Cassandra will scale a lot more, but have less flexibility in data structure.

Your talked about ad-hoc querying of data and it being hard. But in MongoDB I find the Aggregation framework to be far more powerful and easy to understand and write code for than some complex SQL query that likely has 10 joins, varying in inner and outer and left and right joins or whatever they may be and adding different bits to it. In MongoDB you've likely already put the data as a copy where it needs it.

If your usage patterns change a lot then adding some indexes in MongoDB isn't very hard and it suggests which you should create.

Want full text search? MongoDB Atlas has Lucene search (the same thing Elasticsearch uses). Want Vector Search? It's got that too. I tried for nearly 8 hours to get a Redis Vector Search working. Never could and it was very convoluted. Took barely 30mins with MongoDB.

But yeah, I think my brain prefers the NoSQL of MongoDB and I'm fine with other options like CouchDB, etc.. But I never particularly liked SQL. Despite spending many years using it.

But then again when everyone at University started drinking beer I realised I just don't like the bitter taste and never got used to it. So I'll drink 🍷 wine or spirits or even cider.

I think the author here has decided that he got used to drinking beer (aka SQL and specifically Postgres) and has written about his love for it by saying he doesn't like the taste of other beverages.

But really it's a taste thing.

It also means he's not going to a cocktail party or going to go on a wine tour any time soon. E.g he's limited to using Postgres up to a certain size of website. Which is fine. It's just that I don't think it should be given as universal advice to everyone.

Let others work out what they enjoy.

That said Postgres does seem to be developing and is a better DB than MySQL from what I can tell. It's just most of my websites and systems use either MySQL or MongoDB and I haven't had a chance to play with it much.