PostgreSQL and UUID as Primary Key

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 ]