Does unique_rowid primary key remove the need for a created_at::timestamp column

I currently have a pg database where we adopted id’s very similar to unique_rowid values (we used the functions in this article as a base: https://instagram-engineering.com/sharding-ids-at-instagram-1cf5a71e5a5c)

Since the ids contain a timestamp, we thought not having a created_at column was a great idea as we get indexed date range queries built in for free (with some added functions to convert dates to ids). Some people seem to do this with MongoDB’s ObjectID as well (http://www.mongotips.com/b/a-few-objectid-tricks/)

I understand unique_rowid to be the de-facto default with CockroachDB so I’m curious if anyone else is using the id’s in lieu of created_at and if it’s a great idea, I think it would be cool if CockroachDB could have built in support for doing the date queries on the id as it internal knowledge of the EPOCH offset, e.g. unique_rowid_to_timestamp and timestamp_to_rowid

Thanks,

Troy

Troy,

thanks for your question, this is an interesting idea!

The timestamp embedded in a rowid is not fully reliable in theory because it partially overlaps with the node ID bits. With very large clusters with sufficiently many nodes the timestamp would be partially erased (technically, XORed with the upper bits of the node ID).

Also we’ve not been keen on documenting the internal structure of the value returned by unique_rowid() because we wanted to keep some freedom to change/update/upgrade this function over time. Perhaps this is time to revisit that decision.

Finally, I’d like to highlight that underneath SQL CockroachDB also stores a MVCC timestamp next to every value written. We do not yet expose this data via SQL for fine-grained queries (e.g. ranges constraints) but you can also use AS OF SYSTEM TIME to use this data to “scroll back in time” in a table.

I’ll push your inquiry internally in the team to see if we can provide more feedback to you.

Regards