From SQLite to PostgreSQL: Lessons From a Greenfield Project

When starting a new greenfield project, choosing the right technology stack is one of the most consequential decisions you make. Cost, operational overhead, performance, and long‑term maintainability all play a role.

For my latest project, I initially embraced the idea of using SQLite as the primary database. The appeal was obvious: zero‑cost hosting, portability, simplicity, and the growing “local‑first” movement that champions SQLite in production.

But as the project matured, I ran into practical limitations that ultimately pushed me toward PostgreSQL—while still keeping SQLite in the stack for caching.

This article outlines the challenges I encountered, why I migrated, and how SQLite still plays a valuable role in my architecture.

Why SQLite Looked Like the Perfect Fit

Several factors made SQLite attractive:

  • Cost efficiency — no managed database fees, no extra infrastructure.
  • Operational simplicity — backups are just file copies; deployments are trivial.
  • Portability — the database travels with the application.
  • Growing industry confidence — frameworks like Rails 7/8 and the local‑first community have been advocating SQLite in production.

References:

Despite these strengths, real-world constraints surfaced. Some of the constraints I highlighted below may have been solved in SQLite so my points may no longer be valid and require review.

Challenges I Encountered With SQLite

1. Limited JSON Query Capabilities

Coming from PostgreSQL’s powerful JSONB ecosystem, I underestimated how limited SQLite’s JSON functions are.

SQLite supports JSON storage, but:

  • Querying nested JSON is clunky.
  • SQLAlchemy integration is less ergonomic.
  • Many operations must be done in Python instead of the database.

For a project that relied heavily on structured JSON data, this friction added up.

References:

2. Write Concurrency Constraints

SQLite allows many readers but only one writer.

Even though my workload was read-heavy, I still had to think about:

  • Write contention
  • Queuing writes
  • Occasional delays before updated data became visible
  • The risk of corruption if writes weren’t handled carefully

These are solvable problems, but they introduce engineering overhead I didn’t want to spend time on.

Reference:

3. Scaling and Operational Concerns

Running SQLite across multiple nodes requires additional tooling for:

  • Replication
  • Failover
  • Backups
  • Corruption recovery

Tools like Litestream make this possible, but they add another moving part.

Reference:

Given my limited time and desire to focus on product features—not database plumbing—this became a deciding factor.

Migrating to PostgreSQL

Switching to PostgreSQL solved the issues immediately:

  • Full JSONB support
  • Mature tooling for backups, replication, and monitoring
  • Better concurrency
  • Built‑in full‑text search (no extra tables or extensions needed)

The migration was smooth, with only minor code adjustments—mostly around JSON handling and removing SQLite-specific full‑text search hacks.

I opted for a managed PostgreSQL service. Running my own instance would have been possible, but unnecessary for this project’s goals. Managed DBs free me from maintenance and let me focus on building.

But SQLite Didn’t Leave the Stack

Even after migrating the primary database to PostgreSQL, SQLite still plays a crucial role: caching.

Instead of Redis or Memcached—which would consume RAM and add operational cost—I use DiskCache, a Python library that stores cache entries on disk using SQLite under the hood.

Why this works well:

  • SSDs are fast enough for most caching workloads
  • Disk space is cheap
  • Docker volumes make the cache accessible across multiple containers
  • No extra infrastructure or memory footprint

Reference:

This hybrid approach gives me the best of both worlds: PostgreSQL for structured, scalable data storage, and SQLite for cheap, persistent caching.

Final Thoughts

SQLite is a remarkable piece of engineering. For many projects—especially local-first apps, embedded systems, or small deployments—it’s an excellent primary database.

But for my project’s needs:

  • Rich JSON querying
  • Concurrency
  • Operational tooling
  • Future scalability

PostgreSQL was the more pragmatic choice.

Still, SQLite remains in the architecture where it shines: lightweight, zero‑cost caching.

If you’re starting a new project, consider SQLite seriously—but also be honest about your data model, concurrency needs, and operational constraints. The right tool is the one that lets you ship features without unnecessary friction.