gem-hunter
Language: C
A tiny Postgres extension to create version 7 UUIDs
Note
As of Postgres 18, there is a built in uuidv7() function, however it does
not include all of the functionality below.
A tiny Postgres extension to create valid version 7 UUIDs in Postgres. Supports Postgres 13 through 18.
These are regular Postgres UUIDs, so they can be used as primary keys, converted to and from strings, included in indexes, etc:
SELECT uuid_generate_v7(); uuid_generate_v7 -------------------------------------- 018570bb-4a7d-7c7e-8df4-6d47afd8c8fc (1 row)
The timestamp component of these UUIDs can be extracted:
SELECT uuid_v7_to_timestamptz('018570bb-4a7d-7c7e-8df4-6d47afd8c8fc'); uuid_v7_to_timestamptz ---------------------------- 2023-01-02 04:26:40.637+00 (1 row)
Timestamps can be converted to v7 UUIDs:
SELECT uuid_timestamptz_to_v7('2023-01-02 04:26:40.637+00'); uuid_timestamptz_to_v7 -------------------------------------- 018570bb-4a7d-7630-a5c4-89b795024c5d (1 row) -- for date range queries set the second argument to true to zero the random bits SELECT uuid_timestamptz_to_v7('2023-01-02 04:26:40.637+00', true); uuid_timestamptz_to_v7 -------------------------------------- 018570bb-4a7d-7000-8000-000000000000 (1 row)
uuid_generate_v7() is as fast as the native gen_random_uuid() function. See
the benchmarks for more details.
Version 7 UUIDs have a few advantages. They include a 48-bit Unix timestamp with millisecond accuracy and will overflow far in the future (10899 AD). They also include 74 random bits which means billions can be created every second without collisions. Because of their structure they are globally sortable and can be created in parallel in a distributed system.
Important
These instructions are for x86_64 Linux. On other architectures (e.g. Apple M1, Raspberry Pi, etc.) follow the build instructions instead.
.tar.gz release
and extract it to a temporary directorypg_uuidv7.so for your Postgres version into the Postgres module
directorypg_uuidv7--1.7.sql and pg_uuidv7.control into the Postgres extension
directoryCREATE EXTENSION pg_uuidv7;# example shell script to install pg_uuidv7 cd "$(mktemp -d)" curl -LO "https://github.com/fboulnois/pg_uuidv7/releases/download/v1.7.0/{pg_uuidv7.tar.gz,SHA256SUMS}" tar xf pg_uuidv7.tar.gz sha256sum -c SHA256SUMS PG_MAJOR=$(pg_config --version | sed 's/^.* \([0-9]\{1,\}\).*$/\1/') cp "$PG_MAJOR/pg_uuidv7.so" "$(pg_config --pkglibdir)" cp pg_uuidv7--1.7.sql pg_uuidv7.control "$(pg_config --sharedir)/extension" psql -c "CREATE EXTENSION pg_uuidv7;"
pg_uuidv7 only requires the libpq headers and Postgres extension tools to
build the code. On Debian, these headers are included in the libpq-dev and
postgresql-server-dev-all packages.
To build the code run make. To install the extension locally run make install.
A Dockerfile is available to build the code using the official
Postgres Docker image:
docker build . --tag pg_uuidv7A prebuilt x86_64 version of this image is on GitHub:
docker pull ghcr.io/fboulnois/pg_uuidv7:1.7.0
The prebuilt image is similar
to a vanilla Postgres instance so the extension needs to be enabled manually or
with an initialization script with CREATE EXTENSION pg_uuidv7;.
A separate Dockerfile is available to build the extension
against a specific version of Postgres and run the regression tests:
docker build . --file test/Dockerfile --tag pgxn-test docker run --rm -it pgxn-test /bin/sh # once in container pg-start 18 pg-build-test # optionally run the benchmarks cd test/benchmark ./benchmark.sh postgres
See the CONTRIBUTING.md file for more details. In short, follow the style guidelines, agree to the Developer Certificate of Origin, and submit a PR.