Skip to content

Universally Unique Lexicographically Sortable Identifier (ULID) for PostgreSQL

Notifications You must be signed in to change notification settings

geckoboard/pgulid

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

3 Commits
 
 
 
 

Repository files navigation

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

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

About

Universally Unique Lexicographically Sortable Identifier (ULID) for PostgreSQL

Resources

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published