I'm looking to extract only the rows that have changed in CockroachDB since a given time. We're doing this to be able to maintain a slave-copy of the table in a different DB technology. I've looked into changefeeds, but they seem pretty basic (especially if we decide to go non-enterprise). I've also read about how Cockroach uses MVCC, but I haven't found any information about accessing the timestamps that are associated with rows in the underlying storage technology.
Is there a way to access this timestamp, through SQL or otherwise?
A couple of alternatives I'm considering:
I could use AS OF SYSTEM TIME to run 2 queries at different times, and work out the diff.
I could add a "last updated" and an "is deleted" field to my table, and enforce their use.
If it's not realistic to use the low-level timestamp, do you have any advice related to the above approaches?
The v20.2 release of CockroachDB includes crdb_internal_mvcc_timestamp that could help here. You can see release notes about it here: https://www.cockroachlabs.com/docs/releases/v20.2.0-alpha.3.html
However, that column cannot be indexed, so filtering on it will be pretty slow if the table is very large.
Using a last_updated_at column that you maintain, and adding an index to it probably would be better. Note that if you do a lot of sequential writes to the table, you might want to use a hash-sharded index.