SQLite Performance Optimization

1. For all native targets, write-ahead logging (WAL) is enabled to support concurrent reads and writes. PowerSync also dispatches queries to multiple threads for parallelism. With the JavaScript web SDK, a WAL-like option is available on Chromium browsers.
2. SDKs also configure connections for performance by enabling a large page cache of around 50MB and setting pragma synchronous = normal to avoid frequent fsync operations.

​

Debugging Query Performance

1. The query itself is slow, e.g. because it reads a lot of rows or uses unoptimized joins not backed by an index. The Fixing Slow Queries section describes how these queries can be optimized.
2. The database is blocked. There can only be a single writer at a time, and the number of concurrent readers is bound by an option set when opening the database. So a query that appears slow might actually be fast, having run at an unfortunate time when the database was busy.

• PowerSync queries from client code.
• Internal statements from PowerSync, including queries saving sync data, and begin/commit statements.

• The time spent waiting for the global transaction lock. It still includes all overhead in worker communication, so you generally won’t see concurrent queries reflected in the trace.
• Internal statements from powersync-sqlite-core, used by the Sync client for Sync Stream bookkeeping.

export const db = new PowerSyncDatabase({
  schema: AppSchema,
  database: {
    dbFilename: 'powersync.db',
    debugMode: true // Defaults to false. To enable in development builds, use
      // debugMode: process.env.NODE_ENV !== 'production'
  }
});

​

Fixing Slow Queries

1. For auto-updating watched queries on frequently-changed tables, queries might run very often. Watched queries should typically be cheap to run, as they otherwise risk blocking SQLite connections for too long. For more expensive queries that still need to be watched, consider increasing their throttle to run them less often.
2. As an alternative to regular watched queries, use High Performance Diffs, which use triggers internally to only report changed rows back to your app.
3. Where possible, filter on the id column of tables to reduce the number of rows read, which makes queries more efficient. When filtering on other columns of large tables, make sure these columns are covered by indexes declared in your app’s schema.
4. PowerSync tables are views over JSON data that extract columns by parsing from JSON each time a row is accessed. While SQLite has a cache for parsed JSON, this can still be inefficient for queries computing on columns. Raw Tables let you use plain SQLite tables instead, which are faster to query but require special consideration for migrations.

​

Prepared Statement Cache

const db = new PowerSyncDatabase({
  schema,
  database: {
    dbFilename: 'my_database.db',
    // Cache up to 64 prepared statements
    preparedStatementsCache: 64,
  },
});

final db = PowerSyncDatabase(
  schema: Schema([...]),
  path: 'my_database.db',
  sqliteOptions: SqliteOptions(
    // Cache up to 64 prepared statements
    preparedStatementCacheSize: 64,
  ),
);