snowflake

snowflake : Snowflake-style 64-bit ID generator and sequence utilities for PostgreSQL

Overview

Packages

Source

pig build pkg snowflake;		# build rpm/deb

Install

Make sure PGDG and PIGSTY repo available:

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

Install this extension with pig:

pig install snowflake;		# install via package name, for the active PG version

pig install snowflake -v 18;   # install for PG 18
pig install snowflake -v 17;   # install for PG 17
pig install snowflake -v 16;   # install for PG 16
pig install snowflake -v 15;   # install for PG 15

Create this extension with:

CREATE EXTENSION snowflake;

Usage

Sources:

• snowflake v2.6.0 README
• Creating a Snowflake sequence
• Converting PostgreSQL sequences
• Function reference
• v2.6.0 changelog
• v2.6.0 C implementation
• Tagged SQL API definitions
• v2.6.0 control file

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();

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

-- Decode a fixed example: node 1, count 0
SET TIME ZONE 'UTC';
SELECT snowflake.get_epoch(136169504773246976);
-- 1704996539.845

SELECT to_timestamp(snowflake.get_epoch(136169504773246976));
-- 2024-01-11 18:08:59.845+00

SELECT snowflake.get_node(136169504773246976);
-- 1

SELECT snowflake.get_count(136169504773246976);
-- 0

SELECT snowflake.format(136169504773246976);
-- {"id": 1, "ts": "2024-01-11 18:08:59.845+00", "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.

Version 2.6.0 adds PostgreSQL 19 support; the current Pigsty pgEdge bundle covers PostgreSQL 15–18. Assign snowflake.node within 1–1023; its default is intentionally invalid. The control file fixes schema snowflake and does not require shared preload. The 2.5.0 dump/restore repair remains relevant when upgrading converted sequences from earlier releases.

Use an administrator to install the C extension and convert identity definitions. ID generation requires USAGE or UPDATE on the selected sequence; the current value is defined only after that sequence has generated an ID in the same session.