26

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.

  • 2
    I believe "federated database" is the buzzword for your situation. And, yes, UUIDs are the solution for that. That was the very reason UUIDs were invented decades ago: for sharing data amongst distributed systems without centralized coordination. – Basil Bourque Aug 18 '15 at 22:45
  • Months later: Indeed, the "federated database" Basil Bourque brought up is what we're going for. Not only do we have multiple servers, but we have clients (which can be thought of as more parts of the federated DB) creating IDs while offline, too. That's why we use UUIDs. – sudo Aug 19 '15 at 20:43
31

Use PostgreSQL's built-in uuid data type, and create a regular b-tree index on it.

There is no need to do anything special. This will result in an optimal index, and will also store the uuid field in as compact a form as is currently practical.

(Hash indexes in PostgreSQL prior to version 10 were not crash-safe and were really a historical relic that tended to perform no better than a b-tree anyway. Avoid them. On PostgreSQL 10 they've been made crash-safe and had some performance improvements made so you may wish to consider them.)

If for some reason you could not use the uuid type, you would generally create a b-tree on the text representation or, preferably, a bytea representation of the uuid.

  • 2
    While the statement regarding hash indexes versus b-tree is a commonly held belief, I think it would be helpful to cite sources for such a claim. – Volte Apr 24 '17 at 20:30
  • 1
    As of PostgreSQL 10, hash indexes are now crash-safe. That said, hash indexes can only be used with =, so if you need any other operators, b-tree is still preferable. – rintaun Oct 31 '17 at 14:54
  • 1
    A couple of years later, in my experience, hash hasn't been much faster than b-tree, even in Postgres 10. But since hash indexes take so much less disk space than b-tree, it might be faster in a setup where big indexes become a problem, which I feel hasn't been the case for me. Well I'll keep an eye out now that I can actually use them safely in v10. – sudo Jan 30 '18 at 0:25
  • There are some good write ups on hash index perf improvements in v10 and v11: rhaas.blogspot.com/2017/09/… - amitkapila16.blogspot.com/2017/03/… – Glenn Morton Jun 30 '18 at 6:00
3

Hash indexes are missing in action in PostgreSQL. PostgreSQL knows it needs hash indexes, and that it's code for hash indexes is old and moldy, but they don't remove it because they are waiting for someone to come along and overhaul hash indexing. See this thread:

http://www.postgresql.org/message-id/4407.1115698257@sss.pgh.pa.us

  • Yeah, I get a warning when I try to use a hash index. "Highly discouraged" or something. – sudo Apr 10 '16 at 7:57
  • Hash indexes work well in PostgreSQL under some circumstances, but I recently found they caused my queries to return no results when I tried optimizing with hash indexes on built-in UUID data type primary & foreign keys. There truly are benefits to hash indexes, if only they worked for all data types, and PostgreSQL devs know this, they're just too lazy to fix it themselves, and they keep their code situated as if they are praying to/for their eventual savior. – derekm Apr 10 '16 at 15:58
  • 2
    Someone has rescued hash indexes, I'm guessing because they play a critical role in data partitioning, which Pg10 has been focusing on: wiki.postgresql.org/wiki/… But they still don't give you everything I've seen theoretically useful in college database class ;) – sudo Jan 30 '18 at 0:30

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.