Snowflake Sequences for Postgres

Table of Contents

• Snowflake Overview
• Building the Snowflake Extension
• Basic Configuration and Usage
• Creating a Snowflake Sequence
• Converting an Existing Postgres Sequence
• Using Snowflake Functions
• Reviewing Snowflake Meta-Data

Snowflake is a Postgres extension that provides an int8 and sequence based unique ID solution to optionally replace the Postgres built-in bigserial data type.

Installation

If you're installing Snowflake from source code, we assume that you're familiar with how to build standard Postgres extensions from source. Before building Snowflake:

1. Build a copy of Postgres in your preferred version.
2. Add pg_config to your $PATH environment variable.

Then, you can build the snowflake extension

cd contrib
git clone https://github.com/pgEdge/snowflake.git
cd snowflake
USE_PGXS=1 make
USE_PGXS=1 make install

After installing the Snowflake extension with your pgEdge deployment or from source code, connect to your Postgres database and create the extension with the command:

CREATE EXTENSION snowflake;

Configuring Snowflake

The Snowflake extension uses a custom GUC named snowflake.node that determines the node part of each Snowflake generated by the Postgres instance. The permitted values are 1 thru 1023.

This GUC has an invalid default value (on purpose); if not set, the Snowflake extension will throw an exception on a call to snowflake.nextval(). This is intended to prevent you from accidentally missing this GUC in your postgresql.conf file.

If you intend to use Snowflake in a multi-node, distributed, or replicated setup, it is important to set the GUC to a unique value for each Postgres instance. There is no protection in place to prevent assigning multiple Postgres instances in a multi-master cluster the same node number.