Rethinking Database Keys: Auto-Incrementing BigInt and Public IDs Approach

TLDR;

Choosing the right database key strategy is crucial for both performance and security. After years of using auto-incrementing integers and UUIDs, I’ve found a better approach inspired by PlanetScale’s production practices.

Auto-Incrementing BigInt and Public IDs Approach:

Use BigInt as your primary key for internal performance and joins.
Add a public_id column with NanoIDs for anything client-facing.
Be consistent: don’t expose primary keys externally, always expose public_id.
Store NanoIDs as fixed-length CHAR for speed.

Traditional Options

Auto-Increment IDs

The issues with integers become obvious once you think about them:

Predictability – If my user ID is 1001, you can guess there’s probably a 1000 and a 1002. That’s a small but real security leak.
Sharding and scaling – Auto-incrementing means your database has to coordinate on who gets the next number, which gets messy in distributed systems.
Unintended exposure – Sequential IDs in URLs or APIs basically leak how much data your app holds.

UUIDs: Better, But Still Clunky

Naturally, many of us moved on to UUIDs. They fix some problems: they’re globally unique and don’t require the database to hand out numbers in sequence.

But UUIDs come with tradeoffs:

They’re not URL-friendly (36 long of characters with lots of hyphens -).
They can hurt database performance when used as clustered indexes because inserts are random rather than sequential.

They solve predictability, but they’re still awkward to work with.

PlanetScale Approach: BigInt + Public IDs

One best practice I really liked from PlanetScale is how they balance internal performance with external safety.

Here’s how they do it:

Keep a auto-incrementing BigInt primary key. This gives the database fast joins, efficient indexing, and the reliability of a classic numeric key.
Add a public_id column on any table that needs to expose identifiers to clients (like users, API tokens, or projects). This column stores a NanoId.

Why this matters:

Performance: Internally, your DB keeps the performance benefits of integers.
URL Friendly: Externally, clients only see unpredictable, secure, URL-safe IDs.
Predictability: If someone sees proj_khR1e7y6pDz3sY8xCqZ2, they can’t guess the existence of other projects.

It’s the best of both worlds: internal efficiency and external security.

Why NanoIDs Are a Great Fit for Public IDs

Here’s why NanoID is particularly well-suited for public-facing identifiers:

Compact and URL-safe: At just 21 characters by default (compared to UUID’s 36), NanoIDs are significantly shorter while maintaining the same level of uniqueness. They use a URL-safe alphabet (A-Za-z0-9_-), making them perfect for REST APIs and browser URLs without encoding issues.
Collision-resistant: With 21 characters and 64 possible symbols, NanoIDs provide ~126 bits of entropy. You’d need to generate a billion IDs per second for ~41,000 years to have a 1% probability of collision. That’s more than safe enough for any application.
Performance-friendly: NanoIDs are extremely fast to generate (2-3x faster than UUIDs), and because they’re fixed-length strings, they can be stored efficiently in a CHAR(21) column. This allows for better database indexing performance compared to variable-length VARCHAR columns.
Customizable: Unlike UUIDs, you can adjust the alphabet and length to fit your specific needs. Shorter IDs for a smaller dataset or a custom alphabet? No problem.

Sample Implementation in Drizzle

Note: I originally wrote this example using Prisma, but have since migrated to Drizzle ORM for its better TypeScript support and performance. The pattern remains the same regardless of your ORM choice.

If you’re using Drizzle, this pattern is straightforward to set up. Here’s what it looks like in your schema:

import {
  bigserial,
  char,
  timestamp,
  varchar,
  pgTable,
} from "drizzle-orm/pg-core";

export const users = pgTable("users", {
  // Internal primary key
  id: bigserial("id", { mode: "number" }).primaryKey(),

  // Public-facing ID
  publicId: char("public_id", { length: 21 }).notNull().unique(),

  email: varchar("email", { length: 255 }).notNull().unique(),
  name: varchar("name", { length: 255 }),
  createdAt: timestamp("created_at").defaultNow().notNull(),
});

Notes:

id remains your primary key for internal operations like joins and foreign keys.
publicId is a unique NanoID string you can safely expose to clients in your API responses and URLs.
Using fixed-length CHAR(21) instead of VARCHAR provides better indexing performance since the database knows the exact length.
bigserial is PostgreSQL’s equivalent to MySQL’s auto-incrementing BigInt.

import { nanoid } from "nanoid";
import { db } from "@/server/db";
import { users } from "@/server/db/schema";

// Creating a new user
await db.insert(users).values({
  publicId: nanoid(),
  email: "example@test.com",
  name: "Alice",
});

// Querying by public ID (what you expose to clients)
const user = await db
  .select()
  .from(users)
  .where(eq(users.publicId, "user_abc123xyz"))
  .limit(1);

// Internal joins still use the efficient BigInt primary key
const userWithPosts = await db
  .select()
  .from(users)
  .leftJoin(posts, eq(posts.userId, users.id)) // Fast integer join
  .where(eq(users.publicId, "user_abc123xyz"));

Important: In this example, we’re generating the NanoID at the application level for clarity. In production, you might want to:

Generate it in a beforeInsert hook
Use a database function (if your DB supports it)
Handle it in a service layer to ensure consistency

This gives you the full PlanetScale pattern: BigInt primary key + public NanoID.

Wrapping Up

Database key design is one of those foundational decisions that’s easy to overlook early on, but can have lasting impact on your application’s security, performance, and scalability.

The hybrid approach of using auto-incrementing BigInt for primary keys and NanoIDs for public-facing identifiers gives you the best of both worlds:

Internal database operations remain fast and efficient
External APIs stay secure and unpredictable
URLs remain clean and shareable
Your architecture stays ready for scale

After reading about PlanetScale’s production model, this pattern really clicked for me. It’s a small design choice, but one that could save a lot of pain down the road.