Yeah, I wish this article had a better title since it was a good read and clearly written by someone with experience. The article (+ the medium.com domain) made me think it was going to be clickbait though.
I thought it was going to be like the titular list in “10 Things I Hate About You” and the final point would be something like “I hate that I love you so much” or something like that.
This. I really wish that had been at the top, because some of these are pretty deep dives/issues at scale, and many people may not get to the end (especially if there’s an Oracle salesperson calling frequently).
That was far more interesting than I’d have hoped. Especially because it was more about operating this at scale. For my non-petabyte-scale stuff I’ve always felt like mysql is easier to use as developer. The permissions system for example is confusing. But I was also bitten by things like using utf8mb4 over utf8 in mysql. (and I always recommend mariadb)
I’m a little stunned to hear anyone say they prefer MySQL over PostgreSQL as a developer. A couple things I’ve really disliked about MySQL over the years:
silent coercion of values (where PG, in contrast, would noisily raise a fatal error to help you as a developer) – it makes it a lot harder to debug things when what you thought you stored in a column is not what you get out of it
the MySQL CLI had significantly poorer UX and feature set than psql. My favourite pet peeve (of MySQL): Pressing Ctrl-C completely exited the CLI (by default), whereas, in psql, it just cancels the current command or query.
After spending three years trying to make MySQL 5.6.32 on Galera Cluster work well, being bitten by A5A SQL anomalies, coercion of data type sillyness et al, I’ve found Postgres to be a breath of fresh air and I never want to go back to the insane world of MySQL.
Postgres has it’s warts, incredible warts, but when they’re fixed, they’re usually fixed comprehensively. I’m interested in logical replication for zero downtime database upgrades, but being the only girl on the team who manages the backend and the database mostly by herself, I’m less than inclined to hunt that white whale.
the MySQL CLI had significantly poorer UX and feature set than psql
Hmm I’ve always felt the opposite way. The psql client has lots of single-letter backslash commands to remember to inspect your database. What’s the difference the various combinations of \d, \dS, \dS+, \da, \daS, \dC+, and \ds? It’s all very confusing, and for the same reason we don’t use single-letter variables. I find MySQL’s usage of Show tables, show databases, describe X to be a lot easier to use.
Yeah this is also bugging me. Sure “show databases” is long and something like “db” would be nice, but I know it and (for once at least) it’s concistent to “show tables” etc.
I grant you that, but \? and \h are 3 keystrokes away, and the ones I use most frequently I’ve memorized by now. But I just couldn’t stand the ^C behaviour, because I use that in pretty much every other shell interface of any kind without it blowing up on me. MySQL was the one, glaring exception.
Totally agree, this is almost exactly my situation too. I had always used mysql and knew it pretty well, but got burned a few times trying to deal with utf8, then got hit with a few huge table structure changes (something I think has improved since). Ended up moving to Postgres for most new stuff and have been pretty happy, but I do miss Mysql once and a while.
Every time an on-disk database page (4KB) needs to be modified by a write operation, even just a single byte, a copy of the entire page, edited with the requested changes, is written to the write-ahead log (WAL). Physical streaming replication leverages this existing WAL infrastructure as a log of changes it streams to replicas.
First, the PostgreSQL page size is 8KB and has been that since the beginning.
The remaining part. According to PostgreSQL documentation[1] (on full page writes which decides if those are made), a copy of the entire page is only written fully to the WAL after the first modification of that page since the last checkpoint. Subsequent modifications will not result in full page writes to the WAL. So if you update a counter 3 times in sequence you won’t get 3*8KB written to the WAL, instead you would get a single page dump and the remaining two would only log the row-level change which is much smaller[2]. This is further reduced by WAL compression[3] (reducing the segment usage) and by increasing the checkpointing interval which would reduce the amount of copies happening[4].
This irked me because it sounded like whatever you touch produces an 8KB copy of data and it seems to not be the case.
Although PostgreSQL is my go to for relational databases I’d echo this especially #4 MVCC Garbage Collection. Additionally I find the permission system labyrinthine and dislike how a basic “select count(*) from table” takes non-trivial time compared to other databases.
Very nice to read. I’m quite the Postgres fanboy (as a MySQL refugee, Postgres feels like an oasis of sanity), but it’s good to be aware of pitfalls. Personally, the only annoyance I’d ran into was the limit on identifiers (they can be only 63 bytes), which is usually easily worked around.
The corruption-spreading replication sounds especially nasty, since we’ve been considering using replication as a more light-weight form of backup than an half-hourly pg_dump, which can be heavy on the server.
I wonder if having a logical replication in place would’ve be a solution to this particular scenario, as the text only implies the problem exists with binary streaming. The replica then could be used for creating reliable backup’s if the load on the main server is a problem. Also there’s always barman or pgbackrest for implementing complex backup & restore policies with point-in-time recovery, which might be a better solution than only using pg_dump.
At scale, every design decision starts to come with tradeoffs.
My perspective to others is this: Demonstrate why Postgres will not work, using algorithmic analysis, knowledge of Postgres, and the estimated data size and TPS read/write mix. Things such as, a shopping cart system for 100 million concurrent users, may be the wrong problem for Postgres.
Until that has been done, please use Postgres.
(cited example was what led to Dynamo’s invention)
I don’t know if it’s like this in other databases or not, but something that tripped me up multiple times over the years with pg was using “timestamp” as opposed to “timestamp with time zone”. The timezone information gets thrown away instead of converted to UTC so applications need to actively fiddle with it.
Is it strange that I assumed “timestamp” being “timestamp without time zone” meant that postgres didn’t store timezones because it’s always in UTC and clients would automatically convert back and forth?
Don’t forget to read the ‘All That Said…’ at the end. It’s likely the most important advice in this whole list.
Yeah, I wish this article had a better title since it was a good read and clearly written by someone with experience. The article (+ the medium.com domain) made me think it was going to be clickbait though.
I thought it was going to be like the titular list in “10 Things I Hate About You” and the final point would be something like “I hate that I love you so much” or something like that.
I guess it’s pretty close, though.
This. I really wish that had been at the top, because some of these are pretty deep dives/issues at scale, and many people may not get to the end (especially if there’s an Oracle salesperson calling frequently).
That was far more interesting than I’d have hoped. Especially because it was more about operating this at scale. For my non-petabyte-scale stuff I’ve always felt like mysql is easier to use as developer. The permissions system for example is confusing. But I was also bitten by things like using utf8mb4 over utf8 in mysql. (and I always recommend mariadb)
I’m a little stunned to hear anyone say they prefer MySQL over PostgreSQL as a developer. A couple things I’ve really disliked about MySQL over the years:
psql. My favourite pet peeve (of MySQL): Pressing Ctrl-C completely exited the CLI (by default), whereas, inpsql, it just cancels the current command or query.After spending three years trying to make MySQL 5.6.32 on Galera Cluster work well, being bitten by A5A SQL anomalies, coercion of data type sillyness et al, I’ve found Postgres to be a breath of fresh air and I never want to go back to the insane world of MySQL.
Postgres has it’s warts, incredible warts, but when they’re fixed, they’re usually fixed comprehensively. I’m interested in logical replication for zero downtime database upgrades, but being the only girl on the team who manages the backend and the database mostly by herself, I’m less than inclined to hunt that white whale.
Hmm I’ve always felt the opposite way. The
psqlclient has lots of single-letter backslash commands to remember to inspect your database. What’s the difference the various combinations of\d,\dS,\dS+,\da,\daS,\dC+, and\ds? It’s all very confusing, and for the same reason we don’t use single-letter variables. I find MySQL’s usage ofShow tables,show databases,describe Xto be a lot easier to use.Yeah this is also bugging me. Sure “show databases” is long and something like “db” would be nice, but I know it and (for once at least) it’s concistent to “show tables” etc.
I grant you that, but
\?and\hare 3 keystrokes away, and the ones I use most frequently I’ve memorized by now. But I just couldn’t stand the^Cbehaviour, because I use that in pretty much every other shell interface of any kind without it blowing up on me. MySQL was the one, glaring exception.Totally agree, this is almost exactly my situation too. I had always used mysql and knew it pretty well, but got burned a few times trying to deal with utf8, then got hit with a few huge table structure changes (something I think has improved since). Ended up moving to Postgres for most new stuff and have been pretty happy, but I do miss Mysql once and a while.
First, the PostgreSQL page size is 8KB and has been that since the beginning.
The remaining part. According to PostgreSQL documentation[1] (on full page writes which decides if those are made), a copy of the entire page is only written fully to the WAL after the first modification of that page since the last checkpoint. Subsequent modifications will not result in full page writes to the WAL. So if you update a counter 3 times in sequence you won’t get 3*8KB written to the WAL, instead you would get a single page dump and the remaining two would only log the row-level change which is much smaller[2]. This is further reduced by WAL compression[3] (reducing the segment usage) and by increasing the checkpointing interval which would reduce the amount of copies happening[4].
This irked me because it sounded like whatever you touch produces an 8KB copy of data and it seems to not be the case.
[1] - https://www.postgresql.org/docs/11/runtime-config-wal.html#GUC-FULL-PAGE-WRITES
[2] - http://www.interdb.jp/pg/pgsql09.html
[3] - https://www.postgresql.org/docs/11/runtime-config-wal.html#GUC-WAL-COMPRESSION
[4] - https://www.postgresql.org/docs/11/runtime-config-wal.html#GUC-CHECKPOINT-TIMEOUT
Postgresql is a great database, but I think people could use SQLite a lot more often.
Although PostgreSQL is my go to for relational databases I’d echo this especially #4 MVCC Garbage Collection. Additionally I find the permission system labyrinthine and dislike how a basic “select count(*) from table” takes non-trivial time compared to other databases.
“Tuning it was 40% dark art, 40% brute force, and 10% pure luck.”
This made me smile :)
Very nice to read. I’m quite the Postgres fanboy (as a MySQL refugee, Postgres feels like an oasis of sanity), but it’s good to be aware of pitfalls. Personally, the only annoyance I’d ran into was the limit on identifiers (they can be only 63 bytes), which is usually easily worked around.
The corruption-spreading replication sounds especially nasty, since we’ve been considering using replication as a more light-weight form of backup than an half-hourly pg_dump, which can be heavy on the server.
I wonder if having a logical replication in place would’ve be a solution to this particular scenario, as the text only implies the problem exists with binary streaming. The replica then could be used for creating reliable backup’s if the load on the main server is a problem. Also there’s always barman or pgbackrest for implementing complex backup & restore policies with point-in-time recovery, which might be a better solution than only using
pg_dump.At scale, every design decision starts to come with tradeoffs.
My perspective to others is this: Demonstrate why Postgres will not work, using algorithmic analysis, knowledge of Postgres, and the estimated data size and TPS read/write mix. Things such as, a shopping cart system for 100 million concurrent users, may be the wrong problem for Postgres.
Until that has been done, please use Postgres.
(cited example was what led to Dynamo’s invention)
I don’t know if it’s like this in other databases or not, but something that tripped me up multiple times over the years with pg was using “timestamp” as opposed to “timestamp with time zone”. The timezone information gets thrown away instead of converted to UTC so applications need to actively fiddle with it.
Is it strange that I assumed “timestamp” being “timestamp without time zone” meant that postgres didn’t store timezones because it’s always in UTC and clients would automatically convert back and forth?