What is SQLite good for at the edge of a system?
SQLite shows up in every stack debate like a guest who might be brilliant or might steal your silverware. 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. It runs in process, stores data in a single file, and optimizes for local reads and writes without a network hop. That profile makes it excellent at the edge of a system. It is a poor substitute for the center of gravity where multiple services, concurrent writers, and long lived audit trails converge.
My default: use both SQLite and Postgres on purpose, with clear jobs. Stop arguing about which one wins. The winner is the architecture that puts each engine where its strengths actually matter.
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. It is not the vault where your company's financial history must survive a datacenter migration.
Postgres stays the 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 your team needs 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. 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 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.
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 sitting quietly on a server.
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.
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.
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. Never assume the edge file is authoritative for money.
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.
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. Edge SQLite stays inside trusted binaries.
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 I would not repeat
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.
Ship one offline capable screen end to end before you bet the company on sync. 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.
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.