4 ms·

Specifically about UTF-8 in Postgres: "PostgreSQL collation is a massive footgun" https://gist.github.com/rraval/ef4e4bdc63e68fe3e83c9f98f56af7a4 https://gist.g

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
- cakoose 5y ago
  Oh whoops, my previous response was about which collation I (want to) use. Were you asking which DB I use? The answer is Postgres. I have some previous experience with MySQL and there are advantages and disadvantages. The biggest advantage so far is partial indexes. But since I don't have as much experience with Postgres, there's still the occasional surprise, e.g. index names not being scoped to the table, this collation thing. And from what I've read online, I should probably set up some monitoring for vacuuming issues. The article about Postgres operational footguns recommends outsourcing those issues by using a managed Postgres service (e.g. AWS RDS or Google Cloud SQL), and that's what I do.
seanw444 5y ago
I think the open-source nature is a major factor in the reason it's chosen so much. That's why I roll it.
jtc331 5y ago
What else would they reasonably default to? Are you proposing requiring collations to always be specified (a breaking change from the SQL spec)?
- cakoose 5y ago
  I think the behavior of the "C" collation is what most software developers expect. Plus, there is an advantage to the behavior being the same everywhere, rather than dependent on the system/environment settings. But those preferences may be specific to my situation -- a software developer running an HA web application. * If you're not a software developer, you might be surprised by the "C" collation, which puts "Z" before "a". * If you're not running an HA web application, then it's easy to change the collation later -- just dump/restore the DB.
  - jtc331 5y ago
    If you have any non-ASCII characters wouldn’t the C collation be very surprising? I’m having a hard time seeing how the C collation is a sensible default for UTF encoded text. If you want to limit to ASCII the why are you using UTF?
    - cakoose 5y ago
      The C collation applied to UTF-8 just orders everything by Unicode codepoint.

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
cakoose 5y ago
Oh whoops, my previous response was about which collation I (want to) use. Were you asking which DB I use? The answer is Postgres. I have some previous experience with MySQL and there are advantages and disadvantages. The biggest advantage so far is partial indexes. But since I don't have as much experience with Postgres, there's still the occasional surprise, e.g. index names not being scoped to the table, this collation thing. And from what I've read online, I should probably set up some monitoring for vacuuming issues. The article about Postgres operational footguns recommends outsourcing those issues by using a managed Postgres service (e.g. AWS RDS or Google Cloud SQL), and that's what I do.

seanw444 5y ago

I think the open-source nature is a major factor in the reason it's chosen so much. That's why I roll it.

jtc331 5y ago

What else would they reasonably default to? Are you proposing requiring collations to always be specified (a breaking change from the SQL spec)?

cakoose 5y ago
I think the behavior of the "C" collation is what most software developers expect. Plus, there is an advantage to the behavior being the same everywhere, rather than dependent on the system/environment settings. But those preferences may be specific to my situation -- a software developer running an HA web application. * If you're not a software developer, you might be surprised by the "C" collation, which puts "Z" before "a". * If you're not running an HA web application, then it's easy to change the collation later -- just dump/restore the DB.
- jtc331 5y ago
  If you have any non-ASCII characters wouldn’t the C collation be very surprising? I’m having a hard time seeing how the C collation is a sensible default for UTF encoded text. If you want to limit to ASCII the why are you using UTF?
  - cakoose 5y ago
    The C collation applied to UTF-8 just orders everything by Unicode codepoint.