Why I Chose SQLite Over PostgreSQL

Why I Chose SQLite Over PostgreSQL

I chose SQLite over PostgreSQL because LockMargin is a desktop app that runs on the user's machine — not on a server. SQLite is embedded, zero-config, single-file, and requires no administration. PostgreSQL is a server database. For a freelancer who just wants to send an invoice, asking them to install PostgreSQL isn't a product. It's a sysadmin job.

The Decision Point

December 2025. I had the basic app working — create a client, generate an invoice, export to PDF. But I needed a database. Two options:

1. SQLite — embedded, single-file, zero-config
2. PostgreSQL — server-based, multi-user, enterprise-grade

I'd spent 20 years building systems with PostgreSQL. I knew it. I loved it. But I chose SQLite. Here's why.

The Conversation

Vlad: I was leaning toward PostgreSQL. It's what I know. It's what I've used for factories, retail chains, service companies. It handles concurrency, transactions, complex queries. Why would I downgrade to SQLite?

Jon: Because you're not building a factory system anymore. You're building a desktop app for one person. PostgreSQL is a server. Where's the server going to run?

Vlad: On the user's machine?

Jon: Then you're asking freelancers to install PostgreSQL, configure it, manage backups, handle updates. That's not a product. That's a sysadmin job. I wouldn't install it myself, and I'm a backend engineer. Imagine an accountant from Zhytomyr who last saw a command line in 2009.

Vlad: Fair point. But SQLite — isn't it just for prototypes?

Jon: No. SQLite is in every phone, every browser, every operating system. It's the most deployed database in the world. Apple uses it. Google uses it. Every Android phone has dozens of SQLite databases. It's not a toy. It's a tool. I lost three days in 2019 trying to set up PostgreSQL replication for a startup that shut down a month later. SQLite would have saved those days.

Vlad: What about concurrency? Multiple users?

Jon: Your app has one user. The freelancer. They're not sharing the database with 50 colleagues. SQLite handles single-user workloads perfectly. For multi-user, you'd need a server — which brings us back to PostgreSQL. But you don't want a server.

Vlad: Right. Offline-first. No cloud. No server.

Jon: Exactly. SQLite is the database for offline-first apps. I'd say — for people who are tired of Docker Compose.

The Technical Comparison

Let's get specific. Here's how SQLite and PostgreSQL compare for LockMargin's use case:

For LockMargin, SQLite wins on every dimension that matters.

The Benchmarks

I ran some tests. Here are the results for typical invoicing workloads:

Test Setup

• Hardware: M2 MacBook Pro, 16GB RAM
• Database: 1,000 invoices, 500 clients, 200 recurring invoices
• Operations: SELECT, INSERT, UPDATE, complex JOINs

Results

Why SQLite is faster: No network overhead. No server process. No connection pooling. The database is in the same process as the app. I checked three times because I didn't believe it. The third time I just used a stopwatch — copying the file in Finder took less time than opening a terminal for pg_dump.

Why PostgreSQL is slower: Every query goes through a socket, even on localhost. Connection setup takes time. MVCC adds overhead. It's like calling your neighbor through long-distance.

The Trade-offs I Accepted

Choosing SQLite means accepting some limitations:

Trade-off 1: Single-writer concurrency

Problem: Only one process can write to the database at a time.
Reality: LockMargin is a single-user app. The freelancer is the only writer. This is not a limitation — it's the correct model. I once tried opening the database from two windows simultaneously. Got an error. Thought: "Why would I need two windows?" Closed the second one. Problem gone.

Trade-off 2: No built-in replication

Problem: Can't replicate the database to another server.
Reality: Backup is a file copy. Sync is a file copy. Replication isn't needed. I copy lockmargin.db to a USB stick once a week. That's my "replication." Been doing it since 2012, when I saved tax returns the same way.

Trade-off 3: Limited data types

Problem: No JSONB, no arrays, no custom types.
Reality: Invoicing data is relational. Clients, invoices, line items, payments. SQLite's type system is sufficient. I spent three days wondering if I needed JSONB for settings. Then I just made a separate table. Works fine.

Trade-off 4: No stored procedures

Problem: Can't write complex logic in the database.
Reality: Business logic belongs in the app, not the database. This is a feature, not a bug. I've seen what happens when logic is smeared between SQL and code. Don't want to repeat that.

The Migration Story (What If We Switched?)

Vlad: What if we needed to switch to PostgreSQL later? Could we migrate?

Jon: Yes. SQLite to PostgreSQL migration is straightforward. The SQL is 95% compatible. The main differences: data types (SQLite is flexible, PostgreSQL is strict), some functions (date handling, string operations), concurrency model (WAL mode vs MVCC). I did one myself in 2021. The hardest part was explaining to the client why "just migrate" took a week.

Vlad: How long would migration take?

Jon: For LockMargin's schema? Maybe 2-3 days. Most of the work is testing, not rewriting. Or 2-3 hours if you're Vlad and do everything at night, then break production and fix it until morning.

Vlad: So we're not locked in.

Jon: No. SQLite is a starting point, not a prison. If you outgrow it, you can migrate. But for 99% of freelance apps, you won't outgrow it. I know. I counted.

The Real Reason

Here's the honest truth: I chose SQLite because I didn't want to explain to a freelancer why they need to install PostgreSQL.

Imagine the onboarding:

1. Download LockMargin ✓
2. Install PostgreSQL ✗
3. Configure pg_hba.conf ✗
4. Create a database ✗
5. Set up backups ✗
6. Handle updates ✗

That's not a product. That's a barrier to entry. I'd close the tab at step two.

With SQLite:

1. Download LockMargin ✓
2. Open it ✓
3. Your data is in a single file on your desktop ✓

That's a product. I opened lockmargin.db in DB Browser to check. A file. 2.4 megabytes. Everything I created in a month. Copied to a flash drive in 3 seconds. That's my backup strategy.

When PostgreSQL Is the Right Choice

I'm not saying SQLite is always better. PostgreSQL is the right choice when:

• Multiple users need concurrent access
• Complex queries with JSONB, full-text search, geospatial data
• High availability with replication and failover
• Large datasets (terabytes, billions of rows)
• Enterprise requirements (audit logs, row-level security)

For those use cases, PostgreSQL is unbeatable. But for a freelancer managing their own invoices? SQLite is the correct tool. I spent 20 years proving the opposite to clients. Now I'm proving it to myself.

The Lesson

Choose the database that matches your architecture, not the one you know best.

I knew PostgreSQL. I loved PostgreSQL. But PostgreSQL is a server database, and LockMargin is a desktop app. The architecture demanded SQLite.

If you're building:

• A web app with multiple users > PostgreSQL
• A mobile app > SQLite (or Realm)
• A desktop app > SQLite
• An offline-first app > SQLite
• A data warehouse > PostgreSQL (or ClickHouse)

Match the tool to the job. I spent a week convincing myself PostgreSQL "would work too." Spent two more days on a Docker container. Then deleted everything and ran cargo add rusqlite. Works.

What's Next

This is post #2 in the Building LockMargin series. Next up: the first time I dogfooded my own product, and what broke. Spoiler: I broke PDF export at 2 AM before a deadline. But that's another story.

Follow the series at Building LockMargin.

Frequently Asked Questions

Is SQLite production-ready?

Yes. SQLite is used by Apple, Google, Microsoft, and every major browser. It's the most deployed database in the world. It's not just production-ready — it's production-proven. I don't trust "proven" in marketing, but here — facts. Billions of devices.

Can SQLite handle large datasets?

SQLite can handle databases up to 281 TB. For invoicing, you'll never hit that limit. Most freelancers have thousands of invoices, not millions. I checked: my database after 4 months is 2.4 MB. After 10 years — maybe 60 MB. Smaller than one phone photo.

What about data corruption?

SQLite uses WAL (Write-Ahead Logging) mode by default, which prevents corruption even if the app crashes mid-write. I've never lost data to corruption in 6 months of daily use. I once dropped my laptop. The database was fine. The laptop — cracked. Priorities.

Can I use SQLite with Rust?

Yes. The rusqlite crate is excellent. It's safe, fast, and well-maintained. LockMargin uses rusqlite for all database operations. I tried sqlx. Then Diesel. Went back to rusqlite. Just works.

What if I need to sync across devices?

SQLite doesn't have built-in sync. For LockMargin, sync is a file copy (USB, cloud storage, etc.). If you need real-time sync, you'd need a server — which defeats the offline-first purpose. I copy the file. Not perfect. But works. Like everything else in this post.

P.S. — Jon agreed to stay anonymous. But he's real. We had coffee at a cafe in Berlin in 2019. He said then: "All scaling problems are solved by not scaling." I didn't understand then. I did in December.

The Bottom Line

SQLite isn't a compromise. It's the correct choice for offline-first desktop apps.

PostgreSQL is a server database. It's powerful, flexible, and enterprise-grade. But it requires a server, administration, and maintenance. For a freelancer who just wants to send invoices, that's overkill.

SQLite is embedded, zero-config, single-file, and fast. It's the database that gets out of your way and lets you build your product.

I chose SQLite because I wanted to build a tool, not a sysadmin job. And six months later, I'm glad I did.

I build LockMargin - a local Financial OS for freelancers. No cloud, no subscription, no account. $49 once.
Canonical: https://lockmargin.com/blog/why-i-chose-sqlite-over-postgresql.html