Accessing rows at an older timestamp

Hi there,

We’re very interested to test cockroachDB for our use case.
We want to save information about users as rows in a table, and we would like
to be able to access the whole update history.

It is my understanding that cockroach DB saves multiple versions of rows.
Is there an API to access that information (list older values of a rows, access data for those timestamps)?
If not, are there any plans to build one?

In addition I’m also wondering if a table can be configured to keep an unlimited amount of versions?

Thanks a lot for your help,

1 Like

Hi Christian,

CockroachDB does save multiple versions of rows, and it is possible to access older versions of a row using what we call “Time Travel Queries”, but they unfortunately don’t really fit your use case.

There isn’t an API for listing the older versions of a row, older versions of a row get garbage collected after one day, and while I wouldn’t rule out us supporting something like this down the road, we don’t have any plans to do so in the near future.

-Alex

1 Like

You can use the ttlseconds in the zone configuration to keep more versions of data. However, it is not advised to use this with rapidly-changing data - keeping too many historical versions can lead to memory spikes on the server because all versions of a row are currently kept together. As a rule of thumb you should avoid setting the ttlseconds field so high that all the changes to a row in that time period could add up to 64MB.

1 Like

Alex, Ben, Thanks for your answers,

We’ll be implementing that in the application layer then since that’s pretty straightforward
anyways.

I’ll be happy to share our experience with cockroach once we’re done

1 Like

There is some related communication on github outlining possibly similar use cases.