Resource Explorer: Filtering & Scale

How Explore filters every resource across an org at scale: the query language, the cost-based DoS guard, and the measured behaviour at one and ten million rows — without giving up the “keep the explore database cheap” property that defines the store.

:::note[Status] The filter language and the cost-budget guard described here are built and shipped: pkg/lucene-to-sql plus the wiring in internal/backend/repositories/resource_index_query.go, the explore service, and the web query bar. The “Deferred work” section at the end is not built — it is the agreed next steps for the facet aggregate and very-large-org storage. :::

What scales, what doesn’t

Explore is an org-scoped search over a denormalized resource_index table in the separate gantrycd_explore database, keyset-paginated and filtered server-side. The query paths that matter for scale:

The diagnostics sweep bounds itself by lane, and each check fails on its own

The two duplicate scans are org-wide double passes, and immediately after the 12-hourly resource-index rebuild — which deletes and re-inserts every row of every stack — they read through as many dead tuples as live ones: 3.2 s at 5M rows, killed outright at 6M. They used to carry the search box’s SET LOCAL statement_timeout = 5s, which is the wrong bound for a background sweep and the right one for an interactive request. Neither scan sets a per-query timeout now: each is bounded by its lane’s session profile (DB_STATEMENT_TIMEOUT, 15 s on the request path behind /explore/duplicates; DB_JOBS_STATEMENT_TIMEOUT, 5 min for the sweep). Search and Facets keep the 5 s override — that one is the search box’s own guard.

Autovacuum is tuned on the two projection tables, by the migration chain. The rebuild rewrites every row of every stack — 12.3M rows per rebuild at load-test scale — and the duplicate scans run 2–3× slower until that churn is reclaimed. Default autovacuum triggers at 20% of the table, which here is ~2.5M dead tuples: far more than an hourly sweep can afford to read through, and reached long after the rebuild that created them.

migrations-explore/00000000000001_autovacuum_projection_tables.sql sets, on both resource_index and dependency_edges:

It lives in the migration chain rather than schema-explore/ because per-table storage parameters are the one thing Atlas cannot hold: Atlas 1.2.0-community renders no reloptions from schema inspect even when they are set on the live table, and silently ignores an unrecognised block in the HCL rather than rejecting it — so a declarative entry would be a no-op that reads like configuration. ALTER TABLE … SET (…) states a value rather than mutating one, so the chain’s replay-on-every-deploy is a no-op, and it restores the settings if Atlas ever recreates a table. This is a documented carve-out from “no DDL in a migration” (see CLAUDE.md and the chain’s baseline), narrow to settings Atlas can neither express nor inspect.

Verify with:

SELECT relname, reloptions FROM pg_class
WHERE relname IN ('resource_index', 'dependency_edges');

If that comes back empty on a deployment whose migrations have run, applying the same ALTER TABLE … SET (…) by hand is the emergency path; the next deploy converges to the same state either way.

The backend does not issue an explicit VACUUM. It cannot: a least- privilege deployment gives the owner credential to the migration job, not the backend, and Postgres answers a non-owner VACUUM with WARNING: permission denied to vacuum "…", skipping it — then returns success. Nothing in the driver path reads notices, so the backend would log a vacuum it never performed.

Autovacuum tuning only shortens the window. The real fix is to stop rewriting rows that did not change: a diff-based projection write would leave almost nothing for autovacuum to reclaim, and is tracked separately.

• Every Insights check fails independently. Cycles, orphan/unsynced references and duplicates each have their own reads and their own completeness flag on the stored findings (cycles_complete, orphans_complete, duplicates_complete). A check whose read fails is recorded as not computed — a Warn line and gantrycd_explore_sweep_check_failures_total{org,check} — while the row is still published with a fresh computed_at and the checks that did run. Insights then says “not checked” for that dimension rather than showing zero, the stack list withholds those badges, and the Slack digest omits its delta. Before this, a killed duplicate scan returned at the first read: no row was written at all, and Insights showed the previous one, silently, for as long as the scan kept failing.

Indexes (schema-explore/tbl_resource_index.hcl): the keyset tuple (org_id, stack_name, stack_id, address), (org_id, resource_type), (org_id, provider), a partial (org_id, cloud_identity_id, cloud_identity_realm, resource_type), and a GIN on attributes. provider_config, mode, and name are deliberately unindexed — we bound them with the cost budget instead of paying an index on every high-cardinality column (see the trade below).

The store, keyset pagination, and edge-capped insights hold at 10M. The work was generalizing the filter model and bounding the cost of the full-scan filters it admits.

The filter language (pkg/lucene-to-sql)

A single query bar replaces the old four-dropdown filter. The query is a small Lucene subset compiled to parameterized PostgreSQL by pkg/lucene-to-sql (package lucene).

type:aws_iam_role     mode:managed        NOT stack:legacy-*
provider:aws.us_east_1     attr.tags.team:platform     tainted:true
type:(aws_iam_role OR aws_iam_policy)      payment service      type:aws_iam_*

Operators: field:value (eq), field:val* (prefix), field:*val* (substring / contains), field:(a OR b) (membership), has:field / field:null (jsonb presence), attr.k.v:x (nested jsonb containment), AND / OR / NOT / parens, and bare words (free-text over name/address/type). Attribute values also take wildcards — attr.region:eu-west-* / attr.note:*urgent* — but unlike exact containment (GIN-indexed) these are an unindexed scan, so they’re priced as a non-indexed scan (one allowed per query, stacking blocked; see the budget below). Dropped Lucene-isms (fuzzy ~, boost ^, regexp, ranges, +/-) buy little here and several are hostile at scale — note attr.region:"eu-west-.*" is a literal value, not a regex.

Structure borrowed from grindlemire/go-lucene (not a dependency): one recursive AST node, an operator-keyed renderer, and a parameterized-only output ($N + an args slice — user values never reach the SQL string). The caller supplies a Schema mapping each user field to a physical column with a per-(field, op) cost tier; an unknown field is a parse error, not SQL. LIKE metacharacters are escaped; jsonb paths/values are bound parameters; NUL/invalid-UTF8 are rejected; input length, nesting depth, and predicate count are capped. (Verified by a real-Postgres injection canary + Go fuzzing.)

The DoS guard: a per-query cost budget

The library enforces structural parsing limits but does not enforce a query-cost budget: Compile reports a Cost and the caller decides. The explore service (resource_index_query.go, explore_service.go) sets the policy:

• Cost weights tuned to measured compute cost, not condition count: Indexed = -4, ScanBounded = 2 (non-indexed exact / jsonb), ScanUnbounded = 8 (substring + free-text — the expensive seq scans). Budget = 12.
• Indexed predicates discount the query (negative weight, floored at 0): an indexed filter narrows the scan, so the rest runs over fewer rows. type:x AND payment costs 4, not 8; an indexed guard “buys” a second scan that would otherwise be over budget.
• Free-text is one surcharge regardless of term count — payment service is one seq scan, so it costs 8, not 16 (the calibration showed free-text latency is flat in term count).
• A query over budget is rejected as a 400. A per-query SET LOCAL statement_timeout = 5s on the read tx is the runtime backstop for everything the static cost can’t predict.

This is the deliberate trade: don’t index provider_config/attributes/name; budget-limit them instead. The cost catches stacking (two scans = 16, rejected) and fan-out (40 OR’d conditions sum past 12); the timeout catches the rest. Why a budget and not a hard “narrowing gate”: latency at scale is data-dependent (a selective guard helps hugely, a common one barely does — see the numbers), which no static rule can see, so the budget rewards narrowing while the timeout enforces the real bound.

The backend states facts, never suggests (“search query exceeds the complexity limit”, “search timed out”); the web query bar owns the guidance (“narrow it with a selective stack: or type:”).

Measured at scale (1 GiB / 1 vCPU Postgres)

The harness (pkg/lucene-to-sql, make test_perf_lucene) spins a hard-capped container, loads N deterministic Terraform rows, and times the real CompileExploreFilter through the production query envelope.

At 1M — every query the budget admits ran < ~800 ms; none timed out. The budget is a real compute bound. Worst allowed: a rare free-text (~605 ms, one seq scan) and a moderately-selective multi-attribute AND (~770 ms, keyset scan + jsonb heap fetch). Rejected (cost 16) payment AND name:*db* is the 800 ms two-scan query the budget keeps out.

At 10M — indexed/common queries stay < 150 ms, but full-scan filters scale ~linearly and cross the 5 s timeout:

At 10M the statement_timeout becomes the primary guard — it kills the 37.6 s multi-attr and 6.9 s free-text; the budget still stops stacking. Cost stops predicting latency here (a cost-4 query is the slowest), which is inherent: the multi-attr cost is selectivity-dependent.

Narrowing helps — but only if selective. Adding an indexed guard to the 38 s multi-attr query:

So the right UX guidance is “add a selective filter” (a specific stack, a rare type), not “add any filter” — a common-value guard doesn’t move the plan off the per-row heap fetch.

Frontend

A single ResourceQueryBar drives both surfaces (web/src/components/resources/):

• Explore (cross-stack) — query in ?q=, shareable; facet dropdowns insert field:value tokens into the query rather than holding separate state; a 400 (too costly / timed out / bad syntax) renders inline with the narrowing tip while previous results stay.
• Stack Resources tab — now Explore pinned to stack:<id>: it lists from the same explore index over the same server-side, indexed, keyset-paginated path and the same query language, instead of filtering a per-run artifact client-side. One filtering implementation, two views.

The 12h rebuild: resumable two-minute slices

The periodic sweep re-derives an org’s whole index from each stack’s latest authoritative run, so the index heals from anything the per-run path missed. It used to be one task that reindexed the whole org in one go, under a 30-minute ceiling:

The 20,000-stack row is run S4 of load_test_inference_10m_2026-08.md (branch loadtest-inference-10m); the extrapolation takes the second pass at close to the cost of the first, because on an org of that shape the reader cap trips early and it revisits nearly every stack.

Past the ceiling it was a loop, not a delay: the handler dequeued only after the whole org was done, so the row stayed queued and the next claim reindexed every stack again from the first — until AsyncTaskMaxAttempts = 20 dead-lettered it and that org stopped being rebuilt.

What it is now. Still one task per org, but the task is resumable:

• Its forward and convergence walks page stacks by keyset (RunRepository.LatestAuthoritativeRunsAfter), so those passes hold one 500-row page rather than materializing the org. The final reap is the exception and is called out below.

• After two minutes it writes v, phase, cursor and the reader set into its own task payload and returns the worker, reporting continue. The next claim — any slot, any replica — resumes there. Two minutes comes from the ceiling, which has to cover a slice plus the one stack that can overrun it; see Async Task Queue.

• The payload carries a version: an older shape reads as absent and the org restarts from its first stack, which is always correct because a rebuild is idempotent. A newer shape is refused without being rewritten, so an older replica in a rolling upgrade cannot erase state it does not understand.

• The continuation is an in-place UPDATE (AdvanceCursor), never a dequeue plus an enqueue of a successor, which would fork one task into two lineages. It is fenced on the lease and clears it in the same statement, so the row is unleased and due atomically with the progress describing it.

• It resets attempt_count whenever a slice advances, so a long rebuild does not dead-letter itself for making progress.

• The convergence pass became a second phase of the same cursor, and the reader set travels in the payload rather than in a needs_convergence column — the fact lives for one sweep.

  The 1,000-id cap is the normal path on a large org. Where most stacks make an identity lookup it trips at stack 1,001, the list is dropped, and the second pass walks the whole org — roughly doubling the slice count. More slices, each still bounded, against a payload row the whole fleet re-reads. “Visits only the readers” holds only below the cap.

• Ordering is the database’s and is never re-checked in Go. Stack ids are slugs over [a-z0-9-], and glibc collation ignores the hyphen, so SQL sorts web2 before web-api and Go sorts them the other way. The push pass compares nothing in Go; the converge pass sorts its id list into Go’s order before binary-searching it.

• A slice whose context dies part-way still commits what it finished: the cursor write is detached from the deadline that ended it. The time box and ctx.Err() are both checked between stacks.

• The reverse reap runs on the final slice only, and a reap that fails fails the slice rather than reporting done.

The ceiling came down with it, from 30 minutes to 10, so crash recovery is 10.5 minutes instead of 30.5. The per-stack walk can no longer reach it merely because the org has more stacks; the final reap still can in principle.

One residual is a single stack, and its shape is memory rather than time. A stack whose reindex cannot finish inside the handler budget is hit first on every retry and dead-letters the org’s rebuild; slicing cannot help, because the unit that does not fit is one stack. A 65 MiB analysis.json decodes in 255 ms and indexes in 3.5 ms but costs ~185 MiB of heap, against a 256 MiB object-store GET cap and four worker slots (local probe, 2026-08-30). The distribution is not measured. The other residual is the final whole-org reap below.

Fairness. Every slice re-queues at the tail (run_at = NOW()), so a many-slice rebuild does not hold the head of the FIFO. Fleet throughput is unchanged, but a big org’s rebuild spreads over as many queue cycles as it has slices, so its completion latency grows with the number of orgs competing.

The dependency-graph rebuild now follows the same pattern: one two-minute slice per claim, the cursor in its own payload, the reap on the final slice, the same 10-minute ceiling. It has no second phase — declared edges come from the stack’s own snapshot, so nothing has to be revisited once other stacks land — so it carries a cursor and nothing else. See Dependency Graph.

Not done here:

• Slice the final reverse reap. Both rebuilds still materialize every live stack id in the org and send that array to Explore on the final slice. The index then runs one whole-org DELETE; the graph runs two. This phase has not been separately load-tested — the 20,000-stack run above stopped during convergence, before reaching it. Unlike the forward walks it still grows with org size and now shares the 10-minute handler ceiling. Each database statement also has the jobs lane’s default five-minute statement_timeout. Measure it, then page or otherwise bound it if it approaches either limit.
• COLLATE "C" on stacks.id, which would make the two orders the same rather than keeping them apart. Out of scope: it rewrites an index on a hot table.
• Shard by size. K = clamp(ceil(stacks / 5,000), 1, 4) cursors per org, the shard id in resource_id. Slices are equal-share today, which under-serves a 20,000-stack org against a 50-stack one.

Deferred work

Not built; the agreed next steps when they bite:

• Facets are still a full GROUP BY per page mount (the one remaining cliff). Fix: a resource_facet_counts(org_id, facet, value, count) rollup maintained in ReplaceStackResources (it already holds the per-stack advisory lock), and/or prefix typeahead — measured 27000× faster than the aggregate at 1M. Deferred because the query bar leans on facets only for autocomplete chips.
• pg_trgm GIN on a generated name||address||type search column — turns the free-text seq scan into an index scan (measured 432× at 1M). The single biggest lever for the queries that time out at 10M.
• Frontend virtualization + page windowing — the list still renders every loaded row; fine to a few thousand, add @tanstack/react-virtual beyond.
• Hot-attribute columns — promoting commonly-filtered attribute keys (region, environment) to indexed columns is the only thing that makes the broad multi-attribute AND fast without a selective guard.
• Store-level scale — declarative hash partitioning by org_id (the access pattern already leads with org_id) before any architecture change; a search engine (OpenSearch / Typesense / ClickHouse) only as a held-in-reserve escape hatch for a single org sustaining >10M where partitioned Postgres misses the latency target.

Decisions at a glance

Related: Resource Explorer, Datastores, Dependency Graph: Cycle Detection at Scale.