Primary Keys: Identity, GUID or Natural Key?

Every table in Part 1 got a bigint identity as its primary key without discussion. This part has the discussion. The same patients table is built three times, with an identity, a sequential GUID and a random GUID, each gets 100,000 fictional rows, and the sizes and page fill are measured on both engines. Then a natural key, the phone number, gets its turn and shows why it is not free.

Three keys, one table

A primary key has one job: to identify a row so that other tables can point at it. Three kinds of value are commonly given that job. Section 2 in PostgreSQL:

CREATE TABLE patients_identity (
    id          bigint GENERATED ALWAYS AS IDENTITY CONSTRAINT pk_patients_identity PRIMARY KEY,
    full_name   varchar(200) NOT NULL,
    phone       varchar(30)  NOT NULL,
    email       varchar(200),
    created_at  timestamptz  NOT NULL DEFAULT now()
);
--    b) UUID version 7: 16 bytes, starts with a timestamp, so new keys increase.
CREATE TABLE patients_seq_guid (
    id          uuid NOT NULL DEFAULT uuidv7() CONSTRAINT pk_patients_seq_guid PRIMARY KEY,
    ...
);
--    c) UUID version 4: 16 bytes, every new row lands at a random place in the key order.
CREATE TABLE patients_random_guid (
    id          uuid NOT NULL DEFAULT gen_random_uuid() CONSTRAINT pk_patients_random_guid PRIMARY KEY,
    ...
);

The SQL Server script uses BIGINT IDENTITY(1,1), UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID() and UNIQUEIDENTIFIER DEFAULT NEWID(). One rule of SQL Server's is that NEWSEQUENTIALID() works only as a column default; PostgreSQL 18's uuidv7() is a plain function you can call anywhere. The key sizes are the first thing the script prints: 8 bytes for the bigint, 16 for either GUID, on both engines.

Section 7 shows what each key looks like once rows exist. PostgreSQL, first three of each:

 id |  full_name
----+--------------
  1 | David Garcia
  2 | Aisha Garcia
  3 | Tomás Garcia

                  id                  |  full_name
--------------------------------------+--------------
 01a0d6fd-774d-7d81-b67b-c20a54d8464f | David Garcia
 01a0d6fd-774d-7ef8-88de-575db759dde1 | Aisha Garcia
 01a0d6fd-774d-7f11-8595-29d5af7f8fc5 | Tomás Garcia

                  id                  |  full_name
--------------------------------------+--------------
 000004cc-28cb-4dd6-8f69-dcae5f9e1f77 | David Chen
 000021da-ae47-4af2-b858-2e47e56bbc55 | Priya Nair
 0001315f-e5af-4a72-a009-95995b92f253 | Maria Tanaka

The three uuidv7 values share their first twelve characters because those bits are the time of the insert in milliseconds; the random ones share nothing, and the "first three" of a random key are simply whichever rows happened to draw the smallest numbers.

An identity refuses a value you choose

Section 3 inserts a row with id = 42. SQL Server:

Msg 544, Level 16, State 1, Line 3
Cannot insert explicit value for identity column in table 'patients_identity' when IDENTITY_INSERT is set to OFF.

PostgreSQL, where the table was declared GENERATED ALWAYS:

ERROR:  cannot insert a non-DEFAULT value into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

That refusal is a feature: an id you typed in is an id the counter does not know about, and the next generated one can collide with it. ClinicLive's real tables, created by EF Core, are GENERATED BY DEFAULT, which accepts a typed id silently; a probe on Part 1's database did exactly that and the next ordinary insert failed with Key (id)=(4) already exists. Prefer ALWAYS when you write the DDL yourself, and know that a seed script with explicit ids is the usual way people meet the collision.

100,000 rows, measured

Section 4 generates 100,000 fictional patients into a temporary table, and sections 5 and 6 load each real table from it, twice, with timing on. On PostgreSQL the identity load took about 122 ms, the uuidv7 load about 166 ms and the random one about 199 ms, in every one of four rounds. On SQL Server all three landed between 86 and 230 ms and the order between identity and sequential GUID changed from round to round, so the only honest claim from the timings is that the random GUID was slowest in all eight measurements. Tens of milliseconds on one PC are not a benchmark. The sizes are, because they are deterministic. Section 8 on PostgreSQL:

      table_name      | table_kb | pk_index_kb | total_kb | total
----------------------+----------+-------------+----------+-------
 patients_identity    |     9880 |        2208 |    12112 | 12 MB
 patients_seq_guid    |    10672 |        3104 |    13800 | 13 MB
 patients_random_guid |    10672 |        4336 |    15032 | 15 MB

        pk_index         | leaf_pages | avg_leaf_density | leaf_fragmentation
-------------------------+------------+------------------+--------------------
 pk_patients_identity    |        274 |            89.78 |                  0
 pk_patients_seq_guid    |        384 |            89.79 |                  0
 pk_patients_random_guid |        537 |             64.3 |              50.28

And on SQL Server, sp_spaceused and the physical index statistics:

name reserved data index_size unused
patients_identity 13064 KB 12992 KB 72 KB 0 KB
patients_seq_guid 13960 KB 13792 KB 104 KB 64 KB
patients_random_guid 19976 KB 19816 KB 104 KB 56 KB

table_name page_count page_full_pct fragmentation_pct
dbo.patients_identity 1624 99.3 .4
dbo.patients_seq_guid 1724 99.2 .6
dbo.patients_random_guid 2477 69.1 98.8

Read the two engines separately, because they store rows differently. In SQL Server the primary key is clustered by default, so the key order is the table's physical order; a random key means every new row lands in the middle of a page, pages split, and the table itself ends up 53 percent bigger with pages 69 percent full. In PostgreSQL rows go into the heap in arrival order whatever the key, so the heap is the same size for both GUID tables; only the primary key index pays, and the random one is twice the identity index and 40 percent bigger than uuidv7's, at 64 percent leaf density. The sequential GUID costs a little space on both for its 16 bytes, and nothing in order.

So the decision is about what a key must do. If ids are made by one database, a bigint identity is the smallest, fastest and simplest key. If ids must be made elsewhere, in an app before the row exists, on a phone that is offline, or in several databases that later merge, a GUID is the right tool, and it should be a sequential one, uuidv7() or NEWSEQUENTIALID(). A random GUID is the one to avoid as a primary key, and the numbers above are why.

The natural key that looked free

A phone number identifies a patient, ClinicLive looks patients up by it, and it is already unique. Why not make it the primary key and save a column? Section 9 builds that table, with visits pointing at the phone, and runs into the two problems in order:

CREATE TABLE patients_by_phone (
    phone      varchar(30)  NOT NULL CONSTRAINT pk_patients_by_phone PRIMARY KEY,
    full_name  varchar(200) NOT NULL
);
CREATE TABLE visits_by_phone (
    id             bigint GENERATED ALWAYS AS IDENTITY CONSTRAINT pk_visits_by_phone PRIMARY KEY,
    patient_phone  varchar(30) NOT NULL
        CONSTRAINT fk_visits_by_phone_patients REFERENCES patients_by_phone (phone),
    visited_on     date NOT NULL
);

First, a second person on a shared household phone cannot be registered at all: duplicate key value violates unique constraint "pk_patients_by_phone", and SQL Server says the same with Msg 2627. The key was never a fact about the person; it was a fact about the household. Second, Yuki Tanaka changes her number, and the update is refused because three visits still point at the old one:

ERROR:  update or delete on table "patients_by_phone" violates foreign key constraint "fk_visits_by_phone_patients" on table "visits_by_phone"
DETAIL:  Key (phone)=(+00-555-0201) is still referenced from table "visits_by_phone".

Section 9c makes the change possible with ON UPDATE CASCADE, and the engine rewrites all three visit rows, and would rewrite every row in every table that ever referenced the patient. A key that changes is a key that spreads. A surrogate id never changes, so the phone can be what it is, an attribute with a unique constraint, exactly as Part 1 built it.

A composite key where it belongs

There is one table where the natural key is right: the junction. Section 10 links doctors to specialties, and the pair of ids is the key:

CREATE TABLE doctor_specialties (
    doctor_id     bigint NOT NULL CONSTRAINT fk_doctor_specialties_doctors REFERENCES doctors (id),
    specialty_id  bigint NOT NULL CONSTRAINT fk_doctor_specialties_specialties REFERENCES specialties (id),
    CONSTRAINT pk_doctor_specialties PRIMARY KEY (doctor_id, specialty_id)
);

Inserting (1, 2) a second time is refused by pk_doctor_specialties on both engines, which is the rule you wanted: a doctor holds a specialty once. Part 4 takes this table further, including the case where a surrogate id on the junction earns its place.

Frequently asked

Should I use int, bigint or GUID for a primary key?

Use a bigint identity when one database creates the ids: it is 8 bytes, always increasing and the smallest index. Use a GUID when ids must be created outside the database or across several databases, and make it sequential (uuidv7 or NEWSEQUENTIALID). Avoid random GUIDs as primary keys.

Why are random GUIDs bad for a clustered primary key?

Each new row lands at a random place in the key order, so pages split and stay part-empty. On 100,000 rows the SQL Server table was 53 percent bigger with pages 69 percent full, and the PostgreSQL primary key index was twice the size of the identity index.

Is a phone number or email a good primary key?

No. Natural keys can be shared (a household phone), can change (a new number), and when they change every referencing row has to change too. Keep them as unique attributes and use a surrogate id as the key. The one place a natural composite key fits is a junction table.

Next: Part 3, foreign keys and ON DELETE, where deleting a patient meets three different rules. The 2024 series introduced keys in Deciding attributes for entities.