Can someone give me an ELI5 on why PostgreSQL is better than MySQL?
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).
Absolutely true. However, which platform do you want to trust your data to: One that recently stopped eating data, or one that has a 20 year history of putting correctness first?
The one that is the better fit for my use case, I'd imagine, if neither platform no longer wants to have my data for lunch. That said, I'd still choose PostgreSQL for a new project.
PostgreSQL is also a SQL database. For a GUI tool there's pg-admin. For backups use pg-backup.
Postgres is generally a better database (imho), with a rich set of features and great stability.
Mysql (especially on INNODB) has had bugs open for years (probably decades now), and is plagued by oracle.
MariaDB is better from what i've heard.
I'm assuming from context that you mean MySQL when you wrote "SQL"
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.
> PostgreSQL is ACID compliant out of the box, whereas MySQL requires the use a of specific data store to get ACID compliance
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.
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).
> PostgreSQL and MySQL offer different replication options
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.
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 :)
(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.
Plus, from a "future-proofing" standpoint, PSQL is the standard interface for many different databases, outside of the PostgreSQL implementation (e.g. CockroachDB).
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.
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.
> - 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.
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.
SQL is used mostly in large corporate envs which can afford to spend money for stability, and here most people are building startups. I've never seen much talk about SQL server. Heck most don't even know what it is lol.
MS SQL mostly aims at the enterprise markets; Oracle, DB2 and MS SQL are usually considered the big three enterprise databases. MySQL was never aimed at that market. I think it's fair to say that MySQL can't be considered to operate on the same level and likely never will.
I am pretty sure that was a typo and he was referring to MySQL, but as for how SQL Server and PostgreSQL compares I would say they are pretty similar databases and for most usages you can pick either, giving PostgreSQL which is cheaper the upper hand. Both have some features missing from the other. SQL Server typically has better OLAP features and OLAP performance, while PostgreSQL is more extensible and has excellent geodata support.
ELI5: PostgreSQL is broad and a bit shallow, while MySQL is narrow and reasonably deep.
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.
In addition to other points in siblings, transactional ddl statemwnts and efficient alter table add column statements for null default columns.
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.
42 comments
[ 3.1 ms ] story [ 98.8 ms ] threadI'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.
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.
(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.
Note : not sarcasm.
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.
[1]:https://www.adminer.org/
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.
Here is the discussion about the release from a month ago:
https://news.ycombinator.com/item?id=14367311 (166 comments)