A snowflake algorithm implemented as a Postgres Extension. It uses time as the first component and remains sortable. IDs are 64 bits, half the size of a UUID.
The ID is composed of:
- Time - 41 bits (millisecond precision with a custom epoch gives us 69 years)
- Instance ID - 10 bits - gives us up to 1024 postgres instances
- Sequence number - 12 bits - rolls over every 4096 per machine (with protection to avoid rollover in the same ms)
You should use NTP to keep your system clock accurate. It protects from non-monotonic clocks, i.e., clocks that run backwards.
- A standard PostgreSQL 12 or 11.4+ installation with a development environment (header files) (e.g.,
postgresql-server-dev-11package for Linux, Postgres.app for MacOS) - C compiler (e.g., GCC or Clang)
git clone https://github.com/mausimag/pgflake.git
cd pgflake
make
sudo make installYou must configure the following two parameters before using:
pgflake.instance_id: Sets the ID of the current instance. It must be unique between instances.
pgflake.start_epoch: Sets the start epoch of the current instance.
To activate the extension, in a database you can simply do:
CREATE EXTENSION IF NOT EXISTS pgflake;
Create one table:
CREATE TABLE ttest (
x bigint,
y bigint NOT NULL DEFAULT pgflake_generate()
);
Now insert some data:
INSERT INTO ttest (x) VALUES (generate_series(1, 5));
Check generated IDs:
SELECT * FROM ttest;
x | y
----+---------------------
1 | 1231425388031266816
2 | 1231425388610080768
3 | 1231425388626857984
4 | 1231425388631052288
5 | 1231425388635246592
(5 rows)
You can use the following functions to extract information from an ID:
SELECT
x,
y,
pgflake_extract_time(y) as t, -- returns creation time of id
pgflake_extract_instance(y) as i, -- returns instance number
pgflake_extract_sequence(y) as s -- returns the sequence number
FROM ttest;
