What are common performance tuning techniques for pgvector at scale?

-- Full performance-tuned setup for large tables:

-- 1. Create table with efficient type
CREATE TABLE items (
    id        BIGSERIAL PRIMARY KEY,
    content   TEXT,
    category  TEXT,
    embedding HALFVEC(1536)   -- 2x storage savings
);

-- 2. Add composite B-tree index for filter columns:
CREATE INDEX ON items (category);   -- speeds up WHERE category = '...'

-- 3. Configure for fast HNSW build:
SET maintenance_work_mem = '4GB';
SET max_parallel_maintenance_workers = 7;

-- 4. Build HNSW index AFTER bulk loading data:
CREATE INDEX ON items USING hnsw (embedding halfvec_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- 5. At query time, tune recall if needed:
SET hnsw.ef_search = 100;  -- increase if recall is insufficient

-- 6. Check query plan:
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, content FROM items
WHERE category = 'tech'
ORDER BY embedding <=> '[...]'::halfvec
LIMIT 10;
-- Verify: 'Index Scan using items_embedding_idx' appears