Research ways to optimize transaction filters

[steven-sheehy]

Problem

The transactions list endpoint can timeout when using multiple request parameters like account.id, type, and transactiontype.

Solution

Explore using pg_roaringbitmap extension

Alternatives

No response

[xin-hedera]

Overall we can consider pg_roaringbitmap as a viable and scalable solution for transaction filtering with all our use cases. The extension shows very good read performance and provides all the bitwise /bitrange operations we need to retrieve information. There are some downsides though, some have already been addressed, some need engineering effort.

• Slow reverse lookup in a range, this has been addressed by an optimization which requires code change in the extension itself
• Write amplification when updating existing large bitmaps (with billions of bits set), this can be addressed by a two phase update process: keep recent changes in a hot table and past accumulated (potentially large) changes in a cold table, periodically merge the hot table into the cold table; on the read end, all operations need to union the result from both the hot and the cold tables
• Lack of 64-bit roaringbitmap support which is required for consensus timestamps at nanos granularity