modfin / pg-xid

pg-xid is a globally unique id generator for postgres

XID for Postgres, Globally Unique ID Generator

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/

Xid layout

0	1	2	3	4	5	6	7	8	9	10	11
time				machine id			pid		counter

• 4-byte value representing the seconds since the Unix epoch,
• 3-byte machine identifier,
• 2-byte process id, and
• 3-byte counter, starting with a random value.

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

• Size: 12 bytes (96 bits), smaller than UUID, larger than Twitter Snowflake
• Base32 hex encoded by default (20 chars when transported as printable string, still sortable)
• Configuration free: there is no need to set a unique machine and/or data center id
• K-ordered
• Embedded time with 1 second precision
• Unicity guaranteed for 16,777,216 (24 bits) unique ids per second and per host/process
• Lock-free (unlike UUIDv1 and v2)

Comparison

Name	Binary Size	String Size	Features
UUID	16 bytes	36 chars	configuration free, not sortable
shortuuid	16 bytes	22 chars	configuration free, not sortable
Snowflake	8 bytes	up to 20 chars	needs machine/DC configuration, needs central server, sortable
MongoID	12 bytes	24 chars	configuration free, sortable
xid	12 bytes	20 chars	configuration 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
('[email protected]'),
('[email protected]');

SELECT * FROM users;

-- +--------------------+-----------------+
-- |id                  |email            |
-- +--------------------+-----------------+
-- |c96r9eo8u0k0b3e6hgcg|[email protected]|
-- |c96r9eo8u0k0b3e6hgd0|[email protected]|
-- +--------------------+-----------------+

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.

pg-xid's Issues

Where is the extension stored?

I'm wondering where the extension is stored in the database itself.

I'm wanting to use this extension for an app, but the database will be served with containers, and the container and host will be swapped out fairly often, but the database storage will be retained of course.

From what I can tell by looking at xid.sql, and based on how you have to specify a database during install, it looks like everything is installed at the database level, with nothing else to worry about.

Just wanting to confirm that is the case.

Thanks!

[question] Is it safe to call this library multiple times

I see that it is setting xid_serial with setval which will override previous value is that ok or care needs to be taken to ensure that it is only initialized once?

Performance Question

First of all, thank you for your awesome work for xid.

I guess using CHAR(20) will perform slowly in terms of sorting and indexing compared to using UUID type and have some questions here.

• Is this okay to use in production-level apps? (In terms of performance)
• Would it perform better when we use raw byte data type instead of CHAR(20) in terms of sorting, and indexing?

How to add a custom prefix?

Hi,

Its a great work that I have been searching for long time. A feature request here: so, Is it possible to add a custom prefix? It is still taking 20chars even if I changed the domain size to public.xid AS CHAR(25) . And then I should be able to default to a custom prefix for a table. Any help is appreciated.