Example: Turbo Boost Mode

Some developers are desperate to wring every last bit of performance out of their database, even to the point of risking data loss or corruption. Some PRAGMA statements tie into performance this way.

For example, normally, many times when SQLite writes data to disk, it will use fsync() or the equivalent to block until all of the bytes are confirmed to be written. This is important in operating systems with write-caching filesystems, as otherwise the data that you think that you wrote might actually just be in a buffer waiting to be written in the future. Android, when using the ext4 filesystem, is one such OS. However, PRAGMA synchronous = OFF tells SQLite to skip those fsync() calls. This speeds up I/O, with increased risk of the database becoming corrupted if there is a major system problem while that I/O is going on. This is a transient PRAGMA, only affecting the current connection.

Even riskier is PRAGMA journal_mode = MEMORY. In effect, this says to keep the transaction log of the database in memory, rather than writing it to disk. As the documentation states, “if the application using SQLite crashes in the middle of a transaction when the MEMORY journaling mode is set, then the database file will very likely go corrupt”. But, some people would consider performance gains as being a valid trade-off here. This is a persistent setting, and so it only needs to be applied once.

The approach for both of these cases is to use a RoomDatabase.Callback, as seen in the CityPop/RoomPragma sample project. RoomDatabase.Callback was introduced in the chapter on the support database API.

The create() method that we use to create an instance of our CityDatabase uses a RoomDatabase.Builder as normal. However, based on a boolean parameter, it may also use addCallback() to add a RoomDatabase.Callback to the builder:

  static CityDatabase create(Context ctxt, final boolean applyPragmas) {
    RoomDatabase.Builder<CityDatabase> b=
      Room.databaseBuilder(ctxt.getApplicationContext(), CityDatabase.class,
        DB_NAME);

    if (applyPragmas) {
      b.addCallback(new Callback() {
        @Override
        public void onCreate(@NonNull SupportSQLiteDatabase db) {
          super.onCreate(db);

          db.query("PRAGMA journal_mode = MEMORY");
        }

        @Override
        public void onOpen(@NonNull SupportSQLiteDatabase db) {
          super.onOpen(db);

          db.query("PRAGMA synchronous = OFF");
        }
      });
    }

    return(b.build());
  }

There are two methods that you can supply on a Callback implementation: onCreate() and onOpen(). As the names suggest, they are called when the database is created and opened, respectively. In each, you are handed a SupportSQLiteDatabase instance, which has an API reminiscent of the framework’s SQLiteDatabase. It has a query() method that works like rawQuery(), taking a simple SQL statement (that might return a result set) and executing it. Since PRAGMA might return a result set, we have to use query() instead of execSQL(). Here, we invoke our PRAGMA statements at the appropriate times.

And, in truth, there does seem to be a significant performance gain:

(tests conducted on a Google Pixel)

Proper use of transactions — such as doing all of the inserts at once rather than one at a time — has a much bigger impact, though. Using these two PRAGMA statements is a bit like using a holodeck with the safeties off: you may have some casualties.