Postgres vs SQLite for Home Lab Apps

“Just use SQLite” became a meme in homelab and indie circles around 2023. And look—it’s not wrong. Single file, no daemon, WAL mode, Litestream replication. Zero ops burden. But the moment you need concurrent writers from multiple processes, full-text search that doesn’t murder performance, network access, real RBAC, or online schema migrations—Postgres stops being overkill and starts being the obvious choice.

Here’s the honest version: both are brilliant at what they’re built for. SQLite wins at “I need a database for one app on one machine.” Postgres wins at “I need a database that scales with my team and infrastructure.” But the line between them is blurrier than the marketing suggests, and it moves based on your actual constraints.

Let’s stop the flamewars and talk specifics.

SQLite: The Single-File Magic

SQLite is not a “lightweight database.” It’s a database library embedded in your application. No separate server, no listening port, no daemon to crash at 2 AM. Your app has direct access to a file. That’s the whole architecture.

For single-writer workloads—and I mean actually single-writer, one process writing at a time—SQLite is practically magic.

Why SQLite Wins

No ops friction. You copy a file. Backup is cp database.db backup.db. Restore is the inverse. No connection strings, no database user permissions, no schema ownership surprises. Your Django app, your Go service, your Python CLI—they all just open the file and go.

Embedded deployment. Self-hosted apps that ship with SQLite (Linkding, Vaultwarden, Pi-hole, Outline, Paperless-ngx) ship with near-zero database setup. Download, extract, run. The database comes with it. Try that with Postgres—your users suddenly need to learn Docker Compose or manage a separate Postgres container.

Edge and embedded. Your iPhone stores its data in SQLite. Edge workers, serverless functions, IoT devices, embedded Linux—SQLite is everywhere because it has no external dependencies. If your app can compile to WebAssembly or run on a Raspberry Pi, SQLite is the obvious choice.

Real concurrency is possible. WAL mode (Write-Ahead Logging) changed the game. With WAL:

• Readers don’t block writers
• Multiple readers work in parallel
• One writer at a time, but readers never stall

Add busy_timeout to your connection string, and SQLite handles surprising amounts of concurrent read load.

Transactions are real. SQLite ACID compliance is rock-solid. You get proper isolation levels, rollback semantics, and crash-safety without thinking about it.

The SQLite Comfort Zone

SQLite shines for:

• Single-app databases: Linkding (bookmark manager), Vaultwarden (password manager), Miniflux (RSS reader), Gitea (smaller instances), Nextcloud (with caveats—see below)
• Edge and ephemeral compute: Cloudflare Workers (Durable Objects), AWS Lambda (with SQLite in layers), serverless functions
• Home dashboards: Grafana with SQLite backend (if you only have one Grafana instance), custom monitoring dashboards, personal wikis
• Local-first apps: Apps that sync to a server later (like Obsidian with local vault)
• Read-heavy with occasional writes: Analytics dashboards, logs, audit trails where you batch-insert at night

The SQLite Tricks That Take You Further

Before you bail on SQLite, know these tricks exist.

WAL mode is standard:

PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 5000;  -- 5 second retry on lock

This lets your app handle multiple concurrent readers while writes queue up. For homelab apps, this is often enough.

Connection pooling helps too. If you’re using SQLite from Python/Go/Node, a connection pool reuses connections and hides the file lock semantics from your code:

# Using sqlite3 with busy_timeout
import sqlite3
conn = sqlite3.connect('app.db', timeout=5.0)  # 5-second retry
cursor = conn.cursor()

Litestream (formerly LiteFS) is the replication trick nobody talks about. It’s a FUSE filesystem daemon that continuously replicates your SQLite database to S3 or object storage. Your SQLite file stays local, gets microsecond-level backup, and can fail over to a replica:

litestream replicate /var/lib/app.db s3://my-bucket/backups/app.db

Your app sees no changes. Backups happen in real-time. Recovery is a one-liner. This is how Fly.io runs SQLite in production.

Partial indexes and AUTOINCREMENT tuning also matter. SQLite is surprisingly fast with the right schema:

-- Partial index for active records (ignores deleted=true rows)
CREATE INDEX idx_active_posts ON posts(created_at) WHERE deleted = false;

-- Full-text search table (if you absolutely need it)
CREATE VIRTUAL TABLE posts_fts USING fts5(title, content);

Full-text search in SQLite is good enough for small datasets (< 100K documents). Beyond that, you’re fighting the indexing model.

Postgres: When SQLite Breaks

Postgres starts earning its keep the moment you hit one of these walls:

Multiple concurrent writers. SQLite allows one writer at a time. Period. If your app has two processes writing simultaneously—a web service and a background job, or two instances of the same service—SQLite will serialize the writes or fail with database is locked. Postgres handles this natively via MVCC (Multi-Version Concurrency Control). Readers and writers coexist without blocking.

Online schema migrations. Adding a NOT NULL column to a 10GB SQLite table locks the entire database. With Postgres, you can add, modify, and index columns while the database is live:

-- In Postgres, this doesn't lock readers/writers
ALTER TABLE posts ADD COLUMN category TEXT NOT NULL DEFAULT 'uncategorized';

-- In SQLite, the table is unavailable for seconds/minutes

(This is why the site has the article “Adding NOT NULL on a Big Table”—it’s a Postgres-specific optimization.)

Role-based access control. Postgres has granular per-user permissions, column-level security, and row-level security policies. SQLite has… none of this. Your app either has full database access or nothing.

If you’re building a multi-tenant app (shared hosting, SaaS), or you want to isolate permissions (readonly reporting user, admin, etc.), Postgres is the only choice.

Rich data types. Postgres has:

• JSONB — JSON with indexing and fast operations, perfect for semi-structured data
• Arrays — native integer[], text[] types with indexing
• Ranges — daterange, int4range with containment queries
• HStore, UUID, Enum — domain-specific types
• PostGIS — full geographic data (points, polygons, spatial indexes)
• Full-text search — native tsvector with ranking, phrase search, and language support

SQLite has TEXT, INTEGER, BLOB, and… that’s it. JSON support exists but is slow and lacks indexes. If your data is relational and simple, SQLite is fine. If you need semi-structured data with fast queries, Postgres wins.

Connection pooling and many clients. Postgres handles thousands of concurrent connections gracefully (via connection pooling with PgBouncer). SQLite file locks get angry with more than a handful of simultaneous readers. For apps with 50+ concurrent users, SQLite will frustrate you.

Replication and high availability. SQLite has no built-in replication. Litestream solves this for backups and failover, but it’s not Postgres-style logical replication or streaming replication with read replicas. If you need to distribute reads across replicas or have strict RPO/RTO requirements, Postgres is the only choice.

Extensions. Postgres lets you install extensions:

• pg_trgm — trigram full-text search (fuzzy matching)
• pgvector — vector similarity (for embeddings and RAG)
• uuid-ossp — UUID generation
• PostGIS — geographic data
• pg_stat_statements — query performance analysis

SQLite extensions exist (json1, fts5) but they’re lighter-weight and fewer.

Real-World Apps That Outgrew SQLite

• Nextcloud — Ships with SQLite, but production deployments use Postgres or MySQL due to concurrency and performance needs
• Mastodon — Has always required Postgres. The distributed nature (federation) and high concurrency make SQLite untenable
• Immich (photo library) — Supports SQLite but recommends Postgres for more than a few thousand photos
• Gitea — Works fine with SQLite for small instances, but multi-user deployments and federation need Postgres

The Breaking Points: A Decision Matrix

Here’s where SQLite stops working:

The Postgres Side: Why It’s Not Overkill

Postgres is heavier: you need a daemon, a connection string, a separate process. But the trade-off is worth it when you hit those constraints.

Setup in homelab is trivial:

docker run -d \
  -e POSTGRES_PASSWORD=secretpassword \
  -v postgres_data:/var/lib/postgresql/data \
  -p 5432:5432 \
  postgres:17-alpine

One command. Your app connects to postgres://user:pass@localhost:5432/appdb. Schema migrations run on startup. Done.

Performance is excellent. Postgres handles millions of queries per second on commodity hardware. For homelab apps (even with thousands of records), it feels infinitely fast compared to SQLite’s serialization model.

Operational visibility is built-in:

-- See running queries
SELECT query FROM pg_stat_statements ORDER BY calls DESC;

-- Find slow queries
SELECT mean_exec_time, calls, query FROM pg_stat_statements WHERE mean_exec_time > 100;

-- Check index usage
SELECT schemaname, tablename, indexname, idx_scan FROM pg_stat_user_indexes ORDER BY idx_scan;

SQLite has no equivalent. You’re debugging blind.

Backups are flexible:

# Logical backup (portable, can restore to other systems)
pg_dump dbname > backup.sql

# Restore
psql dbname < backup.sql

# Physical backup (fast, binary, point-in-time recovery)
pg_basebackup -D /path/to/backup

SQLite’s cp approach works but offers no granularity.

The Litestream Question: “Now SQLite Has Replication”

Litestream is a clever workaround. It continuously replicates your SQLite database to S3, Backblaze B2, or similar. You get:

• Real-time backups
• Multi-region failover (on supported providers)
• Point-in-time recovery

But here’s the catch: Litestream solves backups, not concurrency. If you have multiple writers, SQLite will still serialize them. Litestream just copies the file after writes happen.

For embedded or edge apps (one instance), Litestream is excellent. For multi-instance apps or high concurrency, you still need Postgres.

Homelab Apps: Which Database?

Use SQLite:

• Linkding (bookmarks) — single user, read-heavy
• Vaultwarden (passwords) — single user, occasional writes
• Pi-hole (DNS blocklist) — one instance, background updates
• Miniflux (RSS) — one instance, background feeds
• Gitea (small team) — if it’s 1-5 users, SQLite is fine
• Outline / Trilium (notes) — personal use, one or two writers
• Paperless-ngx (document storage) — batch-ingests, low concurrency

Use Postgres:

• Nextcloud (file sync, calendar, contacts) — multi-user, concurrent uploads
• Immich (photo library) — if you have 10K+ photos and multiple users
• Mastodon / Pixelfed (social) — federation and concurrency demand it
• Matrix homeserver — distributed messaging, high concurrency
• Superset / Metabase (dashboards) — multi-user queries
• Custom apps with 50+ concurrent users — anything with real traffic

On the fence (test with SQLite first, migrate to Postgres if needed):

• Gitea (test limit: ~20 users)
• Nextcloud (test limit: 1-2 concurrent users for file sync)
• Jellyfin (test limit: 5+ concurrent streams; transcoding is CPU-bound, not DB-bound)

The Honest Position

The correct answer is “Postgres first, SQLite if you have a reason.” Not the reverse.

SQLite’s single-file simplicity is seductive. But the moment you ship a multi-user app, enable federation, or want future flexibility, you’ve picked wrong. The switching cost later (migrating 10 GB of data, rewriting queries, handling concurrent writes for the first time) is higher than just starting with Postgres.

The exception: If your app is truly single-writer (one machine, one instance, one user, forever), SQLite wins. The operational simplicity is real. Backup is trivial. Restore is trivial. No secrets to manage, no connection strings, no schema ownership surprises.

For homelab specifically, the sweet spot is:

• Personal apps (bookmarks, notes, RSS) → SQLite + Litestream for backups
• Multi-user services (file sync, photos, social) → Postgres in Docker, 5-minute setup
• Data-intensive apps (analytics, search, mapping) → Postgres, maybe with PostGIS or pgvector

SQLite is not a consolation prize. It’s the right tool for a specific problem. The trick is knowing when you have that problem and when you don’t.

Quick Decision Rubric

Before you pick, ask yourself:

1. Do I have multiple concurrent writers (from different processes or machines)? → Postgres
2. Do I need online schema migrations? → Postgres
3. Do I need RBAC or row-level security? → Postgres
4. Is this a single-instance, single-user app? → SQLite (+ Litestream)
5. Do I expect 100+ concurrent users? → Postgres
6. Is this app going to be shipped to users? → SQLite (embedded)
7. Is this going to change a lot over time? → Postgres (easier migrations)

If you answered “Postgres” to any of the first five, you know what to do. Otherwise, SQLite with WAL mode and busy_timeout will probably surprise you with how far it goes.

The hardest part isn’t picking the database. It’s being honest about what your app actually needs, not what you think it might need someday.

Related Reading

• SQLite Replication: Litestream and rqlite
• Postgres EXPLAIN ANALYZE Without Crying
• Postgres HA: Patroni + etcd + HAProxy
• PostgreSQL on ZFS: Tuning, Snapshots, Pitfalls
• Adding NOT NULL on a Big Table Without Downtime