4 ms·

Impressive that it works correctly across schema changes. I assume the indexes are laid out like (col, timestamp)? Can you handle an access pattern where keys

by grogers 10y ago

Impressive that it works correctly across schema changes. I assume the indexes are laid out like (col, timestamp)? Can you handle an access pattern where keys are continuously inserted with the same value, and then deleted shortly after? Will queries for the value work efficiently, or have to scan all rows inserted with that value for the last 24 hours? Is there a way to tweak GC to run more aggressively on a particular table (knowing that you wouldn't be able to query back in time)? We have an access pattern like that in our database. Rows are inserted into the table with x=null, then later x is set to some unique value, but we need to query where x is null in order to set the actual value. At one point we had a long running transaction that prevented the database from cleaning up old records, so the query for X is null grew slower and slower to the point where it almost browned us out.

state_machine 10y ago
[engineer at cockroach] Basically, yes, all keys the KV, including the index entries, include a timestamp suffix. Repeated edits to the same value will accumulate MVCC revisions until they are GCed, and those will be iterated over during access, so, as you point out, a use-case like yours would likely benefit from shorter a GC cutoff and GC _is_ configurable in cockroach.
pjungwir 10y ago
Yes that is fascinating!: However, we are able to support schema changes between the present and the “as of” time. For example, if a column is deleted in the present but exists at some time in the past, a time-travel query requesting data from before the column was deleted will successfully return it. I've noticed that hardly anyone writes about DDL for temporal tables. Snodgrass's book doesn't cover it. What happens when you add a `NOT NULL` column or change a column to `NOT NULL`? That kind of thing. There is plenty of research on temporal tables, but I haven't found any attempt at defining rules for these changes. (If someone knows of a paper or book chapter, I'd love to hear about it!)

by grogers 10y ago

state_machine 10y ago

[engineer at cockroach] Basically, yes, all keys the KV, including the index entries, include a timestamp suffix. Repeated edits to the same value will accumulate MVCC revisions until they are GCed, and those will be iterated over during access, so, as you point out, a use-case like yours would likely benefit from shorter a GC cutoff and GC _is_ configurable in cockroach.

pjungwir 10y ago

Yes that is fascinating!: However, we are able to support schema changes between the present and the “as of” time. For example, if a column is deleted in the present but exists at some time in the past, a time-travel query requesting data from before the column was deleted will successfully return it. I've noticed that hardly anyone writes about DDL for temporal tables. Snodgrass's book doesn't cover it. What happens when you add a `NOT NULL` column or change a column to `NOT NULL`? That kind of thing. There is plenty of research on temporal tables, but I haven't found any attempt at defining rules for these changes. (If someone knows of a paper or book chapter, I'd love to hear about it!)