DataGrip Introspection Engine Architecture: Cache, Catalog Queries, and Incremental Refresh

Requirements: Near‑Instant Completion at Scale

DataGrip must deliver code completion for tables, columns, routines, and types within milliseconds of a keystroke. The engine supports over 30 database dialects — each with its own system catalog layout (PostgreSQL’s pg_catalog, Oracle’s DBA_* views, SQL Server’s sys.objects, MySQL’s information_schema). It handles schemas containing 100,000+ objects, works over high‑latency VPN links, and must not block the UI while fetching metadata. Read‑only connections are common in production environments, so introspection cannot rely on write privileges.

Smallest Suitable Design: Background Pipeline with Persistent Cache

The architecture centers on a per‑connection background pipeline:

1. Dialect‑specific catalog queries — Each dialect ships a set of parameterized SELECT statements against the vendor’s system views. For PostgreSQL, this means querying pg_class, pg_attribute, pg_proc, pg_indexes, and pg_constraint. For SQL Server, it’s sys.objects, sys.columns, sys.indexes, and sys.foreign_keys.
2. Normalized in‑memory model — Results map to a dialect‑agnostic object graph: DbTable, DbColumn, DbRoutine, DbIndex, DbForeignKey. This model powers completion, navigation (Ctrl+B), and refactoring (rename column → update references).
3. Persistent on‑disk cache — A LevelDB/RocksDB store keyed by connection UUID + schema version. Cache files live under ~/.config/JetBrains/DataGrip<version>/introspection/ as .ldb and .sst files. The UI reads exclusively from this cache; live catalog queries never block the editor.
4. Incremental refresh — DDL changes trigger partial updates. Detection mechanisms vary: PostgreSQL uses pg_notify event notifications, SQL Server uses sys.dm_tran_database_transactions polling, Oracle uses DBMS_ALERT. A configurable polling fallback (default 5 s, ide.introspection.poll.interval.ms) covers dialects without push notifications.
5. Schema version tokens — Each dialect defines a lightweight version check: PostgreSQL uses pg_database.datfrozenxid, Oracle uses SCN, SQL Server uses CHANGE_TRACKING_CURRENT_VERSION(). On connect, DataGrip compares the cached token against the live value; a mismatch triggers incremental refresh.

Trust and Data Boundaries

The introspection process runs under the user’s database credentials. It can read any catalog metadata the account can see — table names, column types, constraints, routine definitions, comments. No table data (rows) is ever read during introspection.

Cache files are stored unencrypted in the project/config directory. On shared machines, other OS users can read schema structure (names, types, comments). If this is a concern, encrypt the config directory at the filesystem level or restrict OS‑level access.

Privilege requirements differ by dialect:

• PostgreSQL: SELECT on pg_catalog.* (granted to public by default).
• MySQL: SELECT on information_schema; slow on large schemas due to lack of indexes on catalog tables.
• Oracle: SELECT_CATALOG_ROLE required for DBA_* views; without it, only USER_*/ALL_* views are visible, hiding objects in other schemas.
• SQL Server: VIEW DEFINITION needed to read routine bodies and column encryption metadata; sys.sql_modules requires it.

Metadata never leaves the machine unless explicitly exported (e.g., File → Export → Database Schema).

Operational Checks: Verifying Health and Freshness

Progress Visibility

In the Database tool window, a spinner next to the data source shows introspection progress. The status bar displays object counts ("Tables: 1,234 / Views: 56 / Routines: 78"). Clicking the spinner opens a detail panel with per‑schema breakdown.

Debug Logging

Enable granular logging via Help → Diagnostic Tools → Debug Log Settings and add:

com.intellij.database.introspection

Trigger a refresh (Ctrl+F5 on a schema node). Open Help → Show Log in Files → idea.log. Search for IntrospectorImpl.introspectSchema to see the exact catalog queries executed, their timings, and any errors. Example log line:

2024-01-15 10:23:45,123 [  12345]   INFO - com.intellij.database.introspection - Introspecting schema 'public' on 'postgres-prod': SELECT c.oid, c.relname, c.relkind FROM pg_catalog.pg_class c WHERE c.relnamespace = 2200 AND c.relkind IN ('r','v','m','f','p') ORDER BY c.relname (executed in 42ms)

Cache Inspection

Navigate to the introspection folder (Help → Show Log in Files, then go up one level to introspection/). Each connection has a subdirectory named by UUID containing LevelDB/RocksDB files. A checksum validates cache integrity on load; corruption triggers automatic rebuild on next connect.

Staleness Detection

Press Ctrl+F5 (Refresh) on any schema node to force a version‑token comparison and incremental update. The status bar briefly shows "Checking for changes..." then "Refreshed 3 objects" or "Up to date.".

Failure Modes and Mitigations

Failure Mode	Symptom	Root Cause	Mitigation
Permission denied on system catalogs	Missing objects in tree; completion gaps; "Insufficient privileges" warnings in idea.log	Account lacks SELECT on pg_catalog, DBA_*, or sys.*	Grant minimal catalog privileges; for Oracle, grant SELECT_CATALOG_ROLE; for SQL Server, grant VIEW DEFINITION.
Catalog query timeout	Incomplete cache; spinner hangs then disappears; partial object counts	Default 30s timeout (ide.introspection.query.timeout) exceeded on large schemas or slow links	Increase timeout via Registry (Ctrl+Shift+A → Registry); optimize catalog statistics (e.g., ANALYZE pg_catalog.pg_class on PostgreSQL).
Dialect mismatch	Wrong catalog queries run; missing partition info, materialized views, or identity columns	Driver reports generic dialect (e.g., "PostgreSQL" for Aurora) instead of vendor‑specific variant	Verify driver class in Data Source properties → Driver tab; use vendor‑specific driver (e.g., com.amazon.redshift.jdbc42.Driver for Redshift).
Cache disk full	"Indexing paused" banner; introspection stalls; no new objects appear	No space for LevelDB/RocksDB writes	Free disk space; cache auto‑recovers on next connect once space available.
DDL not detected	New table created in console doesn’t appear in tree until manual refresh	Event notifications unsupported/disabled; polling interval too long or disabled	Enable polling via ide.introspection.poll.interval.ms (default 5000 ms); on high‑latency links, increase to 30000 ms to reduce load.

Conditions That Would Change the Design

Cloud‑Hosted IDE (Remote Agent)

If DataGrip runs in a browser with a remote backend agent, introspection moves to the agent process. The cache becomes distributed (shared across sessions), and authentication shifts from JDBC credentials to short‑lived tokens (IAM, OAuth). The local UI would stream completion candidates via gRPC/WebSocket rather than reading local LevelDB.

Real‑Time Collaborative Editing

Multiple users editing the same SQL file would need metadata changes propagated instantly. Polling is insufficient; the engine would need a CRDT or operational‑transform layer for schema metadata, with the database’s DDL event stream as the source of truth.

Schema Registry Integration

Organizations using Confluent Schema Registry, Apicurio, or Atlas could bypass live catalog queries entirely. Introspection would read Avro/Protobuf/JSON schemas from the registry, providing a single source of truth across environments (dev/staging/prod) without connecting to each database.

AI‑Assisted SQL Generation

Current structural metadata (names, types, constraints) is insufficient for high‑quality AI completion. The model would need column lineage ("this column derives from orders.total"), data classification (PII, financial), sample values (enum distinct values, date ranges), and query patterns. This requires a separate analytics pipeline, not just catalog introspection.

Practical Verification Checklist

1. Observe a full refresh: Connect to a database → Database tool window → right‑click schema → Refresh (Ctrl+F5) → watch spinner and object counts.
2. Enable debug logging: Add com.intellij.database.introspection to Debug Log Settings → trigger refresh → open idea.log → search for executed catalog queries.
3. Inspect cache files: Help → Show Log in Files → navigate to introspection/ → confirm .ldb/.sst files exist per connection UUID.
4. Test permission failure: Connect with a restricted user (e.g., GRANT USAGE ON SCHEMA public TO readonly; GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly; without catalog access) → note missing objects → check logs for warnings.
5. Verify incremental DDL detection: In one console, run CREATE TABLE test_incremental (id INT); → wait for poll interval (or press Ctrl+F5) → confirm table appears without full re‑introspection (status bar shows "Refreshed 1 object").

Limitations to Keep in Mind

• Introspection reads structure only — no row data. Enum values for completion require a separate "Load Sources" action or explicit SELECT DISTINCT query.
• Bundled JDBC driver versions are pinned. Overriding via "Driver files" in Data Source properties can break introspection if the driver’s DatabaseMetaData implementation changes.
• Multiple consoles sharing a data source share one introspection cache. Schema changes in one console appear in others after refresh — there is no per‑console isolation.
• MySQL’s information_schema performance degrades sharply on schemas with >10k tables; consider splitting schemas or increasing ide.introspection.query.timeout.