Support opening more readers later with the sqlite3_connection_pool

[davidmartos96]

Hello!
We are in the process of trying to squeeze the performance of our app start. SQLite it's a hard dependency when starting so we are currently waiting for the sqlite_async (Powersync) factory to initialize before showing the interactive UI to the users. At the moment we are comfortable with 5 readers, but that means that on app start we need to wait for the initialization of all the readers in the pool (about 1.3 s on my emulator). If we use 1 reader, time is reduced to 500 ms.
What we were thinking is if there could be a way to open with 1 writer / 1 writer + 1 reader, and then have some way of adding more readers at runtime.
We would open the app as thin as possible and then asynchronously spawn the other readers when the user can interact with the UI.

Thanks!

[simolus3]

I didn't want to add the option of resizable pools initially to reduce complexity, but if it's necessary for performance then it sounds like something worth adding.

I'm kind of surprised about those numbers though, that sounds very slow to open a database considering that opening a database and running some pragmas doesn't really do much and should mostly just read a single page. Is this a debug build? But even for those that doesn't sound great.

How would I analyze this to reproduce, is an await PowerSyncDatabase(...).init() in a Stopwatch is roughly what you're waiting on? Apart from SQLCipher, any pragmas / unusual setup steps I should test?

[davidmartos96]

This is currently on an emulator, opening a database of 320 KBs. Using SQLCipher from latest sqlite3 version.

We are using PRAGMA temp_store = FILE but removing it doesn't have any difference. The schema is 10 tables

1 reader: 500 ms
5 readers: 1100 ms

Code that I was measuring:

    final MyFactory psFactory = MyFactory(
      path: dbPath,
      key: <mypassword>
      sqliteOptions: SqliteOptions(
        maxReaders: 5,
      ),
    );

    try {
      final psDb = PowerSyncDatabase.withFactory(
        psFactory,
        schema: makeSchema(),
      );

      await psDb.initialize();

     // print elapsed
}


base class MyFactory extends NativePowerSyncOpenFactory {
  final String? key;

  MyFactory({
    required super.path,
    required this.key,
    super.sqliteOptions,
  }) : super();

  @override
  List<String> pragmaStatements(SqliteOpenOptions options) {
    return [
      if (key != null && key!.isNotEmpty) 'PRAGMA key = ${quoteString(key!)}',
      ...super.pragmaStatements(options),
      // Make sure to set `sqlite3_lib.sqlite3.tempDirectory` on Android. On some versions SQLite cannot
      // reliably know which temp folder to use
      'PRAGMA temp_store = FILE',
    ];
  }

  @override
  Database openNativeConnection(SqliteOpenOptions options) {
    final db = super.openNativeConnection(options);

    // Disable double quoted string literals during tests
    if (kDebugMode) {
      db.config.doubleQuotedStringLiterals = false;
    }

    return db;
  }
}