When SQLite Is the Right Database on a VPS

SQLite is not a toy, and it is not a stand-in for every server database. It is a C library that stores a complete relational database in a file, with ACID transactions, a real query planner, and a SQL dialect that has survived decades of production use. On a single VPS that fact changes the architecture: there is no database daemon to patch, no TCP listener to firewall, no connection pool to size. The application opens a path, and that path is the database.

That simplicity is why so many quiet production services run SQLite. It is also why teams pick it for the wrong reasons. This post is a decision filter for a Linux VPS, not a manifesto.

Workloads that fit a file database

SQLite wins when one machine owns the data and the write rate is honest.

• One application process, or a small set of processes on the same host, is the only writer.
• Read traffic dominates, or writes arrive in modest bursts you can serialize without a queue overflowing.
• The working set fits comfortably on local High-Speed SSD, typically from tens of megabytes to a few tens of gigabytes.
• You want deployments you can copy, snapshot, and reason about without standing up a cluster.

Typical fits: a CMS or blog, an internal admin tool, a queue consumer with local state, a status dashboard, a personal SaaS prototype, a sidecar that must survive restarts without Redis. Plenty of those stay on SQLite for years and never regret it.

The hardware on a Netbay VPS is Intel Xeon Platinum with High-Speed SSD. Sequential writes and fsync latency are the two numbers that matter. A quiet disk is enough; you do not need a clustered filesystem or a dedicated database node.

When it is the wrong default

Do not start with SQLite if any of these are true on day one:

• Several machines must write the same dataset at once.
• You need streaming replication, logical decoding, or hot standbys as a first-class feature of the database itself.
• Connection counts from many app workers will contend on a single writer lock all day.
• The schema already depends on LISTEN/NOTIFY, stored procedures, or index types you cannot map.

Those are Postgres problems. Pretending they are SQLite problems costs more than installing Postgres.

A useful middle path exists: run SQLite in the application today, keep the schema portable, and leave a door to Postgres. Avoid types you cannot explain in both engines. SQLite type affinity is flexible, but your application should still bind integers, text, and blobs explicitly.

One VPS, one file, one ops story

Operations for SQLite on a VPS is file operations. Place the database on a dedicated data directory such as /var/lib/myapp/data/app.db, owned by the service user, mode 640. Enable WAL so readers do not block a writer. Set a busy timeout so a second process waits instead of raising SQLITE_BUSY. Keep the file off /tmp, off the application git checkout, and off any filesystem that tmpwatch or a container restart can wipe.

Backups are a consistent copy of that file, not a dump from a running daemon. WAL, placement, and online copy each deserve their own notes. The architectural point is that the unit of disaster recovery is a file on Lucknow DC01 disk plus an off-box copy you control. L3/L4 DDoS filtering on the public interface does not protect a database sitting in a world-readable home directory.

A small, honest schema

Here is a schema that is enough for a typical app and stays easy to migrate later.

PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 5000;
PRAGMA foreign_keys = ON;
PRAGMA synchronous = NORMAL;

CREATE TABLE IF NOT EXISTS accounts (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS events (
  id INTEGER PRIMARY KEY,
  account_id INTEGER NOT NULL REFERENCES accounts(id),
  kind TEXT NOT NULL,
  payload TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS events_account_created
  ON events(account_id, created_at);

Open it from a single long-lived connection in the app process. isolation_level set to None puts Python in autocommit; wrap multi-statement changes in BEGIN IMMEDIATE so the writer lock is taken up front instead of upgrading later.

import sqlite3

def connect(path):
    conn = sqlite3.connect(path, timeout=5.0, isolation_level=None)
    conn.row_factory = sqlite3.Row
    conn.execute('PRAGMA journal_mode = WAL')
    conn.execute('PRAGMA busy_timeout = 5000')
    conn.execute('PRAGMA foreign_keys = ON')
    return conn

A decision you can write down

Write the choice, not a vibe. Single host, one writer, reads mostly: SQLite. Need many writers across processes you do not control: queue the writes, or use Postgres. Dataset larger than local disk comfortably holds, or query shapes that need a dedicated planner team: Postgres. If you cannot fill that sentence, you do not have a database decision yet.

Measure before you graduate. Count writes per second at the peak hour, not the marketing average. If a single writer with WAL stays under a few hundred committed transactions per second and readers never wait, you are still in SQLite territory. If workers spend their time in SQLITE_BUSY even after a five second timeout, you have already voted for a different architecture.

SQLite is the right database on a VPS when the file is the product and the host is the boundary. You can spin up an Ubuntu 24.04 instance on Netbay in under 60 seconds and follow along — netbayhosts.in.