8

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?

Improve this question
5
  • 1
    BIGSERIAL == 8 bytes, UUID == 16 bytes. Of course the shorter datatype's performance is to be better...
    – Akina
    Commented Jan 5, 2020 at 19:23
  • @Akina But is the difference big enough in real applications so it's worth changing to integer primary keys?
    – civ15
    Commented Jan 5, 2020 at 19:25
  • Depends on the operation. For example, in interactive processing it doesn't matter what type to use...
    – Akina
    Commented Jan 5, 2020 at 19:28
  • @Akina I'm mostly doing INSERT, UPDATE, DELETE and SELECT operations
    – civ15
    Commented Jan 5, 2020 at 19:37
  • 1
    For small inserts the difference hardly noticeable in my experience. For bulk inserts (millions of rows in a single transaction), there is a measurable difference. For SELECT statement using = I could never measure any difference
    – user1822
    Commented Jan 5, 2020 at 20:13

1 Answer 1

Reset to default
11

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:

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.