7 ms·
What does durable mean in the context of a Postgres index? I can sort of guess but from the way it's used here it seems like it has a well know definition. A qu
by almost 10y ago
What does durable mean in the context of a Postgres index? I can sort of guess but from the way it's used here it seems like it has a well know definition. A quick google search (on my phone) returned this article a few times but not much to else that explains it.
- koolba 10y agoHash indexes are not WAL logged in Postgres so they are not replicated and will not survive non-graceful shutdowns, i.e. if the server crashes you have to rebuild it from scratch. As such, they're not recommended unless you know exactly what you're doing.
- Tostino 10y agoJust to clarify, they are WAL logged starting in Postgres 10, which is what this article was talking about.
- ivoras 10y agoDurable in the ACID sense, or specifically that write operations go through the WAL (write-ahead log, i.e. a journal), with all the consequences this brings (both durability and replication). Before this work was done, hash indexes could literally be corrupted by power outages, etc.
- almost 10y agoOh right, that's scary! In the case of corruption would the index just be rebuilt (I realise that's a big problem with big indexes) or would it just silently corrupt query output?
- fabian2k 10y agoCaution 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. That is the warning in the current version of Postgres about hash indexes.
- deleted 10y ago[deleted]