Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Use smaller batches when inserting to postgres. #1454

Open
winder opened this issue Feb 1, 2023 · 0 comments
Open

Use smaller batches when inserting to postgres. #1454

winder opened this issue Feb 1, 2023 · 0 comments
Assignees

Comments

@winder
Copy link
Contributor

winder commented Feb 1, 2023

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: winder@1d5edb2

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 winder added new-feature-request Feature request that needs triage Team Lamprey and removed new-feature-request Feature request that needs triage labels Feb 1, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

3 participants