How would you choose one vs the other?
Most answers allude to either: they are kinda same and full-featured, or different philosophies. Neither really hints at how you pick one.
Ask HN: Is PostgreSQL better than MySQL?
21–30 of 83 posts
Re: Ask HN: Is PostgreSQL better than MySQL?
#22It depends: the projects make different trade offs and support different points of extension. I find it useful to remember that MySQL has various storage engines (that make different trade offs, and may themselves have different options such as various row formats), when using for instance the default of InnoDB knowing that it uses index organized (meaning stores the data in a btree as opposed to heap) tables can inf…
As far as making/performing modifications to the schema/data, as others have stated, transactional ddl is a huge feature for Postgres and makes migrations much more straightforward in my experience.
Re: Ask HN: Is PostgreSQL better than MySQL?
#23It's been a while since I've given serious thought to this question but I'm glad it's asked because I would like to re-learn and find out if those using it do so for the reasons I perceive PostgreSQL to be "superior" based on talk among peers. The basic "MySQL is faster but lighter on features" is what I recall hearing; also that PostgreSQL is "multi-paradigm" in that you can run document/NoSQL tables, ETL, and data…
I started with mysql in late 90's, switched to postgre and would prefer that instead of MySQL if it is my decision. MySQL is faster than postgres is a myth. Please see this https://arctype.com/blog/performance-difference-between-post...
One thing mysql is better than postgres is that mysql can fast replicate across different data centers. See this blog: https://www.uber.com/en-DK/blog/postgres-to-mysql-migration/
However what Uber is doing may not be what you doing. So do some research and hire qualified db admin or devop people to make the informed choice.
Re: Ask HN: Is PostgreSQL better than MySQL?
#24The philosophy of postgresql was to do the best possible job the rightest way. Not necessarily the easiest to use nor fastest. The philosophy of mysql was to do the best it can as fast as it can for as many users as it can. Not necessarily the 'right' or 'best' way. Its not like either philosophy is formally documented or mandatory but "generally" matches up pretty closely to real world behavior. The biggest problem…
I also believe these were the original philosophies, since MySQL emerged from practical issues and Postgresql has more academic origins. But nowadays, I'm not sure MySQL is generally faster. In my experience, the main differences are that many simple things are simpler with MySQL, but Postgres is much more rigorous and complete.
For instance, a major upgrade of a MySQL server is usually painless (Debian even upgrades automatically). My last upgrade from Pg12 to 14 was not that easy. Replication has been easier with MySQL, though I've heard Pg has recently matured on this side. The last time I used a Pg DB that was queried by a web app, PgPool was installed because Pg could not handle many concurrent connections, but I've also heard this may not be required with recent Pg.
Client side, in many cases, a "poor man's search" with LIKE is enough or even suitable. With MySQL, I can declare `description TEXT collate utf8mb4_general_ci`, and then `LIKE '%de toutes façons%'` will match `De toutes facons`. Obtaining the same result with Postgres requires much more work.
Now for the bad side of MySQL: it has so many footguns, like the various compatibility modes (silent truncation when inserting is still the default behavior, I think), or the transactions breaking when a DDL starts. Support of complex types (arrays, JSON, specialized types like ISBN...) is also years ahead in Postgresql, and so are many advanced features.
Re: Ask HN: Is PostgreSQL better than MySQL?
#25Don't miss out on SQLite - https://news.ycombinator.com/item?id=31159281
It is only good if you do few writes, with at least seconds in between.
Wrong. SQLite can handle concurrent writes using a WAL. You do not have to wait any amount of time between writes. Once the write is done, it is done.
Re: Ask HN: Is PostgreSQL better than MySQL?
#26PostgreSQL has a lot of power. Need your table trigger to make a HTTP request, need a stored procedure written in Rust/Java/Javascript/Perl/Python (to name a few _common_ options ;-), want to index JSON fields, or GIS, or NLP vectors. Need everything be strict and correct ACID, referenced integrity, row level security. Need point in time recovery with a The fact it overlaps with MySQL if your use case is "backend for a common ORM" barely makes them comparable.
Re: Ask HN: Is PostgreSQL better than MySQL?
#27I first used MySQL when it didn't even check column constraints, so that left a bad impression. It has improved, but I'd much rather use Postgresql, and a few features which I really like are:
- Streaming Replication (keep a secondary instance for contingency or moving heavy queries away from the main database);
- Procedures in various languages;
- Foreign Data Wrappers for integration with other databases (much sturdier and Oracle's Heterogeneous Services and there's even an FDW for Elasticsearch);
- Backup/restore is quite easy as is upgrading.
The only thing I miss are packages like Oracle's.
So, unless I have no other option, I'll choose Postgresql.
Re: Ask HN: Is PostgreSQL better than MySQL?
#28As a software engineer that worked with a lot of different databases in the last 20 years I have come to believe that PostgreSQL is even better than Oracle (this will certainly spark some fires). I have had shit with a lot of databases but never with PostgreSQL. MySQL has some really nasty documented and undocumented "features" so I rather avoid that database. It has it's place in the universe but it's in on a different plane of existence than mine
Re: Ask HN: Is PostgreSQL better than MySQL?
#29I've operated MySQL in production at different scales ranging up to _very_ large and as such, I place a lot of emphasis on how well that can be done. I posted some of this a couple years ago, but I would describe the production operation of MySQL as a minefield. I maintained a list of the mines I stepped on and here are the two I encountered with the biggest blast radius:
* If you have pre-5.6.4 date/time columns in your table and perform any kind of ALTER TABLE statement on that table (even one that doesn't involve the date/time columns), it will TAKE A TABLE LOCK AND REWRITE YOUR ENTIRE TABLE to upgrade to the new date/time types. In other words, your carefully-crafted online DDL statement will become fully offline and blocking for the entirety of the operation. To add insult to injury, the full table upgrade was UNAVOIDABLE until 5.6.24 when an option (still defaulted to off!) was added to decline the automatic upgrade of date/time columns. If you couldn't upgrade to 5.6.24, you had two choices with any table with pre-5.6.4 types: make no DDL changes of any kind to it or accept downtime while the full table rewrite was performed. To be as fair as possible, this is documented in the MySQL docs, but it is mind-blowing to me that any database team would release this behavior into production. In other words, in what world is the upgrade of date/time types to add a bit more fractional precision so important that all online DDL operations on that table will be silently, automatically, and unavoidably converted to offline operations in order to perform the upgrade? To me, this is indicative of the same mindset that released MySQL for so many years with the unsafe and silent downgrading of data as the default mode of operation.
* Dropping a table takes a global lock that prevents the execution of ANY QUERY until the underlying files for the table are removed from the filesystem. Under many circumstances, this would go unnoticed, but I experienced a 7-minute production outage when I dropped a 700GB table that was no longer used. Apparently, this delay is due to the time it takes the underlying Linux filesystems to delete the large table file. This was an RDS instance, so I had no visibility into the filesystem used and it was probably exacerbated by the EBS backing for the RDS instance, but still, what database takes out a GLOBAL LOCK TO DROP A TABLE? After the incident, I googled for and found this description of the problem (https://www.percona.com/blog/2009/06/16/slow-drop-table/) which isn't well-documented. It's almost as if you have to anticipate every possible way in which MySQL could screw you and then google for it if you want to avoid production downtime.
There are others, too, but to this day, those two still raise my blood pressure when I think about them. In addition to MySQL, I've pushed SQL Server and PostgreSQL pretty hard in production environments and never encountered gotchas like that. Unlike MySQL, those teams appear to understand the priorities of people who run production databases and they make it very clear when there are big and potentially disrupting changes and they don't make those changes obligatory, automatic, and silent as MySQL did. IMO, MySQL has its place for very specific workloads, but if you don't have deep enough knowledge of DB engines and your workload to know that yours is one of those specific workloads, you should default to PostgreSQL.
Re: Ask HN: Is PostgreSQL better than MySQL?
#30If you're going to scale up to 10s of millions of inserts a day and 100s of billions of rows, I recommend MySQL as a step on a journey towards Vitess. Otherwise, I recommend Postgres.
For anything other than a data-oriented startup, I'd pick Postgres all day long.
The troubles each database gives you do have different flavours. Postgres's query planning is mercurial and sometimes quixotic. It can be tedious forcing it to do the right thing, and it does sometimes switch strategy resulting in queries taking 100x or more longer than they used to, just because some stats changed. MySQL's query planner is more stupid, more predictable, and less likely to give you this kind of pain. OTOH you have to be more careful writing queries and you sometimes end up adding hints or encoding a query structure which locks in a query plan which might not be the best one long term. But it usually degrades gradually rather than suddenly. I'm not sure which is worse, though, because gradual degradation doesn't demand fixing.