sql/vecindex: vector index on REGIONAL BY ROW table returns wrong or empty results
Nobody has claimed this yet.
Assessment
- Difficulty
- 5/5
- Estimated time
- Over a week
- Newbie friendliness
- 12/100
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
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_size8, 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 TABLEindex. 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_regionexplicitly 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
- No Dockerfile or Docker Compose file
- No pull request template
- Read the contributing guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
More from cockroachdb/cockroach
-
C-bug O-community X-blathers-untriaged
Difficulty 2/5 1-3 hours Newbie friendliness 76/100
cockroachdb/cockroach#176245 · 1 comment ·
-
sql: pg_database.dattablespace is 0 but pg_tablespace.oid is 1663, so they no longer joinPossibly taken @1AbdullahButt claimed this 14 days ago. OpenC-bug O-community X-blathers-triaged X-move-internal
Difficulty 2/5 1-3 hours Newbie friendliness 68/100
cockroachdb/cockroach#176228 · 1 comment ·
-
PG compat: overflow in integer power calculation triggers incompatible SQLSTATEMay be free again @darshpatel1052 claimed this 18 days ago, and no pull request is open. OpenC-bug O-community X-blathers-untriaged
Difficulty 2/5 1-3 hours Newbie friendliness 62/100
cockroachdb/cockroach#176223 · 2 comments ·
-
util/log: bufferedSinkCloser.Close leaks a goroutine after timeoutPossibly taken @dajiaohuang claimed this 14 days ago. OpenC-bug O-community X-blathers-untriaged
Difficulty 1/5 Under an hour Newbie friendliness 84/100
cockroachdb/cockroach#176210 · 1 comment ·
-
C-bug O-community T-sql-queries X-blathers-triaged X-move-internal
Difficulty 2/5 1-3 hours Newbie friendliness 78/100
cockroachdb/cockroach#176150 · 1 comment ·
All issues in cockroachdb/cockroach
Similar issues
-
area/import-export area/projects concern/ux
Difficulty 2/5 1-3 hours Newbie friendliness 68/100
go-vikunja/vikunja#4455 · 1 comment ·
Maintainers usually reply within 2 days
-
Dependabot PRs no longer merge automaticallyPossibly taken @dwisiswant0 claimed this today. Open
Difficulty 2/5 1-3 hours Newbie friendliness 78/100
projectdiscovery/urlfinder#160 ·
Maintainers usually reply within 1 day
-
database
Difficulty 2/5 1-3 hours Newbie friendliness 72/100
chnm/legal-modernism#450 ·
Maintainers usually reply within 1 day
-
tech-debt
Difficulty 2/5 1-3 hours Newbie friendliness 76/100
Maintainers usually reply within 1 day
-
bug ready-for-triage ready-to-code
Difficulty 2/5 1-3 hours Newbie friendliness 62/100
RedHatInsights/insights-ingress-go#946 · 4 comments ·
Maintainers usually reply within 1 day