rqlite schedules planner maintenance during snapshots

Query planning depends on information about the data, and that information needs maintenance as tables change. rqlite 8.30.0, released on September 5, adds automatic PRAGMA optimize to its SQLite management. The interesting implementation decision is when to perform that work. rqlite places the check inside snapshot creation, where its own synchronization already controls concurrent database activity.

The release changelog records the new capability and shows that the embedded engine had just moved to SQLite 3.46.1. Understanding that engine version matters because SQLite's optimization pragma has version-specific behaviour. A command named optimize can sound like a general performance repair, but its actual work is defined by the engine and the mask passed to it.

I value rqlite's small operational model when it also covers maintenance that otherwise becomes another scheduled task. Automating planner-statistics work fits that preference. The implementation still needs a precise explanation, because a configured interval and the moment an operation actually runs are different things.

The interval marks maintenance as due

The command-line configuration adds -auto-optimize-int with a default of 24h. Setting it to zero disables the automatic operation. The default therefore enables the new maintenance behaviour without an additional flag for an ordinary node launch.

An explicit configuration can retain the default interval or disable it. These are alternative startup examples for the new option.

rqlited -auto-optimize-int 24h /var/lib/rqlite

rqlited -auto-optimize-int 0h /var/lib/rqlite

The store implementation compares the current time with the last recorded optimization time. If no last time is available, it uses a stored base time. An elapsed interval makes optimization due. That comparison does not itself execute the pragma or arrange a separate wakeup at the exact boundary.

The execution point is the state machine's snapshot method. It first establishes its existing snapshot synchronization, checks automatic VACUUM work, and then checks whether optimization is due. If it is due, the method calls the database optimization helper before continuing to construct the snapshot. The elapsed time and the opportunity to create a snapshot jointly determine when the automatic work happens.

This means that a 24-hour setting should not be described as an exact daily job. The code checks the condition when snapshot creation occurs. A node does not gain a new independent scheduler that wakes solely to optimize at that interval. This is a practical consequence of using an existing maintenance boundary.

The snapshot boundary supplies coordination

The snapshot method's comments explain that Raft does not call its apply and snapshot operations concurrently. The method also blocks queries that involve a transaction through the query transaction lock. Automatic maintenance runs within that established control of the database's state. The change reuses a place where rqlite already has reason to coordinate access.

Optimization failure is a snapshot failure in this path. The code increments its automatic-optimization failure counter and returns the error instead of proceeding with snapshot construction. On success, it records the duration, increments the success counter, and writes the last optimization time. These are distinct observable outcomes, rather than a timer that silently invokes SQL in the background.

This placement also explains why maintenance time can be visible in snapshot work. The database helper runs before snapshot construction continues, so its execution is part of that control flow. The counters and timing values give operators information about the added operation. They do not promise that every invocation changes the query planner's statistics.

The first call considers a broader set of tables

The database helper uses a mutex and a boolean to distinguish its first optimization call from later calls. On the first call, it selects OptimizeAll, whose value is 0x10002. Later calls use OptimizeDefault, whose value is 0xFFFE. The helper formats that value into a PRAGMA optimize statement executed through the write database connection.

Those masks have specific meaning in SQLite 3.46.1's pragma implementation. The 0x00002 bit permits ANALYZE work on tables that may benefit. The 0x10000 bit broadens consideration to tables even when the current connection has not queried them. This is why rqlite's first-call choice can cover tables beyond the connection's observed query history.

The first mask does not mean every table receives a complete unconditional analysis. SQLite still applies its table and statistics conditions. The source excludes views and virtual tables from this operation and skips internal table names. It considers whether indexes lack statistics and whether table size has changed sufficiently. The name OptimizeAll is rqlite's constant name, while the engine's rules define the work it triggers.

SQLite's default mask also includes a bit for bounded analysis behaviour. rqlite's initial 0x10002 mask differs in that respect from its later default mask. Consequently, treating the first invocation and every later invocation as identical maintenance would miss an actual difference in the statements the database receives.

The helper sets its first-call boolean before executing the pragma. That state records an attempted initial optimization, even when the underlying call returns an error.

Improving planning without rewriting the application

The optimization pragma can run ANALYZE where SQLite decides the statistics need attention. That supplies information used by the planner. It does not rewrite the application's SQL or create a missing application index. The new automation maintains an input to planning, which is a narrower and more useful description than saying the database automatically fixes slow queries.

My preference is for that kind of bounded responsibility. rqlite can own the timing and synchronization of maintenance while SQLite decides which analysis work its pragma requires. In this release, the node's interval marks the work as due, a snapshot provides the execution opportunity, and the database helper chooses the first-call or subsequent-call mask. Those mechanisms explain the new default without assigning it performance guarantees the implementation does not make.