PostgreSQL Primary Key Types Compared: Identity, UUID, and UUIDv7

Primary keys look like a small schema choice until the application starts sharing URLs, importing customer data, replicating rows, or merging records from multiple systems. PostgreSQL gives you several reasonable ways to identify a row. The hard part is choosing the one whose tradeoffs fit the product you are building, not the one that happens to be popular in the framework starter kit.

For most SaaS applications, the realistic choice is between generated integer identity columns, older serial-style sequences, random UUIDs, and time-ordered UUIDs such as UUIDv7. Each can be correct. The difference is how the key behaves under scale, migrations, external exposure, indexing, and distributed writes.

The decision you are really making

A primary key has two jobs. It gives PostgreSQL a stable way to identify a row, and it gives other tables a compact reference target for foreign keys. In practice, teams often ask it to do more: hide customer count, work safely across environments, support offline inserts, appear in public APIs, and preserve index locality for write-heavy tables.

Those extra requirements are why there is no universal best key. A small internal admin table and a multi-tenant events table do not need the same identifier. A billing object exposed in URLs has different risks than a join table that never leaves the database. The cleanest design starts by separating internal relational identity from public product identifiers. Sometimes they are the same column. Often they should not be.

PostgreSQL options at a glance

PostgreSQL's modern SQL-standard option for generated integers is an identity column, declared with GENERATED BY DEFAULT AS IDENTITY or GENERATED ALWAYS AS IDENTITY. Older schemas often use serial or bigserial, which are shorthand patterns built around a sequence. PostgreSQL also has a native uuid data type, and current documentation describes UUID generation functions including version 4 random UUIDs and version 7 time-ordered UUIDs.

The table hides one important point: primary keys are also index keys. PostgreSQL enforces a primary key with a unique index, commonly a B-tree. Wide or random keys therefore affect every referencing table and every write that touches the primary-key index. That does not make UUIDs bad. It means UUIDs are an architectural choice, not just a prettier ID format.

Identity columns: the boring default for internal relational data

If the identifier is mostly internal, an identity bigint is usually the simplest strong default. It is compact, efficient in indexes, easy for humans to inspect during debugging, and well understood by ORMs, migration tools, and database administrators. PostgreSQL's identity-column documentation also makes the generation rule explicit in the table definition, which is clearer than relying on older serial shorthand.

Use GENERATED BY DEFAULT AS IDENTITY when imports or controlled backfills may need to supply IDs. Use GENERATED ALWAYS AS IDENTITY when application-supplied values should be rejected unless a migration deliberately overrides the rule. In both cases, the generated value comes from sequence behavior, so it is not a security boundary and it should not be treated as secret.

The common objection is predictability. If /accounts/418 is visible to users, the next account may be easy to guess. The right fix is not always to abandon integer primary keys. A durable SaaS pattern is to keep the integer primary key for internal joins and add a separate public identifier for URLs, API payloads, or customer-visible references. That keeps the database model efficient while avoiding product-level ID enumeration.

Serial and bigserial: still common, but not where new designs should start

serial and bigserial are not true data types in the same sense as integer or uuid. PostgreSQL documents them as convenient notation that creates an integer column, a sequence, and a default expression that pulls from that sequence. Many mature schemas use them safely, and there is no need to rewrite a working schema only because identity columns are newer.

For new projects, identity columns are usually the cleaner expression of intent. They are part of the SQL standard, they make generated-column behavior more obvious, and they avoid some of the historical confusion around the sequence object created behind a serial column. If you inherit a bigserial schema, the operational guidance is the same as for identity-backed integers: monitor sequence headroom, do not expose predictable IDs where enumeration matters, and reset sequences carefully after imports.

One practical rule is to avoid plain serial for tables that can grow for years. A 32-bit integer may look large during a prototype, but a busy event, audit, message, or background-job table can burn through values faster than expected. bigint-backed identity or bigserial is the safer default for production tables.

UUIDv4: useful when IDs cross system boundaries

UUIDv4 is attractive because it can be generated before a row reaches PostgreSQL, is difficult to guess, and remains unique enough for ordinary application architecture without coordinating a central sequence. That makes it useful for offline clients, multi-region ingest pipelines, data imports from separate environments, and public API objects where predictable integer values would invite enumeration.

The tradeoff is physical behavior. Random UUIDs distribute inserts across the B-tree rather than mostly appending near the end of the index. PostgreSQL can handle that, but it can mean more page churn than a steadily increasing integer key, especially in write-heavy tables with many foreign-key references. UUID columns are also wider than bigint, and that width is copied into referencing indexes.

The operational recommendation is to use UUIDv4 where the product needs its properties, not everywhere by reflex. A table of user-visible documents, invitations, webhook events, or integration objects may be a good fit. A private join table or an append-only internal counter probably does not benefit enough to pay the complexity cost.

UUIDv7: time-ordered IDs for teams that want UUID semantics

UUIDv7 keeps the broad UUID shape while adding timestamp ordering. PostgreSQL documentation for UUID functions describes uuidv7() as generating a version 7 UUID from a Unix timestamp with random bits. The practical appeal is straightforward: applications get non-enumerable UUID-style identifiers, while indexes receive values that are more time-local than fully random UUIDv4 values.

That makes UUIDv7 especially interesting for SaaS objects created frequently and exposed externally: events, jobs, customer resources, API tokens, invitations, and records that move between systems. It is not magic. The key is still wider than bigint, and teams still need to verify driver support, migration tooling, database version support, and any managed-provider restrictions before standardizing on it.

UUIDv7 also changes the privacy conversation. It is harder to guess than an integer sequence, but it carries time-ordering semantics. If exposing approximate creation order is sensitive for a specific object, evaluate that explicitly. Most applications are comfortable with creation-time leakage because timestamps already exist elsewhere, but security-sensitive tokens should be designed as secrets rather than ordinary primary keys.

A recommendation matrix for SaaS schemas

For a new ArmorDB-style managed PostgreSQL application, start with identity bigint for internal tables and add separate public identifiers where customers, webhooks, or integrations see IDs. Move to UUIDv7 when external generation, cross-system merging, or public API ergonomics matter enough that the ID format itself should carry those properties. Use UUIDv4 when you need UUID compatibility but cannot rely on UUIDv7 support across every environment yet.

This matrix is intentionally conservative. Changing primary keys after launch is possible, but it is among the more invasive migrations because foreign keys, application routes, caches, analytics, and external integrations may all depend on the old value. Pick a boring internal default, then be more specific where the product boundary demands it.

Migration notes before changing an existing key

If a production table already uses a working primary key, do not change it only to match a newer preference. Add a public identifier first, backfill it, enforce uniqueness, update reads and writes to use it at the product boundary, and only then consider whether the primary key itself needs to change. In many systems, it never does.

When moving from integer IDs to UUID-style public IDs, deploy in stages. Add the new column as nullable, backfill in batches, create a unique index, make new writes populate it, update API responses and routes, then tighten the column constraint once every path is ready. For large tables, build indexes carefully and schedule backfills so they do not compete with peak traffic. If you use managed PostgreSQL, review plan limits and backup posture before a high-write migration; /docs/backups is relevant because identifier migrations are exactly the kind of operation where a restore plan should be boring.

Also check assumptions in tests. Many test suites sort by integer ID accidentally. UUID-based keys may expose ordering bugs that were hidden by sequence order. If the application needs creation order, query by created_at or another explicit column instead of depending on primary-key shape.

Takeaway

Use identity bigint when the key is mostly relational infrastructure. Use a separate public ID when exposing predictable integers would create a product or security problem. Choose UUIDv4 when distributed uniqueness matters and UUIDv7 is not available everywhere. Choose UUIDv7 when you want UUID-style external behavior with better time locality for inserts. The best PostgreSQL primary key is not the most fashionable one; it is the one whose operational cost matches how the row will actually be used.

Sources and further reading

• PostgreSQL documentation: identity columns
• PostgreSQL documentation: serial types
• PostgreSQL documentation: UUID type
• PostgreSQL documentation: UUID generation functions
• PostgreSQL documentation: index types