Waiting for PostgreSQL 10 – hash indexing vs. WAL

For a long time hash indexed were not crash safe, and couldn't be used on replication slave, because they skipped WAL. Now, thanks to these two commits, it has changed:

On 14th of March 2017, Robert Haas committed patch:

hash: Add write-ahead logging support.
 
The warning about hash indexes not being write-ahead logged and their
use being discouraged has been removed.  "snapshot too old" is now
supported for tables with hash indexes.  Most importantly, barring
bugs, hash indexes will now be crash-safe and usable on standbys.
 
This commit doesn't yet add WAL consistency checking for hash
indexes, as we now have for other index types; a separate patch has
been submitted to cure that lack.
 
Amit Kapila, reviewed and slightly modified by me.  The larger patch
series of which this is a part has been reviewed and tested by Álvaro
Herrera, Ashutosh Sharma, Mark Kirkwood, Jeff Janes, and Jesper
Pedersen.
 
Discussion: http://postgr.es/m/CAA4eK1JOBX=YU33631Qh-XivYXtPSALh514+jR8XeD7v+K3r_Q@mail.gmail.com

and then, ~ 13 hours later, Robert committed also:

hash: Support WAL consistency checking.
 
Kuntal Ghosh, reviewed by Amit Kapila and Ashutosh Sharma, with
a few tweaks by me.
 
Discussion: http://postgr.es/m/CAGz5QCJLERUn_zoO0eDv6_Y_d0o4tNTMPeR7ivTLBg4rUrJdwg@mail.gmail.com

It's hard to show what it means – as there isn't much of a change visible to user, with one exception – usage on slave.

On previous PostgreSQL, having table like:

            TABLE "xxx.depesz_test"
 COLUMN |           TYPE           | Modifiers
--------+--------------------------+-----------
 i      | INTEGER                  |
 rnd    | TIMESTAMP WITH TIME zone |
Indexes:
    "t" hash (i)

If I tried to query the table, in a way that would use the index, from slave, I would get:

$ EXPLAIN analyze SELECT * FROM depesz_test WHERE i = 123;
ERROR:  could NOT READ block 0 IN file "base/16402/458955269": READ ONLY 0 OF 8192 bytes

Where the file depends on oid and relfilenode for database and index.

Now, with these patches, hash indexes will become much more usable.

Thanks to all involved.

=$
|

Waiting for PostgreSQL 10 – hash indexing vs. WAL

One thought on “Waiting for PostgreSQL 10 – hash indexing vs. WAL”