2

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?

  • 1
    BIGSERIAL == 8 bytes, UUID == 16 bytes. Of course the shorter datatype's performance is to be better... – Akina Jan 5 at 19:23
  • @Akina But is the difference big enough in real applications so it's worth changing to integer primary keys? – civ15 Jan 5 at 19:25
  • Depends on the operation. For example, in interactive processing it doesn't matter what type to use... – Akina Jan 5 at 19:28
  • @Akina I'm mostly doing INSERT, UPDATE, DELETE and SELECT operations – civ15 Jan 5 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 – a_horse_with_no_name Jan 5 at 20:13
1

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:

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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