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.
