I'm trying to understand connectionn pooling in application.
Supposing we are using a postgresql database with max_connections=100 what should be the best value for connection pool size in my java (or another language) application ?
Does it make sens to use java jdbc connection pool size higher than max_connections?
Is it a good idea to close (release) a connection just after the query is executed? or it is better to let connection opened (so that we can reuse it)?
Your maximum connection pool size should be lower than the max_connections configuration (and if your application is run on multiple nodes, take into account the total size over all nodes!). You don't want to run the risk that an administrator or maintenance tool can't connect to the database because your application has all connections in use.
The configuration definitely should not be higher than max_connections, because that would just lead to errors if the connection pool tries to allocate connections beyond the server maximum.
In fact, depending on your application needs, you can probably get away with a very small connection pool (maybe 5 or 10 connections), and keep in mind that more connections isn't always better. You need to test what makes sense for your application, because we can't just say "this is the best configuration in all situations".
As to your second question: Closing a connection in a JDBC-compliant connection pool is what returns the connection to the connection pool for reuse. So you should close the connections and not hold it open for too long. Not closing the connection or keeping a connection open for too long, will starve the pool, meaning it will need to open more connections (or exhaust the pool) to have sufficient connections for your application.
A connection pool creates physical connections and puts them in the pool. When your application code requests a connection from the pool, the connection pool 'checks out' a physical connection, wraps it in a logical connection and returns the logical connection to your application code.
When your application code closes that logical connection, it signals to the connection pool that the physical connection is available for reuse. The pool will then either return the physical connection to the pool, or possibly close it because the pool already has too many idle connections, or the connection is too old, or other validation reasons. After closing, the logical connection and derived objects like statements and result sets behave (or should behave) as closed (ie: it can no longer be used).
Your code should close the connection as soon as a unit-of-work is finished, and a unit-of-work should be as small as possible, but not so small that it leads to broken behaviour (eg you shouldn't commit work and close the connection in the middle of something, while the expected application behaviour requires that subsequent work should be in the same transaction).