I'm new to PostgreSQL and somewhat new to databases in general. Is there an established way of how we should index UUID values in Postgres? I'm split between using hashing and using a trie, unless there's already something built-in that it uses automatically. Whatever I use is going to be handling huge amounts of data.
The SP-GiST operator family "text_ops" indexes using a trie. Because UUIDs are quite long and very dissimilar, these sound appealing even though I would only ever do full match searches.
There's also a hash option. Hashing is O(1), and I won't need to do any comparisons besides equality of course, but because UUIDs are quite long, I'm afraid that generating hashes from them would waste a lot of time.
Or is this something that depends too much on system and use specifics?
I'd rather use bigserial in most cases, but I've been told to use uuid for this. We need uuid because we might have multiple servers using different databases, so there isn't a guarantee that we'll have unique bigints. We could use a different sequence (and seed) for each server, but it's still not as flexible as UUIDs. For example, we wouldn't be able to migrate database entries from one server to another without converting the IDs and their references everywhere.