db-connections (application architecture)

So, I am working on some system designs for a web application, and I wonder
if there is any definitive answer on how to best connect to a postgres
database.

I could have it so that each time a query, or set of queries, for a
particular request, needs to be run, a new connection is opened, queries
are run, and then connection is closed / dropped.

OR, I could create a persistent connection that will remain open as long as
a user is logged in and then any queries are run against the open
connection.

I can see how, for only a few (hundreds to thousands) of users, the latter
might make more sense but if I need to scale up to millions, I might not
want all of those connections open.

Any idea of how much time / overhead is added by opening and closing a
connection everytime?

Any and all information is welcome.

Thanks in advance

-- Mark M