If you run a single application, or a few instances of the same application, you don't need an external pool and most frameworks have an internal connection pool anyway.

Not sure if I'm missing anything here, but if I want case-insensitive search I simply create an index on lower(column) and use that to query.

VACUUM is something you need to pay attention to at scale. And at that point you need to know your DB anyway and tune it. For smaller applications (and I don't mean only toy applications) it usually isn't an issue.

>if I want case-insensitive search I simply create an index on lower(column) and use that to query

Or even pg_trgm trigram indexes, which are case-insensitive by default and support similarity search to accept typos and misspellings.