PostgreSQL and UUID as Primary Key

Re:PostgreSQL and UUID as Primary Key by necovek on Friday July 05, 2024 @11:48PM (#40888034)

I've never played enough with UUIDs in Postgres, but I wonder if you could publicly expose only the random bits (i.e. the second half if an UUIDv7) and have another non-b-tree index on SUBSTR(id, 16) for quick lookups based on that value. Similar is done for "short commit hashes" too.

Though I would wonder why go with UUIDs in that case at all?

[ Parent | on HN ]

Re:PostgreSQL and UUID as Primary Key by mjevans on Saturday July 06, 2024 @12:00AM (#40888086)

Offhand, I remember _reading_ about that but haven't ever used it in practice so please test and confirm...

# Postgres can Index Function Results (including what you described)

# Postgres does not magically know to USE those results. To use them you must E.G. JOIN (probably works for WHERE?) or ORDER or LIMIT by the same parameter that went into the Index (which is how it's matched up).

Generally, the Primary Key should either be 'small' (BigInt can count as such) and only used internally or be something strongly relevant to how the records in that table can be limited for most (or the most) queries.

[ Parent | on HN ]

Re:PostgreSQL and UUID as Primary Key by necovek on Sunday July 07, 2024 @03:52AM (#40895927)

Yeah, it works for "partial indexes" very well. There are limits to what gets matched, for sure.

In the old times, if you created a "partial index" with a condition of "field_foo IS TRUE" it would not match queries asking for "field_foo = TRUE" (it did actual text matching). Probably some of that is improved today.

[ Parent | on HN ]