PostgreSQL UUID vs BIGSERIAL Foreign Keys – Which to Use?

postgresqlprimary-keyuuid

I have 8 tables which use UUID Primary Keys and 7 of them have UUID Foreign Keys. Would it be better for performance to use BIGSERIAL instead of UUID?

Best Answer

Use uuid if you need it.
Don't use it if you don't.

bigint is smaller and a bit faster in multiple ways. 8 vs. 16 bytes do not sound like much, but the difference stacks up for big tables and multiple instances. (And rarely would either uuid or bigint make sense for small tables to begin with.)

bigint is typically also much easier on the human eye and for human handling and for representation when numbers don't get that big after all. UUID always requires 32-character representation (optionally plus dashes).

If you have two of those (PK & FK) in the same table, rows grow by 16 bytes. That may make a difference for narrow rows, or be negligible for wide rows. More importantly, associated indexes also grow in size. While the PK index for a bigint occupies 16 bytes per row (incl. 8 byte index tuple overhead), it's 24 bytes for uuid. If RAM is not available in abundance, that can mean the difference of several indexes residing in RAM vs. being evicted and read back from storage repeatedly. And that would be a substantial difference.

Do you even need bigint? Often, a 4-byte integer is good enough (if it's really good enough) ...

Related:

Related Question