I'm working on a website that helps users understand privacy policies by generating summaries using LLMs. Currently, I'm storing data in a map with app names as keys, which I realize isn't ideal. I'd like to transition to a SQL database but I'm uncertain whether to go with SQLite or PostgreSQL. I know SQLite is lightweight and fast, but I've heard PostgreSQL is more robust for handling data traffic. Can anyone guide me on which might be the better choice for my application?
5 Answers
Both databases can handle your needs well; it really depends on your project specifics. You should consider your current data volume and traffic. For example, if you’re only cataloging about 100 apps and have a handful of users daily, SQLite should serve you just fine for now without any major security concerns. Just keep your public and private directories secure.
What are the actual limits on SQLite for production use?
If you're just starting out, SQLite is a solid choice. It's simpler to deploy since it doesn’t require a separate server, and performance is usually fine for small to medium apps. Just remember to add indexes to your tables for optimal querying. Plus, the SQL syntax is quite similar between SQLite and PostgreSQL, so migrating later on won't be too painful if you need to. Just be aware that PostgreSQL has a broader range of features if you decide to scale up later.
I totally agree! But can you elaborate on why ORMs shouldn't be used here?
Sounds good! What if the app grows beyond small-scale, though?
I’d recommend starting with SQLite for your application since it’s easier to set up and you'll likely find it’s more than enough for now. Just make sure to plan for scaling if your usage grows significantly.
If your web app is running all on one machine and you're not expecting massive traffic, SQLite can work perfectly! However, if you anticipate needing congestion support or parallel writes, PostgreSQL is the way to go.
SQLite is great for client-side applications, but if you’re thinking of running a web app with a backend, PostgreSQL might be your best bet. It handles more concurrent connections and is generally better suited for traffic-heavy applications. Just ensure that your deployment is set up to handle your needs as your user base grows.
What makes PostgreSQL better for concurrent usage though?
Gotcha! But does that mean I'd need to use AWS or something similar for PostgreSQL?
How do I know when to make the switch to PostgreSQL if I start with SQLite?