I've been developing a SaaS app and originally intended to use PostgreSQL as I usually do, but lately, I've been looking into SQLite. I see some companies using SQLite in production with great success, which is intriguing. The big draws for me are its simpler deployment—there's no separate database server to manage, and lower latency due to everything being in one place. It seems like a solid option for a small to medium app that doesn't require extensive horizontal scaling.
However, I have my concerns: what happens when I need to scale beyond one server? How will I manage backups and replication? Am I overlooking potential issues that might arise later on?
Has anyone successfully launched a production web app with SQLite? What was your experience like? Would you recommend it, or do you think PostgreSQL is still the safer option for something that needs to be reliable and scalable?
5 Answers
I've used SQLite in a successful deployment with thousands of installations, and it was fast and efficient. But keep in mind, it doesn't scale beyond one server. When scaling, be cautious; developing generic code to ensure database portability takes experience. Don't lock yourself into SQLite if you foresee significant growth—it may complicate future migrations.
I think SQLite is great for small-scale apps, especially if your user base is under 500 daily users. It's robust when it comes to small projects, but if you ever plan to scale your app to multiple servers or handle heavier traffic, you'll run into issues. Scaling SQLite across servers requires a lot of work and may lead to performance problems due to its design. If you switch to a real database system like PostgreSQL or MySQL later, expect to rewrite a significant portion of your code. They are also relatively easy to set up and manage once you're familiar with them.
SQLite has its niche; it's fantastic for lightweight, single-writer scenarios. However, it's definitely not general-purpose. If you can guarantee that your app won't need to scale massively later, it's a viable choice. Otherwise, starting with a more robust system like PostgreSQL saves a lot of headaches down the line. Just consider your use case carefully!
SQLite can work for production, especially for lightweight applications. However, it's not meant for larger, more complex systems. For backups, I frequently snapshot the database, treating it like a normal file system object, because it's true that corruption can happen. My recommendation? If you plan on growth, consider starting with PostgreSQL; it gives you more room to expand later.
In my experience, SQLite is perfect for small projects or applications that don’t require heavy write operations. If you're just reading data, it’s fine, but for anything else, especially SaaS, it might be worth just sticking with PostgreSQL from the start. The overhead of switching later isn't worth the hassle for most cases.
I agree! Transitioning from one database to another can be challenging if you're not careful. If you keep your SQL code simple and parameterized, it helps a lot during migration. Just be ready for data synchronization headaches.