UUID vs Bigint Primary Keys in Postgres (and UUIDv7)

For a Postgres primary key, a bigint generated always as identity column is the smallest and simplest choice for data created only on your server. A uuid primary key is better when IDs must be created outside the database (mobile apps, offline sync, several services), must not reveal how many rows you have, or must be unique across databases. If you choose UUIDs, prefer time-ordered UUIDv7 over random UUIDv4: v7 values sort by creation time, so inserts land at the end of the index like bigint keys do. Postgres 18 has a built-in uuidv7() function; on Postgres 17 you can add a short SQL function, shown below.

The rest of this article compares the options point by point and shows the SQL for each.

#The three options

-- 1. bigint identity (the modern replacement for serial)
create table public.orders_a (
  id bigint generated always as identity primary key,
  ...
);

-- 2. random UUID (version 4), built in since Postgres 13
create table public.orders_b (
  id uuid primary key default gen_random_uuid(),
  ...
);

-- 3. time-ordered UUID (version 7)
create table public.orders_c (
  id uuid primary key default uuidv7(),   -- Postgres 18; see below for 17
  ...
);

#Use identity, not serial

serial and bigserial still work, but identity columns are the SQL-standard way. They tie the sequence to the column, follow the SQL standard, and with generated always they reject accidental manual ids unless you say overriding system value. Use bigint, not int: a 4-byte integer runs out at about 2.1 billion, and changing the type of a primary key on a big table later is a long, locking rewrite.

#Side-by-side comparison

#Storage and index size

A UUID is twice the size of a bigint. That cost shows up wherever the key is stored: the primary key index, every foreign key column that points to it, and every index on those foreign keys. For a table with a few million rows it rarely matters. For a large table referenced from several others, the extra bytes add up across all those indexes, and more index pages means less of it fits in memory.

Measure on your own schema before deciding on size alone; pg_relation_size tells you exactly:

select pg_size_pretty(pg_relation_size('public.orders_a_pkey')) as bigint_pk,
       pg_size_pretty(pg_relation_size('public.orders_b_pkey')) as uuid_v4_pk;

#Why random UUIDs hurt inserts

This is the bigger effect. A B-tree keeps keys sorted. With bigint identity or UUIDv7, each new key is larger than the last, so inserts go to the right-most leaf page, which is almost always in memory. With UUIDv4, each new key lands on a random page anywhere in the index. Once the index is bigger than memory, inserts keep pulling cold pages from disk, and pages split all over the index, leaving them partly empty.

Random keys also hurt locality for reads: rows created together (one user's session, one import batch) are scattered across the index, so "the last 100 orders" touches many more pages.

UUIDv7 fixes this. Its first 48 bits are a Unix timestamp in milliseconds, so new values sort after old ones, while the remaining bits stay random for uniqueness. You get the "generate anywhere" property of UUIDs with nearly the index behavior of a sequence.

#UUIDv7 on Postgres 18

Postgres 18 added uuidv7() (and uuidv4(), an alias for gen_random_uuid()). It also extended uuid_extract_timestamp() to version 7 values:

select uuidv7();
select uuid_extract_timestamp(uuidv7());   -- creation time from the id
select uuid_extract_version(uuidv7());     -- 7

uuidv7() takes an optional interval to shift the timestamp, which is handy for tests. See the Postgres 18 UUID functions.

#UUIDv7 on Postgres 17

Postgres 17 only generates version 4 UUIDs natively. You can build a valid UUIDv7 from one in plain SQL: overwrite the first 48 bits with the current Unix time in milliseconds, then set the version bits to 7. The variant bits from the random UUID are already correct.

create or replace function public.uuid_generate_v7()
returns uuid
language sql
volatile
as $$
  select encode(
    set_bit(
      set_bit(
        overlay(
          uuid_send(gen_random_uuid())
          placing substring(int8send(floor(extract(epoch from clock_timestamp()) * 1000)::bigint) from 3)
          from 1 for 6
        ),
        52, 1
      ),
      53, 1
    ),
    'hex'
  )::uuid;
$$;

create table public.events (
  id uuid primary key default public.uuid_generate_v7(),
  created_at timestamptz not null default now(),
  payload jsonb
);

How it works: int8send gives the 8-byte big-endian millisecond timestamp, substring(... from 3) keeps the low 6 bytes (48 bits), and overlay writes them over the first 6 bytes of a random UUID. The random UUID has version nibble 0100; setting bits 52 and 53 turns it into 0111, version 7. Check it:

select uuid_extract_version(public.uuid_generate_v7());  -- 7

Two notes for Postgres 17: uuid_extract_timestamp() only reads version 1 UUIDs there and returns NULL for v7, and values created in the same millisecond are not ordered among themselves (the remaining bits are random). Neither matters for index locality. When you upgrade to Postgres 18, switch the default to the built-in uuidv7(); existing values remain valid UUIDs.

You can also generate UUIDv7 in the application. Many languages have libraries for it, and some newer runtimes include it. Generating in the app means you know the id before the insert, which is the main reason to use UUIDs in the first place.

#Security and privacy

Sequential ids leak information. /invoices/1042 tells a customer you have issued about a thousand invoices, and changing it to /invoices/1041 is the first thing an attacker tries. The real protection is authorization, not obscurity: with Row Level Security, a user who guesses another id still gets no row. But unguessable ids are a useful second layer and avoid leaking business metrics.

UUIDv7 has its own leak: anyone can read the creation time from the id. For most data that is harmless (it is often shown anyway). For something like user accounts, where signup time is sensitive, use UUIDv4 for the public id.

#A hybrid that works well

Some teams use both:

create table public.invoices (
  id bigint generated always as identity primary key,       -- internal joins, small FKs
  public_id uuid not null unique default gen_random_uuid(), -- used in URLs and APIs
  customer_id bigint not null references public.customers,
  total numeric(12,2) not null
);

Joins and foreign keys stay small; URLs stay unguessable. The cost is an extra unique index and two ids to keep straight in code. If your app is small, pick one type and move on.

#When to pick which

Pick bigint identity when all rows are created by your backend, the table is large and heavily referenced, and ids are not exposed (or exposure is fine).

Pick UUIDv7 when clients or several services create ids, you sync data offline, you merge data from several databases, or you do not want ids to reveal counts. It is a good default for new app tables.

Pick UUIDv4 when ids must not reveal creation time, or the table is small enough that index locality does not matter.

#Primary keys on StitchDB

StitchDB projects are Postgres databases, so all three options work. A few platform details push toward UUIDs for app tables:

• User ids are UUIDs. Users live in auth.users with a uuid id, and the usual pattern references it directly (Tables and SQL):

create table public.profiles (
  id uuid primary key references auth.users on delete cascade,
  username text unique,
  created_at timestamptz not null default now()
);

• Offline sync needs client-made ids. Rows created offline need their primary key before the server sees them, so the docs recommend a uuid key with default gen_random_uuid() and crypto.randomUUID() in the app. Tables whose key is generated always as identity reject rows with an id, so offline writes to them fail (Offline sync).
• Table Editor presets. New tables can start with an identity id, a uuid id, or a user_id that references auth.users.
• Safe schema changes. Adding a column with default gen_random_uuid() (or any per-row default) to an existing table rewrites it and blocks reads and writes. The SQL Editor warns you and suggests adding the column, setting the default, then filling old rows in batches (Safe schema changes).

To see which Postgres major version your project runs, and therefore whether uuidv7() is built in, run select version(); in the SQL Editor.

Creating a row with a client-side UUID from the SDK:

const { data, error } = await stitchdb
  .from('notes')
  .insert({ id: crypto.randomUUID(), title: 'Draft' })
  .select()

For more on schema design for SaaS apps, see multi-tenant SaaS on Postgres.

#FAQ

#Should I use UUID or bigint as a primary key in Postgres?

Use bigint identity for server-created rows in large, heavily joined tables. Use UUIDs, preferably v7, when ids are created outside the database, must be unique across systems, or should not reveal row counts.

#Is UUIDv7 better than UUIDv4 for Postgres?

For primary keys, usually yes. UUIDv7 values increase over time, so inserts go to the end of the B-tree index instead of random pages. Use v4 only if leaking the creation time is a concern.

#Does Postgres 17 support uuidv7()?

No. The built-in uuidv7() arrived in Postgres 18. On Postgres 17, use a SQL function like the one above, or generate UUIDv7 values in your application.

#Is serial deprecated in Postgres?

Not deprecated, but identity columns (generated always as identity) are the recommended, SQL-standard replacement. Use bigint to avoid running out of values.

#How much bigger is a UUID than a bigint?

A uuid is 16 bytes and a bigint is 8 bytes. The difference repeats in every index and foreign key that stores the key, so measure on your own tables.

Build your schema on a real Postgres database: create a free StitchDB project.