Quick Answer
I chose SQLite over PostgreSQL because LockMargin is a desktop app that runs on the user's machine — not a server. SQLite is embedded, zero-config, single-file, and requires no administration. PostgreSQL is a server database that needs installation, configuration, and maintenance. For an offline-first invoicing tool used by freelancers, SQLite is the only sane choice. PostgreSQL would add $5-50/month in hosting costs and a dozen points of failure.
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:
- SQLite — embedded, single-file, zero-config
- PostgreSQL — server-based, multi-user, enterprise-grade
I'd spent 20 years building systems with PostgreSQL. I knew it well. I loved it. But I chose SQLite. Here's why.
The Conversation
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?
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?
On the user's machine?
Then you're asking freelancers to install PostgreSQL, configure it, manage backups, handle updates. That's not a product. That's a sysadmin job.
Fair point. But SQLite — isn't it just for prototypes?
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.
What about concurrency? Multiple users?
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.
Right. Offline-first. No cloud. No server.
Exactly. SQLite is the database for offline-first apps.
The Technical Comparison
Let's get specific. Here's how SQLite and PostgreSQL compare for LockMargin's use case:
| Dimension | SQLite | PostgreSQL |
|---|---|---|
| Installation | Zero (embedded) | Required (server) |
| Configuration | None | Complex (pg_hba.conf, postgresql.conf) |
| Administration | None | DBA needed for production |
| Storage | Single file (.db) | Multiple files, tablespaces |
| Backup | Copy the file | pg_dump, WAL archiving |
| Concurrency | Single-writer, multi-reader | Multi-writer, MVCC |
| Scalability | Limited to one machine | Unlimited (clustering, replication) |
| Cost | $0 | $0 (self-hosted) or $5-50/month (managed) |
| Performance (single-user) | Faster (no network) | Slower (network overhead) |
| Performance (multi-user) | Limited | Excellent |
| Offline support | 100% | Requires server connection |
| Data portability | Copy the file | Export/import required |
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
| Operation | SQLite | PostgreSQL | Winner |
|---|---|---|---|
| Simple SELECT (1 row) | 0.3ms | 1.2ms | SQLite (4x faster) |
| Complex JOIN (5 tables) | 2.1ms | 3.8ms | SQLite (1.8x faster) |
| INSERT (1 row) | 0.5ms | 2.1ms | SQLite (4x faster) |
| UPDATE (1 row) | 0.4ms | 1.9ms | SQLite (4.7x faster) |
| Backup (full database) | 5ms (copy file) | 2.3s (pg_dump) | SQLite (460x faster) |
| Restore | 5ms (copy file) | 1.8s (psql) | SQLite (360x faster) |
Why SQLite is faster: No network overhead. No server process. No connection pooling. The database is in the same process as the app.
Why PostgreSQL is slower: Every query goes through a socket, even on localhost. Connection setup takes time. MVCC adds overhead.
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.
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. No replication needed.
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.
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.
The Migration Story (What If We Switched?)
What if we needed to switch to PostgreSQL later? Could we migrate?
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)
How long would migration take?
For LockMargin's schema? Maybe 2-3 days. Most of the work is testing, not rewriting.
So we're not locked in.
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.
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:
- Download LockMargin ✓
- Install PostgreSQL ✗
- Configure pg_hba.conf ✗
- Create a database ✗
- Set up backups ✗
- Handle updates ✗
That's not a product. That's a barrier to entry.
With SQLite:
- Download LockMargin ✓
- Open it ✓
- Your data is in a single file on your desktop ✓
That's a product.
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.
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.
What's Next
This is post #2 in the Building LockMargin series. Next, I'll write about the first time I dogfooded my own product — and what broke.
Follow the entire 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.
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.
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.
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.
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.
