Some notes from AWS Database Blog: Getting more with PostgreSQL purpose-built data types.
Use PostgreSQL’s native UUID type instead of commonly use varchar(36) (need 36 bytes to represent UUID correctly). Here is why:
- Native UUID only uses 16 bytes
-
varchar(36)uses 37 bytes (1 byte for column header)
16/37 = 56.75% saving of each row, this will also make index smaller. For 10 million rows in a table, the table is 22% smaller using a UUID type and the index is 47% smaller.
There are two extensions to generate uuid.
gen_random_uuid() by pgcrypto extension
uuid_generate_v4() by uuid-ossp extension
If you don’t need anything else from uuid-ossp, just use gen_random_uuid().
From this benchmark post (PostgreSQL 12.x), gen_random_uuid is about 2-9x faster than uuid_generate_v4:
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
EXPLAIN ANALYZE SELECT uuid_generate_v4() FROM generate_series(1, 10000);
EXPLAIN ANALYZE SELECT gen_random_uuid() FROM generate_series(1, 10000);