Home
/
Blog
/
Blog article

7/8/2026

PostgreSQL vs SQLite for Side Projects — Here's My Honest Take

Blog featured image: PostgreSQL vs SQLite for Side Projects — Here's My Honest Take

Every time I start a new side project, I hit the same question: which database do I grab? And I go back and forth every single time.

PostgreSQL is the default choice for anything serious. SQLite is the go-to for anything small. But the middle ground where most side projects live is a gray area that doesn't get talked about enough.

I want to share what I've learned after building a bunch of projects with both. Not the marketing claims, not the benchmark numbers you'll find on a vendor blog. Just what actually works and what doesn't when you're building something solo.

What I Actually Use Each For

Here's my rule of thumb. It took me way too long to land on it.

I use SQLite when the project is something I will run locally or ship as a single binary. A CLI tool. A personal dashboard. A small web app where I am the only user or there are maybe two people using it at the same time.

I use PostgreSQL when there are multiple people hitting the database at once, when I need to run analytical queries during writes, or when the data structure is complex enough that I want proper constraints and triggers.

That sounds simple. But here's why it took me a while to get there.

The Case for SQLite

SQLite gets a bad reputation because people think of it as a "toy" database. It's the opposite. SQLite is the most deployed database engine in the world by a huge margin. Every phone, every browser, every embedded device runs it.

For a side project, the advantages are real.

Zero setup.

No server, no daemon, no config file. You install the library, you open a file, you're done. I've started projects where the database setup took longer than writing the first query. With SQLite, that never happens.

import sqlite3

conn = sqlite3.connect("myproject.db")
conn.execute("CREATE TABLE IF NOT EXISTS todos (id INTEGER PRIMARY KEY, title TEXT)")

That's it. The database file exists. You can copy it, back it up, email it to yourself. Try that with PostgreSQL.

Shipping is trivial.

When I build a tool I want others to run, SQLite makes my life easy. The user doesn't need to install PostgreSQL, create a database, set up users, or mess with pg_hba.conf. They just run the binary and everything works.

Performance for single-user loads.

SQLite can be extremely fast for read-heavy and single-writer workloads. I have a personal analytics dashboard that queries a SQLite database with about 2 million rows. Queries come back in milliseconds. WAL mode makes reads and writes happen concurrently without blocking each other.

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;

Those two lines alone make SQLite feel like a completely different database for concurrent access.

Full text search is built in.

FTS5 is part of SQLite. You don't need a separate Elasticsearch instance or a Postgres extension. You create a virtual table and search with MATCH.

CREATE VIRTUAL TABLE posts_fts USING fts5(title, content);

SELECT * FROM posts_fts WHERE posts_fts MATCH 'search term';

For a personal blog or a note-taking app, this is all you need.

The Case for PostgreSQL

PostgreSQL is what you reach for when SQLite starts to pinch. And it will pinch eventually if the project outgrows its original scope.

Concurrency is real.

SQLite handles multiple readers fine, but multiple writers is a problem. Only one write transaction can happen at a time, and if you have a busy web app, that becomes a bottleneck. PostgreSQL handles dozens of concurrent writers without breaking a sweat. It's one of those things that doesn't matter until it does, and then it matters a lot.

JSONB is genuinely useful.

I know putting JSON in a relational database feels wrong to some people. But for side projects, it's incredibly practical. You can have a clean relational schema for your core tables and use JSONB for things that don't deserve their own table yet.

CREATE TABLE projects (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    config JSONB DEFAULT '{}'
);

-- Query inside JSON
SELECT * FROM projects WHERE config->>'theme' = 'dark';

-- Index on JSON paths
CREATE INDEX idx_projects_theme ON projects ((config->>'theme'));

Extensions unlock capabilities you didn't plan for.

pgvector for embeddings. PostGIS for geospatial queries. pg_cron for scheduled jobs. I've started side projects with a simple schema and later added vector search or cron jobs without changing my database. PostgreSQL grows with you.

CREATE EXTENSION vector;

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    content TEXT,
    embedding VECTOR(1536)
);

SELECT content FROM documents
ORDER BY embedding <=> '[0.1, 0.2, ...]'
LIMIT 5;

Proper constraints prevent data rot.

CHECK constraints, exclusion constraints, deferrable constraints. These sound boring until you've had a side project silently accumulate bad data for six months and you spend a weekend cleaning it up. PostgreSQL's constraint system is the best in class.

One Honest Thing

Here's the part that's hard to admit.

Most side projects do not grow. They do not get 10,000 users. They do not need to scale. And for the ones that don't, you are paying a real cost for using PostgreSQL.

The cost is not money. PostgreSQL is free. The cost is friction. Every time you need to set up PostgreSQL for a new project, you spend time on infrastructure that could have been spent on the actual product. Every time you deploy, you need a running PostgreSQL instance somewhere. Every time you backup, you think about pg_dump schedules and WAL archiving instead of just copying a file.

SQLite removes all of that friction. And for 90% of the side projects I've built, SQLite was sufficient for the entire lifetime of the project.

The projects that outgrew SQLite? I knew they would from the start. They had multiple users hitting the database simultaneously from day one. They needed concurrent writes. They were never going to stay on SQLite.

The trap is using PostgreSQL for a project that doesn't need it. You add complexity without getting anything back.

The Catch

That said, SQLite has real limitations that will bite you at the worst time.

ALTER TABLE is weak.

You can add columns. You cannot drop or reorder them. You cannot change a column's type or constraints. For a project where the schema evolves rapidly, this is painful. The workaround works but it's annoying.

-- SQLite won't let you do this
ALTER TABLE users DROP COLUMN deprecated_field;

-- You have to do this
CREATE TABLE users_new (...);
INSERT INTO users_new SELECT ... FROM users;
DROP TABLE users;
ALTER TABLE users_new RENAME TO users;

No user management.

SQLite has no user system, no role system, no row-level security. If your app needs multiple users with different permissions, you are implementing that yourself in application code. PostgreSQL gives you this out of the box.

You cannot safely run on a network filesystem.

SQLite's documentation explicitly warns against storing the database file on NFS or SMB. If your deployment involves a shared filesystem, you need PostgreSQL.

The Decision Framework

After all this, here is my actual decision process when starting a project.

Use SQLite if:

  • You are the only user or there are very few concurrent users
  • You want to ship the app as a single binary or container
  • Zero setup matters more than future scalability
  • The data is simple and relational
  • You want backups to be as simple as copying a file

Use PostgreSQL if:

  • Multiple users will read and write simultaneously
  • You need JSON queries, vector search, or GIS features
  • Schema evolution will be aggressive and you need full DDL support
  • You are deploying to a platform that provides PostgreSQL (Railway, Supabase, Render)
  • The project involves sensitive data and you want proper access control

What I actually do now:

I start new side projects with SQLite. Every single time. If the project grows and SQLite becomes the bottleneck, I can migrate to PostgreSQL. It is not as hard as people think. And most of the time, I never need to.

The projects that fail do not die because of the database. They die because nobody wanted them. Spending time setting up PostgreSQL for a project that will never have users is optimizing for the wrong thing.

Build the thing first. Choose the database that gets out of your way. If the problem of "which database" is the hardest part of your day, you are not building hard enough things.

And that is the real takeaway here.