indexer icon indicating copy to clipboard operation
indexer copied to clipboard

Use smaller batches when inserting to postgres.

Open winder opened this issue 2 years ago • 0 comments

For large blocks, inserting transactions and updating accounts can be sped up by sending multiple smaller batches of updates instead of one large batch.

Modifying the batches can be done for all updates, but probably would have the biggest impact for these tables:

  • transaction inserts
  • account upserts

A proof of concept was made here: https://github.com/winder/indexer/commit/1d5edb24b48122077bd90c6ed01c088fbaa2aef2

Here are some rough notes from tests done with the above branch repeatedly inserting the same full block. Unsafe refers to the serialization level.

Note Vacuumed AddBlock Txn Participation AddTransactions Overall
Baseline 401.9ms 1.9s 29s
Baseline 2 X 390ms 1s 26.4s
Rollback instead of commit X 82ms 984ms 3s
Unsafe Serialization (txns) X 247ms 1.3s 23.7s
Unsafe Serialization (all)
Unsafe + Txn batches (10k) 193.6ms 1.5s 9s
Unsafe + Txn batches (1k) X 214ms 7.1s 7.7s 8.7s
Unsafe + Txn batches (2k) X 223ms 4.8s 6.6s 7.6s
Unsafe + Txn batches (2k) X (full) 233ms 2.8s 7.1s 8.3s

winder avatar Feb 01 '23 22:02 winder