What Should My Postgres Primary Key Be?

Snowflake for Developers/Developer Blog/What Should My Postgres Primary Key Be?

On this page

What Should My Postgres Primary Key Be?

Postgres

Elizabeth Christensen

Overview

Few decisions in your database are as important as the primary key. This ties together all your tables and even external systems so it is something you need to get right from the beginning. Postgres has some new features for UUIDs, released in Postgres 18, and newer standards for IDENTITY. So today let's look at primary keys, what options Postgres has, and what considerations you might want to factor in to pick for a primary key. And yes, you might want to know this before your LLM picks one for you.

What Is a Primary Key?

Before we get into the options, let's be clear on what conditions a primary key has, and what its use in Postgres implies:

Must be unique: no two rows can have the same value Cannot be null: every row must have a value Automatically creates a B-tree index: lookups are fast by default

CREATE TABLE customers (
   id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   email TEXT NOT NULL
);

-- Postgres automatically creates an index like:
-- CREATE UNIQUE INDEX customers_pkey ON customers USING btree (id);

A primary key is just a UNIQUE NOT NULL constraint that has been associated with a specific table.

Foreign Keys

A foreign key is another table's reference to a primary key. And this relationship with foreign keys and primary keys is what makes reference relationships in relational databases possible.

CREATE TABLE authors (
   id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   name TEXT NOT NULL
);

CREATE TABLE books (
   id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   author_id BIGINT NOT NULL REFERENCES authors(id),
   title TEXT NOT NULL
);

Kinds of Primary Keys

Plain PRIMARY KEY (natural keys)

The simplest primary key is just slapping PRIMARY KEY on a column that already exists in your data:

CREATE TABLE countries (
   country_code TEXT PRIMARY KEY,  -- 'US', 'DE', 'JP'
   name TEXT NOT NULL,
   population BIGINT
);

CREATE TABLE isbn_books (
   isbn TEXT PRIMARY KEY,  -- '978-0-13-468599-1'
   title TEXT NOT NULL,
   published_date DATE
);

This is called a "natural key". The identifier comes from something real in your data — a state name, a customer email address. These can work as primary keys if the value is unique and never null. A desirable but not required property is that the data is stable: country codes or ISBNs generally don't change.

Sometimes no single column uniquely identifies a row, but multiple columns do. That's where composite keys come in:

CREATE TABLE enrollments (
   student_id BIGINT NOT NULL REFERENCES students(id),
   course_id BIGINT NOT NULL REFERENCES courses(id),
   enrolled_at TIMESTAMPTZ NOT NULL DEFAULT now(),
   grade TEXT,
   PRIMARY KEY (student_id, course_id)
);

However, most real-world data isn't that clean. Email addresses change, usernames get renamed, SKUs get reissued. If you have to update a primary key, you will also have to update all foreign keys and these cascades can be complicated. While foreign key constraints can help keep keys updated automatically, it can sometimes be tricky.

For many cases, a synthetic key is the better choice. It never changes, it's compact, and it keeps your foreign keys stable. That's what the next sections are about.

SERIAL Is Out, IDENTITY Is In

If you've been using Postgres for a while, you've probably seen SERIAL:

CREATE TABLE orders (
   id SERIAL PRIMARY KEY,
   amount NUMERIC(10,2)
);

SERIAL creates a sequence and sets a default using the INT data type, BIGSERIAL maps to BIGINT.

The best practice now is to use GENERATED ALWAYS AS IDENTITY. This ensures that your application can't accidentally write a SERIAL or BIGSERIAL and primary keys always come from the database.

CREATE TABLE orders (
   id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   customer_id BIGINT NOT NULL,
   amount NUMERIC(10,2) NOT NULL,
   created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Why BIGINT and not INT? Because running out of integers is a real thing and the storage and cost difference is pretty small. Check out my blog Postgres Serials Should be BIGINT and how to migrate for a deep dive. This also goes into how to update foreign keys which can be tricky.

UUIDs

UUIDs are 128-bit identifiers that are randomized letter/number combos and look like this a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11. They're generated without coordination so any server, any client, etc. Collisions are effectively impossible because there's so many combinations. UUID has a defined spec.

CREATE TABLE api_tokens (
   id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
   user_id BIGINT NOT NULL,
   token_name TEXT NOT NULL,
   created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

UUIDs have grown in popularity in recent years for a variety of reasons:

Safer if your primary key appears in URLs: sequential IDs let anyone guess other resources (/users/1, /users/2, /users/3). UUIDs are opaque. Anything can generate an ID: UUIDs can be generated client side by a mobile app, distributed system, or anywhere you can't round-trip to the database before creating a record. Multi-tenancy: this allows application-side ID generation without database coordination, while being able to merge data across distributed databases. Ideal for REST APIs: the client side can create the UUIDs and they are also better for anonymization.

The tradeoff with UUIDs has always been size and index performance. A UUID is 16 bytes vs 8 for a BIGINT. UUIDs scatter inserts across the B-tree index, which means more random I/O and worse cache behavior on large tables.

The performance issue has partially been solved with Postgres 18 adding uuidv7 to the core platform. These UUIDs are both time-ordered, so they insert in-order like integers, but are still globally unique:

CREATE TABLE events (
   id UUID PRIMARY KEY DEFAULT uuidv7(),
   event_type TEXT NOT NULL,
   payload JSONB,
   created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Shared Keys Across Systems

In practice, your primary key doesn't just live in your database — it lives in Stripe, in your CRM, in your email platform, in log aggregators. This is where the concept of a "shared primary key" or external_id comes in. It's like a foreign key, but pointing at another system entirely.

CREATE TABLE users (
   id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
   email TEXT NOT NULL UNIQUE,
   name TEXT NOT NULL,
   stripe_customer_id TEXT UNIQUE,
   hubspot_contact_id TEXT UNIQUE,
   auth0_user_id TEXT UNIQUE
);

-- When support gets a Stripe webhook, they can find the user instantly
SELECT * FROM users WHERE stripe_customer_id = 'cus_R4nD0m5tr1ng';

If your primary key is a UUID, you can use it as the external ID in other systems too. Set your Stripe customer metadata to your internal UUID, use it as the user identifier in your analytics platform, and pass it in support tickets. This gives you one ID, unambiguous across every system it touches.

Foreign Keys Need Their Own Indexes

Postgres automatically adds a basic B-tree index to every primary key field. But Postgres does NOT automatically index foreign key columns. This has nothing to do with choosing a primary key, but hey, we're already here and this is something everyone needs to know.

Postgres often needs to look up the child table in joins or cascading deletes so indexing foreign keys can have big performance payoffs.

CREATE TABLE books (
   id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   author_id BIGINT NOT NULL REFERENCES authors(id),
   title TEXT NOT NULL
);

CREATE INDEX idx_books_author ON books(author_id);

Primary Keys in Your Analytics Dataset

If your Postgres data ends up in an analytics warehouse (Snowflake, DuckDB, etc.), your primary key choice still matters. Snowflake and most columnar engines don't enforce primary key constraints — they're purely OLTP guarantees. But the values still matter for joining, deduplicating, and tracking lineage across systems.

Integer PKs are compact and compress well in columnar formats like Parquet/Iceberg. UUIDs take more storage but are essential if you're merging data from multiple databases or multiple Postgres instances.

Most analytics systems will need a partitioning strategy, which you can base on time-series data or maybe a primary key. You could even use the time part of a UUID as a generated column to create Iceberg partitions like this:

CREATE TABLE uuid_iceberg_v7 (
   id UUID DEFAULT uuidv7(),
   data TEXT,
   id_ts TIMESTAMP GENERATED ALWAYS AS (
       to_timestamp(
           ('x' || replace(substring(id::text from 1 for 13), '-', ''))::bit(48)::bigint / 1000.0
       )
   ) STORED
) USING iceberg
WITH (
   location = 's3://testbucket/uuid_iceberg_v7/',
   partition_by = 'month(id_ts)'
);

TL;DR — What Should You Pick?

Use case	Primary key type	Why
Most internal tables	BIGINT GENERATED ALWAYS AS IDENTITY	Simple, fast, compact
Legacy serials	BIGINT GENERATED BY DEFAULT AS IDENTITY	Allows preserving/updating old IDs
IDs exposed in URLs/APIs	uuidv7	Opaque, no enumeration risk
App-side ID generation	uuidv7	No coordination needed

Conclusion And Resources

What You Learned

What a primary key is and why it matters
The difference between natural keys and synthetic keys
Why IDENTITY has replaced SERIAL as the standard
How UUIDs (especially uuidv7 in Postgres 18) solve distributed ID generation
Why foreign keys need their own indexes
How primary key choices carry into analytics systems

Related Resources

Updated Sep 8, 2026

This content is provided as is, and is not maintained on an ongoing basis. It may be out of date with current Snowflake instances