Maintenance Operations in QoreDB: VACUUM, ANALYZE, OPTIMIZE, REPAIR

VACUUM, ANALYZE, OPTIMIZE, REPAIR, CHECK, REINDEX, CLUSTER, compact, validate, integrity_check. Each engine has its own vocabulary for talking about table maintenance, and these words don't mean the same thing. A PostgreSQL VACUUM doesn't do what a MySQL OPTIMIZE does, a MyISAM REPAIR TABLE has nothing to do with a SQL Server DBCC CHECKTABLE, and compact on MongoDB touches a different object (a collection, not a table).

In QoreDB, these operations are exposed via a dedicated dialog, accessible by right-clicking on a table. It's a small feature on the surface, but the way it's wired says a lot about the product's principles. This post describes how the dialog is built, why the operations aren't unified behind a common vocabulary, and how the execution result is presented to the user.

One menu per engine, not a unified menu

When you open the maintenance dialog on a table, the first thing QoreDB does is call the Tauri command list_maintenance_operations. It queries the active driver to find out what this engine can do, and returns a typed list: operation, is_heavy flag (whether it can be slow on a large table), has_options flag (whether there are parameters to check).

On PostgreSQL, the driver exposes Vacuum (with the FULL option), Analyze, Reindex, and Cluster (with the choice of index). On MySQL and MariaDB, they are Optimize, Analyze, Check, Repair, and ChangeEngine (with the target engine as a parameter). On SQLite, you find Vacuum, Analyze, Reindex, and IntegrityCheck. On MongoDB, they are Compact and Validate. On SQL Server, Check via DBCC CHECKTABLE. Managed cloud engines like Neon or Supabase inherit the PostgreSQL set via the pg_compat module.

The important technical choice is here: QoreDB doesn't offer an abstract "Optimize the table" checkbox that would trigger a VACUUM or an OPTIMIZE depending on the engine. The name shown in the interface is the engine's name. A developer who sees OPTIMIZE knows they're going to run an OPTIMIZE command, with OPTIMIZE semantics. This refusal to unify is consistent with the product's line: not hiding the differences between databases, because those differences matter at the moment something goes wrong.

A dedicated dialog to make the action explicit

The main entry point is the context menu of the table tree. A right-click, Maintenance, and a dialog opens with the list of operations relevant for this (driver, table) pair. Heavy operations are visually flagged; those with options expose a form: a FULL checkbox for PostgreSQL VACUUM, an index choice for CLUSTER, a target engine for ChangeEngine on MySQL.

Making a separate dialog, rather than letting the user type VACUUM in the SQL editor, serves two goals. The first is to remind you that this isn't an ordinary query: it modifies state without returning rows, and its execution time is hard to predict. The second is to make the action repeatable on the same table without having to memorize the exact syntax, which changes from one engine to another. The dialog displays the final command that will be executed before launching it, so the user sees what's going to be sent to the server.

A structured execution result

A maintenance operation doesn't return rows, it returns messages. A PostgreSQL VACUUM ANALYZE can emit a dozen NOTICEs with the pages scanned and the row estimate. A MySQL CHECK TABLE returns a table with a status per row. A SQL Server DBCC CHECKTABLE produces an integrity report. A MongoDB validate returns a BSON document with a valid boolean and counters.

QoreDB exposes these outputs in a common format on the frontend: a MaintenanceResult with the command actually executed, the list of typed messages (info, warning, error, status), the execution time in milliseconds, and the success boolean. This is what makes it possible to have a single rendering in the dialog, with warnings in yellow and errors in red, whatever the engine.

The format is common, but the content is not. A MongoDB message stays a MongoDB message. QoreDB doesn't reinterpret "1 invalid index" into a generic category, it displays it as-is. The normalization stops at the structure (level, text) because reinterpreting an engine's diagnostics would amount to lying about what it says.

The environment guardrails apply

A maintenance operation is not a read. VACUUM FULL rewrites the table on disk, OPTIMIZE can take an exclusive lock, REPAIR touches integrity. These are mutations, in the broad sense. They therefore go through the same path as other mutations: the run_maintenance command first checks the session_manager's read-only mode. If the connection is read-only, the call is blocked before it even reaches the driver, with a clear message.

Then the Universal Query Interceptor is called with a preview of the command, for example MAINTENANCE Vacuum ON mydb.orders, and the connection's environment (Dev, Staging, Prod). The safety rules defined for this environment can block the operation, require a confirmation, or just emit a warning. This uniformity matters: whether the mutation comes from the SQL editor, a context menu, or the maintenance dialog, it goes through the same control point.

In practice

The typical user flow looks like this. Right-click on a table in the tree, Maintenance entry. The dialog fills with the operations the driver announced. You select VACUUM, check FULL if you want to reclaim disk space, and launch. The dialog shows a spinner, then the SQL command actually executed, the list of messages, and the time taken. If the operation produced a warning or an error, it's visible without having to go dig through the logs.

On a development pool, everything goes through. On a connection marked Prod, the interceptor can require an explicit confirmation before letting a VACUUM FULL through, because that type of operation is sometimes undesirable during business hours. The driver doesn't need to know these rules: they are configured at the environment level, applied at the Tauri command level, and the driver receives an order to run or an order to do nothing.

What this small dialog says about the product

Maintenance is a textbook case for a multi-database client: it's where the temptation to create a common abstraction is strongest, and it's also where it fails fastest. QoreDB chooses the other path. Each driver declares its own set of operations, with their real options. The interface offers a consistent UI to launch them and read their output, but it never pretends that a command from one engine is equivalent to a command from another. The developer stays facing the engine they use, with the words they know, and QoreDB handles the rest: discovery, execution, display, guardrails.