Hibernate Slow to Acquire Postgres Connection
I fixed it =) I really had to root around to find the answer for this one. Basically, it comes down to loading metadata and the JDBC driver. It's loading ALL META DATA, including comments beside sql columns and other various constructs, which are not needed for operation. I don't know why this is on by default but you should definitely turn this feature off unless you explicitly need it:
config.setProperty("hibernate.temp.use_jdbc_metadata_defaults","false");Instant connection now!
The only info I could find on this is in code:
107 // 'hibernate.temp.use_jdbc_metadata_defaults' is a temporary magic value.108 // The need for it is intended to be alleviated with future development, thus it is109 // not defined as an Environment constant...110 //111 // it is used to control whether we should consult the JDBC metadata to determine112 // certain Settings default values; it is useful to *not* do this when the database113 // may not be available (mainly in tools usage).114 boolean useJdbcMetadata = ConfigurationHelper.getBoolean( "hibernate.temp.use_jdbc_metadata_defaults", configValues, true );
I had to also enable hibernate.jdbc.use_get_generated_keys otherwise Identity generation strategy was throwing an exception. Before it was enabled automatically based on metadata received from DB. So my whole solution was to add the following two lines to persistence.xml:
<property name="hibernate.jdbc.use_get_generated_keys" value="true" /><property name="hibernate.temp.use_jdbc_metadata_defaults" value="false" />
Almost 7 years later and this is still an issue...
Turning the "temp" key off helps but it's a nuisance to enable properties one-by-one that would be auto-configured (see other answers).
In case of Oracle and Teradata I didn't notice such a slowdown so I digged deep and found where the slowest part is:https://github.com/pgjdbc/pgjdbc/blob/master/pgjdbc/src/main/java/org/postgresql/jdbc/PgDatabaseMetaData.java#L2237
This resultSet has in my case 372 rows (types). Then, for each driver fires couple of calls from https://github.com/pgjdbc/pgjdbc/blob/master/pgjdbc/src/main/java/org/postgresql/jdbc/TypeInfoCache.java#L204 which results in tons of SQL statements against the DB.
No idea how to speed this up right now, luckily it's needed once and then it's cached.