A UUID is a 128-bit identifier conventionally written as 32 hexadecimal digits separated by hyphens. PostgreSQL natively supports UUIDv4, whose randomly generated values make duplicates extraordinarily unlikely and harder to guess than sequential integers. section 5.4
The tempting mistake is to use UUIDs everywhere simply because their range and opacity look universally useful. UUIDs occupy 16 bytes, compared with 8 bytes for bigint, and random generation and indexing add measurable cost. section 5.4 section 5.7
A production-shaped comparison
A PostgreSQL test inserts 100 million rows, adds a primary key, and inspects the resulting index. UUIDs take about 06:28 to insert and 01:16 to index, with a 3008 MB index. bigint takes about 01:23 to insert and 00:38 to index, with a 2142 MB index. The UUID index is therefore 40% larger in this comparison. section 5.4
Table 5.3 PostgreSQL data type sizes
|
Data type
|
Size in bytes
|
|---|---|
boolean
|
1
|
int
|
4
|
bigint
|
8
|
timestamptz
|
8
|
double precision
|
8
|
uuid
|
16
|
text
|
1 + string bytes (+4 if > 127 bytes)
|
Use the data-size table to compare UUID's 16-byte storage with bigint's 8-byte representation and other PostgreSQL types. section 5.4
\timing
Timing is on.
CREATE TABLE test.tab (id uuid, content text);
CREATE
Time: 11.737 ms
INSERT INTO test.tab
SELECT gen_random_uuid(), 'test' FROM generate_series(1,100000000);
INSERT 0 100000000
Time: 387838.234 ms (06:27.838)
ALTER TABLE test.tab ADD PRIMARY KEY (id);
ALTER TABLE
Time: 75875.825 ms (01:15.876)
Inspect the executable psql workload that generates UUIDv4 values, inserts 100 million rows, and builds a primary-key index. section 5.4
Choose UUIDs for a requirement that genuinely needs their broad, opaque identifier space; otherwise, a bigint index may provide sufficient uniqueness with less storage and better performance. Large UUID-heavy rows can also increase pressure on PostgreSQL's TOAST storage. section 5.4 section 5.7
