Is my new SurrealDB index a duplicate?
Duplicate index
Warning: the statement succeeds, then writes fail, access changes, or the table is held up.
What happens
Two indexes over the same fields, in the same order, on one table hold the same entries twice. Queries use one of them, and every write to the table pays to maintain both.
Why it is dangerous on a populated table
SurrealDB checks a schema change against new writes, not the records already stored, so an empty table hides it: on a populated one every old record is a write waiting to fail, and whole-table work runs for the length of the table in one transaction.
Fires on
DEFINE INDEX purchase_customer ON TABLE purchase FIELDS customer CONCURRENTLY;
DEFINE INDEX purchase_by_customer ON TABLE purchase FIELDS customer CONCURRENTLY;The safe pattern
Keep one index per field list. When one of the two is UNIQUE, keep that one: it serves the same lookups and enforces the constraint.
DEFINE INDEX purchase_customer ON TABLE purchase FIELDS customer CONCURRENTLY;Fixtures
The rule ships with these files and the test suite runs them on every change: the first set must fire, the second must stay silent.
Fires (2)
DEFINE INDEX purchase_customer ON TABLE purchase FIELDS customer CONCURRENTLY;
DEFINE INDEX purchase_by_customer ON TABLE purchase FIELDS customer CONCURRENTLY;DEFINE INDEX user_email_lookup ON TABLE user FIELDS email CONCURRENTLY;
DEFINE INDEX user_email ON TABLE user FIELDS email UNIQUE CONCURRENTLY;Stays silent (5)
DEFINE INDEX purchase_customer ON TABLE purchase FIELDS customer CONCURRENTLY;
DEFINE INDEX purchase_customer_date ON TABLE purchase FIELDS customer, created_at CONCURRENTLY;DEFINE INDEX a ON TABLE purchase FIELDS customer, created_at CONCURRENTLY;
DEFINE INDEX b ON TABLE purchase FIELDS created_at, customer CONCURRENTLY;DEFINE INDEX purchase_customer ON TABLE purchase FIELDS customer CONCURRENTLY;
DEFINE INDEX refund_customer ON TABLE refund FIELDS customer CONCURRENTLY;DEFINE INDEX post_title ON TABLE post FIELDS title CONCURRENTLY;
DEFINE INDEX post_title_search ON TABLE post FIELDS title FULLTEXT ANALYZER simple BM25 CONCURRENTLY;DEFINE INDEX purchase_customer ON TABLE purchase FIELDS customer CONCURRENTLY;
DEFINE INDEX OVERWRITE purchase_customer ON TABLE purchase FIELDS customer CONCURRENTLY;How to check locally
SurrealDB support is in beta: this rule runs locally in the free CLI over .surql migrations, static only, and not in the hosted service yet. No install, nothing leaves your machine:
npx bolvrk check migration.surql --engine=surrealdb