What is SQLite good for at the edge of a system?
SQLite appears in conversations for opposite reasons. Some teams treat it as a free Postgres for production web apps. Others dismiss it as a toy for mobile prototypes. Both takes miss the point. SQLite is an embedded database engine that runs in process, stores data in a single file, and optimizes for local, low latency reads and writes without a network hop.
That profile makes it excellent at the edge of a system and a poor substitute for the center of gravity where multiple services, concurrent writers, and long lived audit trails converge. Founders who understand that split stop arguing about SQLite versus Postgres as if one must win. They use both, on purpose, with clear jobs.
What SQLite actually is
SQLite is not a server. There is no separate process accepting connections on a port. Your application links the library, opens a file, and executes SQL in the same memory space. Backups can be as simple as copying a file while handling write locks carefully. Deployments can ship the database beside the binary.
The engine is battle tested in phones, browsers, appliances, and countless desktop apps. It respects ACID transactions on a single file. It handles surprising concurrency for read heavy workloads. It fails loudly when you ask it to be a multi writer hub for a SaaS control plane.
Think of SQLite as a precision tool for local state, not the vault where your company's financial history must survive a datacenter migration.
Postgres as center of gravity
PostgreSQL earns the center role in serious products because many clients need the same records at once, constraints must survive application bugs, and operators need backups, replication, and point in time recovery without inventing ceremony.
Postgres is the system of record for orgs, billing, permissions, and anything two different services must agree on. It scales operationally through managed hosts, connection poolers, and read replicas. It exposes a wire protocol so web apps, workers, and ETL jobs can share truth.
SQLite does not replace that job when the product is a multi tenant cloud service with a team on call. It complements it when something local needs fast structure without another network round trip.
Where SQLite shines at the edge
Client local caches and offline first UX. Mobile apps, desktop agents, and field tools that must work in elevators and airplanes keep a SQLite file on device. Sync layers reconcile with Postgres when connectivity returns. Users get instant search and drafts. The cloud keeps authoritative billing and sharing rules.
Edge and serverless functions with read heavy config. A worker that needs structured config, feature flag snapshots, or reference data at cold start can bundle a SQLite file or hydrate one at boot. Reads are microseconds. You avoid hammering Postgres for data that changes hourly while user requests arrive by the second.
Embedded analytics on a snapshot. Export a consistent snapshot from Postgres, attach SQLite in a reporting container, and let analysts run aggressive queries without touching production connections. The snapshot is stale by definition. That is acceptable for internal dashboards if everyone knows the cutoff time.
Single tenant appliances and on prem boxes. Hardware products, kiosk mode deployments, and air gapped installs often ship one SQLite database per device because ops cannot assume a reliable path to your cloud. Postgres in the cloud still collects aggregated telemetry if the model allows delay.
Tests and local development. Spinning up Postgres for every CI job has cost. SQLite gives fast, hermetic tests for data access layers when dialect differences are managed consciously. It is a dev speed tool, not a production identity.
CLI tools and internal utilities. Scripts that need joins and indexes without standing up Docker deserve SQLite. Founders underestimate how many internal workflows become permanent with a SQLite file in /var/lib/company-tool.
In each case SQLite sits at the boundary: close to a user, a device, a function instance, or a temporary job. Postgres remains where multiple actors need shared, durable truth.
Where SQLite is the wrong center
Multi tenant SaaS primary database. One file per tenant sounds elegant until you have ten thousand tenants and backup policies that make finance cry. Operational tooling for many SQLite files is not free. Postgres partitioning and managed hosts exist because this problem is common.
High concurrent writers on one file. SQLite has improved write concurrency. It is still not the engine you pick when hundreds of sessions mutate the same rows continuously. Write storms belong on a server database with mature locking and connection pooling stories.
Complex permission models across services. Row level security, roles, and audit in Postgres integrate with how backend teams already secure APIs. Replicating that across SQLite replicas on devices requires custom sync auth that becomes its own product.
Heavy geographic distribution of writes. If every continent writes authoritative state locally and merges later, you are building CRDTs or sync engines, not "just SQLite." That can be the right product bet. It is not a shortcut.
Regulatory retention on many edges. Devices get lost. Files corrupt. Without a cloud ledger in Postgres, proving what happened on a given date gets harder.
The split founders should draw
Use a two tier mental model:
| Tier | Question | Typical engine |
|---|---|---|
| Center | What must every service agree on forever? | Postgres |
| Edge | What must this device or function know right now? | SQLite |
Data flows center to edge as snapshots or deltas. Flows edge to center as events, uploads, or sync batches with idempotency keys. Never assume the edge file is authoritative for money.
If a row represents a payment intent, it lives in Postgres. If a row represents "draft invoice the user edited offline," it can live in SQLite until sync confirms.
Sync is the hard part, not the file format
Teams romanticize SQLite because the file is portable. The product risk is sync: conflict resolution, schema migrations on old app versions, tombstones for deletes, and backpressure when a device returns online after a month.
Founders should scope sync as carefully as the initial CRUD demo. Ask:
- What happens when two edges edit the same record offline?
- How do migrations run on app update with a populated local file?
- Which side wins for each field class?
- How do you detect partial sync failures before users trust the UI?
SQLite makes local UX fast. Postgres makes shared truth durable. The engineering weeks live in the arrow between them.
SQLite alongside schema driven APIs
Some stacks expose REST from the database via PostgREST or Supabase for the center, while clients keep SQLite for offline reads. That combination is coherent when roles are clear: HTTPS APIs mutate center truth, sync jobs pull allowed subsets to the edge, and local writes queue until the server accepts them.
Trying to expose SQLite directly to the public internet as if it were Postgres is a category error. There is no wire protocol designed for untrusted clients. Edge SQLite stays inside trusted binaries.
Operational contrasts that matter on a budget
Postgres hosting shows up as a line item with storage, IOPS, and connection limits. SQLite at the edge shows up as support tickets when someone's local database corrupts after a failed OS update.
Postgres backups are industry ritual. Edge backups are often "hope the user reinstalls." Product decisions should reflect that asymmetry. Anything that must survive device loss belongs in Postgres before it belongs in a phone file.
Conversely, every read against Postgres from a mobile feed costs latency and money. Aggressive edge caching in SQLite can reduce cloud load and make UX feel instant. That trade is measurable. Instrument both sides before declaring SQLite unnecessary.
Decision guide for founders
Reach for SQLite at the edge when:
- Users need offline or low latency structured data on device.
- A worker needs local reference data without remote queries per invocation.
- You can define clear sync rules and stale UI indicators.
- Loss or delay of edge data is recoverable from center truth.
Keep Postgres as center when:
- Multiple services write the same entities.
- Finance, legal, or SLAs require centralized audit trails.
- Permissions are enforced server side against shared records.
- You need managed backups and replicas without custom per device ops.
Using both is normal, not a failure to pick a winner.
Anti patterns to avoid early
Do not choose SQLite for the production web app's primary database because Docker feels heavy. You will rebuild within a year.
Do not duplicate billing state in SQLite because the mobile UI wanted instant render. Cache display copies, not money.
Do not skip versioned migration strategy for edge schemas. Mobile users update slowly. Old files persist for months.
Do not assume file copy equals backup under write load. Use SQLite backup APIs or brief write locks.
A proof of concept that clarifies architecture
Ship one offline capable screen end to end. Local SQLite for drafts, Postgres for submitted records, sync on reconnect with explicit conflict UI. Measure support burden for two weeks. If conflicts are rare and rules are clear, expand. If every tester finds a new edge case, you learned cheaply that sync is the product risk.
Also load test the center. SQLite success at the edge should not increase Postgres write volume unexpectedly because devices flush huge queues at once.
The bet in plain language
SQLite is good for structured state at the edge where latency, offline life, and deployment simplicity matter more than multi writer global truth. Postgres remains the center of gravity for shared, durable, auditable records in serious products.
Founders win when they stop treating SQLite as a budget Postgres and start treating it as the local half of a deliberate center plus edge architecture. The file is small. The boundary decisions are not.
Work with Kleto
I am James Cowan, a product engineer and the founder of Kleto. Kleto is a product engineering agency that ships production software from strategy through handoff. We help teams place SQLite at the edge without losing Postgres as the system of record, design sync that survives real users, and keep the data model honest as the product grows. If that matches your stack, contact Kleto and we will scope a sensible first step.