15k inserts/s with Rust and SQLite (2021) 3 years ago
This reminded me of a prior discussion[0] on bulk data generation in SQLite with Rust (vs Python vs PyPy) which previously led me to trying out two different techniques using just SQLite[1]. The approach here is so similar I tried my prior solution on the slowest VPS I have access to (1 vCPU core 2.4GHz, 512Mi, $2.50/month from vultr):
sqlite> create table users (id blob primary key not null, created_at text not null, username text not null);
sqlite> create unique index idx_users_on_id on users(id);
sqlite> pragma journal_mode=wal;
sqlite> .load '/tmp/uuid.c.so'
sqlite> .timer on
sqlite> insert into users(id, created_at, username)
select uuid(), strftime('%Y-%m-%dT%H:%M:%fZ'), 'hello'
from generate_series limit 100000;
Run Time: real 1.159 user 0.572631 sys 0.442133
In the interest of finding where the bulk of the time is spent and on a hunch I tried swapping the UUID for plain auto-incrementing primary keys as well:
sqlite> insert into users(created_at, username) select strftime('%Y-%m-%dT%H:%M:%fZ'), 'hello' from generate_series limit 100000;
Run Time: real 0.142 user 0.068090 sys 0.025507
[0] https://news.ycombinator.com/item?id=27872575
[1] https://idle.nprescott.com/2021/bulk-data-generation-in-sqli...