🆔

TSID

Time-Sorted Unique Identifier generator for DuckDB. 32-character hex strings that sort chronologically by plain string comparison while remaining unique under bulk generation. Stable surrogate keys, audit-log primaries, and time-range pagination keys.

Browse Contents

Install

-- Install the extension
INSTALL tsid FROM community;

-- Load it into your session
LOAD tsid;

-- 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);

Technical Overview

Why Use TSID?

Mint sortable, time-ordered unique identifiers from inside DuckDB SQL. tsid() gives you the same property UUIDv7 and ULID provide — IDs that sort chronologically by plain comparison — 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.
• Experimental status: The upstream README marks the extension experimental. The exposed surface is small (tsid, tsid_to_timestamp), so churn risk is low — but pin a known-good version in production schemas that depend on the on-disk hex layout.

🎯 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.

Reference

Extension Contents

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

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

Detailed documentation for each function including signatures, parameters, and examples.

tsid

Scalar Function Generation

Signature

tsid() → VARCHAR

Parameters

Parameter	Type	Mode	Description

Returns

Description

Generate a new Time-Sorted Unique Identifier — a 32-character hex string. Optional seed string makes IDs from the same seed reproducible-ordered. Calls within the same microsecond stay distinct.

Examples

One-off ID

SELECT tsid();

Bulk generation with random seed

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

Related Functions

tsid_to_timestamp() — Extract the embedded timestamp from a TSID

Back to Extension Contents

tsid_to_timestamp

Scalar Function Inspection

Signature

tsid_to_timestamp(tsid: VARCHAR) → TIMESTAMP

Parameters (Positional)

Parameter	Type	Mode	Description
`tsid`	`VARCHAR`	Positional

Returns

Description

Extract the embedded timestamp from a TSID. Returns TIMESTAMP — the time the ID was generated.

Examples

Recover when a row was inserted from its id alone

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

Related Functions

tsid() — Generate a new Time-Sorted Unique Identifier — a 32-character hex string

Back to Extension Contents

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

Software License MIT

Pricing Free

Written In C++

Source Available Yes

View on GitHub

Usage

11,280+

loads in last 30 days

Platforms

Linux

Linux (musl)

macOS

Windows

WASM

Supported platform architectures

Linux: aarch64, x86_64

Linux (musl): x86_64

macOS: Apple Silicon, Intel

Windows: x86_64

WASM: eh, threads, mvp

Compiled binary sizes

Platform	Architecture	Size
Linux	aarch64	2.65 MB
Linux	x86_64	3.01 MB
Linux (musl)	x86_64	2.11 MB
macOS	Apple Silicon	1.81 MB
macOS	Intel	2.12 MB
Windows	x86_64	7.50 MB
WASM	eh	14.9 KB
WASM	mvp	12.8 KB
WASM	threads	12.8 KB

Gzipped download size from the DuckDB community-extensions registry.

DuckDB Versions

Release calendar

Supported

v1.4.4 v1.5.2

Sortable Unique IDs in SQL

Install TSID for time-sortable unique identifiers — chronological by string compare, unique by construction, recoverable timestamps. Drop-in replacement for UUIDv4 when ordering matters.

Install Now View Other Extensions

TSID

Install

Why Use TSID?

🆔 What this extension is for

⚙️ How it works

🧭 TSID vs UUIDv4 vs UUIDv7 vs ULID

🛡️ Caveats

🎯 Common Use Cases

Primary keys for append-mostly tables

Recent-first pagination without timestamp join

Drop a created_at column

Replacement for uuid() where ordering matters

Technical Details

What you can do with one query

TSID vs the alternatives

Why time-sortable IDs help the index

Bulk generation and ordering

When to keep uuid() instead

Extension Contents

Function Documentation

tsid

Signature

Parameters

Returns

Description

Examples

Related Functions

tsid_to_timestamp

Signature

Parameters (Positional)

Returns

Description

Examples

Related Functions

Cookbook

Generate IDs

Use as a primary key

Recover the timestamp

Time-range pagination

TSID vs UUID

Compatibility

Quick Facts

Platforms

DuckDB Versions

Sortable Unique IDs in SQL

Drop a `created_at` column

Replacement for `uuid()` where ordering matters

When to keep `uuid()` instead