SQLite utilizes up to 90% of the my SSD disk when SHIB coin riches its local peaks with more than 10.000 – 18.000 trades/min that is 166 – 300 trades/second. My C++ app inserts 64-bit values to tables like this:
CREATE TABLE trades (time INT PRIMARY KEY NOT NULL, price REAL, volume REAL) WITHOUT ROWID;
so it is only 3984 – 7200 bytes/second. Totally across all markets there can be about 30.000 trades/min that is 500 trades/second and only 12.000 bytes/second and it is enough to slow down my SQLite database.
I use WAL with 4GB cache:
PRAGMA journal_mode = WAL; PRAGMA cache_size = -4 * 1024 * 1024;
and do all the inserts in a transaction once a 5 seconds into 500+ tables (a separate table for each market) with a prepared statement.
See the screenshots of my app below:
In WAL mode, a writer and readers do not block each other, but there is still only one writer allowed, see https://dba.stackexchange.com/a/45400/198776