4 ms·
The design philosophy differences between Postgres and MySQL should be a case study. When I first started using SQL databases, Postgres had a reputation for be
by drblast 16y ago
The design philosophy differences between Postgres and MySQL should be a case study.
When I first started using SQL databases, Postgres had a reputation for being a fully ACID compliant database that paid a performance penalty for it, and was hard to use. They made it "correct" as a top priority.
MySQL had a reputation for being blazingly fast, even outperforming expensive commercial DB's in some areas. However, things like foreign keys were missing. There were debates in the MySQL community as to whether foreign keys were even necessary, since you could just do all that stuff in the application layer.
That seemed reasonable to me at the time and MySQL was trivially easy to install at a time when Postgres was a huge pain, so I picked MySQL. I almost immediately got bitten by the lack of foreign keys, and although I didn't know it at the time, spent most of my time coding application logic that was a workaround for MySQL's lack of views. So I switched to Postgres, it solved many of my problems and have tried to use it whenever possible.
Every release the MySQL people get closer to Postgres's features, but there's always something missing, and as of a few years ago at least, all of these features were dependent on what backend table type you were using, which became especially annoying if you didn't have control over the installation (cheap web hosting.)
The funny thing is, MySQL's performance on microbenchmarks really meant nothing in more complex applications. A few years ago I was doing something more than a simple search that involved a few joins and intersections with MySQL; the query optimizer in MySQL seemed determined to pick the slowest possible path to return a result (intersection of a small set of rows with a huge one somehow would cause MySQL to check every row of the large set for a match). It ended up being 50 times faster to implement the logic in PHP and use two queries instead of one.
So I switched to a host with Postgres access, and the identical query that took forever in MySQL was no problem for Postgres.
After a few experiences like that, you begin to see the virtue in doing things right the first time and optimizing later. You also see the virtue in using a database system that takes the onus off of the application developer to implement logic that should be in the DB.
- deleted 16y ago[deleted]
- SkyMarshal 16y agoAfter a few experiences like that, you begin to see the virtue in doing things right the first time and optimizing later. You know what they say about premature optimization being the mother of all fuckups...
- Lewisham 16y agoMySQLs lack of views for a long time was just killer. I'm truly surprised MySQL has done as well as it has for as long as it has done. I thought that it's popularity would have tailed off when people realized just how much it was missing (I felt a little bit unwell when I found it didn't have views), but it seems that it just hit critical mass.
- derefr 16y agoI have a feeling that the basic "need" for ORMs was originally driven purely by the unwieldiness of querying a MySQL database. When everything is in the application layer, you have to write abstractions on top of it to keep it comprehensible. When you have triggers and views, on the other hand, you can just "ask the right questions" of the database, and get your information; the SQL becomes self-documenting, rather than a tangled mess.
- pvg 16y agohttp://en.wikipedia.org/wiki/Object-relational_impedance_mismatch http://en.wikipedia.org/wiki/Object-relational_impedance_mis... Whatever their merits, ORM layers and the problems they try to address existed long before and quite independently of the particulars and limitations MySQL.
- mmt 16y agoI've always felt that begs the question, by assuming that a mismatch is somehow fundamental, rather than an implementation artifact. Just as real RDBMSes don't strictly adhere to the formal math relational model, object-oriented languages vary in their, for lack of a better word, orientation. Looked at another way, what about a relational-object model instead? Making a useful one for MySQL doesn't seem as plausible, given its chronic feature absence.
- gaius 16y agoJust as the "need" for sharding is driven by MySQL's lack of a sensible way to do big joins.
- 16y ago
- chrisbolt 16y agoAnother thing that makes Postgres a huge pain compared to MySQL: upgrading. Any major version upgrade requires a full dump and restore of all data.
- rbranson 16y agohttp://developer.postgresql.org/pgdocs/postgres/pgupgrade.html http://developer.postgresql.org/pgdocs/postgres/pgupgrade.ht...
- silentbicycle 16y agoYeah, but where Postgres would nag you to dump and restore, YourSQL respects your time and just truncates your data.
- gaius 16y agoYeah, I remember this. Every feature MySQL was lacking, they had "good reasons" that you didn't need it anyway. Even transactions, if you needed to roll back, do it in the application. Atomic update across several rows or several tables, why would anyone need that? Foreign keys? Too slow. Triggers? Do it in the application. Stored procs? Do it in the application. And so on and so on. If you ignore the hype ("OMG! Slashdot uses it!!") MySQL occupies a dead ground between SQLite and Postgres... There's just no reason for it to exist.
- StavrosK 16y agoOn the contrary, it's great for sabotaging enemies by suggesting they use it.