Add covering index for Zoekt reserved storage bytes by node

What does this MR do and why?

Adds a covering index on zoekt_indices (zoekt_node_id) INCLUDE (reserved_storage_bytes).

Search::Zoekt::Node.available_for_search_indexing, which Search::Zoekt::SelectionService uses to rank nodes for placement, sums reserved_storage_bytes per node over the whole zoekt_indices table. Today that reads every heap row through index_zoekt_indices_on_zoekt_node_id_and_id. With the covering index it becomes an index-only scan, with about 30 times fewer buffers on a production-sized clone.

Found while measuring queries for !264628. That MR doesn't change this query. The cost already exists on master.

No application code changes: the planner picks the index for the existing query.

References

• !264628
• #635711

Screenshots or screen recordings

Not applicable: database index only.

How to set up and validate locally

1. Run the migration:

   bin/rails db:migrate

2. In gdk psql, check that the placement aggregate uses the new index:

   EXPLAIN SELECT zoekt_node_id, sum(reserved_storage_bytes) FROM zoekt_indices GROUP BY zoekt_node_id;

   With little local data, the planner may still choose a sequential scan.

Database

zoekt_indices is a small table (table_size: small), about 126,000 rows on gprd. The index is 3.9 MB, and creating it with CREATE INDEX CONCURRENTLY on a Database Lab clone took 351 ms (command). It's a post-deployment migration because the index isn't needed for correctness.

Query

The existing Node.available_for_search_indexing query, from a gitlab-production-main Database Lab clone taken on 2026-10-26. The last_seen_at bound is a fixed time before the snapshot, so nodes count as online on the clone. The plans with the index use index_zoekt_indices_on_zoekt_node_id_include_reserved_bytes, created with the migration's definition and name.

SELECT zoekt_nodes.*, (zoekt_nodes.usable_storage_bytes - COALESCE(reserved_storage_bytes_total, 0)) AS unclaimed_storage_bytes
FROM "zoekt_nodes"
LEFT OUTER JOIN (
  SELECT "zoekt_nodes"."zoekt_node_id", sum(reserved_bytes) AS reserved_storage_bytes_total
  FROM ((SELECT 0, "zoekt_indices"."zoekt_node_id", sum(reserved_storage_bytes) AS reserved_bytes FROM "zoekt_indices" GROUP BY "zoekt_indices"."zoekt_node_id")) zoekt_nodes
  GROUP BY "zoekt_nodes"."zoekt_node_id"
) reserved_bytes ON zoekt_nodes.id = reserved_bytes.zoekt_node_id
WHERE (0 = ANY(services))
  AND "zoekt_nodes"."last_seen_at" >= '2026-10-24 08:24:28.000000'
  AND ((zoekt_nodes.usable_storage_bytes - COALESCE(reserved_storage_bytes_total, 0)) > 0)
ORDER BY unclaimed_storage_bytes DESC

Run	Plan	Time	Buffers
Before, cold after clone reset	176342	3.7 s	120,675 (9,717 read)
Before, warm	176343	78 ms	120,411
After, production visibility map	176352	23 ms	3,747 (8,950 heap fetches)
After, after VACUUM	176354	19 ms	538 (0 heap fetches)

"Production visibility map" means the index was added with only ANALYZE (command), so the clone kept gprd's vacuum state. That's the closer estimate for gprd, since zoekt_indices is updated often.

Write cost: one more index entry on each insert and on each update that changes zoekt_node_id or reserved_storage_bytes. It doesn't cost HOT updates: changes to reserved_storage_bytes already can't be HOT, because index_zoekt_indices_on_watermark_level_reserved_storage_bytes has WHERE (reserved_storage_bytes > 0). On a clone without the new index, 200 updates changing only reserved_storage_bytes gave 0 HOT updates, against 175 of 200 for a control update of the unindexed updated_at.

Migration output

Up

main: -- index_exists?(:zoekt_indices, :zoekt_node_id, {include: :reserved_storage_bytes, name: "index_zoekt_indices_on_zoekt_node_id_include_reserved_bytes", algorithm: :concurrently})
main:    -> 0.0040s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- add_index(:zoekt_indices, :zoekt_node_id, {include: :reserved_storage_bytes, name: "index_zoekt_indices_on_zoekt_node_id_include_reserved_bytes", algorithm: :concurrently})
main:    -> 0.0024s
main: -- execute("RESET statement_timeout")
main:    -> 0.0002s
main: == 20261024125031 AddIndexZoektIndicesOnNodeIdIncludeReservedBytes: migrated (0.0451s)

Down

main: -- index_name_exists?(:zoekt_indices, "index_zoekt_indices_on_zoekt_node_id_include_reserved_bytes")
main:    -> 0.0009s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- remove_index(:zoekt_indices, {algorithm: :concurrently, name: "index_zoekt_indices_on_zoekt_node_id_include_reserved_bytes"})
main:    -> 0.0017s
main: -- execute("RESET statement_timeout")
main:    -> 0.0003s
main: == 20261024125031 AddIndexZoektIndicesOnNodeIdIncludeReservedBytes: reverted (0.0403s)

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

🤖 Generated with Claude Code