Is there any way to create a primary key using the hash method? Neither of the following statements work:
oid char(30) primary key using hash
primary key(oid) using hash
Asked
Active
Viewed 1.3k times
10
Tom Yeh
- 1,987
- 2
- 15
- 23
-
Why would they work? What do you think "using hash" means? Are you trying to set up a hashed index (if so why)? Are you trying to "hash" some numeric key? – Richard Huxton Jul 04 '14 at 07:07
-
Why do you want a hash index? It has some serious problems and no benefits. – Frank Heikens Jul 04 '14 at 07:48
-
To support offline operations, our client programs are allowed to create entities without accessing the database. Thus, we generate the primary keys with UUID-like approach. To index this kind of primary keys, I think hash is more efficient (in term of performance). Also the key is immutable and the order doesn't matter. – Tom Yeh Jul 04 '14 at 07:52
-
It's not supported, this is the error message I get: ERROR: access method "hash" does not support unique indexes – Frank Heikens Jul 04 '14 at 07:54
-
2@TomYeh "you think" hash is more efficient? Evidence? I've never seen anyone show that PostgreSQL's rather unloved hash indexes are better than btree for anything, and their removal is frequently contemplated. Don't guess, *measure*. In any case, you can't do it whether you want to or not. – Craig Ringer Jul 04 '14 at 08:45
-
2Just for completeness: they perform almost the same (if you want to select only 1 row), but btree has a little lower actual runtime, f.ex. http://sqlfiddle.com/#!15/37175/1 – pozs Jul 04 '14 at 14:07
-
1Hash indexes can give faster lookups than btree, perhaps [around 40% faster](https://dba.stackexchange.com/a/281652/50610). – qris Jan 21 '21 at 09:20
-
According to my experiments, btree's index size is much smaller if there are a lot of repeated values. – Tom Yeh May 10 '21 at 10:34
1 Answers
20
I assume, you meant to use the hash index method / type.
Primary keys are constraints. Some constraints can create index(es) in order to work properly (but this fact should not be relied upon). F.ex. a UNIQUE constraint will create a unique index. Note, that only B-tree currently supports unique indexes. The PRIMARY KEY constraint is a combination of the UNIQUE and the NOT NULL constraints, so (currently) it only supports B-tree.
You can set up a hash index too, if you want (besides the PRIMARY KEY constraint) -- but you cannot make that unique.
CREATE INDEX name ON table USING hash (column);
But, if you are willing to do this, you should be aware that there is some limitation on the hash indexes (up until PostgreSQL 10):
Hash index operations are not presently WAL-logged, so hash indexes might need to be rebuilt with REINDEX after a database crash if there were unwritten changes. Also, changes to hash indexes are not replicated over streaming or file-based replication after the initial base backup, so they give wrong answers to queries that subsequently use them. For these reasons, hash index use is presently discouraged.
Also:
Currently, only the B-tree, GiST and GIN index methods support multicolumn indexes.
Note: Unfortunately, oid is not the best name for a column in PostgreSQL, because it can also be a name for a system column and type.
Note 2: The char(n) type is also discouraged. You can use varchar or text instead, with a CHECK constraint -- or (if the id is so uuid-like) the uuid type itself.
