Re: Checking for data changes across a very large table

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

> We have an application design which includes a potential 2
> billion row table (A). When the application user kicks off
> an analysis process, there is a requirement to perform a
> check on that table to verify that the data within hasn't changed.
...
> But my main concern is performance - I fear that this won't be able to
> perform (in a few seconds rather than minutes). Does the general
> approach make any sense? Can anyone suggest a better starting point?

It's not clear if you need to check the entire table, or just a
subset related to that user each time, but one solution for either
is a trigger (after insert, update, delete) that simply increments
a sequence. Lock the sequence down and make the function security
definer. Don't increment if an update hasn't actually changed
anything. If the sequence number is not the same as last time the
app checked, then the data is not the same. You can store the sequence
value in a table if you need some persistence, or add multiple columns
if you need to check for a user-derived subset of the data (with
multiple sequences or simply increment the values in the table itself
like a version control number).

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201012100942
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAk0CPLYACgkQvJuQZxSWSsi8NQCgz4+bmWPMZm+aIX9maelZhj/+
wycAoNT32GFwudXF1Totvpw25+TXsu+E
=jc8n
-----END PGP SIGNATURE-----