4 ms·

Only semi-related, I've discerned a Postgres > MySQL/MariaDB sentiment on HN for the last year or so. Is that just my imagination? If not, why is that? MySQL in

by jrcii 11y ago

Only semi-related, I've discerned a Postgres > MySQL/MariaDB sentiment on HN for the last year or so. Is that just my imagination? If not, why is that? MySQL in my experience is a very powerful RDBMS, maybe I just haven't run up against its limitations.

austinhyde 11y ago
There's probably a couple reasons, but I'll summarize my experiences with them. I haven't dug into MySQL for a few years (last I really worked with it was 5.5), but out of the box, it does a lot of things that are pretty unsafe or encourage bad practices, such as truncating data when it's longer than the column size, inserting the zero-value for a NOT NULL column rather than erroring when a NULL is inserted, confusing timestamp column behavior, no DDL-level transactionality, etc. Additionally, and not necessarily a bad thing, but it has made some odd implementation and feature decisions that can be (IMHO) counter-intuitive or have a large impact, particularly with regards to how foreign keys get implemented, but also with things like not having schemas (database > tables, rather than database > schemas > tables), not having a boolean type, its datatype specification (int(1) means an integer that displays only a single digit, rather than denoting storage sizes), or the fact that every ALTER TABLE causes a complete on-disk table rewrite. PostgreSQL, on the other hand, makes every attempt to keep 100% data integrity at all times, has a lot of killer features (probably the best date/time math implementation I've ever used, typesafe operators, etc), is generally very extensible, and most importantly, is extremely predictable. True, it doesn't have the same scalability features out of the box that MySQL does, but that's getting better every release, and as mentioned elsewhere, there's plenty of adequate third-party tooling available (e.g. Slony). I think MySQL is very much so the PHP of the RDBMS world - it does a lot of silly stupid stuff - mostly for historical reasons - but in the hands of someone who knows how to use it properly, it can be an extremely useful tool. Postgres just defaults to being an extremely useful tool out of the box without needing to know all the gotchas that come with it.
- morgo 11y ago
  > I haven't dug into MySQL for a few years (last I really worked with it was 5.5), but out of the box, it does a lot of things that are pretty unsafe or encourage bad practices, such as truncating data when it's longer than the column size, inserting the zero-value for a NOT NULL column rather than erroring when a NULL is inserted, [..] This is no longer the default in MySQL 5.7.
cwyers 11y ago
The short version is, Postgres is developed under the "do it right, then make it fast" philosophy, for the most part. Early versions of MySQL were like, "ACID? Pbbbbbbbbbt." So they treated your data like it was essentially worthless and it could lose it at about any time. For a lot of applications built on hardware at the time and the needs at the time, the data really was kinda worthless and so people built stuff on it anyway. But a lot of people remember those early days when MySQL was a pretty crappy database that just happened to be fast and scale out well. MySQL has mostly caught up to Postgres in the whole "not losing data" sense, and is catching up in terms of SQL features as well (although I wish it had CTEs, among many other things). Postgres has made a lot of advances in speed and scalability too, but still lacks some of what MySQL has in terms of clustering and the like without using plugins or add-ons.

by jrcii 11y ago

austinhyde 11y ago

There's probably a couple reasons, but I'll summarize my experiences with them. I haven't dug into MySQL for a few years (last I really worked with it was 5.5), but out of the box, it does a lot of things that are pretty unsafe or encourage bad practices, such as truncating data when it's longer than the column size, inserting the zero-value for a NOT NULL column rather than erroring when a NULL is inserted, confusing timestamp column behavior, no DDL-level transactionality, etc. Additionally, and not necessarily a bad thing, but it has made some odd implementation and feature decisions that can be (IMHO) counter-intuitive or have a large impact, particularly with regards to how foreign keys get implemented, but also with things like not having schemas (database > tables, rather than database > schemas > tables), not having a boolean type, its datatype specification (int(1) means an integer that displays only a single digit, rather than denoting storage sizes), or the fact that every ALTER TABLE causes a complete on-disk table rewrite. PostgreSQL, on the other hand, makes every attempt to keep 100% data integrity at all times, has a lot of killer features (probably the best date/time math implementation I've ever used, typesafe operators, etc), is generally very extensible, and most importantly, is extremely predictable. True, it doesn't have the same scalability features out of the box that MySQL does, but that's getting better every release, and as mentioned elsewhere, there's plenty of adequate third-party tooling available (e.g. Slony). I think MySQL is very much so the PHP of the RDBMS world - it does a lot of silly stupid stuff - mostly for historical reasons - but in the hands of someone who knows how to use it properly, it can be an extremely useful tool. Postgres just defaults to being an extremely useful tool out of the box without needing to know all the gotchas that come with it.

morgo 11y ago
> I haven't dug into MySQL for a few years (last I really worked with it was 5.5), but out of the box, it does a lot of things that are pretty unsafe or encourage bad practices, such as truncating data when it's longer than the column size, inserting the zero-value for a NOT NULL column rather than erroring when a NULL is inserted, [..] This is no longer the default in MySQL 5.7.

cwyers 11y ago

The short version is, Postgres is developed under the "do it right, then make it fast" philosophy, for the most part. Early versions of MySQL were like, "ACID? Pbbbbbbbbbt." So they treated your data like it was essentially worthless and it could lose it at about any time. For a lot of applications built on hardware at the time and the needs at the time, the data really was kinda worthless and so people built stuff on it anyway. But a lot of people remember those early days when MySQL was a pretty crappy database that just happened to be fast and scale out well. MySQL has mostly caught up to Postgres in the whole "not losing data" sense, and is catching up in terms of SQL features as well (although I wish it had CTEs, among many other things). Postgres has made a lot of advances in speed and scalability too, but still lacks some of what MySQL has in terms of clustering and the like without using plugins or add-ons.