data checksums

On Tue, Aug 6, 2024 at 11:12 AM bruno vieira da silva <brunogiovs@gmail.com>
wrote:

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?

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!

On Aug 6, 2024, at 08:11, bruno vieira da silva <brunogiovs@gmail.com> 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
mentions a considerable performance penality, how considerable it is?

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.

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

On Tue, 2024-08-06 at 09:29 -0700, Christophe Pettus wrote:

On Aug 6, 2024, at 08:11, bruno vieira da silva <brunogiovs@gmail.com> 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).

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?

the pg doc
mentions a considerable performance penality, how considerable it is?

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

On Aug 6, 2024, at 19:45, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
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?

"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.

Turning data checksums on will write WAL for hint bits, which can significantly increase
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.)

On 6 Aug 2024, at 18:29, Christophe Pettus <xof@thebuild.com> wrote:

On Aug 6, 2024, at 08:11, bruno vieira da silva <brunogiovs@gmail.com> wrote:

the pg doc
mentions a considerable performance penality, how considerable it is?

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.

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

/messages/by-id/20190330192543.GH4719@development

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

--
Daniel Gustafsson

On Wed, Aug 7, 2024 at 3:41 AM Daniel Gustafsson <daniel@yesql.se> wrote:

On 6 Aug 2024, at 18:29, Christophe Pettus <xof@thebuild.com> wrote:

On Aug 6, 2024, at 08:11, bruno vieira da silva <brunogiovs@gmail.com>

wrote:

the pg doc
mentions a considerable performance penality, how considerable it is?

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.

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

/messages/by-id/20190330192543.GH4719@development

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...