[silvestrov]

Depends very much on how the SSDs are designed internally. I think these days we have to settle for "can never be sure" of the real page size for atomicity. Pages can also become corrupt in other ways.

It is weird that "--data-checksums" isn't the default for new databases, even when it cost a bit in performance. Integrity should be more important than performance.

[isosphere]

Was thinking the same thing when I saw those zeros in the checksum field. Perhaps the consequences are significant.

Here's a benchmarking exercise I found: https://www-staging.commandprompt.com/uploads/images/Command...

With a tidy summary:

> Any application with a high shared buffers hit ratio: little difference. > Any application with a high ratio of reads/writes: little difference. > Data logging application with a low ratio of reads/inserts, and few updates and deletes: little difference. > Application with an equal ratio of reads/inserts, or many updates or deletes, and a low shared buffers hit ratio (for example, an ETL workload), especially where the rows are scattered among disk pages: expect double or greater CPU and disk I/O use. > Run pg_dump on a database where all rows have already been previously selected by applications: little difference. > Run pg_dump on a database with large quantities of rows inserted to insert-only tables: expect roughly double CPU and disk I/O use.

[silvestrov]

On my M1 mac "dd ... | cksum" takes 3 seconds while "dd | shasum" (sha1) takes 2 seconds. So cksum might not be the best tool for performance checking.

There is CPU specific code in the PG source in src/include/storage/checksum_impl.h

It is written as a plain nested loop in C. So performance is fully dependent on the compiler being able to parallelize or vectorize the code.

I would not be surprised if manually written SIMD code would be faster.

[anarazel]

The bottleneck isn't at all the checksum computation itself. It's that to keep checksums valid we need to protect against the potential of torn pages even in cases where it doesn't matter without checksums (i.e. were just individual bits are flipped). That in turn means we need to WAL log changes we don't need to without checksums - which can be painful.

[Joe_Cool]

Interesting. I guess M1 doesn't have the 'crc32' "acceleration" that is included in SSE4.2.

[loeg]

M1 has CRC32 acceleration intrinsics.

https://dougallj.wordpress.com/2022/05/22/faster-crc32-on-th...

https://github.com/corsix/fast-crc32?tab=readme-ov-file#appl...

[silvestrov]

So when using these intrinsics an Intel Core i7 can do 30 GB/s but the performance check linked above (by isosphere ) says only 300 MB/s, i.e. 1%

Something is amiss here.

If a CPU can do 30 GB/s then a CRC check should not have any real performance impact.

[loeg]

I don't know where you're getting 300 MB/s from.

[tremon]

I'm not sure how much bearing internal storage organization should have on Postgres' page size. Since pg explicitly chooses not to implement their own storage organization layer, there's always a filesystem between a pg database and the underlying storage.

[berkes]

> Integrity should be more important than performance.

Most often it is. But not always. There certainly are cases where speed is far more important than integrity in databases. I cannot think of a case where this would be true for a RDBMS or even a Document DB (Though MongoDB had different opinions on this...).

But e.g. redis as caching server, or memcached, or even these non-normalized data that I have in a PG that can be reproduced from other sources easily in case of corruption or stale-ness: it's fine to trade in integrity for speed there.

[speed_spread]

If physical integrity is already provided by the backing filesystem, such as ZFS, wouldn't --data-checksums be redundant?

[magicalhippo]

If data is served from ARC, it's primary cache, ZFS does not perform a checksum check before handing it to you, as the data was checked when it got read into the ARC.

If you use ECC you're quite safe, but ECC can't detect multi-bit errors, just single and double bit errors.

So if you care much about your integrity, you might want Postgres to do its checksum check as well.

[ikiris]

If you somehow have 3+ bit errors coming out of ram on an ECC board, you have much bigger problems than trying to verify your postgres data via checksum.

[magicalhippo]

AFAIK RowHammer attacks can cause multiple bits to flip in a row[1], no?

But sure, it's not for the vast majority of folks.

[1]: https://www.vusec.net/projects/eccploit/

[ikiris]

Rowhammer would qualify as a bigger problem, yes.

[loeg]

The 8k size long predates SSDs or 4k sectors.