Database Selection - Postgres vs SQLite vs MySQL for Small Services

Status: Active
Last Updated: 2026-08-26
Category: Databases - Fundamentals
Prerequisites: schema-overview, prisma-connections
Time: 1 hour
Tags: postgres, sqlite, mysql, database-selection, architecture, prisma

Summary

A decision matrix for choosing between PostgreSQL, SQLite, and MySQL for small self-hosted services like fogserv.cloud's CRM/CMS. Covers when SQLite is genuinely enough, when Postgres earns its operational cost, and why MySQL rarely wins on small greenfield projects.

🎯 What You'll Learn

By the end of this article, you'll be able to:

✅ Score a workload against a Postgres/SQLite/MySQL decision matrix
✅ Recognize the signals that mean "SQLite is enough"
✅ Avoid the classic mistake of running SQLite behind multiple writers
✅ Justify a database choice in a design note or PR description

Context / Why This Matters

The fogserv.cloud schema (schema-overview) currently plans SQLite in development and PostgreSQL in production via Prisma. That split is only safe if we understand why each database is appropriate where it sits. Picking a database by habit ("always Postgres") wastes ops hours on tiny services; picking it by fashion ("SQLite everywhere!") causes production lockups under concurrent writes.

Database choice is also an ORM choice. Our stack runs Prisma (prisma-connections), and while Prisma abstracts most dialect differences, some features (row-level locking hints, JSON operators, full-text search) leak through. Deciding early avoids painful migrations later.

Implementation / Core Content

The Decision Matrix

Score your service against these dimensions before choosing:

Dimension	SQLite	PostgreSQL	MySQL
Concurrent writers	1 (whole-file lock)	Thousands	Hundreds
Reads while writing	OK with WAL	Excellent	Excellent
Ops burden	Zero (a file)	Medium (server, pooling, backups)	Medium-high
Data size sweet spot	< ~10 GB	Any	Any
Replication / HA	Via litestream-style file shipping	Built-in streaming replication	Built-in
Extensions (JSON, FTS, PostGIS)	Basic FTS5	Richest ecosystem	Good
Managed hosting options	Limited	Everywhere	Everywhere
Backup story	File copy (careful)	pg_dump / WAL archiving	mysqldump / binlog
Fit for our CRM/CMS	Dev + single-node prod	Production target	Not needed

The Five Questions

Answer these for any new service:

How many processes write concurrently? More than one → not SQLite (unless carefully serialized).
What's the realistic data volume at 10x growth? Under a few GB → SQLite fine.
Do you need server-side features? Row-level security, materialized views, extensions, advisory locks → Postgres.
Who operates it, and how much time do they have? One part-time operator → SQLite removes a whole class of work.
Is there an existing instance to reuse? If a Postgres container already runs nearby (see docker-compose-patterns), adding a database costs nothing.

Scoring: two or more "yes, we need that" answers on questions 1, 3, or 5 → Postgres.

Why MySQL Loses on Small Greenfield Projects Here

MySQL is excellent at what it does, but for our stack it is dominated: Postgres matches or beats it on every dimension we care about, Prisma's docs and type generation are first-class for both, and our team already knows Postgres tooling (psql, pg_dump, PgBouncer). Introducing MySQL adds a second operational skillset with no offsetting benefit. Keep it as an option only when integrating with third-party software that ships MySQL-only.

The Dev/Prod Split Problem

Our current plan (SQLite dev → Postgres prod) has real risk: dialect drift. PRAGMA behaviors, case sensitivity, date handling, and constraint enforcement differ. Mitigations:

Run integration tests against the same engine as production, even if quick unit checks use SQLite.
Prefer Prisma-level abstractions over raw SQL so the dialect boundary stays inside Prisma.
Alternatively, run Postgres in Docker for dev too — with docker-basics-level skills this costs about five minutes and eliminates the entire class of drift bugs.

Practical Examples

Example 1: A tiny internal tool — SQLite wins

Single-writer admin dashboard, ~50 MB of data, deployed as one container with a mounted volume:

// prisma/schema.prisma
datasource db {
  provider = "sqlite"
  url      = env("DATABASE_URL") // file:/data/app.db
}

Zero database servers to patch, monitor, or back up beyond a file copy. See sqlite-production-patterns.

Example 2: The fogserv.cloud CRM/CMS — Postgres wins

Per schema-overview: CampaignLog rows arrive from webhooks concurrently with page-view tracking, comment moderation, and email sends. Multiple workers writing simultaneously plus ~500 MB projected growth plus future row-level security needs = clear Postgres territory.

datasource db {
  provider   = "postgresql"
  url        = env("DATABASE_URL")
}

Example 3: Documented decision record

## DB choice: newsletter-service
Q1 concurrent writers? YES (webhook ingest + scheduler)
Q3 server features? YES (advisory locks for send dedup)
→ Decision: PostgreSQL 16, shared cluster, own database name.
Revisit if write volume stays under 1 req/s after 6 months.

Common Pitfalls & Troubleshooting

Problem	Cause	Fix
"database is locked" errors in prod	SQLite with multiple writer processes	Move to Postgres or serialize writes through one worker; see sqlite-production-patterns
Query behaves differently in dev vs prod	SQLite dev / Postgres prod drift	Test against Postgres in CI; see prisma-migrations-guide
Postgres container OOM-killed	shared_buffers sized for bare metal, not container	Cap memory per the container; see postgres-tuning-basics
Chose Postgres but nobody maintains backups	Ops cost underestimated	Either commit to the backup runbook (backup-recovery-drill) or drop back to SQLite + litestream
Migration to another DB later hurts	Raw SQL scattered through code	Keep raw SQL centralized; rely on Prisma query API

Next Steps / Ops Actions

Running SQLite in production? Read sqlite-production-patterns before anything else.
Running Postgres? Set up tuning defaults and pooling: postgres-tuning-basics.
Whatever you chose, define the backup path now: backup-recovery-drill.
Wire schema changes into CI: prisma-migrations-guide and woodpecker-first-pipeline.

Sources & Related

External references consulted:

Change Log

2026-08-26

Initial creation covering Postgres/SQLite/MySQL decision matrix for small services.