perf(filtering): split METADATA into thin index + fat content table

[vlasky]

Problem

The filtering::delete function re-sequences _subset_ primary keys after removing rows to maintain the dense 0-based ID invariant required by the PLAID vector index. On large indices (~90K rows, ~5GB database), this takes ~1s even for single-file updates.

The root cause is that _subset_ is the INTEGER PRIMARY KEY (i.e. the rowid). Changing it relocates the row in the B-tree, which requires reading and rewriting all ~27KB per row (including overflow pages holding large TEXT columns like code, signature, etc).

Benchmarks on a 20K-row table with 27KB rows, shifting ~19K rows:

Approach	Time
_subset_ as PK (current)	0.94s
Autoincrement PK + indexed _subset_	0.21s
Thin table (no fat columns)	0.008s

The thin table is ~115x faster than the current approach because SQLite never touches overflow pages.

Proposal

Split METADATA into two tables:

METADATA (thin, ~5-10MB for 90K rows):

_subset_ INTEGER PRIMARY KEY,      -- dense 0-based, re-sequenced on delete
_content_id_ INTEGER NOT NULL,     -- FK to METADATA_CONTENT (stable, monotonic)
file TEXT,
name TEXT,
qualified_name TEXT,
line INTEGER,
end_line INTEGER,
language TEXT,
unit_type TEXT,
complexity INTEGER,
has_loops INTEGER,
has_branches INTEGER,
has_error_handling INTEGER

METADATA_CONTENT (fat, ~2.5GB for 90K rows):

_content_id_ INTEGER PRIMARY KEY,  -- stable monotonic, never re-sequenced
code TEXT,
signature TEXT,
docstring TEXT,
parameters TEXT,
calls TEXT,
called_by TEXT,
variables TEXT,
imports TEXT,
return_type TEXT,
extends TEXT,
parent_class TEXT

Impact on public API

The public interface (create, update, delete, get, where_condition) stays the same. Internal changes only:

Function	Change
create / update	INSERT into both tables
delete	Re-sequence thin table only (~8ms); cascade-delete orphaned content rows
get	JOIN on _content_id_
where_condition	No change (filters on thin columns only)
where_condition_regexp	JOIN when pattern targets a content column
count / exists	No change

Migration

Use PRAGMA user_version to track schema version:

• v0: current single-table layout
• v1: split layout

On first load with new code, detect v0 and run a one-time migration:

1. CREATE METADATA_CONTENT, populate from existing fat columns
2. Rebuild METADATA as thin table (CREATE new, INSERT, DROP old, RENAME)
3. Set PRAGMA user_version = 1
4. VACUUM

Pure row shuffling, no re-encoding needed. ~30-60s on a 5GB database. Transaction ensures no partial state on interruption.

Context

Complements PR #135 which addresses the other half of incremental delete performance (IVF patching). Together these would bring a small incremental update from ~60s to well under 1s on large indices.

[raphaelsty]

Hi @vlasky merged your suggestion into #141, thank's a lot for this issue. Please note the observations you made, i.e timing to delete a sample depends on wether the sample is recent or old in the database. I observe much faster deletion for recent samples :)

[vlasky]

Thanks for the quick turnaround on #141 - the v1 layout is a clean improvement and the forward-compat reasoning makes sense for deployed API servers.

I wanted to share some additional benchmarks that show the remaining gap. With _subset_ demoted to a regular indexed column (your approach), SQLite still reads and rewrites the full row record during UPDATE - it just doesn't have to relocate it in the B-tree. The cost scales with row size and the number of rows shifted:

Reproducible benchmark (Python, anyone can run this):

import sqlite3, os, time, tempfile, shutil

db_dir = tempfile.mkdtemp()
db_path = os.path.join(db_dir, "test.db")
conn = sqlite3.connect(db_path)
conn.execute("PRAGMA journal_mode=WAL")
conn.execute("PRAGMA page_size=4096")

big_text = "x" * 27000  # ~27KB per row, typical for colgrep METADATA
N = 20000
shift = 50
deleted_at = 1000  # simulate deleting files indexed early

# Your approach: autoincrement rowid, _subset_ as indexed column
conn.execute("""CREATE TABLE auto_rows (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    seq INTEGER NOT NULL,
    payload TEXT
)""")
conn.execute("CREATE INDEX idx_auto_seq ON auto_rows(seq)")
conn.execute("BEGIN")
for i in range(N):
    conn.execute("INSERT INTO auto_rows (seq, payload) VALUES (?, ?)", (i, big_text))
conn.execute("COMMIT")

conn.execute("BEGIN")
conn.execute("DELETE FROM auto_rows WHERE seq >= ? AND seq < ?", (deleted_at, deleted_at + shift))
start = time.time()
conn.execute("UPDATE auto_rows SET seq = seq - ? WHERE seq >= ?", (shift, deleted_at + shift))
elapsed_auto = time.time() - start
conn.execute("COMMIT")

# Thin table: same operation, no payload column
conn.execute("CREATE TABLE thin_rows (id INTEGER PRIMARY KEY AUTOINCREMENT, seq INTEGER NOT NULL)")
conn.execute("CREATE INDEX idx_thin_seq ON thin_rows(seq)")
conn.execute("BEGIN")
for i in range(N):
    conn.execute("INSERT INTO thin_rows (seq) VALUES (?)", (i,))
conn.execute("COMMIT")

conn.execute("BEGIN")
conn.execute("DELETE FROM thin_rows WHERE seq >= ? AND seq < ?", (deleted_at, deleted_at + shift))
start = time.time()
conn.execute("UPDATE thin_rows SET seq = seq - ? WHERE seq >= ?", (shift, deleted_at + shift))
elapsed_thin = time.time() - start
conn.execute("COMMIT")

print(f"Demoted PK + fat rows (your v1): {elapsed_auto:.3f}s")
print(f"Thin table (no payload):          {elapsed_thin:.3f}s")
print(f"Ratio: {elapsed_auto / elapsed_thin:.0f}x")

conn.close()
shutil.rmtree(db_dir)

Results on my machine (20K rows, 27KB each, delete near front):

Approach	Time	Ratio
Demoted PK + fat rows (v1)	211ms	26x
Thin table (no fat columns)	8ms	1x

The v1 layout is great for the common case (deleting recently-indexed files at the end), but when a user modifies a file that was indexed early - which happens regularly when editing core modules in a large project - they still hit the multi-second path.

The thin/fat split eliminates this position-dependence entirely: 8ms regardless of where in the index the deleted file sits.

I understand the forward-compat concern with deployed next-plaid-api instances. A possible path forward:

1. Keep v1 as the default (ships today, safe for existing deployments)
2. Introduce the thin/fat split as v2, opt-in via a migration command or config flag
3. The get function JOINs transparently - SELECT * still returns the same columns to callers

This way deployed APIs stay on v1 indefinitely, while users who hit the worst-case path (colgrep on large codebases with edits to old files) can opt into v2 for position-independent delete performance.

Happy to contribute the implementation if you're open to it.

[raphaelsty]

@vlasky Always open for a MR, thank's again for taking the time to do it, this is very cool for colgrep 🥹

The most important to me is to assert backward compatibility, and that newly created indexes benefit from this feature, I'm totally ok to keep previous code logic to assert backward compatibility along with the new code as long as the version is clear with comments and within a dedicated file 🙏

I.E it's ok not to have this acceleration on existing next-plaid-api indexes, but newly created colgrep or next-plaid-api should benefit from this. For colgrep we should bump the version of the index structure to force re-computation on version bump for this feature

[vlasky]

@raphaelsty Done! Check out #144