Research: Check `range` type in Postgres and see if it could improve the new validator projection

[ysong42]

Task Details

This ticket is a follow-up on #669 .

The new validator projection performance against Crypto.org testnet is fine. But the performance against Crypto.org Mainnet is terrible.

We want to optimize the performance. So in this ticket I will do the following:

• Study the range type. Apply it on Delegations view.
• Test it to see if it could significantly improve the projection performance.

Definition of Done

• Record the finding under this ticket.
• If the result is positive, then apply the same changes to other views.

[ysong42]

I implemented the range type for Delegations view. And the sync up speed seems much better.

• Testnet, I ran it for around 13.5 hours, and it synced up around 1,686,000 blocks in total.
• Mainnet, I ran it for around 2 hours, and it synced up around 150,000 blocks in total.

[ysong42]

After implemented the range type for Delegations, Validators, UnbondingDelegations and Redelegations views. The sync up speed did improve a lot.

Testnet

On Testnet, all blocks could be synced up in less than 18 hours (2,836,000 blocks in total)

Mainnet

However on Mainnet, the performance seems getting worse when more data written in to DB.

I ran it for around 13.5 hours, it synced up 588,175 blocks (while we have around 4,400,000 in total).

I did a speed test at around 588,000 height, the indexer sync up speed is around 6.13 blocks/second. If it keeps this speed, it will take 7.12 days to finish the sync up.

But I checked the DB and noticed a pattern that as the projection getting more data, the Msg handling are taking more time.

[ysong42]

Found out the evil SQL.

SQL-1

SELECT COUNT(*)
	FROM public.view_vd_validators
	WHERE operator_address = 'crocncl1u5ryf5jwc2jhd9xyvmasfqzacxp03v8dcj8xry' AND lower(height) = 719959;

SQL-2

SELECT COUNT(*)
	FROM public.view_vd_validators
	WHERE operator_address = 'crocncl1u5ryf5jwc2jhd9xyvmasfqzacxp03v8dcj8xry' AND height = '[719959,)';

SQL-2 is more than 100 times faster than SQL-1.

This SQL is used when updating validators, which is a common operation in mainnet.

[ysong42]

Final Outcome

Testnet

Sync up to latest height. Run the validation script to do the cross check.

Performance: sync up to latest height within 2 days.

Correctness: Picked 1000 height (from 1 to latest). All validators, delegations, unbonding delegations and redelegations records are matched with Cosmos RPC records.

Mainnet

Sync up to height 4232032. The server stopped there cause we need to disable MsgExec to let parser continue to work.

Performance: sync up to this 4232032 in around 55 hours.

Correctness: Picked 1000 height (from 1 to 4232032). All validators records are matched with Cosmos RPC records.

Conclusion

Now I am pretty confident at the projection's performance and correctess. The following task are to handle:

• The MsgExec parser. (Problem: MsgExec may squeeze multiple message events into a single one #673).
• Add logic to handle params in genesis and proposals. (Problem: [New Validator Projection] Missing tracking of genesis and on-chain data. [PART 3] #663)