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.
> 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.
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.
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.
That's measuring 'cksum', which must have an awfully slow implementation. The document notes that this is distinct from measuring PG's checksum performance. (I think it's a pretty useless measurement.)
Earlier (page 4):
> How much CPU time does it take to checksum...
> ...a specific amount of data? This is easy to estimate because PostgreSQL uses the crc32 algorithm which is very simple, and (GNU) Linux has a command line program that does the same thing: cksum.
Yeah, using cksum as an estimate here appears to be very flawed.
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.
> 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.
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.
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.
8k is a very common page size, but 4k isn't unheard of. Oracle's default is 4k.
The issue is that page size caps row size (for on-row storage). Also, if you have a smart clustering index, larger pages can be more efficient use of index addressing. So it's a trade-off.
Historically there was no atomicity at 4k boundaries, just at 512 byte boundaries (sectors). That'd have been too limiting. Lowering the limit now would prove problematic due to the smaller row sizes/ lower number of columns.
I guess it's a tradeoff. Ideally you'd want a small enough page size such that pages are unlikely to be split across multiple LBAs, but the performance wouldn't be good. Standard filesystems don't really guarantee LBA alignment anyway.
