perf: tune SQLite connection pragmas

TheLastCicada · TheLastCicada · commit 6083289bff65 · 2026-05-06T10:36:03.000-07:00
Apply CADT's SQLite write-path PRAGMAs whenever V1 or V2 opens a
SQLite connection so catch-up uses a larger page cache without requiring
operator configuration.
diff --git a/src/database/index.js b/src/database/index.js
@@ -12,6 +12,7 @@ import { migrations } from './migrations';
 import { seeders } from './seeders';
 
 import dotenv from 'dotenv';
+import { installSqlitePragmas } from './sqlite-pragmas.js';
 dotenv.config({ quiet: true });
 
 // possible values: local, test
@@ -40,10 +41,12 @@ if (nodeEnv === 'test') {
 }
 
 export const sequelize = new Sequelize(config[dbConfigKey]);
+installSqlitePragmas(sequelize);
 
 const mirrorConfig =
   (process.env.NODE_ENV || 'local') === 'local' ? 'mirror' : 'mirrorTest';
 export const sequelizeMirror = new Sequelize(config[mirrorConfig]);
+installSqlitePragmas(sequelizeMirror);
 
 // Snapshot of whether V1 MIRROR_DB was fully configured at module-load time.
 // Captured alongside sequelizeMirror construction so mirrorDBEnabled() stays
diff --git a/src/database/sqlite-pragmas.js b/src/database/sqlite-pragmas.js
@@ -0,0 +1,43 @@
+const SQLITE_PRAGMAS = [
+  'PRAGMA synchronous = NORMAL',
+  'PRAGMA cache_size = -65536',
+  'PRAGMA temp_store = MEMORY',
+  'PRAGMA mmap_size = 268435456',
+];
+
+const applySqlitePragmas = async (connection) => {
+  if (!connection || typeof connection.exec !== 'function') {
+    return;
+  }
+
+  await new Promise((resolve, reject) => {
+    connection.exec(SQLITE_PRAGMAS.join('; '), (error) => {
+      if (error) {
+        reject(error);
+        return;
+      }
+      resolve();
+    });
+  });
+};
+
+export const installSqlitePragmas = (sequelize) => {
+  if (sequelize.getDialect() !== 'sqlite') {
+    return;
+  }
+
+  const configuredConnections = new WeakSet();
+  const { connectionManager } = sequelize;
+  const getConnection = connectionManager.getConnection.bind(connectionManager);
+
+  connectionManager.getConnection = async (...args) => {
+    const connection = await getConnection(...args);
+
+    if (!configuredConnections.has(connection)) {
+      await applySqlitePragmas(connection);
+      configuredConnections.add(connection);
+    }
+
+    return connection;
+  };
+};
diff --git a/src/database/v2/index.js b/src/database/v2/index.js
@@ -11,6 +11,7 @@ import { migrations } from './migrations';
 import { seeders } from './seeders';
 
 import dotenv from 'dotenv';
+import { installSqlitePragmas } from '../sqlite-pragmas.js';
 dotenv.config({ quiet: true });
 
 // possible values: local, test
@@ -39,6 +40,7 @@ if (nodeEnv === 'test') {
 }
 
 export const sequelizeV2 = new Sequelize(config[dbConfigKey]);
+installSqlitePragmas(sequelizeV2);
 
 // Determine if MySQL mirror is configured by checking the actual config values
 // This allows MySQL mirror to work in any environment (local, test, production)
@@ -80,6 +82,7 @@ const isMysqlMirrorConfiguredForReconnectV2 = () => {
 };
 
 export const sequelizeV2Mirror = new Sequelize(config[mirrorConfig]);
+installSqlitePragmas(sequelizeV2Mirror);
 
 // Snapshot of "is the mirror Sequelize instance actually pointing at MySQL"
 // taken at module-load time, alongside sequelizeV2Mirror construction.
