Large Oracle/PostgreSQL schemas: freezes, slow metadata and dead connections - measurements and proposed fixes

[tessarotto21]

Hi everyone!

I've had a hard time using DBeaver with large databases: freezes while loading objects, the editor getting stuck when the connection drops, and autocomplete that never shows up. More than once that pushed me back to paid tools. Instead of giving up, I decided to try to contribute.

At work we use Oracle with a large dictionary: thousands of tables, packages, procedures, functions and indexes. With that schema size and a VPN in the middle, DBeaver was hard to use. Other tools handle schemas of this size, so I forked DBeaver and spent some time measuring where the time goes, working with Claude (Opus 5.5) as a coding assistant. Everything below comes from DBeaver's own code and from measurements. Nothing came from other products.

How I measured

• Synthetic databases generated by a script, no real data:
  • Oracle 23ai: one schema with 14,161 tables, 162,842 columns, 15,531 indexes, 740 packages and 3,744 procedures.
  • PostgreSQL 16 with a similar shape.
• 100 ms round trip simulated with Toxiproxy.
• A read-only user that doesn't own the objects, so Oracle goes through the ALL_* views.
• A repeatable UI script that times the IDE and samples the UI thread.
• Every change was measured against an unmodified build of devel.

What I found and changed

Problem	Before	After
PostgreSQL: opening the Indexes folder runs one constraint query per table with a unique index (5,725 queries)	~10 min, UI busy	3.4 s, 1 query
Oracle: autocomplete SCHEMA. on a cold connection	popup never appears (60 s timeout)	0.1–0.4 s
Oracle: opening Tables / Indexes	2.7 s / 28–31 s	0.2–0.6 s / 0.2–1.1 s
SQL editor (all databases): popup silently missing when . is typed while a parsing session is still reading metadata (race in SQLBackgroundParsingJob)	empty proposals, no error	fixed
Dead network (VPN drop): the query hangs forever, Cancel doesn't stop it, and the tab refuses new queries ("Cannot execute more than one query in one tab")	stuck until the tab is closed	Cancel works in ~3 s; next execution reconnects and runs (PG 5.8 s)
After a Cancel, the grid keeps showing the previous result as if it were the new one	stale data, no message	"Query execution was canceled" shown in place of the grid
PostgreSQL connect: 2 redundant round trips per connection	3.1 s to connected	2.7 s

Small bugs found along the way:

• Oracle: constraint and FK columns come back empty with "Always use DBA views", because ALL_CONS_COLUMNS is hardcoded in two queries.
• PostgreSQL: PostgreExecutionContext.refreshDefaults stores the current schema as the active user (queryStrings reads column 1 of each row). This affects $user resolution and the Redshift privilege filter.
• JDBCUtils.isConnectionAlive passes milliseconds to Connection.isValid(seconds).

For Oracle I also have an opt-in "keep table metadata between sessions" option, off by default:

• It keeps a per-schema snapshot of the metadata rows.
• It restores the snapshot in the background on connect.
• It refreshes changed objects incrementally using LAST_DDL_TIME/STATUS.

This is the largest change, and I'd like to hear your opinion on the approach before proposing it.

Related issues and discussions

I searched before posting. These seem to describe the same problems:

• Dead connection / cancel that never ends:
  • Invalidate/Reconnect does not discard Oracle Cancel Requests #39200 (open): Oracle cancel times out, the query keeps running, and the only way out is dropping the VPN.
  • When we cancel a long query ... DBeaver looks frozen and never comeback. We need to kill the process. #34316: cancel leaves DBeaver looking frozen; a user suggests that the client should drop the connection and give control back.
  • "Cannot execute more than one query in one tab" is still there #37752: "Cannot execute more than one query in one tab" until restart; closed because it couldn't be reproduced. I can reproduce it with a silently dropped connection.
  • Cancel operation timed out #19223 and discussion Cancel operation timed out #19398: "Cancel operation timed out".
  • Discussion MySQL (Amazon RDS) connections "expire" silently; further queries hang DBeaver #287: connections that expire silently and make further queries hang.
• Metadata kept between sessions / prefetch:
  • metadata refresh for code completion #39295 (open): an offline copy of metadata, refreshed on demand.
  • Req: prefetch for "search proposals" #7064 (open): prefetch proposals and cache them until DDL.
  • Keep the suggestions cached after disconnection #40124 (open): keep suggestions after disconnection.
  • The Oracle snapshot option is one possible implementation for these.

I didn't find existing reports for:

• the PostgreSQL index-folder N+1;
• the autocomplete race;
• the empty constraint columns with DBA views;
• the PostgreSQL active-user bug.

Proposal

I'd like to send these as small, independent PRs, each with its own issue, tests where possible, and before/after numbers:

1. PostgreSQL index-folder N+1.
2. SQL editor autocomplete race.
3. Dead connection: cancel that actually ends the operation, faster reconnect, and a clear message instead of stale results.
4. The small bugs above.
5. PostgreSQL connect round trips.
6. (to be discussed) The Oracle metadata snapshot.

Would you be interested in reviewing these? I'm happy to adjust the approach to whatever fits the project best, and I can share the benchmark scripts as well.

AI disclosure: this work was done with the help of Claude (Anthropic), and I reviewed and tested every change myself.

Thanks!
Isa

[tessarotto21]

Thanks for reading! I opened the PRs, one per topic:

• dbeaver/dbeaver#42467 Oracle: read constraint columns through DBA views when enabled #42459 Oracle: constraint columns through DBA views
• dbeaver/dbeaver#42468 PostgreSQL: read schema constraints once when loading all indexes #42460 PostgreSQL: index-folder N+1 (~10 min → 3.4 s)
• dbeaver/dbeaver#42469 SQL editor: keep completion requested while a parse session is finishing #42461 SQL editor: autocomplete race (popup silently missing)
• dbeaver/dbeaver#42470 JDBC: recover from a dead connection when a query is canceled #42462 Dead connection: cancel that ends the operation, faster reconnect, no stale grid
• dbeaver/dbeaver#42471 JDBC: pass seconds to Connection.isValid in the connection check #42463 JDBC: seconds in Connection.isValid
• dbeaver/dbeaver#42472 PostgreSQL: fewer round trips per connection, fix active user #42464 PostgreSQL: fewer round trips on connect, active user fix
• dbeaver/dbeaver#42473 Oracle: optional metadata snapshot between sessions #42465 Oracle metadata snapshot, depends on dbeaver/dbeaver#42467 Oracle: read constraint columns through DBA views when enabled #42459, open to discussion

Happy to adjust anything, including splitting or dropping parts.
Isa