Hacker News Reader

Hacker News

Comment by matthewmacleod | Hacker News Reader

Parent Full thread

matthewmacleod

why PostgreSQL is better than SQL?

(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.

View on HN

> - Postgres is much more reliable, featureful and stable.

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.

As a heavy PostgreSQL user, who by now may be blind to some issues, I have to ask what makes PostgreSQL replication hard to configure. As far as I recall setting up replication in MySQL and PostgreSQL is very similar.

Every time I try to look into setting up master-master replication in PostgreSQL, I walk away more confused than when I started.

PostgreSQL has gotten massively easier in terms of replication during the 9.x series. It used to be the case that you cobbled something together yourself or had to rely on complex third party solutions that might do things like install a ton of triggers. Now it's about as simple as MySQL. But it took many years to catch up, so that definitively used to be a pain point.

The big remaining piece now is "sane" multi-master replication.

Oh yeah, I too have written my own shell scripts for log shipping and failover some time 6-8 years ago when I was still using PostgreSQL 8.4. But talking about this in a comparison makes about as much sense as bringing up MyISAM related issues.

Plus, from a "future-proofing" standpoint, PSQL is the standard interface for many different databases, outside of the PostgreSQL implementation (e.g. CockroachDB).

The best GUI for PostgreSQL I've ever seen is the one that is bundled in IntelliJ IDEA as a plugin (Database Tools and SQL). Autocompletion is amazing, lots of features.

Reply on news.ycombinator.com