3 ms·

What made you switch to Postgres?

by gmiller123456 5y ago

deathanatos 5y ago
https://grimoire.ca/mysql/choose-something-else/ https://grimoire.ca/mysql/choose-something-else/
- derekperkins 5y ago
  As with most similar rants against the earlier days of MySQL, the majority of these points are based on old versions and don't reflect how things have been for almost a decade.
  - deathanatos 5y ago
    The question was what made me switch, which was all the crap MySQL does to a dev. That blog post is the best that I know of that captures the fractal of bad design that is MySQL. While, yes, it matches my understanding that some of those points are no longer valid, it doesn't match my understanding that most of the points are no longer valid. (E.g., just a few years ago, when I last used MySQL, I seem to remember that it still struggled w/ UTF-8, still struggled with the definition of what a "key" is, and still struggled with functional dependencies.)
    - derekperkins 5y ago
      Postgres is a great choice too, and for most intents and purposes, Postgres or MySQL is the correct default choice for a database, so I'm not trying to persuade you to switch back. Just clarifying that almost none of the points in that article are still valid, including UTF-8 handling. The author themselves put a disclaimer at the top of the article stating as much. Not quite sure what you mean by "definition of a key" or "struggled with functional dependencies", so I can't speak to those directly.
      - deathanatos 5y ago
        E.g., the following used to be a statement in MySQL, at the time I used it; I presume it still is: CREATE KEY … It is the primary form of this alias: CREATE INDEX which is what the command does (it creates an index; it does not "create" a "key".) But since the form of the command with the correct words is itself an alias, the wrong form gets output any time MySQL self-describes something, generating endless confusion from people thinking a set of columns are a key, when they actually may or may not be. (They might also be a key, but index does not imply key.) > struggled with functional dependencies E.g., in GROUP BYs: SELECT a FROM table GROUP BY b; where a is not a functional dependency of b. (The statement is thus nonsense.) MySQL will permit this statement to execute, and generate undefined results for column `a` in the output. In the day I used it, UTF-8 was not the default (the default was latin1, IIRC). Even better, the "utf8" encoding wasn't UTF-8. It was like UTF-8 except where it wasn't. Generated endless numbers of "ah crap, this table/DB isn't UTF-8 and will need a migration." Simple date math in SELECTS would result in idiocy. (The output of SELECT date_a - date_b was something like "stringify the dates, remove the hyphens, cast to int, subtract" — yes, that sounds crazy, but yet that was a bug I had to work out once. There's a separate DATEDIFF function or something to do the right thing, but the point is that the naïve thing neither errors nor does anything sensible.) Schema changes escaped from transactions.
        derekperkins 5y ago
        > it creates an index; it does not "create" a "key" I'm still not sure what you mean. By key, do you mean a unique key? With some quick searching, I'm not finding any difference in the Postgres docs. > MySQL will permit this statement to execute That was disabled by default in 5.7, 6 years ago. I totally agree that it should have been turned on by default earlier, but isn't a valid criticism of modern MySQL. > Simple date math in SELECTS would result in idiocy I've never tried that before, and it looks like that hasn't changed. No idea why that doesn't throw an error. select date('2021-11-01') - date('2021-10-01'), datediff(date('2021-11-01'), date('2021-10-01')) results in: 100, 31 > Schema changes escaped from transactions Since 8.0 (April 2018), schema changes are transactional, but you can't include DML changes in the same transaction as a DDL. I haven't found that to be a dealbreaker, but would certainly be nice to have.

What made you switch to Postgres?

deathanatos 5y ago

https://grimoire.ca/mysql/choose-something-else/ https://grimoire.ca/mysql/choose-something-else/

derekperkins 5y ago
As with most similar rants against the earlier days of MySQL, the majority of these points are based on old versions and don't reflect how things have been for almost a decade.
- deathanatos 5y ago
  The question was what made me switch, which was all the crap MySQL does to a dev. That blog post is the best that I know of that captures the fractal of bad design that is MySQL. While, yes, it matches my understanding that some of those points are no longer valid, it doesn't match my understanding that most of the points are no longer valid. (E.g., just a few years ago, when I last used MySQL, I seem to remember that it still struggled w/ UTF-8, still struggled with the definition of what a "key" is, and still struggled with functional dependencies.)
  - derekperkins 5y ago
    Postgres is a great choice too, and for most intents and purposes, Postgres or MySQL is the correct default choice for a database, so I'm not trying to persuade you to switch back. Just clarifying that almost none of the points in that article are still valid, including UTF-8 handling. The author themselves put a disclaimer at the top of the article stating as much. Not quite sure what you mean by "definition of a key" or "struggled with functional dependencies", so I can't speak to those directly.
    - deathanatos 5y ago
      E.g., the following used to be a statement in MySQL, at the time I used it; I presume it still is: CREATE KEY … It is the primary form of this alias: CREATE INDEX which is what the command does (it creates an index; it does not "create" a "key".) But since the form of the command with the correct words is itself an alias, the wrong form gets output any time MySQL self-describes something, generating endless confusion from people thinking a set of columns are a key, when they actually may or may not be. (They might also be a key, but index does not imply key.) > struggled with functional dependencies E.g., in GROUP BYs: SELECT a FROM table GROUP BY b; where a is not a functional dependency of b. (The statement is thus nonsense.) MySQL will permit this statement to execute, and generate undefined results for column `a` in the output. In the day I used it, UTF-8 was not the default (the default was latin1, IIRC). Even better, the "utf8" encoding wasn't UTF-8. It was like UTF-8 except where it wasn't. Generated endless numbers of "ah crap, this table/DB isn't UTF-8 and will need a migration." Simple date math in SELECTS would result in idiocy. (The output of SELECT date_a - date_b was something like "stringify the dates, remove the hyphens, cast to int, subtract" — yes, that sounds crazy, but yet that was a bug I had to work out once. There's a separate DATEDIFF function or something to do the right thing, but the point is that the naïve thing neither errors nor does anything sensible.) Schema changes escaped from transactions.
      - derekperkins 5y ago
        > it creates an index; it does not "create" a "key" I'm still not sure what you mean. By key, do you mean a unique key? With some quick searching, I'm not finding any difference in the Postgres docs. > MySQL will permit this statement to execute That was disabled by default in 5.7, 6 years ago. I totally agree that it should have been turned on by default earlier, but isn't a valid criticism of modern MySQL. > Simple date math in SELECTS would result in idiocy I've never tried that before, and it looks like that hasn't changed. No idea why that doesn't throw an error. select date('2021-11-01') - date('2021-10-01'), datediff(date('2021-11-01'), date('2021-10-01')) results in: 100, 31 > Schema changes escaped from transactions Since 8.0 (April 2018), schema changes are transactional, but you can't include DML changes in the same transaction as a DDL. I haven't found that to be a dealbreaker, but would certainly be nice to have.