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.