Progress on HafSQL:
I have been developing views for all the operations including the virtual operations. That is done now and the result is 93 views for all the operations.
I did play with index creation for a while and after some testing on a fully synced haf node, ended up with 36 total indexes (partial indexes).
It takes around 3.5 hours for indexes to be created on i9-13900 with 12 parallel workers. This test is with ZFS+LZ4 on raid 0 NVMe setup.
Total storage usage is only by indexes and it is 185GB (non-compressed). 128GB of this space is used by indexes of custom and custom_json operations. This enables retrieving custom_json operations by id and by the username from required_auths or required_posting_auths parameters. I figured this can be a very useful feature for our apps when this gets implemented on the public RPC nodes.
The rest of the development will probably be making the live sync part of the application to track things like delegations.
The current code works as it is and is under the tag v0.0.1. Readme should be good enough to set everything up.
I did try my best to cover all the operations with indexes. Feedback is very welcome.
Code: https://gitlab.com/mahdiyari/hafsql
Proposal: DHF proposal - Hive related development
The HAF documentation says (https://gitlab.syncad.com/hive/haf/-/blob/develop/src/hive_fork_manager/Readme.md#important-notice-about-irreversible-data):
As I see in the code all indexes are created on irreversible blocks data tables. Looking at the views they also are created on the irreversible data. Are You sure that new indexes are required additionally to those that are already present in HAF ? Without using views incorporated in the HAF You won't be able to present together irreversible and reversible data (live data). Please let us know if HAF is too slow for Your application, You may add a new issue. Maybe we can incorporate some changes directly inside HAF.
Reversible data won't matter in this case. If it was necessary we can add them to the views and it's not a big deal. Indexes are not necessary on the reversible data though as the data is too small to be worth creating an index for it.
The created indexes are necessary for this app but I don't think they are necessary for the haf itself since they won't be necessary outside this use case.
IMHO haf should stay as generalized as possible and don't put the burden of every single app on the HAF node operators unless they choose to run that app.
It's a development related post but you used a picture of cat as thumbnail it's interesting anyway thanks for sharing this technical information great work Sr. Keep developing hive
Cat is the clickbait
It's totally fine Sr.
Be gentle with the kitty
The cat is very cute. I love cats
thanks for sharing
While some of us don't understand anything you pointed out in your post we are genuinely charmed by your cat and good job with your Development thingy😄
I should probably say something about the cat.
Kitty
Keep doing the positive work, by the way the cat is so cute @mahdiyari
The cat looks adorable. There is a lot of magical charm in his two eyes. Thank you for such a beautiful post about cats.❤️❤️
so cute kitty cat❤️😘