SQLite on FreeBSD: Embedded Database Review

SQLite is the most widely deployed database in the world. It is not a client-server database -- there is no daemon, no socket, no authentication layer. SQLite is a C library that reads and writes directly to a single file on disk. Every smartphone, every web browser, every macOS and iOS device, and most embedded systems use SQLite. On FreeBSD, SQLite is available as a package, used internally by pkg(8) for the package database, and serves as the storage backend for countless applications. This review covers SQLite's architecture, FreeBSD-specific considerations, WAL mode for concurrent access, performance characteristics, and when to choose SQLite over PostgreSQL for small applications.

What SQLite Is

SQLite is a relational database engine implemented as a single C library. It supports most of SQL-92 with extensions. It stores an entire database -- tables, indexes, triggers, views -- in a single cross-platform file. There is no server process. Applications link against the SQLite library and call functions directly. Reads and writes go through the library to the database file on the filesystem.

Core characteristics:

Serverless -- no daemon, no network listener, no configuration file. The database is a file.
Zero configuration -- no setup, no user accounts, no permissions beyond filesystem ACLs.
Self-contained -- the entire engine is a single C source file (the "amalgamation") of roughly 250,000 lines. No external dependencies.
ACID compliant -- full atomicity, consistency, isolation, and durability with journaling or WAL mode.
Cross-platform -- the database file format is binary-identical across platforms. A database created on FreeBSD/amd64 is readable on Linux/arm64 with no conversion.
Public domain -- SQLite is not just open source, it is public domain. No license restrictions of any kind.
Small footprint -- the library is roughly 700 KB compiled. Memory usage depends on workload but starts at a few hundred kilobytes.

SQLite supports transactions, subqueries, triggers, views, common table expressions, window functions, JSON functions, full-text search (FTS5), R-tree indexes for spatial data, and generated columns. It does not support stored procedures, right outer joins (until recently), or concurrent write access from multiple processes (though concurrent reads are fully supported).

Installation on FreeBSD

SQLite is available as a package:

sh
pkg install sqlite3

This installs the sqlite3 command-line shell at /usr/local/bin/sqlite3 and the shared library at /usr/local/lib/libsqlite3.so.

Check the version:

sh
sqlite3 --version

For programming languages, SQLite bindings are typically bundled:

sh
pkg install py311-sqlite3
pkg install php83-sqlite3

Many languages (Python, Go, Rust) include SQLite support in their standard library or most common database drivers.

Building from Ports

For specific compile-time options:

sh
cd /usr/ports/databases/sqlite3
make config
make install clean

The ports build lets you enable or disable FTS5 (full-text search), JSON1, R-tree, and other extensions.

Database Creation and Basic Usage

Create a database and table:

sh
sqlite3 /var/db/myapp/app.db <<'SQL'
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    email TEXT UNIQUE NOT NULL,
    name TEXT NOT NULL,
    created_at TEXT DEFAULT (datetime('now'))
);
CREATE INDEX idx_users_email ON users(email);
SQL

The database file is created automatically when first opened. No CREATE DATABASE command is needed.

Insert and query data:

sh
sqlite3 /var/db/myapp/app.db <<'SQL'
INSERT INTO users (email, name) VALUES ('admin@example.com', 'Admin User');
SELECT * FROM users;
SQL

Setting Pragmas

SQLite behavior is configured through PRAGMA statements, not configuration files:

sh
sqlite3 /var/db/myapp/app.db <<'SQL'
PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;
PRAGMA cache_size=-64000;
PRAGMA foreign_keys=ON;
PRAGMA busy_timeout=5000;
SQL

These pragmas are critical for production use:

journal_mode=WAL -- enables Write-Ahead Logging for concurrent read access (detailed below).
synchronous=NORMAL -- balances durability with performance. FULL is safer but slower.
cache_size=-64000 -- sets the page cache to 64 MB (negative values are in kibibytes).
foreign_keys=ON -- enables foreign key constraint enforcement (disabled by default for backward compatibility).
busy_timeout=5000 -- wait up to 5 seconds when the database is locked instead of returning SQLITE_BUSY immediately.

WAL Mode: The Key to Concurrent Access

WAL (Write-Ahead Logging) mode is the single most important configuration for any SQLite database that handles concurrent access. In the default rollback journal mode, a write locks the entire database -- no other process can read or write until the write completes. In WAL mode, readers do not block writers and writers do not block readers.

How WAL Works

Instead of modifying the database file directly, writes append to a separate WAL file (app.db-wal). Readers see a consistent snapshot of the database as of the start of their transaction -- they read from the main database file and the WAL file. A checkpoint operation periodically transfers WAL data back to the main database file.

Enable WAL mode:

sh
sqlite3 /var/db/myapp/app.db "PRAGMA journal_mode=WAL;"

WAL mode is persistent -- once enabled, it remains active until explicitly changed. You only need to set it once.

WAL Limitations

Single writer -- WAL mode allows concurrent reads but still permits only one writer at a time. If two processes attempt simultaneous writes, one will wait (up to busy_timeout milliseconds) or receive SQLITE_BUSY.
Network filesystems -- WAL mode requires shared-memory primitives (mmap) that do not work correctly on NFS or other network filesystems. Use SQLite on local filesystems only.
Checkpoint overhead -- automatic checkpointing happens when the WAL file reaches 1000 pages. For write-heavy workloads, manual checkpoint management may be needed.

Check WAL status:

sh
sqlite3 /var/db/myapp/app.db "PRAGMA wal_checkpoint(PASSIVE);"

Performance Characteristics

SQLite's performance on FreeBSD is directly tied to filesystem performance. On ZFS, SQLite benefits from ARC caching, compression, and the copy-on-write architecture. On UFS, performance is more straightforward and predictable.

Read Performance

SQLite reads are fast. A properly indexed table can serve point queries in single-digit microseconds. For read-heavy workloads with WAL mode, multiple processes can read concurrently without contention. SQLite regularly outperforms client-server databases for simple queries on small-to-medium datasets because there is no network round-trip, no query parsing on a remote server, and no result serialization.

Write Performance

SQLite writes are bounded by filesystem sync performance. Each transaction commits with a fsync() call (with synchronous=FULL) to ensure durability. On spinning disks, this limits write throughput to roughly 50-100 transactions per second. On SSDs, throughput reaches thousands of transactions per second.

Batch writes in transactions for performance:

sh
sqlite3 /var/db/myapp/app.db <<'SQL'
BEGIN;
INSERT INTO users (email, name) VALUES ('user1@example.com', 'User 1');
INSERT INTO users (email, name) VALUES ('user2@example.com', 'User 2');
INSERT INTO users (email, name) VALUES ('user3@example.com', 'User 3');
COMMIT;
SQL

A single transaction with 1,000 inserts is 100x faster than 1,000 individual inserts because the fsync() cost is amortized across the entire transaction.

ZFS Considerations

On ZFS, set the recordsize to match SQLite's page size for optimal I/O alignment:

sh
zfs set recordsize=4k tank/sqlite-data

SQLite's default page size is 4096 bytes. Matching the ZFS recordsize avoids read-modify-write amplification where ZFS reads a 128 KB record to modify 4 KB.

Disable ZFS sync for development databases where durability is not critical:

sh
zfs set sync=disabled tank/dev-databases

This dramatically improves write throughput but risks data loss on power failure. Never use this for production data.

Use Cases on FreeBSD

Application Configuration Storage

Many FreeBSD applications use SQLite for configuration and state:

sh
# FreeBSD pkg database
ls -la /var/db/pkg/local.sqlite

# Application state
sqlite3 /var/db/myapp/state.db "SELECT * FROM config;"

Small Web Applications

Web applications with modest traffic -- internal tools, personal blogs, documentation sites -- work well with SQLite. A web application handling 100 requests per second with mostly reads and occasional writes is well within SQLite's capabilities.

Data Analysis and Reporting

SQLite is excellent for ad-hoc data analysis. Import CSV data, run queries, export results:

sh
sqlite3 /tmp/analysis.db <<'SQL'
.mode csv
.import /var/log/parsed_access.csv access_log
SELECT status_code, COUNT(*) as count
FROM access_log
GROUP BY status_code
ORDER BY count DESC;
SQL

Embedded Systems

FreeBSD on embedded hardware (ARM boards, appliances) benefits from SQLite's minimal footprint. No database daemon consuming resources, no configuration to manage, just a library and a file.

Testing and Development

SQLite is ideal for development and testing environments where spinning up a PostgreSQL instance adds friction:

sh
# Use SQLite for tests, PostgreSQL for production
# Many ORMs support this pattern
sqlite3 :memory: "SELECT 1;"  # In-memory database for unit tests

SQLite vs PostgreSQL for Small Applications

This is the practical question most developers face. The answer depends on your concurrency requirements and feature needs.

Choose SQLite when:

Your application is single-server (not distributed).
Write concurrency is low (fewer than 10-20 concurrent writers).
You do not need stored procedures, materialized views, or advanced PostgreSQL features.
You want zero operational overhead -- no daemon to monitor, no backups to schedule (just copy the file), no upgrades to coordinate.
Your dataset fits comfortably on a single disk (SQLite supports databases up to 281 TB, but practical limits are lower).
You are building embedded systems, CLI tools, desktop applications, or small web services.

Choose PostgreSQL when:

You need concurrent write access from multiple processes or servers.
You need row-level locking, not database-level locking.
You need replication, streaming replication, logical replication, or any form of multi-node architecture.
You need advanced SQL features: materialized views, table partitioning, foreign data wrappers, custom types, PL/pgSQL.
Your application will grow beyond a single server.
You need user-level authentication and role-based access control at the database level.

The gray zone:

Applications with moderate traffic (100-1,000 requests per second) and low write ratios (90% reads, 10% writes) can work with either. SQLite with WAL mode handles this workload on FreeBSD. PostgreSQL handles it with more headroom for growth. The decision often comes down to operational preference -- do you want a file or a service?

Backup and Recovery

Backing up SQLite is trivially simple compared to client-server databases.

File Copy Backup

The safest approach uses the SQLite backup API via the CLI:

sh
sqlite3 /var/db/myapp/app.db ".backup /var/backups/app-$(date +%Y%m%d).db"

This creates a consistent backup even while the database is in use. Do not simply cp the database file while it is being written to -- the copy may be corrupted. The .backup command uses the SQLite online backup API, which handles this correctly.

ZFS Snapshots

On ZFS, snapshots provide instant, consistent backups:

sh
zfs snapshot tank/sqlite-data@backup-$(date +%Y%m%d)

ZFS snapshots are atomic, so the database file and WAL file are captured in a consistent state. This is the recommended backup strategy for SQLite on FreeBSD with ZFS.

Integrity Check

Verify database integrity after backup:

sh
sqlite3 /var/backups/app-20260409.db "PRAGMA integrity_check;"

A result of ok means the database is structurally sound.

Limitations

Single writer -- only one process can write at a time, even with WAL mode. High-write-concurrency workloads need a client-server database.
No network access -- SQLite is a local file. There is no built-in way to access it over the network. Wrapping SQLite in an HTTP API (like Litestream or rqlite) adds complexity.
No user authentication -- access control is filesystem permissions only. If a user can read the file, they can read all data.
Limited ALTER TABLE -- SQLite's ALTER TABLE support is restricted compared to PostgreSQL. Adding columns is supported, but dropping columns was only added in version 3.35.0, and modifying column types requires recreating the table.
No concurrent schema changes -- schema modifications lock the database like any other write.

Verdict

SQLite on FreeBSD is an underappreciated tool. It is not a replacement for PostgreSQL -- it is a different category of software that solves a different set of problems. For single-server applications, CLI tools, embedded systems, data analysis, development environments, and any scenario where the overhead of a database server is unjustified, SQLite is the right choice. On FreeBSD with ZFS, the combination of ZFS snapshots for backups, ZFS compression for storage efficiency, and SQLite's minimal footprint creates a remarkably capable and low-maintenance data storage stack.

The key to using SQLite well is understanding its concurrency model. Enable WAL mode, set a busy timeout, batch writes in transactions, and stay within the single-writer constraint. Within those boundaries, SQLite is fast, reliable, and effectively maintenance-free.

Frequently Asked Questions

Is SQLite suitable for a production web application on FreeBSD?

Yes, for applications with moderate traffic and low write concurrency. Many production applications serve hundreds of requests per second using SQLite with WAL mode. The constraint is concurrent writes -- if multiple processes need to write simultaneously, PostgreSQL is the better choice.

How do I use SQLite with PHP on FreeBSD?

Install the PHP SQLite extension: pkg install php83-sqlite3. PHP's PDO driver also supports SQLite natively. No database server configuration is needed -- just point PDO at the database file path.

Can SQLite handle a database larger than available RAM?

Yes. SQLite reads only the pages it needs from disk, not the entire file. The cache_size pragma controls how much data is kept in memory. A 10 GB database on a system with 2 GB of RAM works fine -- performance depends on how much of the working set fits in cache and the page cache.

Should I use SQLite on ZFS or UFS?

ZFS is recommended for the snapshot-based backup capability and ARC caching. Set recordsize=4k on the dataset containing SQLite databases for optimal alignment. UFS works but lacks the snapshot and caching advantages.

How do I monitor SQLite performance?

Use the sqlite3_analyzer tool (available in the SQLite source distribution) to analyze database structure and fragmentation. PRAGMA statements like PRAGMA page_count; and PRAGMA freelist_count; provide basic statistics. For runtime monitoring, instrument your application to track query execution times.

Can multiple applications share a single SQLite database on FreeBSD?

Yes, with WAL mode and proper busy timeout configuration. Each application links against the SQLite library and opens the same database file. WAL mode ensures readers do not block each other. Writes are serialized -- only one process writes at a time, but the busy timeout allows others to wait rather than fail immediately.

SQLite on FreeBSD: Embedded Database Review