Developer tools · Database sizing
UUID vs bigint primary key overhead.
How many extra gigabytes a wider key costs across the table, its foreign keys and its indexes.
Inputs
Key storage comparison
Enter the values.
How much more space does a UUID primary key use than a bigint?
A bigint is 8 bytes and a uuid is 16 bytes (128 bits), so each stored copy of the key is 8 bytes bigger, and a uuid kept as 36 character text is 28 bytes bigger. The key is stored more than once: in the table, in every foreign key column that references it, and in index entries. In PostgreSQL an index entry is an 8 byte header plus the key rounded to a multiple of 8, plus a 4 byte line pointer, so a bigint entry is 20 bytes and a uuid entry is 28. In MySQL InnoDB every secondary index record also contains the primary key columns, so a wide primary key inflates every secondary index; the MySQL manual advises a short primary key for this reason.
Worked example (PostgreSQL). 10,000,000 rows and 30,000,000 referencing rows with indexed foreign keys: 40,000,000 key columns at +8 bytes is 320 MB, and 40,000,000 index entries at +8 bytes is another 320 MB, about 640 MB (0.60 GiB) more for uuid than bigint.
Column sizes here count key bytes only and ignore alignment padding in neighbouring columns, B-tree fill and page overhead, so treat the figure as a lower-bound estimate. Random uuid values also scatter inserts across the index, which this size estimate does not capture.
Frequently asked questions
Is a UUID primary key slower or bigger than bigint?
It is bigger: 16 bytes against 8 for each stored copy, on the table, every foreign key column and every index entry. This calculator totals that difference for your row counts.
Why do UUID keys hurt InnoDB secondary indexes more?
Each InnoDB secondary index record contains the primary key columns, so the primary key width is repeated in every secondary index on the table.
How many bytes is a UUID stored as text?
A UUID in its standard text form is 36 characters, which takes 36 bytes in a single-byte character set, against 16 bytes in a binary or native uuid column.
Does PostgreSQL repeat the primary key in every index?
No. PostgreSQL indexes point to the row by its physical location rather than by primary key, so only the primary key index and foreign key indexes hold the key value.
Sources
Checked
- PostgreSQL docs: Numeric Types
- PostgreSQL source: pg_type.dat (uuid and int8 length and alignment)
- PostgreSQL source: itup.h (IndexTupleData)
- MySQL docs: Clustered and Secondary Indexes
Sizes are from PostgreSQL pg_type.dat and itup.h and the MySQL manual page above. Text uuid assumes one byte per character. The 128-bit uuid size follows from the PostgreSQL uuid type definition. Runs entirely in your browser. Nothing you type is sent or stored.
Scope
Planning estimates, not guarantees. Real systems add overhead this model leaves out and behave differently under load. Measure on your own data and load-test before committing to a design or a bill.