pipipi-dev

Posted on Dec 7

Database ID Design: Choosing ID Methods and Primary Key Strategies

#indiehacker #supabase #postgres #database

This is Day 7 of Building SaaS Solo - Design, Implementation, and Operation Advent Calendar 2025.

Yesterday I wrote about "Schema Design with Supabase." Today I'll explain database ID design.

🎯 Considerations for ID Design

Choosing a primary key (ID) for your database is a surprisingly deep topic. While some frameworks have defaults, you'll often need to make this choice yourself. Note that in PostgreSQL, auto-increment is implemented using SERIAL type (internally SEQUENCE).

ID methods like UUID and CUID2 combine timestamps and random values to generate unique IDs without central management. This allows creating data in distributed systems without worrying about ID collisions.

When considering ID design, I found the following video very helpful. It organizes the decision criteria for ID selection and is highly recommended.

https://www.youtube.com/watch?v=pmqRaEcDxl4

In this article, I'll share the actual decisions I made for my indie project, referencing the video's content.

📊 Comparison of Major ID Methods

Here's a comparison of common ID methods.

Method	Length	Time-sortable	PostgreSQL	Characteristics
Sequential (SERIAL/SEQUENCE)	Up to 19 digits	○ (effectively)	Native	Simple, predictable
UUID v4	36 chars	×	Native	Standard, random
UUID v7	36 chars	○	Storable as UUID type	Time-sortable
ULID	26 chars	○	text type	Readable character set
CUID2	24+ chars	×	text type	Short, secure
NanoID	21+ chars	×	text type	Shortest, fast

Selection Criteria

Will IDs be exposed in URLs? → Avoid sequential IDs if exposed
Need time-based sorting? → Use UUID v7 or ULID
Is write performance critical? → Sequential IDs for large datasets
Is ID length important? → NanoID or CUID2 for URLs

🔧 Adoption Strategy for My Indie Project

In Memoreru, my indie project, I use different IDs depending on the use case.

Default: CUID2

I adopted CUID2 for content IDs (pages, tables, dashboards, etc.).

Why I chose CUID2:

Short: 24 characters (UUID v4 is 36 characters)
URL-safe: No hyphens, lowercase alphanumeric only
Double-click selectable: No hyphens means you can select the entire ID
Secure: SHA-3 based, hard to guess

// id-generator.ts
import { init } from '@paralleldrive/cuid2';

// Initialize with fixed 24 character length
const createCuid = init({ length: 24 });

export function generateContentId(): string {
  return createCuid();
}
// Example: "clhqr8x9z0001abc123def45"

Exception: UUID v7 for Bulk Processing Tables

For tables with potential bulk inserts, like table content rows (table_rows), I adopted UUID v7.

Why I chose UUID v7:

Insert performance: Time-ordered IDs are efficient for B-tree indexes
PostgreSQL compatible: Can be stored as UUID type
RFC standard: Compliant with RFC 9562 (established 2024)

import { v7 as uuidv7 } from 'uuid';

export function generateRowId(): string {
  return uuidv7();
}
// Example: "018c1234-5678-7abc-9def-0123456789ab"

Selection Criteria Summary

Use Case	ID Method	Reason
Content ID	CUID2	Used in URLs, prioritize brevity
Table content row ID	UUID v7	Bulk processing, prioritize performance
User ID	Generated by Better Auth	Delegated to auth library

🔑 Composite Primary Key Design

Primary key design also involves choosing between "single primary key" and "composite primary key."

Especially in multi-tenant SaaS (services managing multiple customers' data in one system), composite primary keys can achieve both data isolation and search efficiency.

Single vs Composite Primary Key

-- Single primary key
CREATE TABLE contents (
  id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL,
  ...
);

-- Composite primary key
CREATE TABLE contents (
  tenant_id TEXT NOT NULL,
  content_id TEXT NOT NULL,
  ...
  PRIMARY KEY (tenant_id, content_id)
);

Benefits of Composite Primary Keys

Index efficiency: Fast tenant-scoped searches (tenant_id is at the front of the index)
Data isolation: Prevents cross-tenant data access
Uniqueness guarantee: Ensures uniqueness through the combination of tenant_id and content_id

Definition with Drizzle ORM

import { primaryKey, text } from 'drizzle-orm/pg-core';

export const contents = appContent.table(
  'contents',
  {
    tenant_id: text('tenant_id').notNull(),
    content_id: text('content_id').notNull(),
    title: text('title').notNull(),
    // ...
  },
  table => ({
    pk: primaryKey({ columns: [table.tenant_id, table.content_id] }),
  })
);

💡 Practical Tips

Prepare ID Validation Functions

Having validation functions helps prevent errors from invalid IDs.

export function validateCuid2(id: string): void {
  const cuid2Regex = /^[a-z0-9]{24}$/;
  if (!cuid2Regex.test(id)) {
    throw new Error('Invalid CUID2 format');
  }
}

export function validateUuidV7(id: string): void {
  const uuidRegex = /^[0-9a-f]{8}-[0-9a-f]{4}-7[0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$/i;
  if (!uuidRegex.test(id)) {
    throw new Error('Invalid UUID v7 format');
  }
}

✅ Summary

Here's what I've learned from ID design.

What's working well:

Achieving short, easy-to-handle URLs with CUID2
Ensuring bulk processing performance with UUID v7
Achieving multi-tenant data isolation with composite primary keys

Things to be careful about:

The optimal ID varies by requirements (there's no single right answer)
Plan carefully when migrating from existing data
Match the ID format of external dependencies like auth libraries

As concluded in the video, the optimal ID depends on your project's requirements. Choose what fits your use case.

Tomorrow I'll explain "Database Migration Best Practices: How to Safely Apply Changes."

Other articles in this series

12/6: Schema Design with Supabase: Table Partitioning and Normalization in Practice
12/8: Database Migration Best Practices: How to Safely Apply Changes

DEV Community