sql/vecindex: vector index on REGIONAL BY ROW table returns wrong or empty results

Open
#176,240 1 comment 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Assessment

Difficulty
5/5
Estimated time
Over a week
Newbie friendliness
12/100
Issue type
Bug
Clarity
Mostly clear
Activity status
Active
Tech stack
sql
Domain
databases

Research direction

The bug is in the vector index (titled sql/vecindex) on REGIONAL BY ROW tables, and the payload names no file or test. Start by reproducing with the 4-region SQL setup, comparing recall@10 for a REGIONAL BY ROW table against a REGIONAL BY TABLE table at 200 rows per tenant, where the gap first appears. Done means RBR recall matches the 0.95-1.00 of the other two layouts and an existing row is returned for its own embedding. The issue does not say how row-level homing changes index key encoding, so that needs reading before any change.

Written by the indexing model from the issue text.

Description

C-bug

Describe the problem

A vector index on a REGIONAL BY ROW table returns wrong or empty results once a tenant prefix holds more than about 100 vectors. No error is raised. The same schema as a REGIONAL BY TABLE table, in a non-multi-region database, or in a non-multi-region table explicitly PARTITION BY LIST on a region column, returns recall@10 of 0.95–1.00.

Reported by Sai Kiran Kshatriya and Sharath Chandra Kampili.

Versions where we saw it

  • v26.4.0, fresh install, cluster version 26.4 (finalized): wrong rows (recall@10 0.02–0.10), every query returns 10 rows.
  • v26.4.0 binaries with cluster version 26.2 (12 nodes, 4 AWS regions, unfinalized upgrade): no rows for every query (400 of 400) at 20,000 vectors x 1536 dims per tenant.
  • v26.2.8 local 9-node, 3-region cluster: recall 0.025–0.05, 10–14 of 20 queries empty, at 3,000 rows x 64 dims.

To Reproduce

4-region cluster, one node per region (localities region=aws-ca-central-1, aws-eu-central-1, aws-ap-southeast-1, aws-ap-southeast-2).

CREATE DATABASE mr PRIMARY REGION "aws-ca-central-1"
  REGIONS "aws-eu-central-1", "aws-ap-southeast-1", "aws-ap-southeast-2";
USE mr;
CREATE TABLE ctx_rbr (
  tenant_id STRING NOT NULL, id INT8 NOT NULL, chunk STRING NOT NULL,
  embedding VECTOR(64) NOT NULL, PRIMARY KEY (tenant_id, id)
) LOCALITY REGIONAL BY ROW;
CREATE VECTOR INDEX vidx_rbr ON ctx_rbr (tenant_id, embedding vector_cosine_ops);
-- insert 1,000 clustered unit vectors per tenant, with crdb_region set explicitly to the tenant's region
SELECT id FROM ctx_rbr@{FORCE_INDEX=vidx_rbr}
WHERE crdb_region = 'aws-eu-central-1' AND tenant_id = 'eu'
ORDER BY embedding <=> $1::vector LIMIT 10;

Querying with an existing row's own embedding does not return that row (distance 0). Exact search through the primary key on the same table returns it first.

Expected behavior

Recall comparable to the same index on a REGIONAL BY TABLE table (0.95–1.00 here).

Additional data / screenshots

20 queries per tenant, 4 tenants, recall@10 against brute force, v26.4.0 finalized:

Rows per tenant Index built RBR REGIONAL BY TABLE Non-multi-region
100 during load 1.00 1.00 1.00
200 during load 0.38 0.99 1.00
500 during load 0.18 0.98 0.98
1,000 during load 0.04 0.98 0.98
2,000 during load 0.06 0.96 0.97
1,000 after load 0.05 0.98 0.97
2,000 after load 0.06 0.96 0.95
5,000 during load 0.02 0.98 0.98
1,000 (1536-dim) during load 0.10 0.98 0.96
2,000 (1536-dim, OpenAI ada-002 DBpedia embeddings) during load 0.02 1.00 1.00
  • Explicit PARTITION BY LIST (region) on a non-multi-region table, region as index prefix, 1,000 rows: 0.98. So partitioning on region is not enough to trigger it; row-level homing is.
  • vector_search_beam_size 8, 64 or 512: same recall (0.065).
  • Index size for the same 4,000 vectors: RBR index 4,116 keys / 0.48 MB vs 9,758 keys / 1.83 MB for the REGIONAL BY TABLE index. On the 12-node cluster: 44 MB vs 122 MB for 80,000 vectors. Most vectors look unreachable rather than the search being too narrow.
  • The tenant's home region (primary or not) and naming crdb_region explicitly in the index make no difference.

Repro script and raw JSON results available on request.

Environment:

  • CockroachDB version: v26.4.0 (also v26.2.8)
  • Server OS: Linux (x86_64 for v26.4.0 finalized; aarch64 for the 12-node cluster); macOS arm64 for v26.2.8
  • Client app: Python 3.12, psycopg 3.3.8

Additional context
Impact: silent wrong results. A RAG application using a vector index on a row-homed table retrieves empty or unrelated context with no error, and the query looks fast. Workaround we used: one REGIONAL BY TABLE table per region.

Jira issue: CRDB-72135

Dominant language
Go
Stars
32.6k
Forks
4.2k
PR merge metrics
No merged PRs in 30d

Getting set up

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from cockroachdb/cockroach

All issues in cockroachdb/cockroach

Similar issues

More Go issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.