Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

sqlite incremental vacuum removing only one free page

I have changed value of auto_vacuum PRAGMA of my sqlite database to INCREMENTAL. When I run PRAGMA incremental_vacuum; through 'DB Browser for SQlite' application it frees all the pages in the free_list. But when I am running same statement using any SQLite library in C# (e.g. Microsoft.Data.SQLite), it frees only one page from the free_list

I verified this by getting the number in current free_list by running PRAGMA freelist_count before and after running PRAGMA incremental_vacuum statement.

Also I tried passing different parameters to incremental_vacuum pragma e.g. PRAGMA incremental_vacuum(100) but it would still free only one page.

Please let me if what is it that I am doing wrong here.

Thanks in advance!

like image 226
Shashank Choudhary Avatar asked Oct 31 '25 04:10

Shashank Choudhary


1 Answers

This happens if one ignores the result from sqlite3_step(). If it returns SQLITE_ROW, the code cannot act as if it had returned SQLITE_DONE. One has to keep calling sqlite3_step() until the query is done: pragma or not, a query is a query and it isn't done until it's, well, SQLITE_DONE :). It happens to remove one page at a time:

bool do_incremental_vacuum(sqlite3 *db) {
   sqlite3_stmt *stmt = nullptr;
   auto rc = sqlite3_prepare_v2(db, "pragma freelist_count;", -1, &stmt);
   if (rc != SQLITE_OK) return false;
   rc = sqlite3_step(stmt);
   if (rc != SQLITE_DONE && rc != SQLITE_ROW) return false;
   auto freelist_count = sqlite3_column_int64(stmt, 0);
   rc = sqlite3_errcode(db);
   if (rc != SQLITE_OK && rc != SQLITE_DONE && rc != SQLITE_ROW) return false;
   std::cout << "There are " << freelist_count << " pages in the free list." << std::endl;

   rc = sqlite3_prepare_v2(db, "pragma incremental_vacuum;", -1, &stmt, nullptr);
   uint64_t pages = 0;
   if (rc != SQLITE_OK) return false;
   do {
      rc = sqlite3_step(stmt);
      if (rc == SQLITE_ROW) ++ pages;
   } while (rc == SQLITE_ROW);
   if (rc != SQLITE_DONE) return false;

   std::cout << "Freed " << pages << " pages" << std::endl;
   return true;
}
like image 142
Kuba hasn't forgotten Monica Avatar answered Nov 04 '25 19:11

Kuba hasn't forgotten Monica



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!