Comparison

PostgreSQL vs SQLite: Concurrency and Migration

Decide between SQLite and PostgreSQL based on write concurrency, deployment topology and recovery goals. Includes production accounts and a practical migration trial.

Editorial update

Decision in brief

Choose SQLite for an embedded database or an application with a clear owner host and short writes that meet the latency target. Choose PostgreSQL when several hosts need to write to shared state, you need centralized access controls, or your recovery topology calls for a database server. Measure writer wait under peak load before migrating for performance.

SQLite suits local ownership and short writes. Evaluate PostgreSQL for shared writes across hosts and central database roles. Test peak load and recovery.
Use measured write latency and recovery requirements to validate the choice.

See discussion trends ↓

Which deployment fits your system?

SQLite runs in process and owns a local database file. PostgreSQL is a client-server database designed for multiple sessions that share state. A SQLite database on one host can support many readers and short sequential writes. WAL mode does not allow simultaneous writers on the same file and requires that participating processes be on the same host.

RequirementStart with SQLite when …Evaluate PostgreSQL when …
DeploymentA device app, local service or a single server owns the file.Multiple application hosts must connect to the same authority on the network.
Writes at peakTransactions are short and their queue remains inside the request budget.Write waits or busy errors exceed your budget despite shorter transactions and controlled admission.
RecoverySnapshot backups and recovery on a replacement host meet your loss and recovery targets.You must support coordinated failover, replication or point-in-time recovery and can operate the supporting infrastructure.
Access and SQLFile access maps cleanly to application ownership. Embedded SQLite SQL and extensions satisfy the contract.Different services require separate database roles or a required PostgreSQL feature is part of the design.

SQLite vs PostgreSQL performance under real traffic

A local SQLite read can avoid database network latency. That advantage is meaningful when data and application run together. At peak write load, the waiting time for the single writer can matter more than the SQL execution time. PostgreSQL lets concurrent transactions proceed where the data and locks allow, but contention on a hot row or a slow disk can still dominate.

Do not pick a traffic cutoff from someone else's benchmark. Keep durability and query correctness comparable, and report separate end-to-end latency for reads and writes. Include the cold-start, restart and connection-pool effects the application actually experiences.

  1. Replay the hottest requests against the same sanitized fixture, first at normal load and then at the highest recent burst. On SQLite, capture lock retries, time waiting to write, writer duration and WAL growth. On PostgreSQL, capture connection waits, lock waits and the cost of client-server round trips.
  2. Run a second trial with the changes you would actually deploy, such as a shorter transaction, batching independent writes on one host, or moving shared-state writes to PostgreSQL. Keep the same cold-cache and warmed-up runs for each test.
  3. Restart an isolated instance during the replay. Verify acknowledged writes, restore from backup and time recovery. Reject either configuration if it meets latency targets only by violating the recovery objective.

What teams on Hacker News used each database for

The Orch8 author described SQLite for local development and PostgreSQL as an option for deployments with scaling needs. That documents the author's product design rather than a universal migration threshold. It is a useful model if local self-contained setup is a goal and the team can test both backends.

HN commenter IgorPartola reported using SQLite for temporary SQL transformations and local save files, with PostgreSQL often serving as the permanent shared store. This is a middle path when the system has both local and shared workloads.

HN user somat reported extra work adapting a Python application between the engines. Differences included parameter binding, connection and cursor usage, and row representation. A common API does not make transaction semantics, driver behavior or SQL dialect identical.

Migration decision and rehearsal

Set the trigger before starting, such as writer wait consuming more than its allocated share of p99 latency at peak or the need to add independent owner hosts. A slow query or an oversized transaction is a reason to diagnose the workload first.

  1. Snapshot a representative SQLite source using a supported backup method. Inventory foreign keys, generated IDs, collations, loosely typed columns and driver-specific SQL behavior.
  2. Load the snapshot into a candidate PostgreSQL schema and run the same application tests. Compare key-based aggregates and representative values, not just table totals.
  3. Rehearse the final write freeze or supported change capture. Verify that all acknowledged writes appear on the target at the handoff point.
  4. Define a rollback period with a plan for writes accepted after the switch. If you cannot reconcile those changes, keep the staging trial read-only or schedule a deliberate cutover.

Related articles