PostgreSQL: UUID

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

Speed

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