Re: Metadata performance - Mailing list pgsql-jdbc

Hello,

 

I am writing code that gets the metadata for a database. I use DatabaseMetaData to get tables, their columns, indices, PKs and FKs. I have instrumented my code and get the following metrics:

 

Schemas:    19 in    26ms or  0.3%

Tables :   280 in     3ms or  0.0%

Columns: 5,638 in   922ms or 10.8%

PK     :   280 in   630ms or  7.4%

FK-Out :   351 in 3,049ms or 35.8%

FK-In  :   353 in 2,735ms or 32.1%

Indices:   768 in 1,141ms or 13.4%

------------------------------------

Total: 8,509ms

 

I am on Postgres 12.3 with driver 42.2.18.

 

My questions are:

• Are the methods DatabaseMetaData.getImportedKeys and getExportedKeys expected to take so long?
• If so, is there another method to get that information that would perform much better?
• DatabaseMetaData.getIndexInfo also feels like it should perform better

 

This utility I am working on is run often during development time. It takes a total of 9s and change, and out of that, 8.5s is spent just on fetching the meta-data 😊

 

I have tried multithreading this by distributing table meta-data fetching across multiple connections (I know the driver would block if multiple threads were to use a single connection), and to my surprise, seems that the driver also serializes all the requests across multiple connections: the meta-data APIs took the same amount of time over a single connection, or distributed over 4.

 

Any help or pointers are much appreciated.

 

Thank you,

Laurent.