Shrinking a Room database in 2026: VACUUM, auto_vacuum, and rows that never really leave

Delete ten thousand rows from a Room database and the file on disk doesn’t get smaller. Users notice this the honest way: they clear old transactions, uninstall and reinstall to “start fresh,” and the app’s storage entry in Settings barely moves. Nothing is wrong with the delete — the rows are gone, the query results are correct — but SQLite doesn’t hand pages back to the filesystem just because a table shrank. It marks them free and keeps them, in the same file, for the next write to reuse.

That’s a deliberate default, not a bug, and understanding why it works that way is most of what you need to fix it.

Why the file doesn’t shrink on its own

SQLite organizes a database as fixed-size pages. A DELETE removes rows from a page and adds that page to an internal freelist — available for the next INSERT, but still allocated to the file. Nothing shrinks the file itself unless you explicitly ask SQLite to do so, because that’s a separate, more expensive operation: it has to rewrite the entire database into a new file with the freed pages actually removed, then swap it in.

By default, Room’s auto_vacuum mode is NONE. That’s the right choice for most apps most of the time — it means every write is just a page update, not a file rewrite — but it also means a database that grew to 40 MB during a year of heavy use and then had 90% of its rows deleted is still a 40 MB file. In an app like Granyn, where someone might delete years of old transaction history after switching budgeting methods, that gap is exactly where support emails about “storage bloat” come from.

Three ways to reclaim the space, and their real costs

PRAGMA vacuum rebuilds the whole file and reclaims every free page at once. It’s the most thorough option and the most expensive: it needs roughly as much free disk space as the database currently occupies, it holds an exclusive lock for the duration, and on a database in the tens of megabytes on a mid-range device that can take a noticeable fraction of a second to a few seconds. Never call it on the main thread, and never call it automatically on every app launch — it’s a maintenance operation, not a startup step.

auto_vacuum = INCREMENTAL is the one worth defaulting to for a growing local-first app. Set once, before any tables exist, it doesn’t rewrite the file automatically — instead it lets you reclaim pages a little at a time with PRAGMA incremental_vacuum(N), which you can run on a schedule without the all-or-nothing cost of a full VACUUM.

auto_vacuum = FULL reclaims space after every transaction automatically. It sounds convenient and I’d avoid it: it turns routine deletes into rewrite-heavy operations, trading a rare maintenance cost for a small tax on every write, forever.

auto_vacuum can only be set on an empty database or changed via a full VACUUM afterward — it’s not a setting you can flip later without a rewrite, so decide before you ship:

Room.databaseBuilder(context, AppDatabase::class.java, "app.db")
    .addCallback(object : RoomDatabase.Callback() {
        override fun onOpen(db: SupportSQLiteDatabase) {
            db.query("PRAGMA auto_vacuum").use { if (it.moveToFirst() && it.getInt(0) == 0) {
                db.execSQL("PRAGMA auto_vacuum = INCREMENTAL")
            } }
        }
    })
    .build()

Note that setting the pragma on an existing NONE database only takes effect after the next full VACUUM — this callback prepares a fresh install correctly; an app that’s already shipped with NONE needs a one-time migration that runs VACUUM once to switch modes.

The cleanup job that actually matters more than any of this

VACUUM and incremental vacuuming reclaim space SQLite already knows is free. They do nothing about space your own schema is holding onto deliberately — the far more common cause of bloat in a local-first app. A soft-delete column (isDeleted = true, kept around for undo or sync) that never gets hard-deleted will keep the row, and its space, forever, no matter how aggressively you vacuum.

The fix is a periodic WorkManager job that hard-deletes rows past their soft-delete grace period, then reclaims the pages that frees up:

class DatabaseMaintenanceWorker(
    context: Context,
    params: WorkerParameters,
) : CoroutineWorker(context, params) {

    override suspend fun doWork(): Result {
        val cutoff = System.currentTimeMillis() - THIRTY_DAYS_MS
        val db = AppDatabase.getInstance(applicationContext)

        db.transactionDao().hardDeleteSoftDeletedBefore(cutoff)
        db.openHelper.writableDatabase.execSQL("PRAGMA incremental_vacuum(500)")

        return Result.success()
    }

    companion object {
        private const val THIRTY_DAYS_MS = 30L * 24 * 60 * 60 * 1000
    }
}

Schedule it as unique periodic work, once a week, with a battery-not-low constraint — this is cleanup, not something worth waking the device for:

val request = PeriodicWorkRequestBuilder<DatabaseMaintenanceWorker>(7, TimeUnit.DAYS)
    .setConstraints(Constraints.Builder().setRequiresBatteryNotLow(true).build())
    .build()

WorkManager.getInstance(context).enqueueUniquePeriodicWork(
    "db_maintenance",
    ExistingPeriodicWorkPolicy.KEEP,
    request,
)

incremental_vacuum(500) reclaims up to 500 pages per run rather than trying to do it all at once — cheap enough to run weekly on a background thread without a user ever noticing.

Checking your work

Don’t guess whether any of this helped — measure it. PRAGMA page_count and PRAGMA page_size multiply out to the actual space SQLite is using, and PRAGMA freelist_count tells you how many pages are free but not yet returned to the filesystem:

fun databaseStats(db: SupportSQLiteDatabase): Pair<Long, Long> {
    val pageCount = db.query("PRAGMA page_count").use { it.moveToFirst(); it.getLong(0) }
    val pageSize = db.query("PRAGMA page_size").use { it.moveToFirst(); it.getLong(0) }
    val freePages = db.query("PRAGMA freelist_count").use { it.moveToFirst(); it.getLong(0) }
    return (pageCount * pageSize) to (freePages * pageSize)
}

If freelist_count stays high release after release, incremental vacuuming isn’t running often enough. If the file size itself keeps climbing despite a low freelist, the problem isn’t vacuuming at all — it’s a soft-delete column nobody is hard-deleting. Fix the one that’s actually broken; running VACUUM more often won’t clean up rows your own schema is still keeping.