3 ms·

Running MySQL/Postgres over SQLite: - needs to be provisioned and configured - needs additional tooling and operational overhead - comes with a _large_ perfo

by dgb23 5y ago

Running MySQL/Postgres over SQLite: - needs to be provisioned and configured - needs additional tooling and operational overhead - comes with a _large_ performance overhead that is only won back if you have quite a significant load - especially writes, which means the vast majority of web projects are slower and require more resources than they should. - it makes the whole system more complex by definition It is a cost-benefit thing that tilts towards RDBMS as soon as you need to sustain very high transactional loads and want a managed, individually accessible server that you can query and interact with while it's running in production. But if it is just "a website that needs durability" then you haven't yet shown how that tradeoff is worth it.

rapind 5y ago
Just wanted to add another’s scenario where postgresql has been useful to me. Functions. There are cases where you have expensive operation(s) that reference a lot of persistent data. Even without massive traffic these operations can be prohibitively expensive in the middleware. Leveraging database functions can be a massive performance improvement (100x + for me), especially if your middleware is slow (e.g. rails). I’ve used SQLite in production once and it worked great. But that was a very simple app. For more complex (but not always higher traffic) I’m leaning more and more on postgresql and less on my middleware, like moving business logic to the database when it makes sense.
- dgb23 5y ago
  If you need the expressiveness and power of Postgres then sure, it has also way better JSON support for example, there is generally better tooling for it as well and so on. But in this case, your database becomes it's own _thing_, has much more value outside of being just durability for an application. Like for example Supabase is doing things. That's a very fundamental design decision IMO. I explored this and it is very attractive and robust, but serves different use-cases.

by dgb23 5y ago

rapind 5y ago

Just wanted to add another’s scenario where postgresql has been useful to me. Functions. There are cases where you have expensive operation(s) that reference a lot of persistent data. Even without massive traffic these operations can be prohibitively expensive in the middleware. Leveraging database functions can be a massive performance improvement (100x + for me), especially if your middleware is slow (e.g. rails). I’ve used SQLite in production once and it worked great. But that was a very simple app. For more complex (but not always higher traffic) I’m leaning more and more on postgresql and less on my middleware, like moving business logic to the database when it makes sense.

dgb23 5y ago
If you need the expressiveness and power of Postgres then sure, it has also way better JSON support for example, there is generally better tooling for it as well and so on. But in this case, your database becomes it's own _thing_, has much more value outside of being just durability for an application. Like for example Supabase is doing things. That's a very fundamental design decision IMO. I explored this and it is very attractive and robust, but serves different use-cases.