PostgreSQL UUID vs Auto Increment: Which Primary Key Should You Use?
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.
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
psql -h store -c 'SELECT min(id), max(id), count(*) FROM orders'min | max | count -----+------+------- 1 | 1042 | 1042 (1 row)
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.

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.

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.

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.
psql -h storepsql (18.7) Type "help" for help.
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.

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.
records=> BEGIN;BEGIN
records=> INSERT INTO orders (customer, album, total) VALUES ('Mia', 'Horses', 24.00) RETURNING id;id ------ 1044 (1 row) INSERT 0 1
records=> ROLLBACK;ROLLBACK
records=> INSERT INTO orders (customer, album, total) VALUES ('Mia', 'Horses', 24.00) RETURNING id;id ------ 1045 (1 row) INSERT 0 1
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'.
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.
records=> CREATE TABLE gift_cards (id serial PRIMARY KEY, amount int);CREATE TABLE
records=> SELECT setval('gift_cards_id_seq', 2147483646);setval ------------ 2147483646 (1 row)
records=> INSERT INTO gift_cards (amount) VALUES (25) RETURNING id;id ------------ 2147483647 (1 row) INSERT 0 1
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.

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.
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)
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.
psql -h branch -c '\copy orders_uuid TO STDOUT' | psql -h store -c '\copy orders_uuid FROM STDIN'COPY 312
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.

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.
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.

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.
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.

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.
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)
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)
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.

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.

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.
cat one_v4.sqlINSERT INTO one_v4 (customer, album, total) VALUES ('Mia', 'Blue', 24.00);
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)
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)
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.

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.

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.

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
- PostgreSQL 18, CREATE TABLE (GENERATED ALWAYS AS IDENTITY creates an implicit sequence)
- PostgreSQL 18, Numeric Types (bigint 8 bytes; serial is integer, 4 bytes, max 2147483647)
- PostgreSQL 18, Sequence Manipulation Functions (nextval is never rolled back, so gaps appear)
- PostgreSQL 18, UUID Type (16 bytes)
- PostgreSQL 18, UUID Functions (gen_random_uuid, uuidv4, uuidv7, uuid_extract_timestamp)
- RFC 9562, Universally Unique IDentifiers (UUID version 7: a 48-bit Unix millisecond timestamp first)
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.