Interesting problem. Did you try grouping up transactions? Ex instead of a few hundred million txns, do a few hundred thousand 1000op chunk txns. SQLite is much much faster within a txn.
Edit: a several hundred million txns over a few hours math. How many per second? ~500
According to here (question 19), for old HDDs you could expect 3 orders of magnitude improvement by using bigger txns. Not sure SSD wise but worth a shot.
I have not tried that yet (I was being lazy since it all ran so fast on my M1), but that's a good idea for something to investigate. That way I suppose I can run the whole thing on a less-beefy instance and avoid the scale-up/scale-down cycle.
Yeah I think if you are doing 1 txn per second roughly instead of 100s, it may just work fine. I'm curious how it goes so shoot me an email if you want with updates
