Home

Awesome

XID for Postgres, Globally Unique ID Generator

license

This project is a Postgres implementation of the Go Lang library found here: https://github.com/rs/xid

Description

Xid is a globally unique id generator functions. They are small and ordered.

Xid uses the Mongo Object ID algorithm to generate globally unique ids with a different serialization (base32) to make it shorter when transported as a string: https://docs.mongodb.org/manual/reference/object-id/

<table border="1"> <caption>Xid layout</caption> <tr> <td>0</td><td>1</td><td>2</td><td>3</td><td>4</td><td>5</td><td>6</td><td>7</td><td>8</td><td>9</td><td>10</td><td>11</td> </tr> <tr> <td colspan="4">time</td><td colspan="3">machine id</td><td colspan="2">pid</td><td colspan="3">counter</td> </tr> </table>

The binary representation of the id is compatible with Mongo 12 bytes Object IDs. The string representation is using base32 hex (w/o padding) for better space efficiency when stored in that form (20 bytes). The hex variant of base32 is used to retain the sortable property of the id.

Xids simply offer uniqueness and speed, but they are not cryptographically secure. They are predictable and can be brute forced given enough time.

Features

Comparison

NameBinary SizeString SizeFeatures
UUID16 bytes36 charsconfiguration free, not sortable
shortuuid16 bytes22 charsconfiguration free, not sortable
Snowflake8 bytesup to 20 charsneeds machine/DC configuration, needs central server, sortable
MongoID12 bytes24 charsconfiguration free, sortable
xid12 bytes20 charsconfiguration free, sortable

Installation

cat ./xid.sql | psql your-database

Usage

Generate a xid as base32Hex

SELECT xid() ;

-- +--------------------+
-- |xid                 |
-- +--------------------+
-- |c96r3a88u0k0b3e6hft0|
-- +--------------------+

Generate a xid as a base32Hex at a specific time

SELECT xid, xid_time(xid) FROM(
  SELECT xid( _at => CURRENT_TIMESTAMP - INTERVAL '10 YEAR')
) a

-- +--------------------+---------------------------------+
-- |xid                 |xid_time                         |
-- +--------------------+---------------------------------+
-- |9tvgr208u0k0b3e6hgb0|2012-04-06 15:36:40.000000 +00:00|
-- +--------------------+---------------------------------+

Use a xid as column type


CREATE TABLE users (
    id public.xid PRIMARY KEY DEFAULT xid(),
    email text
);

INSERT INTO users (email)
VALUES
('user1@example.com'),
('user2@example.com');

SELECT * FROM users;

-- +--------------------+-----------------+
-- |id                  |email            |
-- +--------------------+-----------------+
-- |c96r9eo8u0k0b3e6hgcg|user1@example.com|
-- |c96r9eo8u0k0b3e6hgd0|user2@example.com|
-- +--------------------+-----------------+


Decode a xid as byte array

SELECT xid_decode(xid())
-- +---------------------------------------+
-- |xid_decode                             |
-- +---------------------------------------+
-- |{98,77,178,53,8,240,40,5,141,198,140,2}|
-- +---------------------------------------+

Encode byte array as xid

SELECT xid_encode('{98,77,178,53,8,240,40,5,141,198,140,2}'::INT[]);
-- +---------------------+
-- |xid_encode           |
-- +---------------------+
-- |c96r4d88u0k0b3e6hg10 |
-- +---------------------+


Inspect a xid

SELECT id, xid_time(id), xid_machine(id), xid_pid(id), xid_counter(id) 
FROM (
    SELECT xid() id FROM generate_series(1, 3)
) a

-- +--------------------+---------------------------------+-----------+-------+-----------+
-- |id                  |xid_time                         |xid_machine|xid_pid|xid_counter|
-- +--------------------+---------------------------------+-----------+-------+-----------+
-- |c96r2ho8u0k0b3e6hfr0|2022-04-06 15:27:03.000000 +00:00|{8,240,40} |1421   |13011958   |
-- |c96r2ho8u0k0b3e6hfrg|2022-04-06 15:27:03.000000 +00:00|{8,240,40} |1421   |13011959   |
-- |c96r2ho8u0k0b3e6hfs0|2022-04-06 15:27:03.000000 +00:00|{8,240,40} |1421   |13011960   |
-- +--------------------+---------------------------------+-----------+-------+-----------+

Test

To run the tests you'll need to install docker along with go. Run go test -v ./xid_test.go

Licenses

The source code is licensed under the MIT License.