CODEWITHSUNDEEP
cockroachdb
Oscar
Oscar

Reputation: 141

Accessing row's last updated time in CockroachDB

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:

If it's not realistic to use the low-level timestamp, do you have any advice related to the above approaches?

Upvotes: 2

Views: 1424

Answers (1)

rafiss
rafiss

Reputation: 583

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.

Upvotes: 2

Related Questions