I have a suggestion for a table checksumming facility within PostgreSQL.
The applications are reasonably obvious - detecting changes to tables,
validating data migrations, unit testing etc. A possible algorithm is as
follows:
1. For each row of the table, take the binary representations of the
values and serialise them to CSV.
2. Calculate the MD5 sum of each CSV-serialised row.
3. XOR the row MD5 sums together.
4. CSV-serialise and MD5 a list of representations (of some sort) of the
types of the table's columns and XOR it with the rest.
5. Output the result as the table's checksum.
Advantages of this approach:
1. Easily implemented using SPI.
2. Since XOR is commutative and associative, order of ingestion of rows
doesn't matter; therefore, unlike some other table checksumming methods,
this doesn't need an expensive ORDER BY *. So, this should be pretty much
as fast as a SELECT * FROM, which is probably as fast as a table checksum
can be.
3. Using a cursor in SPI, rows can be ingested a few at a time. So memory
footprint is low even for large tables.
4. Output has a convenient fixed size of 128 bits.
Questions:
1. Should this be a contrib module which provides a function, or should it
be a built-in piece of functionality?
2. Is MD5 too heavyweight for this? Would using a non-cryptographic
checksum be worth the speed boost?
3. Is there a risk of different architectures/versions returning different
checksums for tables which could be considered identical? If so, is this
worth worrying about?
I have knocked up some sample code if anyone is interested.
Regards,
Matthew
