4 ms·

This is buried in a few other comments, but it bears repeating as it's perhaps far more important than any performance gains you'd receive from hash indexes ove

This is buried in a few other comments, but it bears repeating as it's perhaps far more important than any performance gains you'd receive from hash indexes over btree... Hash indexes are not crash safe and not WAL logged. This means if you're running with high availability or even using something for disaster recovery like WAL-E[1] and need to recover your database your index would not be in a working state after recovery. Instead you'd need to reindex that data. For any real production database hash indexes should be used with extreme caution if used at all. [1]. https://github.com/wal-e/wal-e https://github.com/wal-e/wal-e

deleted 10y ago
[deleted]
Veratyr 10y ago
This seems to be mentioned in the very first sentence of the article: > PostgreSQL supports Hash Index from a long time, but they are not much used in production mainly because they are not durable. And there's a good reason for bringing them up anyway in the second sentence: > Now, with the next version of PostgreSQL, they will be durable.
- rosser 10y ago
  Saying "they are not durable" downplays the extent of how non-durable they are. That kind of "non-durability" needs flashing neon signs prominently featuring very large arrows, pointing at the specifics, not four vague words.
  - jeltz 10y ago
    You get a warning when you try to create one in PostgreSQL 9.6. Maybe it should have been stronger worded, but since the issue is fixed in 10 anyway it is too late to do something about it. # CREATE INDEX ON t USING hash (id); WARNING: hash indexes are not WAL-logged and their use is discouraged CREATE INDEX
  - arez 10y ago
    why are they not durable? why shouldn't you use them for big tables? For example for PRIMARY KEY lookups, I never ask for a range of PRIMARY KEYs, so why not use a hash? I think I'm missing the important point.
    - jeltz 10y ago
      They are not durable because when durability was added to PostgreSQL a long time ago people were not sure how to add durability to hash indexes, so that task was left for later. But later did not happen, until very recently when some people worked hard on adding durability for them. Adding durability required quite major changes. PostgreSQL 10 which will be released this autumn will include durable hash index, do not use hash indexes until then unless you really know what you are doing.
    - rosser 10y ago
      Changes to hash indexes are not WAL-logged, so they are not crash safe. Wait for 10.0.
    - ksherlock 10y ago
      Durability (the D in ACID) means that if the power goes out or somebody kill -9s it, the database will be in a consistent state and anything that was committed was in fact committed. In current and previous versions of PostGres, pulling the plug at the wrong time could leave hash indexes in an inconsistent state. That's fixable by deleting and recreating the index but the time required to do so is proportional to the table size. In the next version of PostGres, it won't be a problem and hash indexes can be used. In the case of a primary key, a row is inserted or deleted but the power goes out before the hash index is updated. When the power comes back, the hash index still points to rows were deleted and is missing rows that were inserted.
    - godmodus 10y ago
      Collision probabilities are way more probably than people intuitively care to believe. The pigeonhole principle or the birthday Paradoxon exemplify this quiet well. Hash tables are good, for small (relative to available space) static sets that don't change. Dynamic production environments will introduce an escalating probablity for performance degradation, and this can detrimental for critical systems.
nostrademons 10y ago
They're most useful with small memory-only tables that have very frequent lookups. For example, in a lot of systems, you might store the validation rules for each field in a table. Or you might have a set of autocomplete options. Or you may have a table with the set of currently logged-in sessions. These tables have row counts that measure in the hundreds, not millions, and the whole table can fit within a couple pages. For something like this, the cost of reindexing is trivial, and the full data is usually in a .sql file somewhere, so even if there's data loss it takes all of about 10 seconds to restore it. Why would you do this instead of hardcoding this in code, storing it in a local hashtable, or putting it in Redis/Memcached? Bunch of reasons. These tables can be shared across all clients of the database, so if you have multiple apps (in different languages!) that require common validation, they can all make a SQL query instead of recoding the logic. If you don't otherwise need Redis/Memcached, you can avoid adding that dependency to your stack. You can use foreign key constraints to ensure that the values in real data tables match the valid values in the validation table. You can throw an admin interface on top of the tables and let customers edit the set of available options, rather than forcing all changes to go through the developers.
godmodus 10y ago
Seconded.

deleted 10y ago

[deleted]

Veratyr 10y ago

This seems to be mentioned in the very first sentence of the article: > PostgreSQL supports Hash Index from a long time, but they are not much used in production mainly because they are not durable. And there's a good reason for bringing them up anyway in the second sentence: > Now, with the next version of PostgreSQL, they will be durable.

rosser 10y ago
Saying "they are not durable" downplays the extent of how non-durable they are. That kind of "non-durability" needs flashing neon signs prominently featuring very large arrows, pointing at the specifics, not four vague words.
- jeltz 10y ago
  You get a warning when you try to create one in PostgreSQL 9.6. Maybe it should have been stronger worded, but since the issue is fixed in 10 anyway it is too late to do something about it. # CREATE INDEX ON t USING hash (id); WARNING: hash indexes are not WAL-logged and their use is discouraged CREATE INDEX
- arez 10y ago
  why are they not durable? why shouldn't you use them for big tables? For example for PRIMARY KEY lookups, I never ask for a range of PRIMARY KEYs, so why not use a hash? I think I'm missing the important point.
  - jeltz 10y ago
    They are not durable because when durability was added to PostgreSQL a long time ago people were not sure how to add durability to hash indexes, so that task was left for later. But later did not happen, until very recently when some people worked hard on adding durability for them. Adding durability required quite major changes. PostgreSQL 10 which will be released this autumn will include durable hash index, do not use hash indexes until then unless you really know what you are doing.
  - rosser 10y ago
    Changes to hash indexes are not WAL-logged, so they are not crash safe. Wait for 10.0.
  - ksherlock 10y ago
    Durability (the D in ACID) means that if the power goes out or somebody kill -9s it, the database will be in a consistent state and anything that was committed was in fact committed. In current and previous versions of PostGres, pulling the plug at the wrong time could leave hash indexes in an inconsistent state. That's fixable by deleting and recreating the index but the time required to do so is proportional to the table size. In the next version of PostGres, it won't be a problem and hash indexes can be used. In the case of a primary key, a row is inserted or deleted but the power goes out before the hash index is updated. When the power comes back, the hash index still points to rows were deleted and is missing rows that were inserted.
  - godmodus 10y ago
    Collision probabilities are way more probably than people intuitively care to believe. The pigeonhole principle or the birthday Paradoxon exemplify this quiet well. Hash tables are good, for small (relative to available space) static sets that don't change. Dynamic production environments will introduce an escalating probablity for performance degradation, and this can detrimental for critical systems.

nostrademons 10y ago

They're most useful with small memory-only tables that have very frequent lookups. For example, in a lot of systems, you might store the validation rules for each field in a table. Or you might have a set of autocomplete options. Or you may have a table with the set of currently logged-in sessions. These tables have row counts that measure in the hundreds, not millions, and the whole table can fit within a couple pages. For something like this, the cost of reindexing is trivial, and the full data is usually in a .sql file somewhere, so even if there's data loss it takes all of about 10 seconds to restore it. Why would you do this instead of hardcoding this in code, storing it in a local hashtable, or putting it in Redis/Memcached? Bunch of reasons. These tables can be shared across all clients of the database, so if you have multiple apps (in different languages!) that require common validation, they can all make a SQL query instead of recoding the logic. If you don't otherwise need Redis/Memcached, you can avoid adding that dependency to your stack. You can use foreign key constraints to ensure that the values in real data tables match the valid values in the validation table. You can throw an admin interface on top of the tables and let customers edit the set of available options, rather than forcing all changes to go through the developers.

godmodus 10y ago

Seconded.