4 ms·

Use any database enough and you'll find weird table corruption bugs. I've seen them in MySQL and MSSQL in my professional career. Fortunately in both cases it w

Use any database enough and you'll find weird table corruption bugs. I've seen them in MySQL and MSSQL in my professional career. Fortunately in both cases it was caught before there was an unrecoverable issue, but it happens. I've read the article before and I do agree, Postgres wasn't really the tool for what they're doing.

pilif 8y ago
> Use any database enough and you'll find weird table corruption bugs I'm not convinced. The one feature I really need a database to have is to guarantee that whatever data I write to it, I will be able to read back unaltered. I'm a heavy Postgres user since 2001 and I have never seen table corruption, even in the face of defective hardware. The only time something remotely comparable has happened was index corruption on a replica due to a bug in 9.2.0 which was very quickly fixed and as it was just a corrupted index, very easily restored (REINDEX). If a database I trust with production data was corrupting tables, I would move off that system in a heartbeat (which is incidentally why I stopped using MySQL back then)
- debaserab2 8y ago
  The missing context here is the scale. What size of data and transaction volume have you been working with?
  - pilif 8y ago
    By now the data is about 6.2 TB with overall 20k transactions per second, so I would say not small any more, but also certainly not Uber scale. Still. I have had MySQL corrupt tables with way less load and data (in the less than 10GB size range)
    - jjirsa 8y ago
      6.2TB is single machine scale The company here is doing petabytes of data and millions of ops per second. That’s scale. We should be clear about what “at scale” means, and 6.2TB is not.
      - pilif 8y ago
        No matter the scale. Table corruption is never acceptable. If table corruption is acceptable, you might as well just serve data from /dev/urandom and store user data to /dev/null.
        astine 8y ago
        Table corruption may not be 'acceptable,' but that doesn't matter. Even if your software is perfect and has no bugs, it cannot account for issues at the system and hardware level. Backups and redundant systems exist for a reason. I've have data corruption in a PG database before. I don't remember the cause--I think it was the filesystem rather than Postgres that was at fault--but it still happened and I had to deal with it.
        manigandham 8y ago
        Obviously the situation being discussed here is corruption specifically because of the database software. If a comet destroys the entire datacenter and the data is lost, nobody is going to blame Postgres for it.
    - Amezarak 8y ago
      > but also certainly not Uber scale. How many Uber rides are happening every second? I suppose it depends exactly on their setup, but I could see that being at least the right order of magnitude. EDIT: According to the Internet, there are about a billion Uber rides per year, which makes for about 31 rides per second. Even if there are 1000 transactions per ride, 21k/second sounds approximately Uber scale to me.
- Arnt 8y ago
  Postgres has bugs, or behaves in ways that are likely to trigger bugs in your code. Try a combination of long-running transactions and a high commit frequency, such that an appreciable portion of the xid space is active. Or don't. You know that combination is tempting fate. I love postgres. It's so great. The very best thing I can say about software is you have to tempt fate to get bitten by a bug.
  - pilif 8y ago
    > long-running transactions don't do that if you value your uptime at all. Long-running transactions are the bane of any Postgres setup, no matter the XID overflow. A few weeks ago, one slipped through my monitoring and a cache table we often sequence scan which normally had 10k rows in it had 28M dead rows that could never be removed due to that one transaction. That sequence scan suddenly became a lot more expensive.
    - mjcl 8y ago
      Just out of curiosity, what do you find to be long running? Five minutes, five days? Depends on the volume of other transactions happening simultaneously?
      - pilif 8y ago
        As it's the case very often in our business, the answer is: "it depends". It depends on the write load, the read load, and the write patterns. Think about it this way: for the duration of any transaction, Postgres is basically in append-only mode. If you have cache tables that normally get rewritten entirely, no row will be deleted during the duration of any transaction in the whole cluster (so this even affects databases other than the one the transaction is connected to). In our case, we run some heavy import jobs daily which causes transaction lifetimes in hours, depending on the job. This isn't an issue, or rather, it's an issue, you can throw hardware at in order to circumvent it. The transaction I was referring to in my earlier comment had a lifetime in days and nearly brought down the whole system.
      - Arnt 8y ago
- GrayShade 8y ago
  There was a bug related to replication; it was caught and fixed rather quickly at the time, but Uber still got bitten by it. See http://thebuild.com/presentations/uber-perconalive-2017.pdf http://thebuild.com/presentations/uber-perconalive-2017.pdf

pilif 8y ago

> Use any database enough and you'll find weird table corruption bugs I'm not convinced. The one feature I really need a database to have is to guarantee that whatever data I write to it, I will be able to read back unaltered. I'm a heavy Postgres user since 2001 and I have never seen table corruption, even in the face of defective hardware. The only time something remotely comparable has happened was index corruption on a replica due to a bug in 9.2.0 which was very quickly fixed and as it was just a corrupted index, very easily restored (REINDEX). If a database I trust with production data was corrupting tables, I would move off that system in a heartbeat (which is incidentally why I stopped using MySQL back then)

debaserab2 8y ago
The missing context here is the scale. What size of data and transaction volume have you been working with?
- pilif 8y ago
  By now the data is about 6.2 TB with overall 20k transactions per second, so I would say not small any more, but also certainly not Uber scale. Still. I have had MySQL corrupt tables with way less load and data (in the less than 10GB size range)
  - jjirsa 8y ago
    6.2TB is single machine scale The company here is doing petabytes of data and millions of ops per second. That’s scale. We should be clear about what “at scale” means, and 6.2TB is not.
    - pilif 8y ago
      No matter the scale. Table corruption is never acceptable. If table corruption is acceptable, you might as well just serve data from /dev/urandom and store user data to /dev/null.
      - astine 8y ago
        Table corruption may not be 'acceptable,' but that doesn't matter. Even if your software is perfect and has no bugs, it cannot account for issues at the system and hardware level. Backups and redundant systems exist for a reason. I've have data corruption in a PG database before. I don't remember the cause--I think it was the filesystem rather than Postgres that was at fault--but it still happened and I had to deal with it.
        manigandham 8y ago
        Obviously the situation being discussed here is corruption specifically because of the database software. If a comet destroys the entire datacenter and the data is lost, nobody is going to blame Postgres for it.
  - Amezarak 8y ago
    > but also certainly not Uber scale. How many Uber rides are happening every second? I suppose it depends exactly on their setup, but I could see that being at least the right order of magnitude. EDIT: According to the Internet, there are about a billion Uber rides per year, which makes for about 31 rides per second. Even if there are 1000 transactions per ride, 21k/second sounds approximately Uber scale to me.
Arnt 8y ago
Postgres has bugs, or behaves in ways that are likely to trigger bugs in your code. Try a combination of long-running transactions and a high commit frequency, such that an appreciable portion of the xid space is active. Or don't. You know that combination is tempting fate. I love postgres. It's so great. The very best thing I can say about software is you have to tempt fate to get bitten by a bug.
- pilif 8y ago
  > long-running transactions don't do that if you value your uptime at all. Long-running transactions are the bane of any Postgres setup, no matter the XID overflow. A few weeks ago, one slipped through my monitoring and a cache table we often sequence scan which normally had 10k rows in it had 28M dead rows that could never be removed due to that one transaction. That sequence scan suddenly became a lot more expensive.
  - mjcl 8y ago
    Just out of curiosity, what do you find to be long running? Five minutes, five days? Depends on the volume of other transactions happening simultaneously?
    - pilif 8y ago
      As it's the case very often in our business, the answer is: "it depends". It depends on the write load, the read load, and the write patterns. Think about it this way: for the duration of any transaction, Postgres is basically in append-only mode. If you have cache tables that normally get rewritten entirely, no row will be deleted during the duration of any transaction in the whole cluster (so this even affects databases other than the one the transaction is connected to). In our case, we run some heavy import jobs daily which causes transaction lifetimes in hours, depending on the job. This isn't an issue, or rather, it's an issue, you can throw hardware at in order to circumvent it. The transaction I was referring to in my earlier comment had a lifetime in days and nearly brought down the whole system.
    - Arnt 8y ago
GrayShade 8y ago
There was a bug related to replication; it was caught and fixed rather quickly at the time, but Uber still got bitten by it. See http://thebuild.com/presentations/uber-perconalive-2017.pdf http://thebuild.com/presentations/uber-perconalive-2017.pdf