I haven't used SQLite in production, but I've used a single installation of Postgres for some surprisingly high traffic websites, and it works amazingly well with essentially no tweaking. I agree that the vast majority of people using anything more complex very likely don't need to be.