SQLite vs PostgreSQL

Choosing the Right Database for Your Project

I’ve been studying the SQLite vs PostgreSQL argument for a while now and have come to some conclusions. This article should help you pick between the two.

1. SQLite can handle more than you realize: There are things you have to do to optimize it (like enable WAL), but they’re easy to do and for most software projects, SQLite is more than sufficient. I’ve run my own benchmarks to test QPS (queries per second) and I’ve been surprised by the results. I also like this and this video about it.

2. Zero Latency: SQLite’s file-based architecture results in virtually zero latency between the application and database. This allows for hundreds of queries per HTTP request without significant performance penalties. Read more here.

3. Simplified Backup Process: Contrary to popular belief, backing up SQLite databases is straightforward. I mean… it’s just a file. Just upload it to wherever you want to store your backups. There are better ways out there like LiteStream, but your backup strategy can truly be as simple as “upload this file to S3 every 3 minutes”.

4. Ideal for Self-Hosted Applications: SQLite shines in self-hosted environments because no db management is needed. You don’t need to expect your users to start a db, configure the db URL and so on.

5. Trade-off: Materialized Views: One drawback of SQLite is the lack of built-in materialized views. Materialized views are genuinely awesome. You can implement similar functionality at the application level, it requires more effort compared to PostgreSQL’s native support. I made this library in Go called fakeMV to help with the creation of “fake” / “manual” materialized views, but it’s limited compared to real materialized views.

6. Trade-off: Replication sucks: You lose out on easy replication. Not for scale, you probably don’t need that much scale, but for geographical placement. It’s very easy to replicate a Postgres db so you get lower latency across continents.

7. Management Overhead: While SQLite does save you management overhead, it’s really not that hard to spin up a Postgres db either.

Conclusion

Choose SQLite if:

• You’re building self-hosted applications
• Low db<->app latency and high query performance are crucial
• Simplicity and easy deployment are priorities

Opt for PostgreSQL if:

• You need geographical distribution to reduce db<->app<->user latency
• (Good) Materialized views are a requirement
• You’re comfortable with a slight increase in management complexity

Ultimately, if neither of these factors matter for your project, choose the database you’re personally like more.

Both SQLite and PostgreSQL are great options.

P.S.: Why did I say “good” materialized views? Materialized views do not exist natively for SQLite but I’ve made a library to help with the creation of “fake” / “manual” materialized views.