Universally Unique Lexicographically Sortable Identifier

A PostgreSQL (pgcrypto) implementation of alizain/ulid based on OK Log's Go port.

Background

A GUID/UUID can be suboptimal for many use-cases because:

• It isn't the most character efficient way of encoding 128 bits
• UUID v1/v2 is impractical in many environments, as it requires access to a unique, stable MAC address
• UUID v3/v5 requires a unique seed and produces randomly distributed IDs, which can cause fragmentation in many data structures
• UUID v4 provides no other information than randomness which can cause fragmentation in many data structures

A ULID however:

• Is compatible with UUID/GUID's
• 1.21e+24 unique ULIDs per millisecond (1,208,925,819,614,629,174,706,176 to be exact)
• Lexicographically sortable
• Canonically encoded as a 26 character string, as opposed to the 36 character UUID
• Uses Crockford's base32 for better efficiency and readability (5 bits per character)
• Case insensitive
• No special characters (URL safe)
• Monotonic sort order (correctly detects and handles the same millisecond)

Usage

SELECT generate_ulid(); -- Output: 01D45VGTV648329YZFE7HYVGWC
SELECT parse_ulid_timestamp('01D45VGTV648329YZFE7HYVGWC'); -- Output: 2019-02-20 16:23:49.35+00

Specification

Below is the current specification of ULID as implemented in this repository.

Components

• Timestamp
  • 48 bits
  • UNIX-time in milliseconds
  • Won't run out of space till the year 10895 AD
• Entropy
  • 80 bits

String Representation

 01AN4Z07BY      79KA1307SR9X4MV3
|----------|    |----------------|
 Timestamp           Entropy
  10 chars           16 chars
   48bits             80bits
   base32             base32