Database With 90k+ Tables
I have a database with over 90k tables, that takes quite a while to index, it makes the whole application pretty much unusable. My solution for now has been to just filter out the tables, but that's annoying.
So what I was hoping was to find a way to use the same index/model for all tables that are the same. Like let's say all tables following this format <tableprefix>_products uses the same index/model.
Alternatively any other tips would also be appreciated. :)
Please sign in to leave a comment.
Hello,
To clarify, are you referring to introspection or indexing? How long does the process take in your case?
While there is no specific option for this, let's see what we can improve.
If you're referring to introspection (the process of getting the metadata of the database, such as object names, types of columns, and source code), 90k tables are quite a large number. Generally, the more tables there are, the longer introspection will take.
You mentioned filtering out tables - you can configure a Schema pattern for your data source to select only the relevant databases and schemas. This should help speed up both filtering and introspection. For more information, check out the Schema pattern section in the Schemas article.
What is your DBMS? For example, Oracle users might experience a long introspection time if all the objects are being processed. To reduce the amount of introspected objects, DataGrip has three configurable Introspection levels available. We also plan to introduce introspection levels for additional DBMSs in the future.
Additionally, you might find this article useful: Not All Databases/Schemas Are Displayed by Default. Why?
To help diagnose the issue further and provide some additional recommendations, we would need the logs. Please follow the steps in the Slow introspection article to collect the logs and upload them to https://uploads.jetbrains.com/. Once uploaded, provide the uploadID, so I’ll be able to find the logs.
If it is the Indexing process that is taking too long, we would need the following information:
Upload the logs to https://uploads.jetbrains.com/ and share the uploadID once done.
Introspection is the correct term for what I'm referring to. I agree that 90k tables is a ridiculous amount of tables to keep at once, but since there are thousands of them that are duplicate (same structure, not same content), it would be handy to use the same model for those tables to save up on all the introspection.
We cache the objects' information by running the queries over system tables. When running introspection for the first time, we're retrieving the objects for schemas / databases you have selected. We use our cache for the objects we have retrieved, and for the new updates, a smaller synchronization scope is applied to process less data during the incremental introspection. As mentioned in the previous response, we're expecting faster data retrieval as soon as introspection levels and fragmental introspection are implemeted.
To investigate the performance issue with introspection, please upload the following logs onto our FTP https://uploads.jetbrains.com/ and include the UPLOAD ID in your reply
- database log from Help - Show SQL Log in Files
- introspection log file to benchmark the performance of a schema you have selected(please see the article below)
https://www.jetbrains.com/help/datagrip/reporting-loading-of-database-objects-takes-too-much-time.html
We'll have a look