Cloud Explainers

PostgreSQL UUID vs Auto Increment: Which Primary Key Should You Use?

PostgreSQL10 min video

Auto increment or UUID for your PostgreSQL primary key? On real PostgreSQL 18 servers we merge two stores' order tables (duplicate key on order 1), watch a cancelled insert leave a gap, read a store's weekly sales from two order numbers, run a serial column out of numbers, and time 5 million inserts with bigint, random UUIDs and PostgreSQL 18's new uuidv7().

Two stores, both started at 1

Which primary key should your table use: auto increment, or UUID? Pick wrong, and it comes back to bite you. A record store just bought a second store, and Ana copies the second store's orders into the main database. PostgreSQL refuses: duplicate key. Id 1 already exists. Both stores' ids came from a counter, and both counters started at 1. By the end, you'll know which key to pick, with the real speed and size costs measured.

Ana's laptop
psql -h branch -c '\copy orders TO STDOUT' | psql -h store -c '\copy orders FROM STDIN'ERROR:  duplicate key value violates unique constraint "orders_pkey"
DETAIL:  Key (id)=(1) already exists.
CONTEXT:  COPY orders, line 1
Ana's laptop
psql -h store -c 'SELECT min(id), max(id), count(*) FROM orders' min | max  | count
-----+------+-------
   1 | 1042 |  1042
(1 row)
Ana's laptop
psql -h branch -c 'SELECT min(id), max(id), count(*) FROM orders' min | max | count
-----+-----+-------
   1 | 312 |   312
(1 row)

What an auto increment key is

So what is an auto increment key? When a new row arrives without an id, the table asks a small counter, called a sequence, for the next number: the last one plus one. That number becomes the row's primary key: the one value that names this row. It's never used twice. The catch: the counter lives inside one database, for one store.

Auto increment: one counter hands out every id: diagram of Till, Sequence, orders table, Primary key

It works like the take-a-number roll at a deli counter. Pull a ticket, get the next number, no two customers hold the same one. But the deli across town has its own roll, starting at 1. Put both queues in one room, and two people answer to number 1.

A deli's ticket roll vs a sequence: comparison table of Deli counter vs PostgreSQL, Hands out, Never twice, Another place, Put together

In PostgreSQL today you write it like this. bigint: an 8-byte whole number. Generated always as identity: the database makes the sequence and fills the id in for you, so you never send one yourself. Add primary key, and the id is unique, and indexed, so finding a row by its id is fast.

An auto increment key, taken apart: diagram of id bigint GENERATED ALWAYS AS IDENTITY, bigint = 8 bytes, GENERATED ALWAYS AS IDENTITY = the counter, Add PRIMARY KEY: unique, and indexed for fast lookups

Back at the main store, a new order from Mia. Ana doesn't send an id; RETURNING shows the one PostgreSQL picked: 1043, one more than the last order.

Ana's laptop
psql -h storepsql (18.7)
Type "help" for help.
Ana's laptop
records=> INSERT INTO orders (customer, album, total) VALUES ('Mia', 'Kind of Blue', 24.00) RETURNING id;  id
------
 1043
(1 row)

INSERT 0 1

So how do you merge two counters? Renumber one store's orders, and fix every table that points at them. Or make the key two columns together: the store and the order number, so second store, order 1 can't clash. Both work. Both are work. The third way: ids that never need a shared counter. We'll get there.

Merging two counters: cards of Renumber one store, Key on two columns, Both work. Both are work. Third way: no shared counter

What a counter gives away

Now Ana starts an order and cancels it. BEGIN opens a change she can still undo, the insert gets 1044, then ROLLBACK undoes it, so that row never existed. Guess first: the next order, which id does it get? 1045. A sequence never takes a number back, so orders arriving at the same moment never wait on it, and ids have gaps. That's normal; never count rows by the last id.

Ana's laptop
records=> BEGIN;BEGIN
Ana's laptop
records=> INSERT INTO orders (customer, album, total) VALUES ('Mia', 'Horses', 24.00) RETURNING id;  id
------
 1044
(1 row)

INSERT 0 1
Ana's laptop
records=> ROLLBACK;ROLLBACK
Ana's laptop
records=> INSERT INTO orders (customer, album, total) VALUES ('Mia', 'Horses', 24.00) RETURNING id;  id
------
 1045
(1 row)

INSERT 0 1
Ana's laptop
records=> SELECT id, customer, album FROM orders WHERE id > 1042;  id  | customer |    album
------+----------+--------------
 1043 | Mia      | Kind of Blue
 1045 | Mia      | Horses
(2 rows)

Order numbers also talk. Leo, a regular, ordered on November the fourth and got order 851. A week later he got 945. Subtract, and Leo knows the store sold about 94 orders that week. Put that id in a web address, and anyone can count your sales, or try the next number. It's an old trick: in the Second World War, Allied statisticians estimated German tank production from the serial numbers on captured tanks, and their estimates beat the spies'.

Ana's laptop
records=> SELECT id, ordered_at::date FROM orders WHERE customer = 'Leo'; id  | ordered_at
-----+------------
 851 | 2026-11-04
 945 | 2026-11-11
(2 rows)

The counter that runs out

Many tutorials still use serial. Serial is the old way, and it's a 4-byte integer. Ana makes a gift card table with it, and setval fast-forwards its counter near the top, just for this test. One card gets id 2,147,483,647, the largest a 4-byte integer can hold. The next insert fails: the sequence reached its maximum value.

Ana's laptop
records=> CREATE TABLE gift_cards (id serial PRIMARY KEY, amount int);CREATE TABLE
Ana's laptop
records=> SELECT setval('gift_cards_id_seq', 2147483646);   setval
------------
 2147483646
(1 row)
Ana's laptop
records=> INSERT INTO gift_cards (amount) VALUES (25) RETURNING id;     id
------------
 2147483647
(1 row)

INSERT 0 1
Ana's laptop
records=> INSERT INTO gift_cards (amount) VALUES (25) RETURNING id;ERROR:  nextval: reached maximum value of sequence "gift_cards_id_seq" (2147483647)

That's about 2 billion rows: logs and events get there. Fixing it later means rewriting a full table. So for a counter key, start with bigint identity. Eight bytes instead of four, and you won't run out.

Start with bigint identity, not serial: code change of schema.sql, id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, (8 bytes: about 9.2 quintillion ids)

What a UUID is

Remember the third way, ids that never need a shared counter? That's a UUID: a universally unique identifier. No counter: 16 bytes, mostly random, made anywhere, by the database, the app, or a phone offline. The odds of two random ones matching are so small you can ignore them: make a billion every second for about 85 years, and you'd have an even chance of one repeat. Back at the deli, it's as if every customer brought their own long code instead of pulling a ticket. The cost: 16 bytes, twice a bigint, whichever version you make.

Ana's laptop
records=> SELECT gen_random_uuid() FROM generate_series(1, 3);           gen_random_uuid
--------------------------------------
 d789def0-e6db-43c1-b8c7-4aeb137afe06
 d7414e6e-b07e-4875-bba5-264273b5fcc0
 b8aa6952-33a8-49be-a081-5d0e19d6d5aa
(3 rows)
Ana's laptop
records=> SELECT pg_column_size(1043::bigint) AS bigint, pg_column_size(uuidv7()) AS uuid; bigint | uuid
--------+------
      8 |   16
(1 row)

So Ana gives both stores' orders UUID keys, and copies the second store again. PostgreSQL reports 312 rows copied: every order went in. 1354 orders in one table, no clashes, because no two stores were counting.

Ana's laptop
psql -h branch -c '\copy orders_uuid TO STDOUT' | psql -h store -c '\copy orders_uuid FROM STDIN'COPY 312
Ana's laptop
psql -h store -c 'SELECT count(*) FROM orders_uuid' count
-------
  1354
(1 row)

Random or time-ordered

Which UUID, though? PostgreSQL's own docs list two kinds. Version 4 is fully random: gen random UUID. Version 7, new in PostgreSQL 18, is time-ordered: it starts with the time it was made, in milliseconds, then random bits.

Random or time-ordered: browser screenshot from the video

Three of each, side by side. The version 4 ids share nothing. The three version 7 ids, made in the same millisecond, start with the same 12 characters: the clock. Sorted by id, they're sorted by time, like a counter.

Ana's laptop
records=> SELECT uuidv7() FROM generate_series(1, 3);                uuidv7
--------------------------------------
 01a1f10f-cf99-76c5-9202-ee8bd2327e6f
 01a1f10f-cf99-76d3-8c49-4f40cba639e4
 01a1f10f-cf99-76d6-aa5d-cca5fe9c6245
(3 rows)

Taken apart: the first 12 characters are the time, the 7 is the version, and the rest is random, so two stores still never clash.

A UUIDv7, taken apart: diagram of 01a1f10f-cf99-76c5-9202-ee8bd2327e6f, 01a1f10f-cf99 = the time, 76c5-9202-ee8bd2327e6f = 7 = version, then random

That time is readable. uuid extract timestamp pulls it out of the id, and colon colon date keeps just the day. Run on Leo's two orders, it gives back the days he ordered. So a version 7 id doesn't tell how many orders you have, but it does tell when this one was made. If that's private, keep it out of public addresses.

Ana's laptop
records=> SELECT id, uuid_extract_timestamp(id)::date FROM orders_uuid WHERE customer = 'Leo';                  id                  | uuid_extract_timestamp
--------------------------------------+------------------------
 01a1a3e8-95e3-719c-9c7d-8ef61673ba91 | 2026-11-04
 01a1c820-395a-7c8e-924e-55262cc101b1 | 2026-11-11
(2 rows)

Does the bigger key cost you?

Does the bigger key cost you? The primary key's index keeps every key in order, in fixed-size blocks called pages, eight kilobytes each. Picture them as a shelf. Counter ids and version 7 ids always land at the end: the last page fills, then a new one starts. Random ids land anywhere, so full pages split in half, and more pages are touched for every insert.

Where a new key lands in the index: diagram of Page 1, Page 2, Page 3, Last page, New key

Now the cost, measured. Ana times it: 5 million orders into three empty tables, one per kind of key, with psql's timer on. bigint: 3.3 seconds. Random UUID: how long do you think? 9.9 seconds, about 3 times as long. Version 7: 5.2.

Ana's laptop
records=> INSERT INTO bulk_bigint (customer, album, total) SELECT 'Mia', 'Blue', 24.00 FROM generate_series(1, 5000000);INSERT 0 5000000
Time: 3255.885 ms (00:03.256)
Ana's laptop
records=> INSERT INTO bulk_v4 (customer, album, total) SELECT 'Mia', 'Blue', 24.00 FROM generate_series(1, 5000000);INSERT 0 5000000
Time: 9924.740 ms (00:09.925)
Ana's laptop
records=> INSERT INTO bulk_v7 (customer, album, total) SELECT 'Mia', 'Blue', 24.00 FROM generate_series(1, 5000000);INSERT 0 5000000
Time: 5202.387 ms (00:05.202)

And the index: 107 megabytes for bigint, 150 for version 7, 195 for random UUIDs. Both UUIDs are bigger because each key is twice the bytes. Random ones are bigger still, because all those page splits leave pages half empty.

Primary key index for 5 million orders: chart of bigint: 107 MB, UUIDv7: 150 MB, UUIDv4 (random): 195 MB, Bigger keys, plus half-empty pages

And a key never lives in just one place. Every table that points at an order, payments, shipments, the albums in each order, stores a copy of that key, and usually an index on it. So 8 extra bytes per key show up again in each of them. That adds up on big tables, and barely matters on small ones.

Every table that points at an order stores its key: diagram of orders, payments, shipments, order items

But a record store takes orders one at a time. pgbench, PostgreSQL's built-in load tester, sends single inserts from 8 connections for 15 seconds, like 8 tills at once: about 33 thousand a second with bigint, 36 thousand with random UUIDs, 37 thousand with version 7. Differences that small are noise. At this size, all three keep up. The gap opens on big loads, and on tables so large the index no longer fits in the server's memory.

Ana's laptop
cat one_v4.sqlINSERT INTO one_v4 (customer, album, total) VALUES ('Mia', 'Blue', 24.00);
Ana's laptop
pgbench -h store -n -c 8 -T 15 -f one_bigint.sql 2>&1 | grep -E 'latency average|tps'latency average = 0.240 ms
tps = 33379.011685 (without initial connection time)
Ana's laptop
pgbench -h store -n -c 8 -T 15 -f one_v4.sql 2>&1 | grep -E 'latency average|tps'latency average = 0.223 ms
tps = 35807.699901 (without initial connection time)
Ana's laptop
pgbench -h store -n -c 8 -T 15 -f one_v7.sql 2>&1 | grep -E 'latency average|tps'latency average = 0.219 ms
tps = 36507.994064 (without initial connection time)

Which one should you use?

Before you pick, ask three questions. Will ids be made outside this one database: by an app, another server, a phone offline? Will two databases ever be merged? Will the id be shown to customers, in a web address or an email? Three times no: a counter is the simple choice. Any yes: a UUID earns its extra bytes.

Three questions before you pick: cards of Made outside the database?, Merged with another database?, Shown to customers?, All no: a counter

So which one? All three answers no: bigint identity, the smallest and fastest. Any yes, merged later, or shown to customers: a UUID, and make it version 7. Random version 4 makes a fine secret token, but a costly primary key. In short: one counter in one place, or ids made anywhere.

bigint identity or UUIDv7?: comparison table of bigint identity vs UUIDv7, Size, Made by, Merging stores, In a web address, In short

Recap

So: a counter is small and fast, but it has gaps, it shows your volume, and two counters clash. Use bigint, never serial. A UUID can be made anywhere and merged; make it version 7.

Primary keys in one picture: diagram of bigint identity, not serial, UUIDv7, UUIDv4

If this settled your table's primary key, a like helps another beginner find it, and subscribing brings more database choices, measured on real servers.

Every error, time and size in this video came from real PostgreSQL 18 servers, and every command and the lab files are linked in the description. Thanks for watching Cloud Explainers.

Sources

Demos: three containers on our own Mac standing in for two record stores' databases and a laptop; the stores, customers and orders are made up. Every error, time and size was real. Watch the full video on YouTube.

More videos

Python8 min

How to Fix Python ModuleNotFoundError: No Module Named

Go (Golang)5 min

Golang Pointers in 5 Minutes

Kubernetes8 min

How to Fix a Kubernetes Pod Stuck in CrashLoopBackOff

Homelab & self-hosting9 min

How to Fix a Homelab ESXi Host Not Responding

PostgreSQL24 min

PostgreSQL: Full Tutorial for Beginners

Load balancing & Nginx9 min

How a Load Balancer Works with HAProxy

Python9 min

How Python Virtual Environments Work

Homelab & self-hosting9 min

How to Fix Cloudflare Tunnel Errors in a Homelab

APIs9 min

How to Fix API CORS Errors: No 'Access-Control-Allow-Origin'

Auth (OAuth, JWT)9 min

How Authenticator App Login Codes Work (TOTP)

Git22 min

Git and GitHub: Full Tutorial for Beginners

Homelab & self-hosting10 min

How a Homelab Hypervisor Works (VMware ESXi)

Kubernetes9 min

How to Fix Kubernetes ImagePullBackOff Errors

DNS8 min

How to Fix DNS 'Temporary Failure in Name Resolution' on Linux

AWS8 min

How to Fix AWS CLI 'Unable to Locate Credentials'

Homelab & self-hosting10 min

Proxmox vs ESXi for Your Homelab: When Each One Wins

Kubernetes21 min

Kubernetes: Full Tutorial for Beginners

Networking9 min

How TCP Sockets Work

PostgreSQL7 min

How to Fix PostgreSQL 'Could Not Connect to Server'

Claude & the Claude Developer Platform9 min

Claude Code Hooks Will Finally Make Sense After This

DevOps & CI/CD9 min

DevOps with Ansible Will Finally Make Sense After This

Networking9 min

How SNMP Network Monitoring Works

Git8 min

How to Fix GitHub 'Permission Denied (publickey)' Errors

Docker21 min

Docker: Full Tutorial for Beginners

PostgreSQL7 min

How to Fix PostgreSQL 'Password Authentication Failed'

Linux8 min

Why One Linux Server Handles More Users Than You Think

Networking7 min

How to Fix Network 'Connection Refused' Errors

DevOps & CI/CD9 min

DevOps Pipelines: Jenkins vs GitHub Actions

Linux11 min

Linux Nginx Reverse Proxy: Full Tutorial for Beginners

Networking11 min

Every Networking Term a Beginner Needs in 12 Minutes

Docker6 min

How to Fix Docker 'Permission Denied' on docker.sock

Git7 min

How to Fix Git 'Updates Were Rejected' When You Push

Linux7 min

How to Fix nginx 502 Bad Gateway Errors on Linux

Linux8 min

How to Fix Linux apt 'Could Not Get Lock' Errors (Ubuntu)

Networking9 min

How Email Works on the Internet (SMTP) in 10 Minutes

Python8 min

The Python Roadmap for Beginners

Go (Golang)21 min

Golang REST API with Gin: Full Tutorial for Beginners

Networking10 min

How a WireGuard VPN Network Works

Linux9 min

Linux Makefiles Will Finally Make Sense After This

Auth (OAuth, JWT)9 min

SAML vs OAuth vs OpenID Connect: What's the Difference?

Python7 min

How to Fix Python cron Jobs That Never Run

Linux22 min

Linux Bash Scripting: Full Tutorial for Beginners

Networking10 min

How PXE Network Boot Works

Active Directory9 min

How Active Directory Works

Linux10 min

How to Fix a Linux systemd Service That Won't Start

Linux9 min

Linux Logs: syslog vs journald

Auth (OAuth, JWT)9 min

JWT vs Session Cookies: Which One Should You Use?

Docker10 min

Docker vs Docker Compose: Which One Should You Use?

Docker9 min

How to Fix Docker Compose Containers That Can't Talk to Each Other

Django (Python web framework)22 min

Django Full Tutorial for Beginners

Linux9 min

How Linux systemd Works

Networking8 min

How to Fix 'Windows Cannot Access' Network Share Errors (SMB)

APIs10 min

REST vs gRPC: Which One Should You Use?

Certificates & PKI (ADCS)9 min

How to Fix SSL Certificate Chain Errors (Missing Intermediate)

Python9 min

Every Python Concept for Beginners in 10 Minutes

Databases & SQL9 min

Every SQL Join Type in 10 Minutes

OpenRouter (one API for many AI models)9 min

Why OpenRouter Free Models Hit Their Limit So Fast

Gemini API & Google AI Studio9 min

Why Your Gemini API Free Tier Runs Out So Fast

OpenRouter (one API for many AI models)10 min

OpenRouter Will Finally Make Sense After This

Caching & Redis10 min

How Redis Caching Works

Gemini API & Google AI Studio10 min

Gemini API vs Vertex AI: Which One Should You Use?

Gemini API & Google AI Studio8 min

How to Fix a Leaked Gemini API Key

Go (Golang)9 min

Why Golang Uses Goroutines Instead of Threads

Gemini API & Google AI Studio9 min

How to Fix Gemini API Error 429: Resource Exhausted

Claude & the Claude Developer Platform10 min

Claude Token Pricing Will Finally Make Sense After This

Python11 min

Why Python Web Scraping Returns Nothing (and the Fix)

Python10 min

Python uv vs pip: Which One Should You Use?

Python22 min

Python Automation: Full Tutorial for Beginners

Auth (OAuth, JWT)9 min

How OAuth Login Works

Gemini API & Google AI Studio10 min

Google AI Studio Will Finally Make Sense After This

Docker10 min

What Happens When You Run a Docker Container

Claude & the Claude Developer Platform8 min

How to Fix Claude Code Billing Your API Key Instead of Your Plan

Auth (OAuth, JWT)9 min

Why Passkeys Can't Be Phished Like Passwords

PowerShell10 min

PowerShell vs Bash: Which One Should You Learn?

Linux10 min

The Linux Roadmap for Beginners

Django (Python web framework)9 min

Django vs FastAPI: Which One Should You Use?

Databases & SQL10 min

Why SQLite Is the Most Used Database in the World

Databases & SQL11 min

How a NoSQL Database Like MongoDB Works

Python11 min

How to Fix Python pip error: externally-managed-environment

Databases & SQL9 min

How to Fix MySQL Too Many Connections Database Errors

Go (Golang)9 min

Golang Concurrency Will Finally Make Sense After This

AWS10 min

How Amazon S3 Works

Kubernetes10 min

How Kubernetes Works

Git10 min

How Git Works

Docker10 min

How to Fix Docker Port Already Allocated Errors

PostgreSQL10 min

How PostgreSQL Works

Git8 min

How to Fix Git Merge Conflicts

Linux9 min

How to Fix No Space Left on Device in Linux

Networking10 min

How the Internet Works

Databases & SQL9 min

SQL vs NoSQL: When Each One Wins

Auth (OAuth, JWT)8 min

How to Fix JWT Token Expired Errors (401 Unauthorized)

Claude & the Claude Developer Platform9 min

Claude API vs Claude Pro: Why Your Subscription Doesn't Include It

AI agent skills9 min

Why AI Agent Skills Beat Giant Prompts

Linux10 min

Linux Cron Jobs Will Finally Make Sense After This

Cybersecurity10 min

Security Logs Will Finally Make Sense After This

Cybersecurity8 min

How to Fix a Security Leak: An API Key Pushed to GitHub

DNS9 min

DNS Will Finally Make Sense After This

Cybersecurity9 min

Never Ignore a Security Update

Auth (OAuth, JWT)9 min

Two-Factor Authentication Will Finally Make Sense

Networking8 min

Never Expose a Database to the Internet

Cybersecurity10 min

SSH Keys vs Passwords: Which Is Safer for Security?

Linux10 min

The 5 Levels of Linux Boot: From Power On to Login

Networking12 min

The 5 Levels of Network Firewalls

Cybersecurity10 min

Password Security: Why Hackers Crack Weak Passwords

Linux9 min

Why Your Linux Server Runs Out of Memory

Networking10 min

HTTP vs HTTPS: What Actually Changes on Your Network

Linux12 min

Every Linux File Permission You Need to Know

Linux10 min

Linux Processes vs Threads: When Each One Wins

Networking11 min

The OSI Model Will Finally Make Sense After This

Git11 min

The 7 Levels of Git

Networking10 min

Every Network Troubleshooting Command You Need

Cybersecurity10 min

Hashing vs Encryption in Cybersecurity: When Each One Wins

Networking11 min

Why the Internet Ran Out of IP Addresses

Linux11 min

Linux Package Managers: apt vs Snap vs Flatpak

Python9 min

Why Python Is Slow (and When It Doesn't Matter)

Networking11 min

TCP vs UDP: When Each One Wins

Linux8 min

Stop Running Linux as Root (Do This Instead)

Linux11 min

Linux Users and Groups Will Finally Make Sense

Cybersecurity9 min

Why Phishing Beats Good Security (and How to Spot It)

Docker12 min

The 7 Levels of Docker

AI agents & MCP12 min

AI Agents Will Finally Make Sense After This

Git8 min

Stop Using git push --force (Use This Instead)

AI agent skills10 min

Agent Skills vs MCP: When Each One Wins

Networking12 min

Networking Will Finally Make Sense After This

Linux12 min

Every Linux Command You Actually Need

Cybersecurity12 min

The 7 Levels of Cybersecurity

System design12 min

Every System Design Concept You Need, in One Diagram

PostgreSQL10 min

Why PostgreSQL Ignores Your Index