Hey, Threadiverse! I’m looking for informed opinions on database choices.
I can stand up an Internet-facing application and have it use either MySQL or PostgreSQL. Which is the better choice, and why do you think so?
Thanks!
Postgres. It’s more strict by default, which leads to a lot fewer surprises.
Here’s my rule of thumb:
- SQLite - if it’s enough
- Postgres
- MariaDB - if you don’t care about your data and just want the thing to work
- MySQL - if you sold your soul to Oracle, but still can’t afford their license fee
Postgres, hands down. It’s far better than MySQL in every way.
I have historically gone with PostgreSQL and had no complaints. The licensing issues concerning MySQL also give one pause (Oracle are greedy bastards who will use any excuse to extract money from captive customers, so depending on their properties is to be avoided). Having said that, these days, SQLite is probably sufficient for many workloads and has the advantage of not requiring a database server.
I’d choose which ever one is not PostgreSQL.
Then you’d be wrong. Unless you pick SQLite and that’s all you need.
The answer is impossible to answer until you tell us more about your needs. Better choice considering what?
In general, untill you have terabytes of data or a significant amount of traffic (operations per second) database choice does not matter and you should be using cheaper option, where the cost should be assessed as a derivative of price of hosting, cost per operation, cost to deliver (how familiar you are with it).
When you have significant amount of data or traffic - only then you should worry about database kind or language. Until then this could be a premature optimization.
Postgres is far superior in every way.
We used MySQL (and Percona XtraDB) servers at work, and it is so bad. So I made several presentations showing generic and specific reasons why Postgres is better. I had to cut a lot of content because MySQL is just that bad.
Some things may not seem relevant now, but as you keep the DB around long enough, you will appreciate the whole package of Postgres.
Things that will help a lot, but are extensions:
- pg_partman - for automatic partition management
- patroni - management of replicas, automatic failover - it does everything for you with simple commands
There is a DB comparison matrix, but hasn’t been updated in over a year, which is a shame, but still gives you the idea of how different databases support SQL features: link.
Spoiler: postgres has the most support, with a huge lead
Avoid MySQL and MariaDB at all cost.
I used MariaDB for school projects, what exactly is wrong with it? Asking because I’m just unaware
Many things, too many to even remember.
Very bad SQL implementation is a good start, still bad replication support (compared to Postgres), various bugs present for too long…
https://www.sql-workbench.eu/dbms_comparison.html this comparison is a bit out of date, but explains a lot
While there was a time, where those databases were considered “good”, they are only this famous because they have been free or open source for ages. Professors love open source stuff. This does not necessarily mean it is a good product in terms of database functionality. They have been stuck in the old age and simply get outperformed by almost anything. Professors also hate to change their slides and to learn something new. Because their priority is on functionality, not on real world use. And when you want to use a product in the real world, non-functional properties gain a lot of value. One of them is performance.
If you want to have a fast, reliable, open source database, use ClickHouse.
Generally speaking, if a professor recommends something, it probably sucks. Their information is incredibly outdated and is usually whatever they used in their own undergrad program.
At school I learned:
- Java
- PHP
- MySQL
- C#
- C++
- Racket (Lisp)
Each of those has a better alternative, with C# being the least bad. For example:
- Java -> Kotlin
- PHP -> Python
- MySQL -> SQLite or Postgres
- C# -> Python (desktop QT GUIs) or web stack (e.g. Tauri for desktop web stack)
- C++ -> Rust (non-games) or a game engine
- Lisp -> Haskell
Formal education is for learning concepts, learn programming languages and tools on your own.
Click house is for OLAP workloads
It was. Now compare the benchmark of OLTP tasks and you will be surprised
This sounds like a smear campaign from a competitor.
I’ve been using mysql and mariadb in my homelab for years and have not enountered any issues. I don’t really know postgresql, so if I was to choose one, I’d go with MySQL/MariaDB.
The question was for an internet facing application, not a homelab.
As someone who has dealt with MariaDB in production, I would certainly look elsewhere. Haven’t had any colleagues who would disagree…
You’re appealing to authority instead of presenting real arguments.
Smear campaign with an open source product? Are you sure you still have a working organ between your ears?
That being said, my recommendation is based on using databases in big data environments for 15 years. But I am glad that your home lab is working fine with MariaDB. Does not mean it is a good product. And your comment just proves my point.
You aren’t exposing the database right?
We have both MySQL and PostgreSQL in our production environment. Postgres is way nicer as a user of the DB. I created a document months ago outlining a dozen different things that Postgres does that MySQL either doesn’t do or does worse. I can’t speak to managing the DB as I don’t have experience with that.
Postgres, the extensions and open source community have been very helpful.
Postgis for images
CloudNative-pg for running DB clusters in kuberneties.
Most applications can do just fine with SQLite, but if you need something with a lot more write speed, go with PostgreSQL.
Hardly anyone ever says mysql is better. Postgres has a lot of nice features, But they’re still a hell of a lot more people out there with mySQL experience.
If for some reason you really want to go mysql I would urge you to look into percona and percona tools. It’s incredibly fast super optimized. The tools let you do backups that my sequel could only dream of.
That said, if you don’t have any strong needs for mySQL, and you don’t have any experience with it I would probably start picking up postgres.
I’d vote for MariaDB or Postgres
My opinion is that of the two Postres is more “adult”. So if you want to"just wing it" MariaDB would work, but if you’re serious Postgres is a better choice. However Postgres also requires better understanding of you setup etc. So it’s a ROI game - what’s more important to your project, how complex your DB is, what are the requirements for availability, transaction security etc. There is no “better” or “worse” there’s “feasible” and “prohibitive” 😉
PostgreSQL is just better. It’s supports transactions on DDL (things like altering table structure) and enforces unique constraints after transactions complete … so you can actually do a bunch of important stuff (like update your table structure or swap unique values between rows) safely.
