Topic hub

Database Comparison by Workload

Choose a database by where writes happen, what must survive failures, which queries matter, and how much infrastructure your team can operate.

Editorial update

Decision in brief

Start with SQLite for application-owned data and modest write concurrency on one host. Compare PostgreSQL and MySQL when several application instances need shared transactional state. Start with DuckDB for analysis of files such as Parquet. Add Redis when a measured need for shared expiry or caching justifies a separate service and you can specify what happens to keys during eviction or recovery.

Choose a data engine: Local app — SQLite. Shared writes — Server SQL. File analytics — DuckDB.
Add a shared cache only if a measured need justifies operating a separate service.

Find a guide

Choose a database for the workload

Measure performance and protect access

Plan replication and recovery

Choose by data ownership and access path

Separate the system that owns a record from systems that accelerate or analyze it. For each workload, write down the required transaction boundary, which process writes, how stale a read can be, and what must be recoverable. Shared state across independently deployed writers changes the design even when average traffic is small.

Evaluate the part of the workload most likely to disqualify a candidate. SQLite can serve an application on one host with a local file, and its WAL mode still permits one writer at a time. PostgreSQL and MySQL run as client/server systems. DuckDB can query Parquet directly. Redis can evict keys under its configured policy, which fits data you can rebuild.

RequirementCandidate to evaluateEvidence to collect
Offline or single-host application stateSQLite on a local diskWrite concurrency under a burst, file ownership after deployment, and a clean backup restore.
Multiple application hosts writing durable relational recordsPostgreSQL or MySQL using the actual driver and ORMTransaction semantics, concurrent writes, schema changes, pool saturation, and operator recovery.
Recurring scans of a file or reporting extractDuckDB with representative Parquet tablesQuery latency by data size, refresh time, and memory when running reports alongside the application.
Shared cache with explicit expiryRedis next to the authoritative databaseHit rate at the intended memory limit, p95 latency, and upstream load during a cold cache.

Two HN examples of what decides the shortlist

In a discussion about database choices for new projects, vibe_that_works favored SQLite with few simultaneous writes and no row-level security requirement. Their example separates deployment and access requirements from the choice of backend language.

In the Orch8 launch, _alphageek described shipping one workflow engine with SQLite for local development and PostgreSQL for scaling. If you rely on a pattern like this, test both environments, including migrations and the retry behavior that must hold when state is shared.

Evaluate two finalists on the same workload

  1. Pick one write path, one latency-sensitive read, and one report or scheduled job. Seed equivalent synthetic data with realistic volume and skew. Record the maximum tolerable p95, error rate, downtime, and data loss.
  2. Run steady and burst traffic with the intended number of writers, connections, and host boundaries. Compare p95 latency, errors, resource cost, and operator time. Match durability expectations across candidates before interpreting throughput.
  3. Rehearse a schema update and restore from the intended backup. For cache candidates, rerun the workload with an empty cache and verify the source database stays within its own latency and error budget.
  4. Adopt the simplest candidate that passes application and recovery limits. Stop a migration when a required invariant, expected reporting refresh, or rollback test fails.