Expanding HOT updates for expression and partial indexes

Attached find a patch that expands the cases where heap-only tuple (HOT) updates are possible without changing the basic semantics of HOT. This is accomplished by examining expression indexes for changes to determine if indexes require updating or not. A similar approach is taken for partial indexes, the predicate is evaluated and, in some cases, HOT updates are allowed. Even with this patch if any index is changed, all indexes are updated. Only in cases where none are modified will this patch allow the HOT path. Previously, an expression index on a modified column would disqualify the update from the HOT path in the heap access manager. This patch is functional, includes new tests, and passes check-world however I’m sure it will require more work after community review.

Why is this important? A growing number of Postgres users work with JSONB data. Indexes on fields within JSONB columns use expressions preventing all updates on data within JSONB columns from the HOT path. This is unnecessary and a major drawback when using Postgres.

This is not a new idea; indeed, this patch grew out of Surjective functional indexes [1] which was applied [2] and then reverted [3] after it was discovered that it caused a bug and had other quality issues. This patch is a new approach that hopefully address the identified bug and most of the other concerns raised in that and other email threads on the subject. I’m also aware of PHOT [4] and WARM [5] which allow for updating some, but not all indexes while remaining on the HOT update path, this patch does not attempt to accomplish that.

Attached you’ll find two slightly different approaches. The first (v3) patch is slightly less intrusive than the second (v4), but both apply to master (59d6c03956193f622c069a4ab985bade27384ac4). Tests have been added (heap_hot_updates.sql) that exercise this new feature set. Of the two patches I personally prefer v4 as it cleans up the summarizing index logic and removes TU_UpdateIndexes. This opens the door to future improvements by providing a way to pass a bitmap of modified indexes along to be addressed by something similar to the PHOT/WARM logic.

I have a few concerns with the patch, things I’d greatly appreciate your thoughts on:

First, I pass an EState along the update path to enable running the checks in heapam, this works but leaves me feeling as if I violated separation of concerns. If there is a better way to do this let me know or if you think the cost of creating one in the execIndexing.c ExecIndexesRequiringUpdates() is okay that’s another possibility.

Second, I’m sure that creating the rd_indexinfolist should be improved/changed and likely cached via relcache.c as this is likely part of the performance overhead mentioned below.

Third, there is overhead to this patch, it is no longer a single simple bitmap test to choose HOT or not in heap_update(). Sometimes this patch will perform expensive additional checks and ultimately not go down the HOT path, new overhead with no benefit. Some expressions are more expensive than others to evaluate, there is no logic to adjust for that. The Surjective patch/email thread had quite a bit of discussion on this without resolution. I’ve chosen to add a GUC that optionally avoids the expression evaluation. I’m open to ideas here as well, addition of another GUC or removal of the one I’ve added. I’ve tried to avoid rechecking indexes for changes when possible.

Fourth, I’d like to know which version the community prefers (v3 or v4). I think v4 moves the code in a direction that is cleaner overall, but you may disagree. I realize that the way I use the modified_indexes bitmapset is a tad overloaded (NULL means all indexes should be updated, otherwise only update the indexes in the set which may be all/some/none of the indexes) and that may violate the principal of least surprise but I feel that it is better than the TU_UpdateIndexes enum in the code today.

I’ve run two performance tests against this; a very synthetic workload that updates only non-indexed fields in a JSONB document that is small enough not to be TOASTed, and one that measures TPC-C-like workload using a MongoDB API mapped into JSONB.

The synthetic workload randomly updated non-indexed fields within the JSONB documents as fast as possible from 50 client connections. The test started pre-loaded with 10,000,000 documents within a single column with 50 expression indexes (BTREE) into fields in those documents. This test showed a dramatic increase in throughput (28-110%), reduction in per-operation latency (22-52%), and lower storage requirements all while CPU remained within 1% of the unpatched server. The tests ran for 2hrs during which time we observed about a 20-30% reduction in IOPs. On the unpatched server there were no HOT updates, on the patched server with default fillfactor HOT updates made up at least 30% and at times much more as the random-access pattern and pruneheap opened space on pages for HOT updates.

The TPC-C-like workload [7] ran [8] first with —no-execute then [8] with —no-load. This test showed HOT updates for CUSTOMER (7%), DISTRICT (48%), and WAREHOUSE (99%) but zero for STOCK and ORDERS. When compared to a non-patched server the performance with this patch was 7.8% slower than without. This was clearly not the result I expected. I believe that the lower performance may in part be due to how I build and maintain the rd_indexinfolist and the overhead of executing expressions on indexes repeatedly only to find that the update still doesn’t qualify for the HOT path. I’d be very happy to hear thoughts on how I might reduce this gap if you have suggestions.

I hope to further develop this patch into a final form acceptable to this community.

best regards,
-greg

[1] https://www.postgresql.org/message-id/flat/4d9928ee-a9e6-15f9-9c82-5981f13ffca6%40postgrespro.ru
[2] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c203d6cf8
[3] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=05f84605dbeb9cf8279a157234b24bbb706c5256
[4] https://www.postgresql.org/message-id/flat/2ECBBCA0-4D8D-4841-8872-4A5BBDC063D2%40amazon.com
[5] https://www.postgresql.org/message-id/flat/CABOikdMop5Rb_RnS2xFdAXMZGSqcJ-P-BY2ruMd%2BbuUkJ4iDPw%40mail.gmail.com
[6] https://www.postgresql.org/message-id/flat/CABOikdMNy6yowA%2BwTGK9RVd8iw%2BCzqHeQSGpW7Yka_4RSZ_LOQ%40mail.gmail.com
[7] https://github.com/mongodb-labs/py-tpcc
[8] python3 tpcc.py —no-load —duration 3600 —warehouses 2000 —clients 50 —stop-on-error —config gpure.config mongodb