> Postgres ... is the right choice when you need higher availability, broader shared scalability, or other deployment properties that are better served by a network database. It is also the better fit when asynchronous replication to object storage is not the durability model you want... Many workflow systems do not need that on day one and should not start with more infrastructure than their state actually demands.
------
I see this kind of YAGNI thinking a lot, but in my view, it must be balanced against the effort you'd put into resolving any edge cases and adapting current architecture to your use case.
Imagine you deploy Sqlite, and thought it fine by itself, you keep running into some unforeseen challenges with the use to which you are putting. YOu'd need to sink valuable time and effort into addressing those. Then, when you have outgrown it, you'd beed to spend additional valuable times dping the same with Postgres.
This is why, when it comes to Architecture, I increasingly find my myself over-enigneering a bit. Assuming there is a good chance you might need to upgrade your architecture in the not too distant future, that approach is actually kind of very efficient. I find that I am able to uncover a lot of potential gotchas, which feeds back into the what the simplified current architecture should be, and helps me understand the roadmap I'm facing very well. I also avoid wasting too much time going too deep in directions that make sense now, but need a lot of plumbing to get right, when I can see that I'd likely have to throw it all out in a few years. Going from A -> B -C -> D, where each step is the optimal good-enough-for-now architecture but which requires a lot of work to stabilize and iron out the kinks of, is much less efficient than exploring D well enough to know whether you should build A, B, or C now.
Basically, some over-engineering, if done right, is not wasted. It cuts right to the heart of what you are dealing with, efficiently, and allows you to make (maybe) simpler but informed choices now as to how best to allocate your development resources now.
My version of this is the “N+1” principle. Build for one more foreseeable use case than you currently have. The domain model will click in when you need to generalize a solution, and you’ll gain the ability to see your particular solution as one of several to the problem, and thus evaluate fit and tradeoffs more clearly.
Don’t do N+2. The goal isn’t to predict the future, nobody can do that. The goal is a durable understanding of the domain and the best fit implementation you can get with that current understanding and resources.
That said, SQLite passes that bar for me in most use cases.