Query Tool Autocomplete: Restore dropped debounce and introduce lazy on-demand client schema caching

[dev-hari-prasad]

With "Autocomplete on key press" enabled in Query Tool preferences, typing in the SQL editor can feel noticeably slow. Suggestions can take anywhere from a couple hundred milliseconds to 2+ seconds to appear, and in some cases the editor starts to stutter while typing.

There seem to be two things contributing to this:

1. The CodeMirror 6 migration dropped the client-side debounce and request cancellation that previously helped prevent autocomplete requests from being fired on every keystroke.
2. The backend still performs live PostgreSQL catalog queries for autocomplete requests instead of reusing schema metadata that has already been fetched.

This issue proposes fixing the immediate request-flooding problem first, and then moving towards a hierarchical, lazy on-demand schema cache so we don't have to choose between fast autocomplete and loading an entire database schema into the browser.

---

Root Cause & Regression Analysis

1. CodeMirror 6 migration dropped the previous anti-flood protections

In July 2022, commit 631a08189 (refs #4488) added a 300ms setTimeout debounce and token-prefix filtering (autoCompleteList) in Query.jsx.

The idea was fairly simple: don't hit the server for every character the user types.

During the CodeMirror 6 migration (d3ede3151, #7097), those protections appear to have been lost.

Specifically:

• Debounce was dropped: registerAutocomplete in web/pgadmin/tools/sqleditor/static/js/components/sections/Query.jsx (lines 30–56) was changed to a direct promise-based completion handler. The previous 300ms setTimeout debounce and prefix filtering are no longer present, so completion requests can be sent immediately whenever CodeMirror asks for them.
• The abort signal isn't connected to the HTTP request: In web/pgadmin/static/js/components/ReactCodeMirror/CustomEditorView.js (lines 412–414), context.addEventListener('abort') currently removes the loading indicator with this.loadingDiv?.remove(), but the abort signal isn't passed to the Axios request. As a result, a request that is no longer relevant to the user can still continue running against PostgreSQL after they have already typed more characters.

This means a user typing something like:

SELECT * FROM users WHERE em

can potentially trigger several completion requests while they're still typing, even though only the latest request is actually useful.

---

2. Autocomplete still performs live catalog queries

On the backend, web/pgadmin/utils/sqlautocomplete/autocomplete.py performs metadata lookups through functions such as:

• fetch_schema_objects
• fetch_columns
• fetch_foreign_keys
• fetch_functions

These eventually execute queries against PostgreSQL system catalogs using:

• columns.sql
• tableview.sql
• foreign_keys.sql
• functions.sql

through the dedicated conn_id_ac connection.

There currently isn't an in-memory schema snapshot that can be reused between completion requests.

So even after fixing the request flooding, a completion request can still involve network round trips and catalog queries against PostgreSQL.

This becomes much more noticeable with remote databases or databases containing thousands of relations, partitions, columns, etc.

---

Why not just load the entire schema?

It might be tempting to solve this by downloading the complete database catalog when the Query Tool starts.

I don't think that scales particularly well.

For example, an enterprise database could have:

• 50+ schemas
• 20,000+ tables/partitions
• 500,000+ columns

Downloading and keeping all of that in the browser just to make autocomplete fast would introduce its own problems:

• Higher Query Tool startup time
• Significant network traffic
• Increased browser memory usage
• More work when switching databases/schemas
• Metadata becoming stale when another user performs DDL

So rather than eagerly loading everything, the cache could be built incrementally as the user actually needs it.

---

Proposed Architecture: Hierarchical, Lazy On-Demand Cache

The basic idea would be to keep autocomplete metadata at different levels of scope:

┌────────────────────────────────────────────────────────┐
│ Level 1: Static Constants (In JS bundle, 0 I/O)        │
│ SQL keywords, operators, PG core functions             │
│ e.g. now(), count(), coalesce()                        │
└──────────────────────────┬─────────────────────────────┘
                           │
┌──────────────────────────▼─────────────────────────────┐
│ Level 2: Lazy Per-Schema Cache                         │
│ Fetch table/view names when a schema is first accessed │
│ Keep them in memory for the Query Tool session         │
└──────────────────────────┬─────────────────────────────┘
                           │
┌──────────────────────────▼─────────────────────────────┐
│ Level 3: Table-Scoped Columns                          │
│ Fetch columns only for tables actually referenced     │
│ in the current FROM / JOIN clauses                    │
└────────────────────────────────────────────────────────┘

The important part here is that we're not trying to load the whole database up front.

If someone is working with:

SELECT *
FROM customers
JOIN orders ...

there's little reason to have every column from every table in every schema sitting in the browser.

We can fetch what is needed, cache it, and reuse it for subsequent completions.

---

Proposed Implementation Roadmap

Phase 1: Immediate fix — restore the CM5 anti-flood behaviour

This should probably be the first step since it addresses the regression without requiring a larger autocomplete redesign.

1. Re-introduce debounce in Query.jsx

   Add a ~200–250ms debounce before dispatching completion HTTP requests.

2. Wire AbortController into CustomEditorView.js

   Connect CodeMirror 6's context.addEventListener('abort') to an AbortController and pass its signal to Axios.

   This means that when the user continues typing and the previous completion becomes irrelevant, the HTTP request can be cancelled instead of continuing to run in the background.

---

Phase 2: Client-side schema completion using @codemirror/lang-sql

CodeMirror 6's @codemirror/lang-sql is already present in pgAdmin's package.json and supports schema-aware completion through its SQL configuration.

This could allow us to move frequently-used completion data into the client.

1. Static core catalog

Keep standard PostgreSQL keywords and commonly used built-in functions in the client bundle.

For example:

count()
coalesce()
now()
generate_series()
jsonb_build_object()

These don't need a pg_proc lookup every time the user types.

2. Lazy active-schema cache

Fetch table/view names for the active search_path when they are first needed.

For example, if the user starts working with public, fetch the relevant objects once and keep them cached for that Query Tool session.

3. Table-scoped columns

For large schemas, don't fetch all columns immediately.

If the query contains:

FROM customers

we can fetch the columns for customers and add them to the local cache.

If the user never touches audit_logs, there is no reason to fetch all of its columns just for autocomplete.

---

Phase 3: Handling stale metadata

A client-side cache obviously introduces a staleness problem, so there should be a few ways to deal with that.

1. Local DDL invalidation

When the active Query Tool executes statements such as:

CREATE
ALTER
DROP

we can invalidate the affected schema/table entries and refresh them when they're needed again.

2. Manual refresh

Provide a "Refresh Autocomplete Cache" action in the Query Tool.

This would also give users a straightforward way to refresh metadata if another user has changed the database.

Explicit Ctrl + Space / Cmd + Space completion could also be used as a point where a refresh is triggered if appropriate.

3. Cache TTL

Use a reasonable client-side TTL, for example 5–10 minutes.

Instead of blocking autocomplete while the cache is refreshed, stale metadata could continue being used while a background refresh updates the cache.

---

Expected Benefits

The immediate debounce + cancellation changes should already make autocomplete feel noticeably more responsive by preventing unnecessary requests from being sent for every keystroke.

The longer-term caching approach would additionally:

• Reduce repeated PostgreSQL catalog queries.
• Reduce network round trips for autocomplete.
• Avoid unnecessary connection usage and contention.
• Make common completions effectively local once the relevant metadata has been cached.
• Restore the anti-flood behaviour that was previously implemented for CodeMirror 5 in BASE DE DATOS (RM #6662) #4488.
• Scale better for large enterprise databases without downloading the entire schema into the browser.
• Keep the initial Query Tool startup lightweight by fetching metadata only when it is actually needed.

For common cached completion paths, the goal would be near-instant/sub-5ms client-side completion, rather than making a network round trip for every suggestion.

The main point is that we don't need to solve the problem by choosing between "query PostgreSQL for everything" and "load the entire database into the browser". A small amount of request throttling combined with a hierarchical, lazy cache should let us get the benefits of both approaches without introducing the scaling problems of either extreme.

Beyond the obyious technical benefits we have to consider that developers today have become accustomed to near-instant autocomplete from tools like VS Code, DataGrip, and DBeaver. When autocomplete in pgAdmin introduces noticeable typing lag or takes hundreds of milliseconds to return suggestions, the experience feels broken compared to what users are already used to.

In practice, users are likely to disable "Autocomplete on key press" when it starts getting in the way of typing, or simply stop relying on autocomplete altogether. That makes the feature much less useful, even though the underlying functionality is valuable.

Bringing completion latency closer to the near-instant experience users expect from modern developer tools would make autocomplete feel dependable and responsive rather than something users have to tolerate or turn off. It would also make the feature more useful for day-to-day SQL work, especially when writing larger queries.

Whether "Autocomplete on key press" should ultimately be enabled by default is a separate discussion. Before making that decision, the experience itself should be fast enough that enabling it doesn't come at the cost of typing responsiveness.