4 ms·
The benefits are better defaults in terms of data reliability. Hard to say if migration is worth it to you without a lot more details, but I'd definitely recom
by mixmastamyk 8y ago
The benefits are better defaults in terms of data reliability. Hard to say if migration is worth it to you without a lot more details, but I'd definitely recommend trying it in a new project.
- stilldavid 8y agoWe just migrated a medium size project using pgloader with great success and minimal headaches, which seems like a big step up from the last time I had to migrate a project. Highly recommended, and it might be easier than you think!
- barrkel 8y agoMySQL defaults to InnoDB. Is there a different metric for reliability you had in mind? Where I work, we chose MySQL back in 2012 due to production quality async replication. I think (but am never sure) that that is now good in Postgres land. PG has a lot of SQL features I'd love to use and can't. OTOH MySQL's query planner is predictably dumb, which means I can write queries and have good idea about how well (or not) they'll execute.
- evanelias 8y agoFrankly, data reliability concerns with modern MySQL / InnoDB are very outdated FUD. Many of the largest tech companies rely on MySQL as their primary data store. They would not do so if it was unreliable with persistence. There are many valid reasons to choose Postgres over MySQL, or vice versa -- they have different strengths and weaknesses. But there are no major differences regarding data reliability today, nor have there been for many years now.
- mixmastamyk 8y agoTo be clearer, it's about the defaults regarding strictness (of the acceptance) of data that help to avoid trouble up front, not that mysql randomly corrupts/deletes it later.
- evanelias 8y agoSure, and MySQL 5.7, released 4 years ago, fixed those bad defaults. It isn't fair to compare Postgres-of-today to MySQL-of-over-4-years-ago.
- mixmastamyk 8y agoOk, looks like that version is in LTS dists now. Perhaps to poster's legacy apps are taking advantage of them. There were other deficiencies mentioned in Klepmann's book on Designing Data apps, but I don't remember the specifics now.
- evanelias 8y agoEven if OP is using 5.6 or previous, strict mode has been available as an option for over 15 years, and can be enabled dynamically (no restart required). I simply don't see any valid argument for avoiding MySQL due to "data reliability" concerns in 2019. > There were other minor deficiencies mentioned in Klepmann's book on Designing Data apps, but I don't remember the specifics now. Well, I can't really respond to non-specific points from a book I haven't read. I'm happy to respond to any specifics re: data reliability concerns, if you want to cite them. FWIW, I have quite extensive expertise on the subject of massive-scale MySQL (16 years of MySQL use; led development of Facebook's internal DBaaS; rebuilt most of Tumblr's backend during its hockey-stick growth period).
- mixmastamyk 8y agoIt takes years to build up trust, seconds to lose it. Obsolete documentation to disappear. It's an uphill battle. In the meantime there's postgres, and it's free.
- pygy_ 8y agoI haven't kept up, but I remember having constrains that were accepted by the parser, but ignored by the engine (using InnoDB, I don't remember what specific constrain was concerned). Is it still the case?
- evanelias 8y agoIt was the case until very recently. Check constraints are now supported in MySQL 8: https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html https://dev.mysql.com/doc/refman/8.0/en/create-table-check-c... I believe MariaDB added support for them a couple years earlier, but am not certain. More broadly, I would agree it's a very painful "gotcha" to have aspects of CREATE TABLE be accepted by the parser but ignored by the engine. However, in MySQL's defense, theoretically this type of flexibility does allow third-party storage engines to support these features if the engine's developer wishes. Ideally, the engine should throw an error if you try using a feature it does not support, but in a few specific cases it does not (at least for InnoDB). This can be very frustrating, for sure. But at least it's documented. And no database is perfect; they all have similarly-frustrating inconsistencies somewhere.