Subject: data checksums

From: bruno vieira da silva <[email-masked]>
Date: Tue, 6 Aug 2024 11:11:55 -0400

Hello.
I've been dealing with some database corruption events, so i've been
wondering to enable data checksums on my deployments.

so my question is why data checksums aren't enabled by default on pg? the
pg doc
mentions a considerable performance penality, how considerable it is?

Thanks
-- 
Bruno Vieira da Silva

---

From: Ron Johnson <[email-masked]>
Date: Tue, 6 Aug 2024 12:25:15 -0400

> mentions a considerable performance penality, how considerable it is?

I'm going to hazard a guess that the relevant line from the docs were
written many many years ago, back when servers were much slower.

We certainly didn't notice any speed degradation when going from PG 9.6.24
to PG 14.12.  Or... we just use enough hardware and flash-only SAN disks to
make the overhead minuscule.

-- 
Death to America, and butter sauce.
Iraq lobster!

---

From: Christophe Pettus <[email-masked]>
Date: Tue, 6 Aug 2024 09:29:43 -0700

> On Aug 6, 2024, at 08:11, bruno vieira da silva <[email-masked]> wrote:
> 
> so my question is why data checksums aren't enabled by default on pg?

At this point, mostly historical reasons.  They're also superfluous if your underlying file system or storage hardware does storage-level corruption checks (which most don't).

> the pg doc 

That line is probably somewhat out of date at this point.  We haven't seen a significant slowdown in enabling them on any modern hardware.  I always turn them on, except on the type of filesystems/hardware mentioned above.

---

From: Greg Sabino Mullane <[email-masked]>
Date: Tue, 6 Aug 2024 12:29:55 -0400

The penalty is not "considerable", but it is not completely trivial either.
But it's more on the trivial side. Part of the problem is that it is hard
to measure, as it is very workload dependent. As to why it is not the
default, Postgres is very careful and conservative by default, and not
everybody was convinced that enabling checksums is worth the tradeoff,
especially (IIRC) with the lack of real-world examples of people
discovering issues thanks to these checksums. So yes, please enable and
share with us if the checksums catch anything.

I think the argument is worth re-opening again, because (as far as I know),
all of the companies providing Postgres support, whether completely
cloud-managed, setting up a complex HA cluster, or just providing tuning
advice, have enabled checksums by default for many, many years. So a big +1
from me to finally making it the default. It's a de-facto default anyway at
this point.

Cheers,
Greg

---

From: Laurenz Albe <[email-masked]>
Date: Wed, 07 Aug 2024 04:45:44 +0200

On Tue, 2024-08-06 at 09:29 -0700, Christophe Pettus wrote:
> At this point, mostly historical reasons.  They're also superfluous if your underlying
> file system or storage hardware does storage-level corruption checks (which most don't).

I am surprised by that.  Would you say that most storage systems will happily give you a
garbage block if there was a hardware problem somewhere?

> That line is probably somewhat out of date at this point.  We haven't seen a significant
> slowdown in enabling them on any modern hardware.  I always turn them on, except on the
> type of filesystems/hardware mentioned above.

Turning data checksums on will write WAL for hint bits, which can significantly increase
the amount of WAL written.

Yours,
Laurenz Albe

---

From: Christophe Pettus <[email-masked]>
Date: Tue, 6 Aug 2024 20:14:22 -0700

> On Aug 6, 2024, at 19:45, Laurenz Albe <[email-masked]> wrote:

"Most" is hard for me to judge.  HDDs can have uncorrected and undetected errors, definitely.  ZFS, for example, can correct those (within limits); XFS doesn't try.  I have been told that SSDs can have uncorrected/undetected errors as well, but I don't know details on that.

> the amount of WAL written.

I was curious about that, so I just did a quick experiment using pgbench, with identical databases except for checksums.  They both generated the same amount of WAL within 10% or so, so I don't think the impact is huge.  (And you need the hint bits for pg_rewind, which is a nice thing to have in your back pocket if required.)

---

From: Daniel Gustafsson <[email-masked]>
Date: Wed, 7 Aug 2024 09:41:18 +0200

> On 6 Aug 2024, at 18:29, Christophe Pettus <[email-masked]> wrote:

> That line is probably somewhat out of date at this point.  We haven't seen a significant slowdown in enabling them on any modern hardware.  I always turn them on, except on the type of filesystems/…

The last in-depth analysis of data checksums (and hint bits) overhead that I
can remember is from 2019:

  https://www.postgresql.org/message-id/20190330192543.GH4719%40development

Hardware advances in the last five years may very well have made these findings
irrelevant however.

--
Daniel Gustafsson

---

From: Ron Johnson <[email-masked]>
Date: Wed, 7 Aug 2024 09:52:49 -0400

On Wed, Aug 7, 2024 at 3:41 AM Daniel Gustafsson <[email-masked]> wrote:

> > That line is probably somewhat out of date at this point.  We haven't
> seen a significant slowdown in enabling them on any modern hardware.  I
> always turn them on, except on the type of filesystems/hardware mentioned
> above.
>

A quote from that post:
"I have not investigated the exact reasons, but my hypothesis it's about
the amount of WAL generated during the initial CREATE INDEX (because it
probably ends up setting the hint bits), which puts additional pressure
on the storage."

Presuming that hypothesis is true: how often do "you" run CREATE INDEX (or
VACUUM FULL or CLUSTER)?  I certainly don't run them very often.

-- 
Death to America, and butter sauce!
Iraq lobster...