pg_uuid_v8

Overview

Upstream 1.1.0 ships on PGXN only; pinned to public so uuid operator commutators resolve on PostgreSQL 17 and 18.

Version

Build

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

pig build pkg pg_uuid_v8         # build RPM / DEB packages

Install

You can install pg_uuid_v8 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 pg_uuid_v8;          # Install for current active PG version

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

dnf install -y pg_uuid_v8_18       # PG 18
dnf install -y pg_uuid_v8_17       # PG 17
dnf install -y pg_uuid_v8_16       # PG 16
dnf install -y pg_uuid_v8_15       # PG 15
dnf install -y pg_uuid_v8_14       # PG 14

apt install -y postgresql-18-pg-uuid-v8   # PG 18
apt install -y postgresql-17-pg-uuid-v8   # PG 17
apt install -y postgresql-16-pg-uuid-v8   # PG 16
apt install -y postgresql-15-pg-uuid-v8   # PG 15
apt install -y postgresql-14-pg-uuid-v8   # PG 14

Create Extension:

CREATE EXTENSION pg_uuid_v8;

Usage

Sources:

• pg_uuid_v8 1.1.0 on PGXN
• pg_uuid_v8 1.1.0 README
• pg_uuid_v8 1.1.0 control file
• pg_uuid_v8 1.0 base SQL
• pg_uuid_v8 1.0 to 1.1 upgrade SQL
• Pigsty pg_uuid_v8 package matrix

pg_uuid_v8 1.1.0 generates UUID values with UUID-v4 version and variant bits while embedding an obfuscated creation time in the random payload. Its uuid_v8_* convenience functions mirror the lower-level uuid_stego_* API. Use it when hidden time extraction and time-range indexing are useful, but do not treat the embedded value as an authentication token or a substitute for a separate trusted creation timestamp.

Generate Values

CREATE EXTENSION pg_uuid_v8;

SELECT uuid_v8_set_seed('replace-with-a-unique-secret');
SELECT uuid_v8_set_encryption_mode('AES128');

CREATE TABLE events (
  id uuid PRIMARY KEY DEFAULT uuid_v8_generate(),
  data jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO events(data) VALUES ('{"type":"login"}');

The upstream implementation defaults to a published built-in seed and XOR mode. Set a deployment-specific secret before generating values. AES128 and AES256 are also available, but the same seed and mode must be selected when extracting a value.

Extract and Index the Hidden Time

SELECT
  uuid_v8_extract_timestamp(id) AS epoch_microseconds,
  stego_time_to_timestamp(uuid_v8_extract_timestamp(id)) AS created_time
FROM events;

CREATE INDEX events_uuid_time_idx
ON events USING btree (uuid_v8_extract_timestamp(id));

SELECT *
FROM events
WHERE uuid_v8_extract_timestamp(id)
      BETWEEN timestamp_to_stego_time('2026-01-01'::timestamptz)
          AND timestamp_to_stego_time(now())
ORDER BY uuid_v8_extract_timestamp(id);

uuid_v8_extract_timestamp(uuid) returns a microsecond-scaled bigint so it remains compatible with timestamp_to_stego_time() and stego_time_to_timestamp(). In version 1.1 the internal 48-bit field stores milliseconds, so the returned value has millisecond resolution and its last three decimal digits are zero.

uuid_stego_in_range() offers a boolean timestamp-range helper. A functional B-tree index on the extraction function is the explicit and predictable path for indexed time predicates.

Compare Hidden Times

uuid_v8_compare(uuid, uuid) and uuid_stego_compare(uuid, uuid) return ordering by extracted hidden time. The extension also defines <, <=, >, and >= operators for UUID arguments.

Pigsty packages install these added operators in public and qualify their commutator and negator references for PostgreSQL 17 and 18 compatibility. PostgreSQL already has built-in UUID ordering operators, so use the comparison functions or a schema-qualified OPERATOR(public.<) expression when hidden-time semantics must be unambiguous.

Seed and Mode Controls

SELECT uuid_v8_set_seed('replace-with-a-unique-secret');
SELECT uuid_v8_get_seed();

SELECT uuid_v8_set_encryption_mode('XOR');
SELECT uuid_v8_set_encryption_mode('AES128');
SELECT uuid_v8_set_encryption_mode('AES256');
SELECT uuid_v8_get_encryption_mode();

ALTER SYSTEM SET uuid_v8.encryption_mode = 'AES128';
SELECT pg_reload_conf();

The seed is exposed as uuid_v8.stego_seed and the mode as uuid_v8.encryption_mode. Setter functions change the current session; configuration settings can establish defaults for later sessions. uuid_v8_get_seed() returns the active seed, so restrict database access accordingly and never log its result.

Upgrade and Compatibility Boundaries

ALTER EXTENSION pg_uuid_v8 UPDATE TO '1.1';

Version 1.1 changes timestamp storage from microseconds to milliseconds. The old 48-bit microsecond field rolled over about every 8.9 years and could not reliably recover current absolute dates; the 48-bit millisecond field lasts about 8,925 years. Relative ordering of pre-1.1 values was unaffected, but absolute time extraction and range predicates for those existing values remain unreliable after the upgrade because their encoded representation is not rewritten.

The PGXN metadata targets PostgreSQL 12 or later; current Pigsty packages cover PostgreSQL 14–18. Pigsty packages pin the extension to public and make it non-relocatable so the added operators resolve consistently. Keep an ordinary created_at column when provenance, auditability, sub-millisecond precision, or migrations across seeds and modes matter.