4 ms·

I largely agree, but a couple points I'd like to challenge: Is hardware corruption really happening and making it into the WAL stream with checksums on? The n

I largely agree, but a couple points I'd like to challenge: Is hardware corruption really happening and making it into the WAL stream with checksums on? The next point, on planner hints: it's really just something that hasn't been done. If a few engineers made plans to tackle the problem, a lot could be done in a couple releases' worth of work. In the mean time, people are getting by with various half-measures anyway, such as extensions[1], planner tunables[2], and statistics tweaks[3]. The only dogma is that a half-baked solution isn't wanted. It's got a lot of architectural impact and long-term supportability implications. And a lot of different use cases that need to be considered that may drive different technological solutions. "Make plans stable/managable" is a different use case than "I know something the planner doesn't" which is different from "Make the planner do this thing because I said so". [1] https://github.com/ossc-db/pg_hint_plan/blob/master/doc/pg_hint_plan.html https://github.com/ossc-db/pg_hint_plan/blob/master/doc/pg_h... [2] https://www.postgresql.org/docs/current/runtime-config-query.html https://www.postgresql.org/docs/current/runtime-config-query... [3] https://www.postgresql.org/docs/12/sql-createstatistics.html https://www.postgresql.org/docs/12/sql-createstatistics.html

DiabloD3 7y ago
Hardware corruption can happen anywhere to anything. If you care about your data: Use ECC, and use a checksumming filesystem like ZFS, and also on top of this all, export your WALs to a second machine.
- jeffdavis 7y ago
  Well, I'm asking specifically if the author saw corruption that snuck past the checksumming features that postgres has. Extra layers are always good, but since I was one of the main authors of checksums in Postgres, I'd like to know if there's room for improvement. (Aside: the page checksum is only 16 bits, so if you have frequent corruption it's entirely believeable that a few sneak past. But I haven't seen it personally.)
ptman 7y ago
Also, pg has initdb --data-checksums
- scurvy 7y ago
  Doesn't this require a full dump/import? Can you stream from a primary server w/out checksums to a replica that has checksums enabled? Or does this fall into the "everything neat with PostgreSQL requires major downtime" category? (features, version upgrades, etc).
  - jeffdavis 7y ago
    No dump/reload, but you do need to shut down the server cleanly, enable checksums[1], and start it back up. Note that enabling checksums is expensive, but not as expensive as dump/reload. [1] https://www.postgresql.org/docs/current/app-pgchecksums.html https://www.postgresql.org/docs/current/app-pgchecksums.html
    - scurvy 7y ago
      That tool is only available in the latest versions of Postgresql (11 and 12), which very few stable shops are running in production. Checksums were introduced in 9.3, yet not made the default until 9.6. Converting it requires lengthy downtime, back to the original author's point of major version upgrade pain. Everything worthwhile in PG is introduced over a long time, requires a lot of pain to adopt, and if not adopted quickly, suddenly becomes "lol why aren't you doing this it's been there for years." That's the disconnect between people who actually run these clusters and the somewhat ivory-tower views of the postgresql devlopers.
      - jeffdavis 7y ago
        The postgres developers are not living in an ivory tower, they are solving real problems for real customers. There is so much enthusiasm that the original author felt there was not enough dissent. Maybe you should learn and understand why people are happy with postgres in the first place, and then understand your complaints in that broader picture. Then you wouldn't resort to mockery and other unproductive comments.
ahachete 7y ago
Few production clusters have checksums turned on. Since they cannot be turned online, only clusters of newer project turn it on --and even then, they are still unknown to many. So we cannot rely on this, and WAL stream corruption is a notable risk.

DiabloD3 7y ago

Hardware corruption can happen anywhere to anything. If you care about your data: Use ECC, and use a checksumming filesystem like ZFS, and also on top of this all, export your WALs to a second machine.

jeffdavis 7y ago
Well, I'm asking specifically if the author saw corruption that snuck past the checksumming features that postgres has. Extra layers are always good, but since I was one of the main authors of checksums in Postgres, I'd like to know if there's room for improvement. (Aside: the page checksum is only 16 bits, so if you have frequent corruption it's entirely believeable that a few sneak past. But I haven't seen it personally.)

ptman 7y ago

Also, pg has initdb --data-checksums

scurvy 7y ago
Doesn't this require a full dump/import? Can you stream from a primary server w/out checksums to a replica that has checksums enabled? Or does this fall into the "everything neat with PostgreSQL requires major downtime" category? (features, version upgrades, etc).
- jeffdavis 7y ago
  No dump/reload, but you do need to shut down the server cleanly, enable checksums[1], and start it back up. Note that enabling checksums is expensive, but not as expensive as dump/reload. [1] https://www.postgresql.org/docs/current/app-pgchecksums.html https://www.postgresql.org/docs/current/app-pgchecksums.html
  - scurvy 7y ago
    That tool is only available in the latest versions of Postgresql (11 and 12), which very few stable shops are running in production. Checksums were introduced in 9.3, yet not made the default until 9.6. Converting it requires lengthy downtime, back to the original author's point of major version upgrade pain. Everything worthwhile in PG is introduced over a long time, requires a lot of pain to adopt, and if not adopted quickly, suddenly becomes "lol why aren't you doing this it's been there for years." That's the disconnect between people who actually run these clusters and the somewhat ivory-tower views of the postgresql devlopers.
    - jeffdavis 7y ago
      The postgres developers are not living in an ivory tower, they are solving real problems for real customers. There is so much enthusiasm that the original author felt there was not enough dissent. Maybe you should learn and understand why people are happy with postgres in the first place, and then understand your complaints in that broader picture. Then you wouldn't resort to mockery and other unproductive comments.

ahachete 7y ago

Few production clusters have checksums turned on. Since they cannot be turned online, only clusters of newer project turn it on --and even then, they are still unknown to many. So we cannot rely on this, and WAL stream corruption is a notable risk.