5 ms·
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
by nicc 9y ago
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).
- deleted 9y ago[deleted]
- Sharlin 9y agoPostgresql does not silently truncate your data by default. That in itself is reason enough to use postgres.
- akx 9y agoNeither do modern versions and configurations of MySQL, mind.
- jpitz 9y agoAbsolutely 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?
- akx 9y agoThe 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.
- neoeldex 9y agoPostgreSQL 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.
- dagw 9y agoI'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.
- barrkel 9y ago> 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.
- nkkollaw 9y ago> I'm assuming from context that you mean MySQL when you wrote "SQL" Oops, yes—fixed it.
- nkkollaw 9y agoGot it. Thanks, this is very helpful. 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).
- Herald_MJ 9y agopgAdmin3 is the postgres GUI tool you are looking for. Avoid pgAdmin4.
- brianwawok 9y agoOr datagrip is quite nice.
- scrollaway 9y agoDatagrip is nice but not free :( I'd like to turn the HN community's attention on sqlectron: https://github.com/sqlectron/sqlectron-gui/ 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 :)
- elmigranto 9y agoIs that the one running `select * from table` when you click on it?
- scrollaway 9y agoNo?
- brianwawok 9y agoIt's free as in you already paid for it if you get the toolbox ;) Life is too short to use tools that don't work well to save a few hundred bucks a year.
- kennydude 9y agoSQLPro Studio on Mac is nice as well, and it's on SetApp
- timlyo 9y agoWhat's wrong with pgAdmin4?
- matthewmacleod 9y agowhy 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.
- minxomat 9y agoPlus, from a "future-proofing" standpoint, PSQL is the standard interface for many different databases, outside of the PostgreSQL implementation (e.g. CockroachDB).
- jeltz 9y agoAs 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.
- barrkel 9y agoEvery time I try to look into setting up master-master replication in PostgreSQL, I walk away more confused than when I started.
- vidarh 9y agoPostgreSQL 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.
- deleted 9y ago[deleted]
- TokenDiversity 9y agoSQL 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.
- dom0 9y agoMS 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.
- jeltz 9y agoI 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.
- dboreham 9y agoBecause it was written by people who knew what they were doing. Note : not sarcasm.
- derencius 9y agomaking schema changes on a large mysql table can be painful, specially if you have foreign keys and don't have the premium tools.
- barrkel 9y agoELI5: 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.
- jimktrains2 9y agoI feel like pg is broad and deep and mysql is shallow and narrow. Pg has capabilities above and beyond anything one could dream of in mysql. Pg supports a wide array of back up and replication strategies.
- mnazim 9y agoAdminer[1] is nice web gui, works for both MySQL and Postgres. [1]:https://www.adminer.org/ https://www.adminer.org/
- jimktrains2 9y agoIn 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.