Speedup Reward and EpochStake insertions

[kderme]

Syncing with disable-leger provides a considerable speedup. This is because ledger rules are not applied and because Reward and EpochStake tables are not populated. This provides the motivation to investigate why these tables take so long to populate.

The reward table has a unique key which is checked every time a new entry is inserted. It exists because the reward table is never deleted. After a rollback the same values will be reattempted and the unique key guards against it. Since #1190 is done, there are no rollbacks when db-sync syncs, they only happen when db-sync reaches the tip of the chain. So for the most part, the unique constaint checks provides delays without any benefit. We should try and find a way to delay the migration which creates this unique key untill db-sync reaches the tip of the chain.

Also rewards are added in bug chunks. We should consider limit the size of the insertions. Persistant cache is also affected by it

Similar things apply to EpochStake.

Depends on #1087 #1293

[marshada]

DBSync sync time is 40% faster when disable-ledger is enabled, chiefly because the Reward and EpochStake tables aren't populated.

If we remove the unique key check on the reward table during sync, we'll get a big improvement. This ticket refers to work that will temporarily remove the unique key check during syncing.

[marshada]

@kderme can you clarify whether this performance hit is due to processing for the epoch boundary?

[kderme]

No this operation is spread acrosss most of the epoch and not done during the epoch boundary

[Cmdv]

So I’ve been trying to research postgresql constraint system and how it’s slowing down our inserts for rewards before db-sync reaches the tips of the chain. From what I gather even if we delay adding the constraint once the tip is reached/after a migration. The db would have to recalculate all the existing rows to check if adding constraints is valid. From what I gather there is another option of using COPY but that tends to use CSV/files.

So we could translate the bulk insert into a copy but I’m unsure of performance benefits or if it’s something that could even happen?

[kderme]

I belive COPY is mostly used to copy data from files/stin. So instead we use insert with a big list of entries. An additional optimization, which is proposed in this ticket, is to remove unecessary constraints, which trigger checks from Postgres and delay insertions.

[Cmdv]

would these constraints be permanently removed as from my understanding

The db would have to recalculate all the existing rows to check if adding constraints is valid

So even though we'd think by delaying adding them after the insertion would be more performant it might actually not be the case 🤔

[kderme]

When the unique key is inserted on a fully populated table it will take some time to check for the uniqueness in the whole table. However it is always faster to do a single check on a fully populated table, than do the check every time a new entry is inserted for this entry. This has been the basis for many other optimizations like #1293

[kderme]

To overcome the limitations of persistent it may be wise to completely remove the unique key from these 2 tables from TH. That way we can have a user defined Unique Key which lives outside Persistent and TH. To make this safer we can validate it against the entityFields of the TH generated EntityDef.

Also before creating the Unique Key it's necessary to first check if it has already been created before. Postgres queries usually have a IF NOT EXISTS clause for this. Alternatively a separate query needs to check it.