snowflake

Overview

works on pgedge kernel fork. Set snowflake.node (1..1023) before using snowflake.nextval().

Version

Build

You can build the RPM / DEB packages for snowflake using pig build:

pig build pkg snowflake         # build RPM / DEB packages

Install

You can install snowflake directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add repo and update cache

Install the extension using pig or apt/yum/dnf:

pig install snowflake;          # Install for current active PG version

pig ext install -y snowflake -v 18  # PG 18
pig ext install -y snowflake -v 17  # PG 17
pig ext install -y snowflake -v 16  # PG 16
pig ext install -y snowflake -v 15  # PG 15

dnf install -y pgedge-18       # PG 18
dnf install -y pgedge-17       # PG 17
dnf install -y pgedge-16       # PG 16
dnf install -y pgedge-15       # PG 15

apt install -y pgedge-18   # PG 18
apt install -y pgedge-17   # PG 17
apt install -y pgedge-16   # PG 16
apt install -y pgedge-15   # PG 15

Create Extension:

CREATE EXTENSION snowflake;

Usage

Sources:

• snowflake v2.5.0 README
• Creating a Snowflake sequence
• Converting PostgreSQL sequences
• Function reference
• v2.5.0 changelog

snowflake generates distributed bigint identifiers from a timestamp, a per-node identifier, and an in-millisecond counter. Existing PostgreSQL sequences can be converted so table defaults continue using nextval(...) while producing Snowflake IDs.

CREATE EXTENSION snowflake;

Configuration

Assign every writable node a distinct identifier in postgresql.conf, then reload PostgreSQL:

snowflake.node = 1

Reusing a node identifier on concurrently writable servers can generate duplicate IDs.

Convert a Sequence

Create a normal PostgreSQL sequence, then convert its definition. Existing values in referencing columns are not rewritten.

CREATE TABLE orders (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  payload jsonb NOT NULL
);

SELECT snowflake.convert_sequence_to_snowflake(
  pg_get_serial_sequence('orders', 'id')::regclass
);

INSERT INTO orders (payload) VALUES ('{"status":"new"}');
SELECT id, snowflake.format(id) FROM orders;

Functions

Examples

-- Generate a snowflake ID
SELECT snowflake.nextval();
-- 136169504773242881

-- Use an already converted named sequence
SELECT snowflake.nextval('orders_id_seq'::regclass);

-- Extract components
SELECT snowflake.get_epoch(136169504773242881);
-- 1704996539.845

SELECT to_timestamp(snowflake.get_epoch(136169504773242881));
-- 2024-01-11 13:08:59.845-05

SELECT snowflake.get_node(136169504773242881);
-- 1

SELECT snowflake.format(136169504773242881);
-- {"id": 1, "ts": "2024-01-11 13:08:59.845-05", "count": 0}

-- Use as default column
CREATE TABLE direct_ids (
  id int8 DEFAULT snowflake.nextval() PRIMARY KEY,
  data text
);

Review and Upgrade

Review converted table defaults with psql \d+ or the PostgreSQL catalogs. They should call snowflake.nextval(...) rather than the original nextval(...):

SELECT table_schema, table_name, column_name, column_default
FROM information_schema.columns
WHERE column_default LIKE 'snowflake.nextval(%';

Version 2.5.0 fixes dump/restore of converted sequences whose MAXVALUE was left at the normal bigint maximum. It also repairs conversion SQL for affected sequences and adds PostgreSQL 18 support.

Caveats

• Conversion changes the sequence definition, not IDs already stored in table rows.
• A Snowflake generator can emit at most 4096 counter values per millisecond. Do not configure a sequence increment above 4096.
• Keep the node identifier stable and unique for the lifetime of concurrent writers; record it as part of cluster provisioning and failover procedures.
• Install the same extension version on every node before logical replication or rolling changes involving converted sequence definitions.