Technical Overview

Sortable, time-ordered IDs minted in SQL

tsid() mints identifiers that sort chronologically by plain comparison — the same property UUIDv7 and ULID provide — without leaving SQL or wiring an application-side library into your pipeline.

What this extension is for

DuckDB ships uuid() — a UUIDv4 generator, random and unsortable. TSID fills the gap: a generator whose output sorts by creation time when compared as text. The same shape PostgreSQL users get from gen_random_uuid() plus now(), except the timestamp is embedded in the ID itself and recoverable.

• Surrogate primary keys that sort by insert time: Use tsid() as a column DEFAULT. ORDER BY id returns insert order — no separate created_at index needed for chronological scans.
• Audit-log and event identifiers: B-tree friendly: new rows append at the high end of the index instead of scattering randomly the way uuid() v4 does. Reduces page splits and improves cache locality on append-mostly tables.
• Time-range pagination cursors: Because IDs are sortable strings, you can paginate by WHERE id < :cursor ORDER BY id DESC without joining a timestamp index — the Twitter snowflake trick, in pure SQL.
• Embedded timestamp recovery: tsid_to_timestamp decodes the leading bits back to a TIMESTAMP. One column does the work of two.

How it works

TSIDs are 128-bit identifiers rendered as 32 lowercase hexadecimal characters — the leading bits are a high-resolution timestamp, the trailing bits are random. The format is conceptually the same family as UUIDv7 and ULID; the rendering differs.

• Output type is VARCHAR, not BIGINT: tsid() returns a 32-character hex VARCHAR, the same width as a UUID hex string. Plain string comparison gives you time order — no special operator or cast required.
• Independent calls per row: Each tsid() call is independent — every row in SELECT tsid() FROM range(N) produces a fresh TSID. Within the millisecond resolution of the timestamp prefix, exact insertion order across rows of a parallel-evaluated query isn't guaranteed; if a strict total order matters, sort by an explicit created_at column rather than by the ID.
• Embedded timestamp: tsid_to_timestamp decodes the leading bits to a TIMESTAMP with sub-millisecond precision. Same primitive UUIDv7-aware tools use to extract creation time from a v7 UUID.
• No external dependencies: The extension generates IDs in-process — no network call, no clock-server coordination. Safe to use as a column DEFAULT in CREATE TABLE without latency surprises.

TSID vs UUIDv4 vs UUIDv7 vs ULID

All four occupy the same niche — opaque, unique, application-mintable IDs. The differences are about ordering, rendering, and standardization.

• vs UUIDv4 (DuckDB built-in uuid()): UUIDv4 is random — great for collision avoidance, terrible for B-tree locality and unusable for time-ordered pagination. TSID wins anywhere insert order, recent-first scans, or index-append behavior matters.
• vs UUIDv7 (RFC 9562): UUIDv7 is the IETF-standardized time-sortable UUID. Functionally TSID and UUIDv7 cover the same ground; UUIDv7 wins on portability (every modern UUID library understands it), TSID wins because DuckDB ships v4 only and TSID gives you the v7-shaped behavior without bringing your own generator.
• vs ULID (ulid/spec): ULID is the same idea — millisecond-precision time prefix plus random tail — rendered in 26-character Crockford base32. TSID's hex rendering is two characters wider but lines up cleanly with how DuckDB users already format UUIDs, and avoids Crockford-base32 ambiguity edge cases.

Caveats

Things to know before reaching for TSID over a built-in UUID.

• TSIDs leak creation time: The timestamp is recoverable by anyone holding the ID — that's the feature, but it's also a privacy and competitive-intelligence concern. Don't use TSIDs as public-facing identifiers for resources where the creation moment is sensitive (signup time, order placement, account creation). uuid() v4 stays the right choice for opaque external IDs.
• Not an IETF or de-facto standard: Unlike UUIDv7 (RFC 9562) or ULID (widely adopted spec), the TSID rendering is library-specific. The conceptual primitive — time prefix + random tail — is portable; the exact 32-hex layout produced here may not parse with TSID libraries from other ecosystems such as f4b6a3/tsid-creator. Stay self-contained, or pin the format you serialize to disk.
• VARCHAR storage, not BINARY: Each TSID occupies 32 bytes as text — twice the 16 bytes a binary UUID column needs in some engines. DuckDB's dictionary encoding compresses this well in practice, but plan for it on multi-billion-row tables where every byte counts.
• Version pinning: The exposed surface is small (tsid, tsid_to_timestamp), but production schemas can depend on the persistent hex layout. Pin a known-good extension and DuckDB version when those identifiers are stored long-term.

Common Use Cases

Primary keys for append-mostly tables

Use tsid() as DEFAULT on the primary-key column of an event log, audit table, or message store. New rows append at the index head, ORDER BY id is ORDER BY created_at, and you can drop a redundant timestamp column.

Recent-first pagination without timestamp join

Paginate by WHERE id < :cursor ORDER BY id DESC LIMIT N — no separate created_at index, no OFFSET cost on large tables. The Twitter-snowflake / cursor-pagination pattern, available in plain DuckDB.

Drop a `created_at` column

When the only thing a created_at column does is record insertion time, replace it with a TSID primary key and recover the timestamp via tsid_to_timestamp when you need it. One column instead of two.

Replacement for `uuid()` where ordering matters

Anywhere you'd reach for uuid() but actually wanted UUIDv7-style ordering, tsid() is the in-DuckDB equivalent. No application-side generator, no client library.

Deep Dive

Technical Details

What you can do with one query

The shortest path from “I need a primary key default” to “rows that already sort by insert time”:

CREATE TABLE events (
  id      VARCHAR DEFAULT tsid() PRIMARY KEY,
  payload JSON
);

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

-- Insertion order, no separate created_at column
SELECT id,
       tsid_to_timestamp(id) AS created_at,
       payload
FROM events
ORDER BY id;

tsid() returns a 32-character hex VARCHAR whose leading bits are a high-resolution timestamp. Plain string compare gives you time order — no special operator, no created_at join. tsid_to_timestamp decodes the leading bits back to a TIMESTAMP when you need the wall-clock value.

TSIDs reveal creation time — by design, and that's a tradeoff

The headline feature is also the headline caveat: the timestamp embedded in a TSID is recoverable by anyone holding the ID. That’s perfect for an internal primary key, an audit log, or a server-side pagination cursor. It’s a leak when the ID surfaces externally — a public order URL, a referral link, a webhook payload — and the creation moment is competitively or personally sensitive (signup time, account age, when an order was placed).

For internal-only IDs, use tsid(). For public-facing identifiers where ordering doesn’t matter, stick with DuckDB’s built-in uuid() (UUIDv4 — random, opaque, leaks nothing).

TSID vs the alternatives

DuckDB ships uuid() — a UUIDv4 generator. It’s random and unsortable, which is the right call for opaque external IDs and the wrong call when insertion order matters. TSID, UUIDv7, and ULID are three renderings of the same idea: high-resolution time prefix plus random tail.

	DuckDB `uuid()`	`tsid()`	UUIDv7	ULID
Time-sortable	No	Yes	Yes	Yes
Embeds creation time	No	Yes	Yes	Yes
Rendering	UUID hex (36 chars w/ dashes)	32 hex chars	UUID hex (36 chars w/ dashes)	26 chars Crockford base32
Standardized	RFC 9562	Library-specific	RFC 9562	ulid/spec
In-DuckDB built-in	Yes	This extension	No	No

Pick tsid() when you want UUIDv7-shaped behavior inside SQL today without wiring a v7 generator into your client. If you control both ends of a system that already produces UUIDv7s elsewhere, keep using those — interop wins. If your IDs only need to make sense within DuckDB, TSID stays self-contained.

The reference TSID concept also lives in non-DuckDB form as f4b6a3/tsid-creator on the JVM and similar libraries in other ecosystems; the rendering produced by this extension is library-specific, so don’t assume cross-ecosystem parsing without testing.

Why time-sortable IDs help the index

A B-tree primary-key index on a UUIDv4 column scatters writes uniformly across the tree — every insert touches a different page, page splits cascade, and the cache works against you. A time-sortable ID concentrates writes at one end (the most recent leaf), so:

New rows append next to each other in physical layout
Recent-first pagination (ORDER BY id DESC LIMIT N) hits a small hot range
Bulk loads compact better — adjacent rows tend to be adjacent in time

This is the same argument that drove UUIDv7 into RFC 9562. TSID brings the property to DuckDB SQL without the client-side generator.

Bulk generation and ordering

Inside one query, parallel evaluation of tsid() can produce IDs in non-deterministic order — same wall-clock millisecond, different threads, different sub-millisecond entropy. Pass a per-row seed to keep them strictly increasing:

-- Strictly increasing within the query
SELECT tsid() AS id
FROM range(1000)
ORDER BY id;

The seed pins the sub-millisecond bits, so the timestamp prefix dominates ordering and the IDs sort cleanly. For one-off inserts (DEFAULT tsid() on a primary key), you don’t need a seed — there’s only one call per row.

When to keep `uuid()` instead

Public-facing IDs where leaking creation time matters — order URLs, share links, account references.
Cross-system identifiers where a peer expects a canonical UUID format and you don’t control the consumer.
You don’t care about insertion order and the table is small enough that index locality is irrelevant.

In every other case where uuid() was the reach-for default — internal primary keys, event logs, audit tables — tsid() is the better fit.

Install

INSTALL tsid FROM community;
LOAD tsid;

Quick Start

Single TSID

SELECT tsid();

Recover the embedded timestamp

SELECT tsid_to_timestamp(tsid()) AS created_at;

Bulk generation

SELECT tsid() AS id FROM range(1000);

Reference

Extension Contents

Quick reference to all available functions and settings organized by category.

Filter extension contents

Name	Type	Description
Generation Mint a new TSID. Calling `tsid()` repeatedly produces strictly increasing values (by string compare) suitable as primary keys.
tsid()	Scalar function	Generate a new Time-Sorted Unique Identifier — a 32-character hex string.
Inspection Extract the timestamp embedded in a TSID. Lets you recover insertion time without storing a separate timestamp column.
tsid_to_timestamp()	Scalar function	Extract the embedded timestamp from a TSID.

API Reference

Function Documentation

Practical Examples

Cookbook

Real-world recipes and patterns for common use cases.

Generate IDs

-- Single TSID
SELECT tsid();
-- '675716e86985495e9cf575f0b9c4a8db'

-- Bulk: 100 unique, time-sorted IDs
SELECT tsid() AS id FROM range(100);

Use as a primary key

TSIDs sort chronologically by ordinary string comparison, so ORDER BY id gives you insert order for free:

CREATE TABLE events (
  id      VARCHAR DEFAULT tsid() PRIMARY KEY,
  payload JSON
);

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

-- Listed in insertion order without a separate timestamp column
SELECT id, payload FROM events ORDER BY id;

Recover the timestamp

You don’t need a separate created_at — the timestamp is embedded:

SELECT id,
       tsid_to_timestamp(id) AS created_at
FROM events
ORDER BY id;

Time-range pagination

Because TSIDs are sortable strings, you can paginate by ID without a separate timestamp index:

-- Page 1: 100 most recent
SELECT * FROM events ORDER BY id DESC LIMIT 100;

-- Page 2: next 100, using the last id from page 1 as a cursor
SELECT * FROM events
WHERE id < :last_id_from_page_1
ORDER BY id DESC LIMIT 100;

This is the same trick Twitter snowflake-style IDs enable — you avoid the offset cost on large tables.

TSID vs UUID

	TSID	UUIDv4	UUIDv7
Time-sortable	✅	❌	✅
Width on disk	16 bytes hex (32 chars)	16 bytes hex (32 chars)	16 bytes hex (32 chars)
Embeds creation time	✅	❌	✅
Cross-system uniqueness	✅	✅	✅

UUIDv7 (also time-sortable) is the closest standard alternative. Pick TSID when you want the integration with DuckDB’s hash type and bulk-generation throughput.

Platform Support

Compatibility

Extension availability may vary by platform and DuckDB version. Check below to ensure this extension supports your environment before installation.

Quick Facts

Release status Stable

Software License MIT

Pricing Free

Written In C++

Source Available Yes

View on GitHub

Usage

31,270

loads · last 90 days

Platforms

Linux x86_64 aarch64
Linux (musl) Not available
macOS Intel Apple Silicon
Windows x86_64
WASM eh mvp threads

Compiled binary sizes

Platform	Architecture	Size
Linux	x86_64	3.20 MB
Linux	aarch64	2.84 MB
macOS	Intel	1.45 MB
macOS	Apple Silicon	1.31 MB
Windows	x86_64	7.38 MB
WASM	eh	21.7 KB
WASM	mvp	18.3 KB
WASM	threads	21.9 KB

Compressed download size from the Haybarn extension repository.

DuckDB & Haybarn

Release calendar

DuckDB v1.5.5 Haybarn 1.5.5-rc1 Supported