sqlite_wal_vs_rollback_mode

.js

const fs = require('fs');
const DB = require('better-sqlite3');

if (fs.existsSync('test.db')){
  fs.unlinkSync('test.db');
}

const db = DB('test.db');


db.pragma('journal_mode = WAL');
db.pragma('foreign_keys = ON');
db.pragma('synchronous = NORMAL');
db.pragma('cache_size = 2000');
db.pragma('temp_store = MEMORY');
db.pragma('mmap_size = 30000000000');
db.pragma('busy_timeout = 5000');

db.prepare('CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY, name TEXT, num INTEGER, num2 REAL, blob BLOB)').run();

const TEST_SIZE = 200_000;   

const startTime = performance.now();
const insertStmt = db.prepare('INSERT INTO test (name, num, num2, blob) VALUES (?, ?, ?, ?)');
for (let i = 0; i < TEST_SIZE; i++) {
  insertStmt.run('test', 100, 100.1, Buffer.from('test blob'));
}
const endTime = performance.now();

console.log(`Inserts time taken: ${(endTime - startTime).toFixed(1)}ms`);
console.log(`Inserts / Second: ${(TEST_SIZE / ((endTime - startTime) / 1000)).toFixed(1)}`);
console.log(`Time / Insert: ${((endTime - startTime) / TEST_SIZE * 1000).toFixed(1)}μs`);

const readStartTime = performance.now();

const stmt = db.prepare('SELECT * FROM test where id = ?');
for (let i = 1; i <= TEST_SIZE; i++) {
  const result = stmt.get(i);
}

const readEndTime = performance.now();

console.log('-----------------------------');
console.log(`Reads time taken: ${(readEndTime - readStartTime).toFixed(1)}ms`);
console.log(`Reads / Second: ${(TEST_SIZE / ((readEndTime - readStartTime) / 1000).toFixed(1))}`);
console.log(`Time / Read: ${((readEndTime - readStartTime) / TEST_SIZE * 1000).toFixed(1)}μs`);