{"id":"pgvector-semantic-search","name":"pgvector-semantic-search","summary":"このスキルを使って、pgvectorを用いたベクトル類似検索をAI/MLの埋め込み、RAGアプリケーション、またはセマンティックサーチに活用してください。","body":"# pgvector for Semantic Search\n\nSemantic search finds content by meaning rather than exact keywords. An embedding model converts text into high-dimensional vectors, where similar meanings map to nearby points. pgvector stores these vectors in PostgreSQL and uses approximate nearest neighbor (ANN) indexes to find the closest matches quickly—scaling to millions of rows without leaving the database. Store your text alongside its embedding, then query by converting your search text to a vector and returning the rows with the smallest distance.\n\nThis guide covers pgvector setup and tuning—not embedding model selection or text chunking, which significantly affect search quality. Requires pgvector 0.8.0+ for all features (`halfvec`, `binary_quantize`, iterative scan).\n\n## Golden Path (Default Setup)\n\nUse this configuration unless you have a specific reason not to.\n- Embedding column data type: `halfvec(N)` where `N` is your embedding dimension (must match everywhere). Examples use 1536; replace with your dimension `N`.\n- Distance: cosine (`<=>`)\n- Index: HNSW (`m = 16`, `ef_construction = 64`). Use `halfvec_cosine_ops` and query with `<=>`.\n- Query-time recall: `SET hnsw.ef_search = 100` (good starting point from published benchmarks, increase for higher recall at higher latency)\n- Query pattern: `ORDER BY embedding <=> $1::halfvec(N) LIMIT k`\n\nThis setup provides a strong speed–recall tradeoff for most text-embedding workloads.\n\n## Core Rules\n\n- **Enable the extension** in each database: `CREATE EXTENSION IF NOT EXISTS vector;`\n- **Use HNSW indexes by default**—superior speed-recall tradeoff, can be created on empty tables, no training step required. Only consider IVFFlat for write-heavy or memory-bound workloads.\n- **Use `halfvec` by default**—store and index as `halfvec` for 50% smaller storage and indexes with minimal recall loss.\n- **Index after bulk loading** initial data for best build performance.\n- **Create indexes concurrently** in production: `CREATE INDEX CONCURRENTLY ...`\n- **Use cosine distance by default** (`<=>`): For non-normalized embeddings, use cosine. For unit-normalized embeddings, cosine and inner product yield identical rankings; default to cosine.\n- **Match query operator to index ops**: Index with `halfvec_cosine_ops` requires `<=>` in queries; `halfvec_l2_ops` requires `<->`; mismatched operators won't use the index.\n- **Always cast query vectors explicitly** (`$1::halfvec(N)`) to avoid implicit-cast failures in prepared statements.\n- **Always use the same embedding model for data and queries**. Similarity search only works when the model generating the vectors is the same.\n\n## Type Rules\n\n- Store embeddings as `halfvec(N)`\n- Cast query vectors to `halfvec(N)`\n- Store binary quantized vectors as `bit(N)` in a generated column\n- Do not mix `vector` / `halfvec` / `bit` without explicit casts\n- Never call `binary_quantize()` on table columns inside `ORDER BY`; store it instead\n- Dimensions must match: a `halfvec(1536)` column requires query vectors cast as `::halfvec(1536)`.\n\n## Standard Pattern\n\n```sql\n-- Store and index as halfvec\nCREATE TABLE items (\n  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n  contents TEXT NOT NULL,\n  embedding halfvec(1536) NOT NULL  -- NOT NULL requires embeddings generated before insert, not async\n);\nCREATE INDEX ON items USING hnsw (embedding halfvec_cosine_ops);\n\n-- Query: returns 10 closest items. $1 is the embedding of your search text.\nSELECT id, contents FROM items ORDER BY embedding <=> $1::halfvec(1536) LIMIT 10;\n```\n\nFor other distance operators (L2, inner product, etc.), see the [pgvector README](https://github.com/pgvector/pgvector).\n\n## HNSW Index\n\nThe recommended index type. Creates a multilayer navigable graph with superior speed-recall tradeoff. Can be created on empty tables (no training step required).\n\n```sql\nCREATE INDEX ON items USING hnsw (embedding halfvec_cosine_ops);\n\n-- With tuning parameters\nCREATE INDEX ON items USING hnsw (embedding halfvec_cosine_ops) WITH (m = 16, ef_construction = 64);\n```\n\n### HNSW Parameters\n\n| Parameter | Default | Description |\n|-----------|---------|-------------|\n| `m` | 16 | Max connections per layer. Higher = better recall, more memory |\n| `ef_construction` | 64 | Build-time candidate list. Higher = better graph quality, slower build |\n| `hnsw.ef_search` | 40 | Query-time candidate list. Higher = better recall, slower queries. Should be ≥ LIMIT. |\n\n**ef_search tuning (rough guidelines—actual results vary by dataset):**\n\n| ef_search | Approx Recall | Relative Speed |\n|-----------|---------------|----------------|\n| 40 | lower (~95% on some benchmarks) | 1x (baseline) |\n| 100 | higher  | ~2x slower |\n| 200 | very-high | ~4x slower |\n| 400 | near-exact | ~8x slower |\n\n```sql\n-- Set search parameter for session\nSET hnsw.ef_search = 100;\n\n-- Set for single query\nBEGIN;\nSET LOCAL hnsw.ef_search = 100;\nSELECT id, contents FROM items ORDER BY embedding <=> $1::halfvec(1536) LIMIT 10;\nCOMMIT;\n```\n\n## IVFFlat Index (Generally Not Recommended)\n\nDefault to HNSW. Use IVFFlat only when HNSW’s operational costs matter more than peak recall.\n\nChoose IVFFlat if:\n- Write-heavy or constantly changing data AND you're willing to rebuild the index frequently\n- You rebuild indexes often and want predictable build time and memory usage\n- Memory is tight and you cannot keep an HNSW graph mostly resident\n- Data is partitioned or tiered, and this index lives on colder partitions\n\nAvoid IVFFlat if you need:\n- highest recall at low latency\n- minimal tuning\n- a “set and forget” index\n\nNotes:\n- IVFFlat requires data to exist before index creation.\n- Recall depends on `lists` and `ivfflat.probes`; higher probes = better recall, slower queries.\n\nStarter config:\n```sql\nCREATE INDEX ON items\nUSING ivfflat (embedding halfvec_cosine_ops)\nWITH (lists = 1000);\n\nSET ivfflat.probes = 10;\n```\n\n## Quantization Strategies\n\n- Quantization is a memory decision, not a recall decision.\n- Use `halfvec` by default for storage and indexing.\n- Estimate HNSW index footprint as ~4–6 KB per 1536-dim `halfvec` (m=16) (order-of-magnitude); 3072-dim is ~2×; m=32 roughly doubles HNSW link/graph overhead.\n- If p95/p99 latency rises while CPU is mostly idle, the HNSW index is likely no longer resident in memory.\n- If `halfvec` doesn’t fit, use binary quantization + re-ranking.\n\n### Guidelines for 1536-dim vectors\n\nApproximate `halfvec` capacity at `m=16`, 1536-dim (assumes RAM mostly available for index caching):\n\n| RAM | Approx max halfvec vectors |\n|-----|----------------------------|\n| 16 GB | ~2–3M vectors |\n| 32 GB | ~4–6M vectors |\n| 64 GB | ~8–12M vectors |\n| 128 GB | ~16–25M vectors |\n\nFor 3072-dim embeddings, divide these numbers by ~2.  \nFor `m=32`, also divide capacity by ~2.\n\nIf the index cannot fit in memory at this scale, use binary quantization.\n\nThese are ranges, not guarantees. Validate by monitoring cache residency and p95/p99 latency under load.\n\n### Binary Quantization (For Very Large Datasets)\n\n32× memory reduction. Use with re-ranking for acceptable recall.\n\n```sql\n-- Table with generated column for binary quantization\nCREATE TABLE items (\n  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n  contents TEXT NOT NULL,\n  embedding halfvec(1536) NOT NULL,\n  embedding_bq bit(1536) GENERATED ALWAYS AS (binary_quantize(embedding)::bit(1536)) STORED\n);\n\nCREATE INDEX ON items USING hnsw (embedding_bq bit_hamming_ops);\n\n-- Query with re-ranking for better recall\n-- ef_search must be >= inner LIMIT to retrieve enough candidates\nSET hnsw.ef_search = 800;\nWITH q AS (\n  SELECT binary_quantize($1::halfvec(1536))::bit(1536) AS qb\n)\nSELECT *\nFROM (\n  SELECT i.id, i.contents, i.embedding\n  FROM items i, q\n  ORDER BY i.embedding_bq <~> q.qb -- computes binary distance using index\n  LIMIT 800\n) candidates\nORDER BY candidates.embedding <=> $1::halfvec(1536) -- computes halfvec distance (no index), more accurate than binary\nLIMIT 10;\n```\n\nThe 80× oversampling ratio (800 candidates for 10 results) is a reasonable starting point. Binary quantization loses precision, so more candidates are needed to find true nearest neighbors during re-ranking. Increase if recall is insufficient; decrease if re-ranking latency is too high.\n\n## Performance by Dataset Size\n\n| Scale | Vectors | Config | Notes |\n|-------|---------|--------|-------|\n| Small | <100K | Defaults | Index optional but improves tail latency |\n| Medium | 100K–5M | Defaults | Monitor p95 latency; most common production range |\n| Large | 5M+ | `ef_construction=100+` | Memory residency critical |\n| Very Large | 10M+ | Binary quantization + re-ranking | Add RAM or partition first if possible |\n\nTune `ef_search` first for recall; only increase `m` if recall plateaus and memory allows. Under concurrency, tail latency spikes when the index doesn't fit in memory. Binary quantization is an escape hatch—prefer adding RAM or partitioning first.\n\n## Filtering Best Practices\n\nFiltered vector search requires care. Depending on filter selectivity and query shape, filters can cause early termination (too few rows, missing results) or increase work (latency).\n\n### Iterative scan (recommended when filters are selective)\n\nBy default, HNSW may stop early when a WHERE clause is present, which can lead to fewer results than expected. Iterative scan allows HNSW to continue searching until enough filtered rows are found.\n\nEnable iterative scan when filters materially reduce the result set.\n\n```sql\n-- Enable iterative scans for filtered queries\nSET hnsw.iterative_scan = relaxed_order;\n\nSELECT id, contents\nFROM items\nWHERE category_id = 123\nORDER BY embedding <=> $1::halfvec(1536)\nLIMIT 10;\n```\n\nIf results are still sparse, increase the scan budget:\n\n```sql\nSET hnsw.max_scan_tuples = 50000;\n```\n\nTrade-off: increasing `hnsw.max_scan_tuples` improves recall but can significantly increase latency.\n\n**When iterative scan is not needed:**\n- The filter matches a large portion of the table (low selectivity)\n- You are prefiltering via a B-tree index\n- You are querying a single partition or partial index\n\n### Choose the right filtering strategy\n\n**Highly selective filters (under ~10k rows)**\nUse a B-tree index on the filter column so Postgres can prefilter before ANN.\n\n```sql\nCREATE INDEX ON items (category_id);\n```\n\n**Low-cardinality filters (few distinct values)**\nUse partial HNSW indexes per filter value.\n\n```sql\nCREATE INDEX ON items\nUSING hnsw (embedding halfvec_cosine_ops)\nWHERE category_id = 11;\n```\n\n**Many filter values or large datasets**\nPartition by the filter key to keep each ANN index small.\n\n```sql\nCREATE TABLE items (\n  embedding halfvec(1536),\n  category_id int\n) PARTITION BY LIST (category_id);\n```\n\n### Key rules\n\n- Filters that match few rows require prefiltering, partitioning, or iterative scan.\n- Always validate filtered queries by measuring p95/p99 latency and tuples visited under realistic load.\n\n### Alternative: pgvectorscale for label-based filtering\n\nFor large datasets with label-based filters, [pgvectorscale](https://github.com/timescale/pgvectorscale)'s StreamingDiskANN index supports filtered indexes on `smallint[]` columns. Labels are indexed alongside vectors, enabling efficient filtered search without the accuracy tradeoffs of HNSW post-filtering. See the pgvectorscale documentation for setup details.\n\n## Bulk Loading\n\n```sql\n-- COPY is fastest; binary format is faster but requires proper encoding\n-- Text format: '[0.1, 0.2, ...]'\nCOPY items (contents, embedding) FROM STDIN;\n-- Binary format (if your client supports it):\nCOPY items (contents, embedding) FROM STDIN WITH (FORMAT BINARY);\n\n-- Add indexes AFTER loading\nSET maintenance_work_mem = '4GB';\nSET max_parallel_maintenance_workers = 7;\nCREATE INDEX ON items USING hnsw (embedding halfvec_cosine_ops);\n```\n\n## Maintenance\n\n- **VACUUM regularly** after updates/deletes—stale entries may persist until vacuumed\n- **REINDEX** if performance degrades after high churn (rebuilds the graph from scratch)\n- For write-heavy workloads with frequent deletes, consider IVFFlat or partitioning by time using hypertables\n\n## Monitoring & Debugging\n\n```sql\n-- Check index size\nSELECT pg_size_pretty(pg_relation_size('items_embedding_idx'));\n\n-- Debug query performance\nEXPLAIN (ANALYZE, BUFFERS) SELECT id, contents FROM items ORDER BY embedding <=> $1::halfvec(1536) LIMIT 10;\n\n-- Monitor index build progress\nSELECT phase, round(100.0 * blocks_done / nullif(blocks_total, 0), 1) AS \"%\" \nFROM pg_stat_progress_create_index;\n\n-- Compare approximate vs exact recall\nBEGIN;\nSET LOCAL enable_indexscan = off;  -- Force exact search\nSELECT id, contents FROM items ORDER BY embedding <=> $1::halfvec(1536) LIMIT 10;\nCOMMIT;\n\n-- Force index use for debugging\nBEGIN;\nSET LOCAL enable_seqscan = off;\nSELECT id, contents FROM items ORDER BY embedding <=> $1::halfvec(1536) LIMIT 10;\nCOMMIT;\n```\n\n## Common Issues (Symptom → Fix)\n\n| Symptom | Likely Cause | Fix |\n|--------|--------------|-----|\n| Query does not use ANN index | Missing `ORDER BY` + `LIMIT`, operator mismatch, or implicit casts | Use `ORDER BY` with a distance operator that matches the index ops class; explicitly cast query vectors |\n| Fewer results than expected (filtered query) | HNSW stops early due to filter | Enable iterative scan; increase `hnsw.max_scan_tuples`; or prefilter (B-tree), use partial indexes, or partition |\n| Fewer results than expected (unfiltered query) | ANN recall too low | Increase `hnsw.ef_search` |\n| High latency with low CPU usage | HNSW index not resident in memory | Use `halfvec`, reduce `m`/`ef_construction`, add RAM, partition, or use binary quantization |\n| Slow index builds | Insufficient build memory or parallelism | Increase `maintenance_work_mem` and `max_parallel_maintenance_workers`; build after bulk load |\n| Out-of-memory errors | Index too large for available RAM | Use `halfvec`, reduce index parameters, or switch to binary quantization with re-ranking |\n| Zero or missing results | NULL or zero vectors | Avoid NULL embeddings; do not use zero vectors with cosine distance |","author":"@timescale","ownerProfile":null,"authorContacts":null,"sourceUrl":"https://github.com/timescale/pg-aiguide/tree/main/skills/pgvector-semantic-search","license":"Apache-2.0","category":null,"lang":"en","tokens":3394,"stars":0,"calls30d":1,"claimed":false,"visibility":"public","origin":"crawler","version":"0.1.0","createdAt":"2026-08-22","updatedAt":"2026-08-22","files":[],"requires":{"mcp":[],"tools":[]},"safety":{"flags":[],"scannedAt":"2026-08-22","hasScripts":false,"networkEndpoints":[]}}