5 ms·

Maybe this is a cynical take - but we used https://pgloader.io/ https://pgloader.io/ a few years ago to migrate to Postgres, and have never been happier. MySQL

by tapoxi 5y ago

Maybe this is a cynical take - but we used https://pgloader.io/ https://pgloader.io/ a few years ago to migrate to Postgres, and have never been happier. MySQL has a lot of stupid decisions like this.

dijit 5y ago
I'm a big posgresql fan, but I'd maybe argue that you're making tradeoffs. PG has weird behaviour sometimes too. I don't think it's always smart to just change out your data layer. But, if you're starting a new project, I do think PG is one of the better options and tends to follow the principle of least surprise. (hence: big fan)
- leetrout 5y ago
  As a huge Postgres fan working at a shop using MySQL I'll point out the first thing I saw MySQL does that Postgres doesn't: Index hints. If you aren't hitting an index in Postgres you have to dig in to table stats and figure out what is wrong but MySQL gives you more control. However, I would still rather work with Postgres AND have to juggle a connection pooler than deal with MySQL. Transactions on DDL are _great_ and the ability to use foreign keys across partitioned tables is how it should be.
  - spookthesunset 5y ago
    But at least Postgres has much better tools for learning about the query plans it might use. The Postgres output of “EXPLAIN” is much much better than MySQL. It’s a shame that almost every job I worked at uses MySQL and not Postgres. But that could be because those companies all got their start like a decade or more ago when Postgres was not as well known.
  - pzduniak 5y ago
    > As a huge Postgres fan working at a shop using MySQL I'll point out the first thing I saw MySQL does that Postgres doesn't: > Index hints. Eh, it's an extension. One you shouldn't use, but it's there.
    - leetrout 5y ago
      I didn't know this!
  - samcrawford 5y ago
    > I would still rather work with Postgres AND have to juggle a connection pooler Is this comment relating to the overhead of idle connections, which has historically necessitated the use of a pooler in front of PG? If so, I believe this is resolved in postgres 14 https://pganalyze.com/blog/postgres-14-performance-monitoring https://pganalyze.com/blog/postgres-14-performance-monitorin...
- hn_throwaway_99 5y ago
  > PG has weird behaviour sometimes too. Perhaps, but I'd argue that the "weird" behavior of postgres just tends to be clearly thought out design decisions that they made for a valid reason that may cause you some pain with how you use it (e.g. their process-per-connection model). MySQL's "weird" behavior, on the other hand, just tends to be completely invalid footguns like this. Despite what some people are arguing in this thread, a 3-byte version of UTF-8 was never in any spec anywhere and was an invalid shortcut from day 1.
  - ipaddr 5y ago
    Oracle, Postgres, MySql, mSql, sqlite all make weird behaviour in different situation. You pick your poison. I hated how postgres forced you to create a system user to connect I wonder if it still requires this.
leftnode 5y ago
I love, love, love Postgres! The one feature MySQL has that Postgres doesn't is the ability to add a new column in an arbitrary position in the table. I like my tables to have a common "layout" and I would love to have this feature in Postgres.
- nkozyra 5y ago
  This is annoying but it's not a huge hassle to duplicate/copy/rename, assuming your data is small and not sharded in some unique way
  - Spivak 5y ago
    If your data is tiny then within reason nothing matters, any popular database will be just fine. Where you care is precisely when your data gets big enough to be unwieldy.
  - leftnode 5y ago
    That's generally what I end up doing. It's not ideal, but it at least allows me to order the columns how I want.
- teaearlgraycold 5y ago
  This seems like something that could be done client side instead. But maybe not an option for psql CLI
  - leftnode 5y ago
    It is, and something an ORM can manage, but I spend a LOT of time in the psql CLI.
- srcreigh 5y ago
  MySQL also lets you control the layout of your table on disk via BTree primary storage and composite primary keys.
andrewmcwatters 5y ago
People say this all the time but never elaborate on what they are nor link to any sources that help educate people what they are, so I just assume that when people say this, they don't know what they're talking about and are just parroting.
gjs278 5y ago
cakoose 5y ago
Specifically about UTF-8 in Postgres: "PostgreSQL collation is a massive footgun" https://gist.github.com/rraval/ef4e4bdc63e68fe3e83c9f98f56af7a4 https://gist.github.com/rraval/ef4e4bdc63e68fe3e83c9f98f56af... Postgres defaults to the system's collation for UTF-8, which caught me by surprise. Now I'm stuck with a production DB where text prefix queries, which should be a perfect use of a btree, are a full table scan. Probably gonna have to take some maintenance downtime to fix the situation :-\ And more generally about operating Postgres in production: https://blog.nelhage.com/post/some-opinionated-sql-takes/ https://blog.nelhage.com/post/some-opinionated-sql-takes/ As for Postgres, I have enormous respect for it and its engineering and capabilities, but, for me, it’s just too damn operationally scary. In my experience it’s much worse than MySQL for operational footguns and performance cliffs, where using it slightly wrong can utterly tank your performance or availability. In addition, because MySQL is, in my experience, more widely deployed, it’s easier to find and hire engineers with experience deploying and operating it. Postgres is a fine choice, especially if you already have expertise using it on your team, but I’ve personally been burned too many times.
- Arch-TK 5y ago
  What do you use instead?
  - Spivak 5y ago
    MySQL or MariaDB/PerconaDB most likely. For all the footguns people are bringing up in this thread it's an extremely well trodden path where anything you could ever imagine doing with a DB someone has already done a hundred times with MySQL and documented it.
  - cakoose 5y ago
    Since I didn't specify the default collation when creating my database, Postgres inferred it from my system, so I ended up with "en_US.UTF-8". The "C" collation is probably what most developers expect. The only supported way to set the database's default collation is at creation time, so I'd need to create a new database, copy the data over, and switch my application over. (There appears to be some Postgres voodoo to change the default collation for an existing database, but that's too risky for me.) Until I get that done, I need to explicitly specify the collation for all new text fields, e.g. CREATE TABLE t ( f1 text NOT NULL COLLATE "C" ) https://simply.name/pg-lc-collate.html https://simply.name/pg-lc-collate.html
JangoSteve 5y ago
I also helped with a project once that used pgloader to migration an old MySQL db to Postgres, and I think this article may actually explain one of the issues we found. We had a UTF-8-configured table in MySQL (we also discovered in the project, that MySQL can have encodings set differently per-table, while Postgres sets the encoding for the entire database) with UTF-8 data, and when we migrated it into a UTF-8-configured Postgres database, some of the UTF-8 characters were silently corrupted. They were UTF-8 characters in both databases, so the corruption didn't raise any errors, but they were different characters, so when the data was read back out by the application, the text was different. We only caught this, because thankfully, we had written a manual checksum script, which looped through every table, read out all values from each row into the application, and hashed the results, then compared between when the app was connected to the source MySQL database vs the destination Postgres database. We ended up having to massage and fix those silently-corrupted characters.

by tapoxi 5y ago

dijit 5y ago

I'm a big posgresql fan, but I'd maybe argue that you're making tradeoffs. PG has weird behaviour sometimes too. I don't think it's always smart to just change out your data layer. But, if you're starting a new project, I do think PG is one of the better options and tends to follow the principle of least surprise. (hence: big fan)

leetrout 5y ago
As a huge Postgres fan working at a shop using MySQL I'll point out the first thing I saw MySQL does that Postgres doesn't: Index hints. If you aren't hitting an index in Postgres you have to dig in to table stats and figure out what is wrong but MySQL gives you more control. However, I would still rather work with Postgres AND have to juggle a connection pooler than deal with MySQL. Transactions on DDL are _great_ and the ability to use foreign keys across partitioned tables is how it should be.
- spookthesunset 5y ago
  But at least Postgres has much better tools for learning about the query plans it might use. The Postgres output of “EXPLAIN” is much much better than MySQL. It’s a shame that almost every job I worked at uses MySQL and not Postgres. But that could be because those companies all got their start like a decade or more ago when Postgres was not as well known.
- pzduniak 5y ago
  > As a huge Postgres fan working at a shop using MySQL I'll point out the first thing I saw MySQL does that Postgres doesn't: > Index hints. Eh, it's an extension. One you shouldn't use, but it's there.
  - leetrout 5y ago
    I didn't know this!
- samcrawford 5y ago
  > I would still rather work with Postgres AND have to juggle a connection pooler Is this comment relating to the overhead of idle connections, which has historically necessitated the use of a pooler in front of PG? If so, I believe this is resolved in postgres 14 https://pganalyze.com/blog/postgres-14-performance-monitoring https://pganalyze.com/blog/postgres-14-performance-monitorin...
hn_throwaway_99 5y ago
> PG has weird behaviour sometimes too. Perhaps, but I'd argue that the "weird" behavior of postgres just tends to be clearly thought out design decisions that they made for a valid reason that may cause you some pain with how you use it (e.g. their process-per-connection model). MySQL's "weird" behavior, on the other hand, just tends to be completely invalid footguns like this. Despite what some people are arguing in this thread, a 3-byte version of UTF-8 was never in any spec anywhere and was an invalid shortcut from day 1.
- ipaddr 5y ago
  Oracle, Postgres, MySql, mSql, sqlite all make weird behaviour in different situation. You pick your poison. I hated how postgres forced you to create a system user to connect I wonder if it still requires this.

leftnode 5y ago

I love, love, love Postgres! The one feature MySQL has that Postgres doesn't is the ability to add a new column in an arbitrary position in the table. I like my tables to have a common "layout" and I would love to have this feature in Postgres.

nkozyra 5y ago
This is annoying but it's not a huge hassle to duplicate/copy/rename, assuming your data is small and not sharded in some unique way
- Spivak 5y ago
  If your data is tiny then within reason nothing matters, any popular database will be just fine. Where you care is precisely when your data gets big enough to be unwieldy.
- leftnode 5y ago
  That's generally what I end up doing. It's not ideal, but it at least allows me to order the columns how I want.
teaearlgraycold 5y ago
This seems like something that could be done client side instead. But maybe not an option for psql CLI
- leftnode 5y ago
  It is, and something an ORM can manage, but I spend a LOT of time in the psql CLI.
srcreigh 5y ago
MySQL also lets you control the layout of your table on disk via BTree primary storage and composite primary keys.

andrewmcwatters 5y ago

People say this all the time but never elaborate on what they are nor link to any sources that help educate people what they are, so I just assume that when people say this, they don't know what they're talking about and are just parroting.

gjs278 5y ago

cakoose 5y ago

Specifically about UTF-8 in Postgres: "PostgreSQL collation is a massive footgun" https://gist.github.com/rraval/ef4e4bdc63e68fe3e83c9f98f56af7a4 https://gist.github.com/rraval/ef4e4bdc63e68fe3e83c9f98f56af... Postgres defaults to the system's collation for UTF-8, which caught me by surprise. Now I'm stuck with a production DB where text prefix queries, which should be a perfect use of a btree, are a full table scan. Probably gonna have to take some maintenance downtime to fix the situation :-\ And more generally about operating Postgres in production: https://blog.nelhage.com/post/some-opinionated-sql-takes/ https://blog.nelhage.com/post/some-opinionated-sql-takes/ As for Postgres, I have enormous respect for it and its engineering and capabilities, but, for me, it’s just too damn operationally scary. In my experience it’s much worse than MySQL for operational footguns and performance cliffs, where using it slightly wrong can utterly tank your performance or availability. In addition, because MySQL is, in my experience, more widely deployed, it’s easier to find and hire engineers with experience deploying and operating it. Postgres is a fine choice, especially if you already have expertise using it on your team, but I’ve personally been burned too many times.

Arch-TK 5y ago
What do you use instead?
- Spivak 5y ago
  MySQL or MariaDB/PerconaDB most likely. For all the footguns people are bringing up in this thread it's an extremely well trodden path where anything you could ever imagine doing with a DB someone has already done a hundred times with MySQL and documented it.
- cakoose 5y ago
  Since I didn't specify the default collation when creating my database, Postgres inferred it from my system, so I ended up with "en_US.UTF-8". The "C" collation is probably what most developers expect. The only supported way to set the database's default collation is at creation time, so I'd need to create a new database, copy the data over, and switch my application over. (There appears to be some Postgres voodoo to change the default collation for an existing database, but that's too risky for me.) Until I get that done, I need to explicitly specify the collation for all new text fields, e.g. CREATE TABLE t ( f1 text NOT NULL COLLATE "C" ) https://simply.name/pg-lc-collate.html https://simply.name/pg-lc-collate.html

JangoSteve 5y ago

I also helped with a project once that used pgloader to migration an old MySQL db to Postgres, and I think this article may actually explain one of the issues we found. We had a UTF-8-configured table in MySQL (we also discovered in the project, that MySQL can have encodings set differently per-table, while Postgres sets the encoding for the entire database) with UTF-8 data, and when we migrated it into a UTF-8-configured Postgres database, some of the UTF-8 characters were silently corrupted. They were UTF-8 characters in both databases, so the corruption didn't raise any errors, but they were different characters, so when the data was read back out by the application, the text was different. We only caught this, because thankfully, we had written a manual checksum script, which looped through every table, read out all values from each row into the application, and hashed the results, then compared between when the app was connected to the source MySQL database vs the destination Postgres database. We ended up having to massage and fix those silently-corrupted characters.