skip to content

What is SQLite's VFS layer, and when would you choose or write a VFS other than the platform default?

level: seniorimportance: nice to knowfreq 22%

answer

  1. bottom of the module stack
  2. every OS call goes through it
  3. unix and win32 are the defaults
  4. mixed locking VFSes corrupt files
  5. shims wrap another VFS

basics

~20 s

The VFS is SQLite's operating-system interface: every file open, read, write, sync and lock goes through it. The defaults are unix and win32; alternatives change locking, add tracing or quotas, or put pages somewhere that is not an ordinary file.

solid answer

~50 s

SQLite is a stack: tokenizer, parser and code generator, then the bytecode engine, the B-tree, the pager, and at the bottom the **OS interface, or VFS**. A VFS is three C objects: `sqlite3_vfs` (open, delete, access checks, randomness, sleep, time), `sqlite3_file` for an open file, and `sqlite3_io_methods` (read, write, truncate, sync, size, lock, unlock, close). Porting SQLite means writing a VFS. Unix builds default to `unix` and, depending on compile-time options, may offer `unix-excl`, `unix-dotfile` and `unix-none`; Windows defaults to `win32`. You pick one with the fourth argument of `sqlite3_open_v2()` or a `vfs=` URI parameter, which wins. Reasons to change: one process owning the file (`unix-excl`), tracing I/O with a shim such as `vfstrace`, quotas, very large files split across smaller ones, or a device with no filesystem. The rule that matters: every process on one file must use compatible locking, and `unix-none` used by two connections at once can easily corrupt the database.

code

console · 11 lines
console
$ sqlite3 catalog.db
sqlite> SELECT count(*) FROM sqlite_schema;
0
sqlite> .vfsname
unix
sqlite> .quit
$ sqlite3 'file:catalog.db?vfs=unix-excl'
sqlite> SELECT count(*) FROM sqlite_schema;
0
sqlite> .vfsname
unix-excl

go deeper

for a junior

Recall that the VFS is SQLite's bottom layer, the part that talks to the operating system, and that unix and win32 are the defaults.

for a middle

Name the three objects a VFS implements and the ways to select one for a connection, with the URI parameter taking priority.

for a senior

Show judgment about alternatives: unix-excl for a single owning process, shims for tracing and quotas, and why incompatible locking VFSes or unix-none corrupt a shared file.

for a principal

Weigh a custom VFS as a long-term liability: it sits beneath every commit, so own its testing with fault-injection shims before betting data on it.

## Where the VFS sits SQLite's documentation describes the library as a stack of modules: 1. **Tokenizer, parser and code generator** turn SQL text into a prepared statement, a bytecode program. Roughly, these are `sqlite3_prepare_v2()`. 2. **The virtual machine** runs that bytecode. 3. **The B-tree module** organises the file into ordered key/value stores, one per table and index. 4. **The pager** loads fixed-size pages into memory, implements transactions and maintains the journal files that protect against crashes. 5. **The OS interface, or VFS**, the bottom layer, through which every other module talks to the operating system. The VFS is what makes SQLite portable: porting it to a new operating system is "simply a matter of writing a new OS interface layer". Because every file operation and every lock goes through it, a VFS can also change *where* pages live and *how* processes coordinate, without the upper layers noticing. ## The three objects a VFS implements | Object | Represents | Methods include | |---|---|---| | `sqlite3_vfs` | The VFS itself, with a unique name | open, delete, check access, canonical path, randomness, sleep, current time | | `sqlite3_file` | One open file | created by the VFS's `xOpen` method | | `sqlite3_io_methods` | Operations on an open file | read, write, truncate, sync, file size, lock, unlock, close | Only the `sqlite3_vfs` object is registered, with `sqlite3_vfs_register()`. Several VFSes can be registered at once, separate connections in one process can use different VFSes, and each attached database can use its own. ## The built-in choices Which of these a build contains depends on its compile-time options. - **`unix`**: the default on Unix, using POSIX advisory locks. - **`unix-excl`**: takes and holds an exclusive lock on the database, keeping other processes out, and keeps the WAL index in heap memory rather than shared memory. - **`unix-dotfile`**: locks with a dot-file beside the database instead of POSIX locks. - **`unix-none`**: every lock operation is a no-op. SQLite's documentation warns that it will easily corrupt the database if two or more connections use it at the same time. - **`win32`**: the Windows default; `win32-longpath` allows path names up to 65534 bytes instead of 1040, and `win32-none` disables locking. The unix variants differ only in locking, and apart from `unix` and `unix-excl`, their locking implementations are **incompatible**. Two processes reaching one file through incompatible VFSes do not see each other's locks and can corrupt it. SQLite recommends staying with `unix` or `unix-excl` unless there is a compelling reason. ## Choosing a VFS for a connection In order of priority: 1. A **`vfs=` parameter in a URI filename**, such as `file:catalog.db?vfs=unix-excl`, which needs URI filenames enabled. 2. The **fourth argument to `sqlite3_open_v2()`**. 3. The **default VFS**, which `sqlite3_vfs_register(pVfs, 1)` can replace process-wide. Naming a VFS that is not registered fails the open with "no such vfs". ## Shims and special-purpose VFSes A **shim** is a VFS that wraps another one, does something extra, and passes each call down. The public source tree carries examples: - **`vfstrace`** logs every VFS call, which is useful when diagnosing what I/O a workload really does. - **`quota`** tracks the combined size of a group of files and can fail a write with `SQLITE_FULL` past a threshold. - **`multiplex`** splits one large database into many smaller files, letting databases outgrow a filesystem's file-size limit. - **`appendvfs`** stores a database at the end of another file, such as an executable. - **`memdb`** keeps a database in memory and backs `sqlite3_deserialize()`. - A demonstration VFS shows SQLite writing straight to a device's flash memory with **no filesystem at all**. ## When a senior engineer reaches for one - **One process owns the file**, as in a desktop application that never shares its database: `unix-excl` makes that explicit, and WAL then works without shared memory. - **A platform without POSIX files**: an embedded device, a real-time OS, or storage that is not a local file needs a custom VFS. - **Observability and limits**: wrap the default VFS in a tracing or quota shim rather than changing application code. - **Never to make a lock error go away.** Choosing `unix-none` because `SQLITE_BUSY` is annoying swaps a visible error for silent corruption; busy handling belongs to the material on SQLite's locking and write concurrency. The cost of a custom VFS is real: it sits under every transaction, so any mistake in locking or sync ordering breaks SQLite's atomic-commit guarantees. That is why SQLite's own test suite uses shims that check write ordering and simulate faults.

  • Why can a process using unix-excl and one using the default unix VFS safely open the same file, while unix-dotfile and unix cannot?
    `unix-excl` uses the same POSIX locks as `unix`; it just takes an exclusive lock and keeps it, so the other process is shut out rather than ignored. `unix-dotfile` signals ownership with a lock file that the POSIX-lock VFS never checks, so each process believes it has the file to itself and both can write at once.
  • How would you find out which file operations a slow SQLite workload actually performs?
    Wrap the default VFS in a tracing shim such as `vfstrace`, a source file in SQLite's tree that you compile into your build. It logs a message for every VFS method call, then passes the call to the real VFS. The log shows each read, write, sync and lock, which can reveal excessive syncs or repeated reads, without changing the application's SQL.

  • The VFS is a virtual table feature for querying external data.
  • unix-none is a safe way to stop SQLITE_BUSY errors between processes.
  • Each process on one file may choose whichever locking VFS it likes.
  • The fourth argument to sqlite3_open_v2() overrides a vfs= URI parameter.
  • Changing the VFS needs changes to the SQL the application runs.