Hi leslie, not sure if still relevant to you but might be to others. Deriving a pools live stake is something you could do using StakeSync with a simple
SELECT sum(balance) FROM stake WHERE pool_id = ‘[POOLID]’. It uses DB Sync as data source, here is roughly how I went about it (check its database for reference):
#1) Loop through all UTXO’s
SELECT tx_out.id, tx_out.tx_id, tx_out.index, tx_out.address, tx_out.address_raw, tx_out.payment_cred, tx_out.stake_address_id, tx_out.value FROM (tx_out LEFT JOIN tx_in ON (((tx_out.tx_id = tx_in.tx_out_id) AND ((tx_out.index)::smallint = (tx_in.tx_out_index)::smallint)))) WHERE (tx_in.tx_in_id IS NULL);
and store addresses and balances in a new table. (keep in mind one address can have multiple outputs)
Use the bech32 binary to get the corresponding stake key of the addresses (only those starting with addr1q…), and sum up their balances as well.
#2) From here process all transactions block by block. Something like this for each block:
SELECT tx_out.address, tx_out.value, tx_out.tx_id FROM tx_out, tx WHERE tx_out.tx_id = tx.id AND tx.block_id = [CURRENT_BLOCK] ORDER BY tx_out.id ASC;
To ensure integrity, allow for roll-backs in case of interruptions.
#3) Process stake registrations / delegations to store the current pool id in the stake key table.
#4) Process stake deregistrations to remove pool ids from deregistered stake keys.
Try to avoid redundancies and unnecessary computation to save resources and allow for sudden peaks in transaction throughput without lagging behind.