Introduction

The uuidv7 package is a PL/PgSQL TLE package that provides support for generating v7 UUIDs in Postgres. It is based on a script by Fabio Lima (https://gist.github.com/fabiolimace/515a0440e3e40efeb234e12644a6a346).

It is recommended to use v7 UUIDs if you use UUIDs as a synthetic key column on a table, particularly if you use them as a clustering key. All UUID types in the uuid-ossp package that ships with a default Postgres installation are unsuitable for this purpose, as they are not monotonically increasing, and therefore cause performance degradation.

Installation

This package is packaged as a TLE. Install via

Copyselect dbdev.install('cem-uuidv7');
create extension "cem-uuidv7"
schema extensions
version '1.0.2';

Usage

Generate a single UUID:
select extensions.uuid_generate_v7();

Use an UUID as the primary key column for a table:
create table foo (foo_id uuid primary key default extensions.uuid_generate_v7());