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?
2 Answers
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 multiple indexes for each. (And rarely would either a uuid or bigint key 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:
answered Jan 7, 2020 at 2:08
Erwin Brandstetter
187k28 gold badges465 silver badges639 bronze badges
Updates
Things have changed, as of 2025.
Identity columns
The BIGSERIAL type is effectively supplanted by the identify column feature defined in the SQL Standard and adopted by Postgres 10+.
See GENERATED clause on the CREATE TABLE command reference. And see the Generated Columns page of documentation. Discussed in this tutorial and many other places.
Version 7 UUID
The new (2024-05) Version 7 UUIDs defined in the rewritten specification RFC 9562 is designed for efficient indexing.
Version 7 also replaces the MAC address of Version 1 UUIDs with randomly generated bits, thereby avoiding related security and privacy risks.
Postgres 18+
Postgres 18 and later supports Version 7 UUIDs with its built-in function uuidv7().
This support of the new Version 7 UUIDs changes the calculus of choosing between integer numbers and UUIDs as the data type of your surrogate key. With indexing-inefficiency and security/privacy problems both eliminated by Version 7 UUIDs, fewer distinctions remain.
Some distinctions do remain. Those include, in brief:
- Integer numbers are easier to read and write than the canonical formatted hexadecimal 36-character strings used to show humans the 128-bit UUID values.
- Integer numbers run the risk of having to be reset or otherwise managed (see
ALTER SEQUENCE). In contrast, UUIDs need no such management. - Integer number sequences require your app(s) to coordinate through the central authority of the database server. In contrast, UUIDs need no such central authority.
- UUIDs are infinite, whereas with sequences you must choose a numeric size not to be exceeded by the eventual size of your table: 16-bit, 32-bit, 64-bit.
- Integer numbers are smaller than UUIDs. Even the largest integers at 64-bits (
BIGINT) are half the size of 128-bit UUIDs, and 32-bit integers are a quarter the size. This saves not only storage but also memory during caching, queries, and joins.
If you are need to generate identifiers without reliable access to a central authority, or you will be federating your data, then UUID is the way to go.
For more discussion, see this longer Answer of mine, as well as several recent other Answers of mine.
=I could never measure any difference