Every new project starts with this question. Here’s how to actually decide.

The flowchart

Is this a side project or MVP?
├── Yes → SQLite (ship faster)
└── No ↓

Will multiple servers write to the database simultaneously?
├── Yes → PostgreSQL (SQLite doesn't handle concurrent writes)
└── No ↓

Do you need full-text search, JSONB, or PostGIS?
├── Yes → PostgreSQL (SQLite's extensions are limited)
└── No ↓

Will your database exceed 10GB?
├── Yes → PostgreSQL (SQLite works but gets unwieldy)
└── No ↓

Do you want zero infrastructure?
├── Yes → SQLite (it's just a file)
└── No → Either works. Pick based on team familiarity.

When SQLite is the right choice

Side projects and MVPs. Zero setup. No server to manage. Your database is a single file. Deploy anywhere.

Embedded applications. Mobile apps, desktop apps, CLI tools, IoT devices. SQLite was literally designed for this.

Read-heavy workloads. SQLite handles 100,000+ reads per second. If your app mostly reads data, SQLite is incredibly fast because there’s no network overhead.

Single-server deployments. If your app runs on one server (Fly.io, Railway, a VPS), SQLite with WAL mode handles most workloads beautifully.

Development and testing. Even if you use PostgreSQL in production, SQLite for local dev and tests is fast and simple.

When PostgreSQL is the right choice

Multiple application servers. The moment you have 2+ servers writing to the same database, you need a database server. SQLite is a file — it can’t handle concurrent writes from different machines.

Complex queries. PostgreSQL’s query planner is more sophisticated. Window functions, CTEs, recursive queries, and advanced joins are better optimized.

Extensions. PostGIS for geospatial, pgvector for AI embeddings, pg_cron for scheduled jobs, pg_stat_statements for query monitoring. SQLite can’t match this ecosystem.

Managed services. Supabase, Neon, RDS — you get backups, monitoring, and scaling without managing a server. There’s no equivalent managed SQLite (Turso is close but different).

Strict data integrity. PostgreSQL’s constraint system is more robust. CHECK constraints, exclusion constraints, and deferred foreign keys give you guarantees SQLite can’t.

The hybrid approach

Some teams use both:

• SQLite for local development (fast, no Docker needed)
• PostgreSQL for production (scalable, managed)

This works if you stick to standard SQL. ORMs like Prisma and Drizzle abstract the differences.

Common mistakes

Using PostgreSQL for a blog. Your blog has 200 posts and 10 readers. SQLite is perfect. You don’t need a database server.

Using SQLite for a SaaS with multiple dynos. Heroku, Render, and similar platforms run multiple instances. SQLite can’t share a file across instances.

Choosing based on what you’ll “eventually” need. You don’t have concurrent writes today. You don’t have 10GB of data today. Use SQLite today, migrate when you actually need PostgreSQL.

The migration path

If you start with SQLite and outgrow it:

1. Export with sqlite3 mydb.db .dump > dump.sql
2. Adjust syntax differences (few if you used standard SQL)
3. Import into PostgreSQL
4. Update your connection string

With an ORM, it’s even simpler — change the database URL and run migrations.

The honest answer

If you’re asking “PostgreSQL or SQLite?” the answer is probably SQLite. You’d know if you needed PostgreSQL — the requirements would make it obvious (multiple servers, complex queries, specific extensions).

Start simple. Migrate when you have a reason, not when you have a fear.

Related: PostgreSQL Cheat Sheet