DeveloperNote.com

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:

Links:

• SQLite as a production database for a low-traffic site?