CONCEPT Cited by 1 source

Public ID column¶

A public_id column is a VARCHAR(N) column holding an externally-visible opaque identifier, separate from the table's internal numeric primary key. The column is:

Populated application-side before INSERT (typically via an ORM callback — Rails before_create, Django save() override, etc.).
Enforced unique at the database layer via a UNIQUE KEY secondary index.
Opaque — typically a NanoID, Snowflake, ULID, or UUID — so that consecutive rows don't produce consecutive IDs and the API can't be enumerated.

The column pairs with a conventional BIGINT AUTO_INCREMENT clustered primary key — see the full public-id-alongside-BIGINT-PK pattern. This page covers the column itself; that page covers the two-column strategy.

Canonical realisation¶

From PlanetScale's 2022-03-29 post (Source: sources/2026-04-21-planetscale-why-we-chose-nanoids-for-planetscales-api):

CREATE TABLE `user` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `public_id` varchar(12) DEFAULT NULL,
  ...
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_public_id` (`public_id`)
);

public_id holds a 12-character base-36 NanoID; the idx_public_id unique index gives O(log N) lookup by public ID and serves as the real-correctness backstop for collision prevention.

Generation contract¶

PlanetScale's Rails PublicIdGenerator ActiveSupport concern is the canonical realisation:

included do
  before_create :set_public_id
end

def set_public_id
  return if public_id.present?
  MAX_RETRY.times do
    self.public_id = generate_public_id
    return unless self.class.where(public_id: public_id).exists?
  end
  raise "Failed to generate a unique public id after #{MAX_RETRY} attempts"
end

The callback fires once per row insert; the where(public_id: ...).exists? check is a pre-insert race-reducer (not a correctness primitive — the UNIQUE KEY at the DB level is). See concepts/nanoid-collision-retry for the retry-loop contract.

Properties¶

Column type: VARCHAR(N) where N = the format's fixed character length. At PlanetScale: VARCHAR(12) for 12-char NanoIDs. For UUIDv4: CHAR(36) or BINARY(16) depending on storage choice.
Nullability: allowed DEFAULT NULL in the canonical example (footgun — allows rows without public IDs if the ORM callback is bypassed); NOT NULL + a DB-default is safer in strict environments.
Collation: typically case-sensitive (utf8mb4_bin or narrower) since opaque identifiers aren't human-readable text — izkpm55j334u and IZKPM55J334U should be different IDs (or one should be invalid).
Index: always UNIQUE KEY. Without uniqueness enforcement, the correctness of the identifier collapses (collisions silently happen).
Population: always application-side before INSERT, never a DB-side default — because:
Application wants validation before the SQL round-trip.
Cross-language consistency requires ID generators in every service language (PlanetScale has Ruby + Go generators).
DB-side UUID() function returns UUIDv1 (not a NanoID, not a UUIDv4).

Why "public_id" as a name¶

Several naming conventions exist in the wild:

public_id (PlanetScale, many Rails apps) — emphasises externally-visible vs internal PK.
slug — used when the ID is human-readable (blog post URL slugs); different semantics.
uuid / uid — common when the format is actually a UUID; less format-neutral.
external_id — explicit about the external/internal split; sometimes used for third-party vendor IDs (Stripe ID, Salesforce ID) rather than self-generated public IDs.
nanoid — locks the name to the format, losing the abstraction.

PlanetScale's choice of public_id is deliberately format-agnostic: if the team ever switches from NanoID to ULID or UUIDv7, the column name still makes sense. The format is an implementation detail; the role of the column is what's being named.

When you need this¶

Any application with a public API where row URLs are exposed to consumers:

REST API URLs (/users/:public_id, /orders/:public_id).
Webhook payloads that include resource IDs.
Email links referencing specific records.
Sharing URLs (/share/:public_id).

When you don't need this: internal admin tools, back-office systems where only employees see URLs, and databases that will never be touched by external consumers. In those cases the BIGINT PK alone is fine.

Caveats¶

Secondary-index write cost on every insert — one extra B+tree update per INSERT. Isolated to one index (not the whole clustered table) but non-zero.
Secondary-index read cost on API lookups — every GET /users/:public_id pays two B+tree walks (secondary → clustered) vs one for GET /users/:id. Negligible for point lookups, noticeable on batch operations.
Race on pre-insert exists? check — application-side uniqueness check is a read-then-write race; the DB-side unique index is the correctness backstop. Under very-low-entropy public IDs this race becomes observable.
Generation determinism — if two processes generate public IDs from the same seed they can collide. Production generators must use a crypto-strong PRNG; default NanoID libraries do, but audit before trusting.
Doesn't prevent /user/:id enumeration if the id PK is also exposed — this only helps if the API surface never leaks the internal id. Slipping id into a JSON response by accident undoes the whole pattern.
Doesn't stop timestamp-correlation attacks — created_at on retrieved rows still leaks creation timing even if public_id is random. For stronger unlinkability, scramble created_at or don't expose it.
public_id doesn't convey object type — Stripe-style prefixed IDs (cus_abc123 for customers, in_abc123 for invoices) encode the object type in the ID itself, giving defence-in-depth against ID mix-ups. PlanetScale's NanoIDs are raw alphanumeric with no type prefix; consumers must know the context.
DEFAULT NULL column definition is a back-door — allows insert paths that bypass the Rails before_create callback (direct SQL, seed scripts, import jobs) to produce rows with public_id = NULL. Multiple NULLs are allowed by MySQL unique indexes. Consider NOT NULL + application-side validation.

Seen in¶

sources/2026-04-21-planetscale-why-we-chose-nanoids-for-planetscales-api — Mike Coutermarsh (PlanetScale, 2022-03-29) canonicalises the public_id VARCHAR(12) column pattern with full CREATE TABLE user schema, Rails PublicIdGenerator concern, and Go publicid package. "For all public-facing models, we have added a public_id column to our database. We still use standard auto-incrementing BigInts for our primary key. The public_id is only used as an external identifier."