It's about tradeoff, sometimes those limitations doesn't really matter that much, sometimes they are. The point is not to settle on a superior option so we never need to think the again but to understand the difference and choose accordingly.

Or at least that's how I view it. Whenever I think about using SQLite, I make sure I read these documents to see if I am fine with the limitations.

https://sqlite.org/whentouse.html

https://sqlite.org/quirks.html