Write-ahead log checkpoints

[scarlac]

Q: Should we be doing WAL checkpoints manually?

By default, the write-ahead-log can grow infinitely, or until the next 'checkpoint'. The issue is that it seems that Watermelon by default will not trigger sqlite_close() on the database when the app shuts down, which means checkpointing never seems to happen in the simulator. I noticed that the WAL file was getting significantly larger than the main DB. WAL kept growing across several reboots of the app.

For now, I've added the following, which triggers a checkpoint upon app startup, but ideally those would happen as intended.

const MAX_WAL_SIZE_BYTES = 5000000;
(adapter as any)._dispatcher._db.unsafeQueryRaw(`pragma journal_size_limit = ${MAX_WAL_SIZE_BYTES}`, []);
// Cause a WAL checkpoint (write WAL to db) on startup
(adapter as any)._dispatcher._db.unsafeQueryRaw('pragma wal_checkpoint', []);

I understand that for many people, the size of the WAL won't be an issue, but I am in a situation where low disk space is unfortunately relatively common, and writes are frequent, meaning the WAL size could potentially cause bad performance.

This question is mainly targeted iOS, where it's clear that closure code was removed with a vague comment "not needed on ios":
ios/WatermelonDB/DatabasePlatformIOS.mm:96

void onDestroy(std::function<void()> callback) {
    // not implemented (not needed on iOS)
}

Everything else seems to have been set up correctly - the database will call sqlite_close() if onDestroy() actually triggered those callbacks.

Android might be fine. So, should we be manually checkpointing at some time? Why not?

[radex]

@scarlac

In its default configuration, SQLite will checkpoint the WAL file at the conclusion of any transaction when the WAL file is more than 1000 pages long

I don't think you need to worry about it - unless you have some information that I don't about iOS/Android config disabling automatic checkpointing.

BTW I did add onDestroy implementation (for an entirely different reason) - c084039 - it should be present in 0.26