I've had to update some software than used it for some reason (there were just a handful of records, it was a super-small app) and compared to MySQL I only found that I couldn't easily find a good GUI tool to look at the database (I use Sequel Pro for MySQL, which is absolutely great). I found Postico and I was never able to export the whole DB.
(I'm not saying PostgreSQL isn't better, I just have no idea why).
First of all Postgres isn't better than MySQL, but it does a few things better.
PostgreSQL is more compliant with the SQL standard if that is important to you. MySQL has, at least in the past, done certain unexpected non-standard things that could cause silent turncations and data type changes that could lead to data loss.
PostgreSQL is ACID compliant out of the box, whereas MySQL requires the use a of specific data store to get ACID compliance.
PostgreSQL and MySQL offer different replication options. Which one is best is dependent on your needs
Performance wise they're about the same in aggregate, but differ quite a bit in specific cases. Very simply you can probably say that PostgreSQL is faster for complex queries over large complex data, while MySQL is faster for simpler queries over more homogeneous data, but YMMV big time.
PostgreSQL supports more 'exotic' data types like key-value HSTOREs and indexed JSON data. It also makes it a lot easier to create your own custom data types and indexes if you want to.
And related to the above PostgreSQL with PostGIS gives you world class support for storing and querying geometric and geographic data, far beyond what MySQL can offer.
I'd rephrase that: MySQL is ACID compliant out of the box; it requires the use of a specific data store to avoid ACID compliance. InnoDB is the default, not MyISAM. Other than that I mostly agree.
Oops, yes—fixed it.
Like always, one has to pick the right tool for the job.
It looks like PostgresSQL would be a much better tool in quite a few cases and it's now a lot clearer to me which cases are.
MySQL's ubiquity is hard to beat, and there are a lot of tools for it, but it could definitely improve in a few aspects and PostgreSQL does do a better job at those (key-value and JSON indexing are particularly useful for me).
With the introduction of Logical Replication as a first-class feature in PostgreSQL 10, I think it'd cover both the major modes of replication offered by MySQL.
(Assuming you meant MySQL there, given the later context!)
Here's the basic tradeoff, as I see it:
- Postgres is much more reliable, featureful and stable.
- Postgres is more difficult to configure for replication generally.
- There are better GUI tools available for accessing MySQL.
That's… basically it. The actual implementation of Postgres is in my experience so far beyond that of MySQL that it's difficult to make a fair comparison in terms of features, but Postgres still has a bit of catching up to do WRT administration.
Basically I can't imagine a situation in which I'd choose to use MySQL, unless it was a prerequisite for some other piece of software.
The big remaining piece now is "sane" multi-master replication.
Not just that. PostgreSQL also takes care of correctness. This is not to be underestimated, and no MySQL tooling is able to weight this up.
MySQL produces silent data loss on innocent-looking SQL queries, so you have to be absolutely careful even with basic stuff like CONCAT, DATE/TIME formatting or GROUP BY. PostgreSQL throws an error and aborts the transaction whenever you are doing something stupid, so no harm was done and you can simply retry with your corrected query.
For me, this is not just an advantage, it is a game changer.
PostgreSQL has a much broader feature set, but isn't as finely tuned for any given use case. MySQL has a very narrow feature set comparatively, but has deep support for things like replication, backup (fast restores in particular with Percona's tools), and fairly speedy insert and update (doesn't have postgresql's write amplification problem).
My tools are psql for PostgreSQL, and mysql for MySQL. I've never needed anything more; most of my time is spent trying different variants of the same semantic SQL to encourage use of one query plan or another. PostgreSQL has much more strategies available, but the downside is it has a more sophisticated query planner, which means it's less predictable and can start using suboptimal plans as the database statistics change. If my query is complex (e.g. using window functions or recursive CTEs) I prefer PostgreSQL, if I have a thorny production performance problem, I have more confidence that it'll stay fixed in MySQL. But with recent PostgreSQL supporting parallel operations in the query plan, it has a big weapon to fight back against MySQL's single-threaded queries.
Pg has capabilities above and beyond anything one could dream of in mysql. Pg supports a wide array of back up and replication strategies.
I'd like to turn the HN community's attention on sqlectron:
https://github.com/sqlectron/sqlectron-gui/
It's a nice electron-based app which works with postgres, sqlite, mysql, redshift, sqlserver and cassandra. It's not perfect but it's free and needs some development help if anyone good with react feels generous :)
Life is too short to use tools that don't work well to save a few hundred bucks a year.
I go for DBeaver, works marvellously.
I legitimately don't understand why they don't use Electron. Irrational hate of all things JS? There's a right tool for the job and this is it.
Well, they do use JS, though it is crapton of medicore jQuery plugins that run in ages old QtWebkit Python bindings.
Postgis, pg_trgm,and pg-routing are amazing plugins. (Postgis is a standard compliant gis extension, pg trgm adds trigram indexing, pgrouting does graph (node-vertex) searching) More complex index types out of the box. My experience is that Pg's full text search, out of the box, is better, easier to make better, and easier to index.
Note : not sarcasm.