snowflake

Overview

Bundled with pgEdge 15.19/16.15/17.11/18.6; set snowflake.node.

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.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.