5 ms·

Postgres has a write amplification problem - when you update or insert a row, it has to check all the indexes to see what was touched (unlike MySQL.) Uber made

Postgres has a write amplification problem - when you update or insert a row, it has to check all the indexes to see what was touched (unlike MySQL.) Uber made this famous in a blog post where they migrated off postgres because of it. So with Postgres I try to keep the number of indexes per table down to around 5, but with MySQL InnoDB, 20 indexes is not a problem for many use cases. The other little-known Postgres problem is that by default, installing it sets up replication that writes a local file in a busy manner. This can cause problems with fsck or on low-end SSD/flash storage resulting in corruption that causes postgres restarts to fail hard, so disable that.

andy_ppp 6y ago
I’m interested to find out why the community feel this answer is incorrect. I don’t know enough about database internals to know! Would be good if someone could help me to become more informed about this. I can’t personally see a way that indexes wouldn’t be touched when inserts happen in MySQL but who knows?
- jchb 6y ago
  Previously on HN: Why Uber Engineering Switched from Postgres to MySQL (2016): https://news.ycombinator.com/item?id=17280239 https://news.ycombinator.com/item?id=17280239 A PostgreSQL response to Uber [pdf]: https://news.ycombinator.com/item?id=14222721 https://news.ycombinator.com/item?id=14222721 One important point is that Uber wrote this running on Postgres 9.2. The latest Postgres release is now 12.4
  - andy_ppp 6y ago
    Thanks, I just always trust Postgres more from my experiences. Reading some of the above I’m fairly unconvinced by Uber’s reasoning...
    - pgaddict 6y ago
      I'd say some of the limitations hit by Uber were real, and were a bit of a PITA even before that. Some were improved significantly since then, others still need to be considered when designing application accessing PostgreSQL. I'd say it was a nice example what happens when you design application based on how you imagine an ideal database would work, not realizing that there's a lot of complexity and limitations. And then when you eventually have to face reality you blame the database for having to do trade-offs and refuse to mitigate the trouble by changing the application.
    - takeda 6y ago
      All jobs that I had where MySQL was used had some kind of issues. First time there was a database that one day just died and refused to start. When investigating, it turns that some tables got corrupted (fortunately, the scan tool that Congress with MySQL was able to fix it, but I also encountered databases which were FUBARed). After checking the machine, it had over 100 days off uptime (so it wasn't abrupt shutdown), the disk also had plenty of free space, so the corruption didn't happen because of disk being full. So MySQL just broke itself out of nowhere. Another time (I think it was a MariaDB) the database get corrupted every few days. First we though it was hardware, so we replaced disks, then the entire machine. But it continued to happen. Eventually it turned out it was a bug and with specific pattern written to the disk, when MariaDB read that block back it incorrectly thought the block was encrypted and discarded it. Updating to a version that fixed it, responded the issue, but that's really a nasty bug that caused data corruption. Postgresql on the other hand with my experience always was nice even in bad situations. When we had network issue (due to failed switch) one the problem was fixed the database reconnected back and trained replication, MySQL in similar situation required to tell it to reconnect again, some even setting up replication again (which requires restoring backup on slave and reconfiguring replication again, which required few hours). In another instance we had main postgresql set up replication to a DR site. The database data was stored on filesystem provided by NetApp. A consultant had a task to deprecate filesystems that were no longer used, so he actually put them offline for 30 days, before deleting them. Around 20 days in we noticed that the master was running out of space. Turns out the disk was utilized by unshipped logs to the standby. Once filesystem at the DR site was put back online everything resolved all by itself.
  - castorp 6y ago
    > One important point is that Uber wrote this running on Postgres 9.2. The latest Postgres release is now 12.4 Just a minor nitpick: Postgres changed the version numbering scheme with version 10. So 9.4 was a major release, but 12.4 marks a minor (bugfix) release. If you want to name minor releases it would be e.g. 9.4.26 vs. 12.4. If you want to compare major versions it would be 9.4 vs. 12
    - takeda 6y ago
      Basically the first number didn't really have any meaning, so it was removed, so the major versions (which I believe are released once a year) are: 9.2 (released in 2012) 9.3 9.4 9.5 9.6 10 11 12 13 (about to be released this year?) That's still a lot of versions in between, each of them as new functionality and requires converting the database to upgrade.
- pgaddict 6y ago
  I haven't down-voted the post, but I'd say it's a bit misleading. Firstly, it's not quite true "Uber migrated away because of write amplification" because they named multiple other reasons contributing to that decision. (And yes, not all of those reasons were quite valid, but that's a different discussion.) Secondly, it's patently false that Postgres sets up local replication writing into a local file. That's plain bogus.
- ants_a 6y ago
  To add to others, writing a file over and over doesn't do anything on pretty much any solid state storage device you would use for a database. You would have to look at something like a bargain basement SD card to find something that doesn't have wear leveling.
fabian2k 6y ago
I've no idea what the part about replication to a local file is. Postgres doesn't set up any replication by default. And if you're getting corruption in your database on low-end storage, that storage is simply broken. The database can't do anything if your storage system lies to it, or writes different data than what you give it. It is possible to configure Postgres in a way that leads to data corruption when e.g. the power goes out, but that is an option with a big warning that you should never use outside some specific circumstances. The Postgres defaults don't corrupt your database if your hardware works correctly (absent some pretty rare bugs in Postgres itself).

andy_ppp 6y ago

I’m interested to find out why the community feel this answer is incorrect. I don’t know enough about database internals to know! Would be good if someone could help me to become more informed about this. I can’t personally see a way that indexes wouldn’t be touched when inserts happen in MySQL but who knows?

jchb 6y ago
Previously on HN: Why Uber Engineering Switched from Postgres to MySQL (2016): https://news.ycombinator.com/item?id=17280239 https://news.ycombinator.com/item?id=17280239 A PostgreSQL response to Uber [pdf]: https://news.ycombinator.com/item?id=14222721 https://news.ycombinator.com/item?id=14222721 One important point is that Uber wrote this running on Postgres 9.2. The latest Postgres release is now 12.4
- andy_ppp 6y ago
  Thanks, I just always trust Postgres more from my experiences. Reading some of the above I’m fairly unconvinced by Uber’s reasoning...
  - pgaddict 6y ago
    I'd say some of the limitations hit by Uber were real, and were a bit of a PITA even before that. Some were improved significantly since then, others still need to be considered when designing application accessing PostgreSQL. I'd say it was a nice example what happens when you design application based on how you imagine an ideal database would work, not realizing that there's a lot of complexity and limitations. And then when you eventually have to face reality you blame the database for having to do trade-offs and refuse to mitigate the trouble by changing the application.
  - takeda 6y ago
    All jobs that I had where MySQL was used had some kind of issues. First time there was a database that one day just died and refused to start. When investigating, it turns that some tables got corrupted (fortunately, the scan tool that Congress with MySQL was able to fix it, but I also encountered databases which were FUBARed). After checking the machine, it had over 100 days off uptime (so it wasn't abrupt shutdown), the disk also had plenty of free space, so the corruption didn't happen because of disk being full. So MySQL just broke itself out of nowhere. Another time (I think it was a MariaDB) the database get corrupted every few days. First we though it was hardware, so we replaced disks, then the entire machine. But it continued to happen. Eventually it turned out it was a bug and with specific pattern written to the disk, when MariaDB read that block back it incorrectly thought the block was encrypted and discarded it. Updating to a version that fixed it, responded the issue, but that's really a nasty bug that caused data corruption. Postgresql on the other hand with my experience always was nice even in bad situations. When we had network issue (due to failed switch) one the problem was fixed the database reconnected back and trained replication, MySQL in similar situation required to tell it to reconnect again, some even setting up replication again (which requires restoring backup on slave and reconfiguring replication again, which required few hours). In another instance we had main postgresql set up replication to a DR site. The database data was stored on filesystem provided by NetApp. A consultant had a task to deprecate filesystems that were no longer used, so he actually put them offline for 30 days, before deleting them. Around 20 days in we noticed that the master was running out of space. Turns out the disk was utilized by unshipped logs to the standby. Once filesystem at the DR site was put back online everything resolved all by itself.
- castorp 6y ago
  > One important point is that Uber wrote this running on Postgres 9.2. The latest Postgres release is now 12.4 Just a minor nitpick: Postgres changed the version numbering scheme with version 10. So 9.4 was a major release, but 12.4 marks a minor (bugfix) release. If you want to name minor releases it would be e.g. 9.4.26 vs. 12.4. If you want to compare major versions it would be 9.4 vs. 12
  - takeda 6y ago
    Basically the first number didn't really have any meaning, so it was removed, so the major versions (which I believe are released once a year) are: 9.2 (released in 2012) 9.3 9.4 9.5 9.6 10 11 12 13 (about to be released this year?) That's still a lot of versions in between, each of them as new functionality and requires converting the database to upgrade.
pgaddict 6y ago
I haven't down-voted the post, but I'd say it's a bit misleading. Firstly, it's not quite true "Uber migrated away because of write amplification" because they named multiple other reasons contributing to that decision. (And yes, not all of those reasons were quite valid, but that's a different discussion.) Secondly, it's patently false that Postgres sets up local replication writing into a local file. That's plain bogus.
ants_a 6y ago
To add to others, writing a file over and over doesn't do anything on pretty much any solid state storage device you would use for a database. You would have to look at something like a bargain basement SD card to find something that doesn't have wear leveling.

fabian2k 6y ago

I've no idea what the part about replication to a local file is. Postgres doesn't set up any replication by default. And if you're getting corruption in your database on low-end storage, that storage is simply broken. The database can't do anything if your storage system lies to it, or writes different data than what you give it. It is possible to configure Postgres in a way that leads to data corruption when e.g. the power goes out, but that is an option with a big warning that you should never use outside some specific circumstances. The Postgres defaults don't corrupt your database if your hardware works correctly (absent some pretty rare bugs in Postgres itself).