Learning is not a spectator sport

Just a quick post today on Time Sorted IDs, but first the background.

Friend from Twitter Vlad Mihalcea posted this article a while back about a (for lack for better term) hybrid between the familar ascending sequence as a primary key or a GUID.

Go give the article a read, but as a quick TL;DR, ascending sequences have the issue of being able to guess their pattern, whereas GUIDs can be inefficient in index trees as well as 128bits being a lot of data to store for every single key. Thus there are implementations of “TSID”, ie, time sorted IDs which give the best of both worlds.

So here is a simple Oracle implementation of a Time Sorted ID, obeying the principles Vlad referred to in his post, namely

• a time component (ie, a continuously ascending value)
• a node component (indicating a source “node”, which you could define in any way you see fit)
• a random component (to reduce the chances of guessing a potential key)

I’ve got a couple of options here. The first one uses a sequence for the ascending component, and then uses the database instance number for the “node” and a random number from a range of 1 million. The instance number could easily be flipped to the start for RAC-style separation if needed.

SQL> create sequence seq start with 4294967296 maxvalue 68719476735 cycle;

Sequence created.

SQL>
SQL> select
  2   hextoraw(
  3    to_char(seq.nextval,'fm0XXXXXXXX')||
  4    to_char(to_number(sys_context('USERENV','INSTANCE')),'fm0X')||
  5    to_char(dbms_random.value(0,1048575),'fm0000X')
  6  )
  7  from dual;

HEXTORAW(TO_CHAR(SEQ
--------------------
1000000000183895

And the second one uses the seconds since the year 2000 plus some fractional seconds

SQL> select
  2   hextoraw(
  3    to_char(
  4          (extract(year from localtimestamp)-2000)*31968000+
  5          to_number(to_char(localtimestamp,'DDD'))*86400+
  6          to_number(to_char(localtimestamp,'FF3')),'fm0XXXXXXXX')||
  7    to_char(to_number(sys_context('USERENV','INSTANCE')),'fm0X')||
  8    to_char(dbms_random.value(0,1048575),'fm0000X')
  9    )
 10  from dual;

HEXTORAW(TO_CHAR((EX
--------------------
02CD05E61012F306

each yielding a 8 byte RAW which you could use as a primary key. That is half the size of a GUID, and the keys are reasonably ascending for good index packing, with excellent (but not absolute) confidence of no duplicates.

Am I suggesting you rush out and use this approach? No. But if you’re keen, then make sure you do some benchmarking, because calls to DBMS_RANDOM can be a little expensive if you heading to the pointy end of transaction rates. If you are serious about adopting TSID’s, then you would probably use one of the library implementations Vlad mentions in his article.

Perhaps one day we’ll have a native implementation directly in the database, but until then, these might get you started, and of course, don’t forget that we already have scalable sequences available in the database.