GenAI DisciplinesFind My LevelMatch a JobBytesDocsAbout

Account

Free lesson · GenAI Data Engineering

Configure AlloyDB with pgvector and ScaNN indexing

Set up AlloyDB as a managed PostgreSQL service with pgvector extension. Configure ScaNN indexes (Google's ANN algorithm, 10x faster than standard pgvector) alongside HNSW.

Course: GenAI Data Pipelines · Chapter 8 · Vector Store Operations on AlloyDB

Free to read — no subscription required.

Subscribe to run the labs

Introduction

When you scale a vector store past a few million embeddings, naive sequential scans collapse from milliseconds to seconds and break the latency budget of any interactive RAG application sitting on top. Teams that deploy pgvector without choosing the right approximate-nearest-neighbor (ANN) index end up paying for compute that should never have been spent — or, worse, ship a "fast" index whose recall is too low to surface the right context. By the end of this lesson you'll be able to enable pgvector on AlloyDB, create both ScaNN and HNSW indexes against the same embeddings table, and benchmark them at matched recall so you can defend your index choice with numbers instead of folklore.

Key Terminology

  • pgvector — PostgreSQL extension that adds a vector column type and distance operators (<->, <=>, <#>); the substrate every index in this lesson sits on.
  • HNSW (Hierarchical Navigable Small World) — Graph-based ANN index that answers queries by greedy traversal of a multi-layer proximity graph; the default high-recall option in stock pgvector.
  • ScaNN — Google's tree-and-quantization ANN index, exposed as a first-class AlloyDB index method, that reaches HNSW-equivalent recall at lower latency on large corpora.
  • recall@k — Fraction of the true top-k nearest neighbors returned by an approximate search; the only metric that makes a ScaNN-vs-HNSW latency comparison meaningful.
  • CONCURRENTLY — PostgreSQL index-build clause that avoids an ACCESS EXCLUSIVE lock on the table; required when adding ANN indexes to a live embeddings table.

Concepts

pgvector as the substrate

The vector extension stores fixed-dimensionality float arrays inside PostgreSQL and exposes the distance operators (cosine <=>, L2 <->, inner product <#>) that every ANN index method ultimately satisfies. The column dimensionality is fixed at table-create time and MUST match the embedding model's output exactly — a mismatch either rejects the insert or silently truncates the vector depending on the PostgreSQL version. Pick the dimensionality from the model card before you write the DDL, not after.

ScaNN vs HNSW: two ANN philosophies

HNSW maintains a multi-layer proximity graph and resolves a query through greedy traversal; you trade recall for latency by tuning the build-time m and the query-time ef_search. It loses ground on very large corpora because graph hops become cache-unfriendly. ScaNN partitions the vector space into a balanced tree of leaves and runs quantized distance computations within the candidate leaves only, dramatically reducing per-query distance evaluations at the cost of a small recall ceiling. On AlloyDB, ScaNN is a first-class USING scann index method that you create against the same vector column an HNSW index would target (see Code Walkthrough).

Recall-matched benchmarking

A "faster" index is meaningless until you fix the recall target. The canonical comparison: pick a target such as recall@10 ≥ 0.95, tune each index's runtime parameters (ef_search for HNSW, num_leaves_to_search for ScaNN) until both clear the target on a held-out query set, then compare latency percentiles. Compare raw latency without matching recall and you are picking the index that returns the wrong answers fastest.

Code Walkthrough

Setting Up pgvector on AlloyDB

The foundation is enabling pgvector and creating properly typed vector columns. The column dimensionality must match your embedding model exactly -- a mismatch causes silent data truncation or insertion errors depending on the PostgreSQL version.

VectorStoreSetup encapsulates the DDL operations needed to configure AlloyDB for vector search. The class creates the pgvector extension, defines the embeddings table with a correctly dimensioned vector column, and creates both ScaNN and HNSW indexes for comparative benchmarking. The setup method runs all operations in a single transaction to ensure atomic schema creation.

Code snippet python 
1import asyncpg
2
3class VectorStoreSetup:
4    def __init__(
5        self,
6        db_pool: asyncpg.Pool,
7        dimensions: int = 1536,
8    ):
9        self.db_pool = db_pool
10        self.dimensions = dimensions
11
12    async def setup(self) -> None:
13        async with (
14            self.db_pool.acquire() as conn
15        ):
16            await conn.execute(
17                "CREATE EXTENSION "
18                "IF NOT EXISTS vector"
19            )
20            await conn.execute(f"""
21                CREATE TABLE IF NOT EXISTS
22                embeddings (
23                    id BIGSERIAL PRIMARY KEY,
24                    chunk_id TEXT UNIQUE
25                        NOT NULL,
26                    content TEXT NOT NULL,
27                    embedding vector(
28                        {self.dimensions}
29                    ),
30                    tenant_id TEXT,
31                    source_doc TEXT,
32                    model TEXT,
33                    created_at TIMESTAMPTZ
34                        DEFAULT NOW(),
35                    updated_at TIMESTAMPTZ
36                        DEFAULT NOW()
37                )
38            """)
39
40    async def create_hnsw_index(
41        self,
42        m: int = 16,
43        ef_construction: int = 256,
44    ) -> None:
45        async with (
46            self.db_pool.acquire() as conn
47        ):
48            await conn.execute(f"""
49                CREATE INDEX
50                    CONCURRENTLY
51                    IF NOT EXISTS
52                    idx_embeddings_hnsw
53                ON embeddings
54                USING hnsw (
55                    embedding
56                    vector_cosine_ops
57                )
58                WITH (
59                    m = {m},
60                    ef_construction =
61                        {ef_construction}
62                )
63            """)
64
65    async def create_scann_index(
66        self,
67        num_leaves: int = 1000,
68    ) -> None:
69        async with (
70            self.db_pool.acquire() as conn
71        ):
72            await conn.execute(f"""
73                CREATE INDEX
74                    CONCURRENTLY
75                    IF NOT EXISTS
76                    idx_embeddings_scann
77                ON embeddings
78                USING scann (
79                    embedding
80                    vector_cosine_ops
81                )
82                WITH (
83                    num_leaves = {num_leaves}
84                )
85            """)
  • Lines 3-10: The setup class accepts a connection pool and dimensionality parameter. Setting dimensions at construction time ensures consistency between the table schema and the embedding model output.
  • Lines 12-34: The setup method creates the pgvector extension and the embeddings table. The vector column uses the parameterized dimensionality, and the table includes metadata columns (tenant_id, source_doc, model) that support partitioning and filtering.
  • Lines 36-54: HNSW index creation uses CONCURRENTLY to avoid locking the table during index build. The m and ef_construction parameters are configurable for tuning experiments.
  • Lines 56-72: ScaNN index creation on AlloyDB uses the scann index method with num_leaves controlling the number of partitions in the tree structure. More leaves improve recall on large datasets at the cost of increased memory usage.

Benchmarking ScaNN vs HNSW

The performance comparison between ScaNN and HNSW must control for recall level: comparing raw latency without matching recall is meaningless because a faster but less accurate index is not a valid substitute. The benchmark fixes a target recall (e.g., 95%) and measures the latency each index achieves at that recall.

IndexBenchmark runs standardized queries against both ScaNN and HNSW indexes, computing recall by comparing approximate results against brute-force exact results. The benchmark reports latency percentiles (p50, p95, p99) at matched recall levels.

Code snippet python 
1import time
2import numpy as np
3
4class IndexBenchmark:
5    def __init__(
6        self,
7        db_pool: asyncpg.Pool,
8    ):
9        self.db_pool = db_pool
10
11    async def exact_search(
12        self,
13        query_vector: list[float],
14        k: int = 10,
15    ) -> list[str]:
16        async with (
17            self.db_pool.acquire() as conn
18        ):
19            rows = await conn.fetch(
20                """
21                SET LOCAL
22                    enable_indexscan = off;
23                SELECT chunk_id
24                FROM embeddings
25                ORDER BY embedding <=>
26                    $1::vector
27                LIMIT $2
28                """,
29                str(query_vector),
30                k,
31            )
32        return [r["chunk_id"] for r in rows]
33
34    async def approximate_search(
35        self,
36        query_vector: list[float],
37        k: int = 10,
38        ef_search: int = 100,
39    ) -> tuple[list[str], float]:
40        async with (
41            self.db_pool.acquire() as conn
42        ):
43            await conn.execute(
44                "SET LOCAL "
45                "hnsw.ef_search = $1",
46                ef_search,
47            )
48            start = time.perf_counter()
49            rows = await conn.fetch(
50                """
51                SELECT chunk_id
52                FROM embeddings
53                ORDER BY embedding <=>
54                    $1::vector
55                LIMIT $2
56                """,
57                str(query_vector),
58                k,
59            )
60            latency = (
61                time.perf_counter() - start
62            )
63        return (
64            [r["chunk_id"] for r in rows],
65            latency,
66        )
67
68    def compute_recall(
69        self,
70        exact: list[str],
71        approx: list[str],
72    ) -> float:
73        exact_set = set(exact)
74        approx_set = set(approx)
75        return (
76            len(exact_set & approx_set)
77            / len(exact_set)
78        )
  • Lines 11-28: The exact_search method disables index scans to force a brute-force sequential scan, providing the ground-truth nearest neighbors for recall computation. This is expensive but necessary for accuracy measurement.
  • Lines 30-52: The approximate_search method uses the configured index (ScaNN or HNSW) and returns both the results and the query latency. The ef_search parameter allows tuning the accuracy-latency tradeoff at query time.
  • Lines 54-61: The compute_recall method calculates the overlap between approximate and exact results as a ratio, producing the recall@k metric that drives index tuning decisions.
Loading diagram...

Do's and Don'ts

Having just walked through index creation and recall-matched benchmarking, the following rules distill those configuration choices into checks you can apply before shipping a pgvector + ScaNN setup to production.

Do's

  1. ✓Pin the pgvector column dimensionality to the exact output size of your embedding model (e.g., vector(1536) for OpenAI text-embedding-3-small) so insertions fail loudly on mismatch instead of silently truncating.
  2. ✓Build both ScaNN and HNSW indexes with CREATE INDEX CONCURRENTLY so the embeddings table stays writable during multi-minute index builds on production data.
  3. ✓Benchmark ScaNN vs HNSW at a fixed target recall (e.g., recall@10 ≥ 0.95) by comparing approximate results against a brute-force scan with enable_indexscan = off, and report p50/p95/p99 latency at that matched recall.

Don'ts

  1. ✗Don't compare raw ScaNN and HNSW latency without matching recall — a faster index at lower recall is not a valid substitute and will mislead the index choice.
  2. ✗Don't create the vector index before bulk-loading embeddings; build the index after the initial load so ScaNN's tree partitioning and HNSW's graph reflect the real data distribution.
  3. ✗Don't reuse a pgvector column built for one embedding model with a different model's output — the cosine distances become meaningless across embedding spaces even when dimensionalities happen to match.

This lesson is free to read. Its 3 hands-on labs — real code, in a cloud IDE — are part of the GenAI Data Engineering subscription.

Subscribe to run the labs Already a subscriber? Sign in →

From · cancel anytime

More free lessons in GenAI Data Pipelines

All free lessons in GenAI Data Engineering →