Short IDs for Public APIs in PostgreSQL

This post was written by an engineer at QueryPlane. QueryPlane is an app builder for your database: bring your own postgres db and you can create interactive applications to share with other developers, coworkers or even your customers. If you’re interested in trying it out, get started here.

---

UUIDs are great for internal IDs, but they make terrible URLs. Nobody wants to type https://app.example/orders/550e8400-e29b-41d4-a716-446655440000. This post explores alternatives for user-facing identifiers.

In this post, we’ll cover:

• Why short IDs matter - URLs, APIs, and user experience
• ULID - Sortable, 26-character identifiers
• NanoID - Customizable compact IDs
• Base62 encoding - Making integers URL-safe
• The hybrid approach - Internal vs external IDs
• Collision math - How short is too short?

Why Short IDs Matter

Long UUIDs create friction in every interaction where humans see or handle identifiers:

# Hard to read and share
https://app.example/invoices/550e8400-e29b-41d4-a716-446655440000

# Better
https://app.example/invoices/inv_01HGW2N7PZR8T1XVBK3MQD9YCS

# Even better for some uses
https://app.example/invoices/5rNmGQ8k

When a customer calls support and needs to provide their order ID, reading out 36 characters including hyphens is painful. Short IDs are easier to copy, paste, and communicate verbally. They make cleaner links in emails and documents—URLs that wrap across lines are confusing. They’re more readable in logs when you’re debugging at 2am. And when someone screenshots an error message, a short ID is more likely to be fully visible.

ULID: Universally Unique Lexicographically Sortable Identifier

ULIDs are 128-bit identifiers (the same size as UUIDs) but with a structure optimized for readability and sorting:

01HGW2N7PZR8T1XVBK3MQD9YCS
|----------|--------------|
 timestamp     randomness
  (48 bits)    (80 bits)

At 26 characters (versus 36 for a standard UUID), ULIDs are meaningfully shorter. They’re lexicographically sortable because the timestamp comes first—records created later have IDs that sort later. They’re case-insensitive and use only alphanumeric characters, so they’re safe in URLs without encoding.

Using ULIDs in PostgreSQL

The pgx_ulid extension adds native ULID support:

CREATE EXTENSION IF NOT EXISTS ulid;

CREATE TABLE orders (
  id ULID PRIMARY KEY DEFAULT gen_ulid(),
  customer_id BIGINT NOT NULL,
  total_cents BIGINT NOT NULL
);

INSERT INTO orders (customer_id, total_cents) VALUES (1, 9999);
SELECT * FROM orders;
-- id: 01HGW2N7PZR8T1XVBK3MQD9YCS

If you can’t install extensions (common on managed database services), you can store ULIDs as text and generate them in your application:

CREATE TABLE orders (
  id TEXT PRIMARY KEY,
  customer_id BIGINT NOT NULL,
  total_cents BIGINT NOT NULL
);

Most languages have ULID libraries. In Python, use import ulid; str(ulid.new()). In Node.js, use import { ulid } from 'ulid'; ulid().

Extracting Timestamps from ULIDs

Because ULIDs encode their creation time, you can extract it:

CREATE OR REPLACE FUNCTION ulid_to_timestamp(ulid_text TEXT)
RETURNS TIMESTAMPTZ AS $$
DECLARE
  chars TEXT := '0123456789ABCDEFGHJKMNPQRSTVWXYZ';
  ts_part TEXT;
  ms BIGINT := 0;
  i INT;
BEGIN
  ts_part := UPPER(LEFT(ulid_text, 10));
  FOR i IN 1..10 LOOP
    ms := ms * 32 + (POSITION(SUBSTRING(ts_part FROM i FOR 1) IN chars) - 1);
  END LOOP;
  RETURN to_timestamp(ms / 1000.0);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

SELECT ulid_to_timestamp('01HGW2N7PZR8T1XVBK3MQD9YCS');
-- 2023-12-01 15:30:45.678+00

This can be useful for debugging or analytics, though you’ll still want an explicit created_at column if you need sub-millisecond precision.

NanoID: Compact Custom IDs

NanoID generates shorter IDs by using a larger alphabet and letting you choose the length:

V1StGXR8_Z5jdHi6B-myT  (21 chars, default)
5rNmGQ8k               (8 chars, custom)

The default alphabet includes uppercase, lowercase, digits, underscore, and hyphen—64 characters total. This density means you can pack more uniqueness into fewer characters than a hex-encoded UUID.

NanoID in PostgreSQL

The nanoid-postgres extension provides native generation:

CREATE EXTENSION IF NOT EXISTS nanoid;

CREATE TABLE short_urls (
  id TEXT PRIMARY KEY DEFAULT nanoid(8),
  original_url TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO short_urls (original_url) VALUES ('https://example.com/very/long/path');
SELECT * FROM short_urls;
-- id: 5rNmGQ8k

Without the extension, generate IDs in your application. Here’s Python:

from nanoid import generate

# Custom alphabet (no confusing characters)
alphabet = '0123456789abcdefghijklmnopqrstuvwxyz'
public_id = generate(alphabet, 12)  # e.g., "k8m2np4qr7tv"

And JavaScript:

import { customAlphabet } from 'nanoid';

const nanoid = customAlphabet('0123456789abcdefghijklmnopqrstuvwxyz', 12);
const publicId = nanoid(); // e.g., "k8m2np4qr7tv"

Custom alphabets let you tailor IDs to your needs. Remove confusing characters like 0/O and 1/l for human readability. Use numbers only for verification codes. Use lowercase only if your URLs are case-insensitive.

Base62 Encoding: Converting Integers

If you’re already using BIGINT IDs internally, you can encode them for display without generating new identifiers:

CREATE OR REPLACE FUNCTION int_to_base62(num BIGINT)
RETURNS TEXT AS $$
DECLARE
  chars TEXT := '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz';
  result TEXT := '';
  remainder INT;
BEGIN
  IF num = 0 THEN RETURN '0'; END IF;

  WHILE num > 0 LOOP
    remainder := num % 62;
    result := SUBSTRING(chars FROM remainder + 1 FOR 1) || result;
    num := num / 62;
  END LOOP;

  RETURN result;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION base62_to_int(encoded TEXT)
RETURNS BIGINT AS $$
DECLARE
  chars TEXT := '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz';
  result BIGINT := 0;
  i INT;
BEGIN
  FOR i IN 1..LENGTH(encoded) LOOP
    result := result * 62 + (POSITION(SUBSTRING(encoded FROM i FOR 1) IN chars) - 1);
  END LOOP;
  RETURN result;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Usage
SELECT int_to_base62(123456789);  -- Returns: 8M0kX
SELECT base62_to_int('8M0kX');    -- Returns: 123456789

This approach keeps BIGINT as the actual primary key and uses Base62 only for display in URLs and APIs. The mapping is 1:1 with no collision risk—you’re just changing the representation, not generating new values.

The tradeoff is that Base62-encoded sequential integers are still somewhat predictable. If a user sees 8M0kX, they might guess that 8M0kW and 8M0kY also exist. For many applications this is fine, but if you need unpredictable IDs, use ULID or NanoID instead.

The Hybrid Approach

The most robust pattern uses both internal and external IDs. Your database uses efficient integers for joins and foreign keys, while your API exposes opaque string identifiers:

CREATE TABLE customers (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  public_id TEXT NOT NULL UNIQUE DEFAULT nanoid(12),
  email TEXT NOT NULL UNIQUE,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX ON customers (public_id);

Internal operations use the integer id. Foreign keys reference it, joins use it, and internal queries filter by it:

CREATE TABLE orders (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  public_id TEXT NOT NULL UNIQUE DEFAULT nanoid(12),
  customer_id BIGINT NOT NULL REFERENCES customers(id),
  total_cents BIGINT NOT NULL
);

-- Efficient internal join
SELECT o.*, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.id = 12345;

API endpoints use the public_id. External users never see sequential integers:

-- API lookup
SELECT * FROM customers WHERE public_id = 'k8m2np4qr7tv';

This gives you the performance benefits of integers internally (compact storage, fast comparisons, efficient B-tree inserts) while exposing secure, non-guessable identifiers externally.

Collision Math: How Short Is Too Short?

Short IDs have collision risk. The math follows the birthday problem: with enough IDs, eventually two will match.

For an alphabet of size a and ID length n, the number of possible IDs is a^n. With k generated IDs, the collision probability is approximately k² / (2 * a^n).

Here’s what that means in practice:

Format	Length	Alphabet Size	Possible IDs	1% collision after
NanoID	8	64	281 trillion	2.4 billion IDs
NanoID	12	36	4.7 quadrillion	300 billion IDs
ULID random	16	32	1.2 × 10²⁴	Effectively infinite
UUID v4	122 bits	2	5.3 × 10³⁶	Effectively infinite

Eight-character alphanumeric IDs are fine for thousands of IDs. Twelve characters are safe for millions. Sixteen characters or more are collision-free for any practical purpose.

Adding Prefixes

Type prefixes make IDs self-documenting and prevent cross-table collisions:

CREATE TABLE customers (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  public_id TEXT NOT NULL UNIQUE DEFAULT 'cus_' || nanoid(12),
  email TEXT NOT NULL UNIQUE
);

CREATE TABLE orders (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  public_id TEXT NOT NULL UNIQUE DEFAULT 'ord_' || nanoid(12),
  customer_id BIGINT NOT NULL REFERENCES customers(id)
);

-- Results:
-- cus_k8m2np4qr7tv
-- ord_9xnp3m7qk2rv

Stripe popularized this pattern with prefixes like cus_ for customers, sub_ for subscriptions, inv_ for invoices, and pi_ for payment intents. When you see an ID in logs or error messages, you immediately know what type of object it refers to.

Comparison Table

Feature	ULID	NanoID	Base62	UUID
Length	26	Configurable	Variable	36
Sortable	Yes	No	Yes (if sequential)	v7 only
URL-safe	Yes	Yes	Yes	Needs encoding
Timestamp extractable	Yes	No	No	v7 only
Collision risk	Very low	Depends on length	None	None
Extension needed	Optional	Optional	No	No

Wrapping Up

For user-facing IDs, the hybrid approach works best: use BIGINT internally for performance and short string IDs externally for usability. Choose ULID if you need sortability and timestamp extraction. Choose NanoID if you want maximum customization and brevity. Use Base62 encoding if you already have sequential integers and don’t need unpredictability.

Add type prefixes (like ord_, cus_) to make IDs self-documenting and prevent accidental cross-table lookups. For ID length, 12 alphanumeric characters is a good default—short enough to be usable, long enough to avoid collisions at scale.