RE: How SPS Stakeholders and Staking HP and HBD?

[beaker007]

Hi thanks for dropping a comment,

I have took a look on those tables. Do you also got some documentation.
Because for me its not clear what the balance is.

balance_history_by_day i assume that is the HP in VESTS?
saving_history_by_day is that the hive saving in vest or hdb saving.

That is why i like the balance_history view a bit more because that a name that i know 😁. and it does the account name mapping directly.

also in my react code i did a call a bit smarter to limit the result directly.

            SELECT * FROM (
                SELECT 
                    bh.*,
                    hb.timestamp as block_timestamp,
                    ROW_NUMBER() OVER (PARTITION BY bh.account_name ORDER BY bh.block_num ASC) AS rn_asc,
                    ROW_NUMBER() OVER (PARTITION BY bh.account_name ORDER BY bh.block_num DESC) AS rn_desc
                FROM hafsql.balances_history bh
                LEFT JOIN hafsql.haf_blocks hb ON bh.block_num = hb.block_num
                WHERE bh.account_name IN (${placeholders})
            ) sub
            WHERE rn_asc = 1 -- first row per account
               OR rn_desc = 1 -- last row per account
               OR (block_num % 100 = 0) -- every 100th block
            ORDER BY account_name, block_num DESC

the python wrapper whas a bit rushed coding, and did not do the partition well, but i like to learn end i might end up using the two tables you recommend.

Lots of data to go trough but really nice you create/maintaining the hafSQL looks really impressive 👏

Comments

[mahdiyari]

This query seems better but still takes too long for "spammy" accounts. It's fine though.

Those tables are not created by me. They are from haf balance tracker. Those tables store each balance separated by their symbol aka nai. So for HIVE balance you have one row and another row for HBD balance. It would be storing VESTS so your can use vests_to_hive function.

Anyway, I plan on dropping balances from hafsql and depending on balance tracker. I can probably recreate the same view you are using so shouldn't be any big breaking changes. I should probably create more views and take advantage of other HAF apps/tables including the two mentioned tables.

[beaker007]

BTW: what i also can do easily is limit the request one account at the time

[mahdiyari]

There is a limit on how many concurrent connections you can have per IP. Currently it is set to 6. So it's not a problem on the database side but your will get errors so you should probably setup a pool on the client side and run queries on the pool.

[beaker007]

oke thanks again just updated my pool to 6. it was on default 10