Start with the queries your application needs
Imagine a project tracker with two requirements: open a project from its public URL, and show the most recently created projects first. A CUID2 can be the lookup key, but it should not carry the responsibility for chronology. The CUID2 maintainers explicitly distinguish the format from sequential identifiers. Source: CUID2 documentation.
For this example, every new ID is exactly 24 lowercase alphanumeric characters and starts with a letter. That is an application contract. If you choose a different length, revisit both the schema and the collision estimate before changing the generator.
Give each constraint one job
CREATE TABLE project (
id text COLLATE "C" PRIMARY KEY
CHECK (id ~ '^[a-z][a-z0-9]{23}$'),
name text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);The primary key rejects duplicates and nulls. The check constraint enforces this application's chosen shape. Neither proves that a string came from CUID2: a deliberately constructed string can satisfy the check. Generate candidates in application code and let the database arbitrate uniqueness when a write occurs. Source: PostgreSQL constraints.
We use text with a separate check so the accepted format is visible in one place. varchar(24) sets an upper bound, not an exact length. PostgreSQL's char(24) is blank-padded; choosing it just because the ID has a fixed length introduces behavior this example does not need. The explicit C collation keeps this identifier comparison and pattern check independent of a database's language-specific ordering. Character types and collation support explain those database choices.
On an insert conflict, first determine which constraint failed. A duplicate project ID and a duplicate business field such as an email address need different responses. A retry of an existing request also needs an idempotency policy; blindly generating a new ID may create a second business record.
Model relationships and time explicitly
CREATE TABLE task (
id text COLLATE "C" PRIMARY KEY
CHECK (id ~ '^[a-z][a-z0-9]{23}$'),
project_id text COLLATE "C" NOT NULL REFERENCES project(id),
title text NOT NULL
);
CREATE INDEX ON task (project_id);
CREATE INDEX ON project (created_at DESC, id DESC);
SELECT id, name, created_at
FROM project
ORDER BY created_at DESC, id DESC
LIMIT 20;The foreign key prevents orphaned tasks. Its index supports lookups by parent; PostgreSQL does not automatically index the referencing column. The project index matches the requested ordering. The ID is a deterministic tiebreaker for equal timestamps, not a second clock. Foreign keys, sorting rows, and multicolumn indexes document the underlying behavior.
Our created_at records a database-side creation time. An imported event may need its own occurrence timestamp. Decide which one “recent” means in your product, and keep that decision explicit in the query. Do not infer it from the ID or from whatever row order happens to appear without ORDER BY.
Run a small, inspectable experiment
The example was tested with PostgreSQL 14 using constructed IDs to exercise the schema constraints. Download cuid2-postgresql.sql and run it against a disposable database:
psql -X -v ON_ERROR_STOP=1 -f cuid2-postgresql.sql YOUR_TEST_DATABASEThe script creates temporary tables inside a transaction and rolls it back. It inserts an older project with an ID starting with z and a newer one starting with a. The chronological query must return “Newer project” first. It also fails if a duplicate, a short ID, an uppercase ID, or an orphaned relationship is accepted. A successful run ends with “CUID2 PostgreSQL: constraints, relationship, and ordering checks passed.”
This verifies behavior, not speed. To decide between a text primary key and a separate internal numeric key, measure your own insert workload, joins, index size, and common reads at a representative scale. That extra numeric key also creates a second identifier to maintain. The identifier selection guide helps frame the decision; this example makes no performance ranking.