{"id":43256,"date":"2026-03-01T20:09:15","date_gmt":"2026-03-01T19:09:15","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=43256"},"modified":"2026-03-01T22:35:59","modified_gmt":"2026-03-01T21:35:59","slug":"pgvector-a-guide-for-dba-part-2-indexes-update-march-2026","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/pgvector-a-guide-for-dba-part-2-indexes-update-march-2026\/","title":{"rendered":"pgvector, a guide for DBA – Part 2: Indexes (update march 2026)"},"content":{"rendered":"\n

Introduction<\/h2>\n\n\n\n

In Part 1<\/a> of this series, we covered what pgvector is, how embeddings work, and how to store them in PostgreSQL. We ended with a working similarity search — but on a sequential scan. That works fine for a demo table with 1,000 rows. It does not work for production.<\/p>\n\n\n\n

This post is about what comes next: indexes<\/strong>. Specifically, the three index families<\/strong> in the pgvector ecosystem as of February 2026 (HNSW, IVFFlat, and DiskANN), including two DiskANN implementations targeting different deployment models<\/strong>, what they’re good at, where they break, and the patterns you need, whether you’re the DBA tuning them or the developer looking to understand the the strenghts of PostgreSQL as a vector store. <\/p>\n\n\n\n

Everything in this post was tested on public dataset: 25,000 Wikipedia articles<\/strong> embedded with OpenAI’s text-embedding-3-large<\/code> at 3,072 dimensions<\/strong>, the maximum the model supports. The high number of dimension is a choice, to highlight some limitations for pedagogical reasons. You would be ok running and testing with lower dimensions or other embedding models, you might want to look into the RAG series, I will probably make a blog post on how to test embedding models against your data sets.
The environment is PostgreSQL 18 with pgvector 0.8.1 and pgvectorscale 0.9.0. <\/p>\n\n\n\n

All the SQL scripts, Python code, and Docker configuration are in the companion lab: lab\/06_pgvector_indexes<\/code><\/a>.<\/p>\n\n\n\n

\n\n\n\n

The Index Types<\/h2>\n\n\n\n

Before we dive in, here’s the landscape. pgvector ships with two built-in index types (HNSW and IVFFlat), and two DiskANN implementations are available from different vendors:<\/p>\n\n\n\n

<\/th>HNSW<\/th>IVFFlat<\/th>DiskANN (pgvectorscale)<\/th>DiskANN (pg_diskann)<\/th><\/tr><\/thead>
Provider<\/strong><\/td>pgvector<\/td>pgvector<\/td>Timescale<\/td>Microsoft<\/td><\/tr>
Availability<\/strong><\/td>Built-in<\/td>Built-in<\/td>Open source, self-hosted<\/td>Azure DB for PostgreSQL<\/td><\/tr>
Algorithm<\/strong><\/td>Multi-layer graph<\/td>Voronoi cell partitioning<\/td>Vamana graph + SBQ<\/td>Vamana graph + PQ<\/td><\/tr>
Best for<\/strong><\/td>General purpose<\/td>Fast build<\/td>Storage-constrained<\/td>Azure + high recall<\/td><\/tr>
Build time (25K, 3072d)<\/strong><\/td>29s<\/td>5s<\/td>49s<\/td>N\/A (Azure)<\/td><\/tr>
Index size<\/strong><\/td>193 MB<\/td>193 MB<\/td>21 MB<\/strong><\/td>Similar<\/td><\/tr>
Query time<\/strong><\/td>2-6 ms<\/td>2-10 ms<\/td>3 ms<\/td>~3 ms<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

That pgvectorscale number is not a typo. 21 MB vs 193 MB for the same data. W<\/p>\n\n\n\n

\n

Note:<\/strong> This post uses pgvectorscale for all DiskANN benchmarks since it’s the open-source, self-hosted option. We’ll compare both DiskANN implementations in detail in Section 3. pg_diskann is available only for Azure Flexible Server for PostgreSQL, the managed instance service from Microsoft. <\/p>\n<\/blockquote>\n\n\n\n

\n\n\n\n

HNSW: The Default Choice<\/h2>\n\n\n\n

HNSW (Hierarchical Navigable Small World) is the most commonly recommended index type for vector search. It builds a multi-layered graph where each node connects to its nearest neighbors, and search navigates this graph from top to bottom.<\/p>\n\n\n\n

The 2,000-Dimension Wall<\/h3>\n\n\n\n

Here’s the first thing you’ll hit with modern embedding models:<\/p>\n\n\n

\nCREATE INDEX idx_content_hnsw\nON articles USING hnsw (content_vector vector_cosine_ops)\nWITH (m = 16, ef_construction = 64);\n\nERROR:  column cannot have more than 2000 dimensions for hnsw index\n<\/pre><\/div>\n\n\n
The vector<\/code> type in pgvector has a 2,000-dimension limit<\/strong> for HNSW indexes. If you’re using text-embedding-3-large<\/code> (3,072 dimensions), or voyage-3-large<\/code> at its 2,048-dimension setting, this is a blocker.<\/p>\n\n\n\n
The workaround: halfvec<\/code><\/strong>.<\/p>\n\n\n
\n-- Step 1: Store a half-precision copy\nALTER TABLE articles ADD COLUMN content_halfvec halfvec(3072);\nUPDATE articles SET content_halfvec = content_vector::halfvec;\n\n-- Step 2: Index the halfvec column (limit: 4,000 dimensions)\nCREATE INDEX idx_content_hnsw_halfvec\nON articles USING hnsw (content_halfvec halfvec_cosine_ops)\nWITH (m = 16, ef_construction = 64);\n\nTime: 28974.392 ms (00:28.974)\n\nSELECT pg_size_pretty(pg_relation_size('idx_content_hnsw_halfvec')) AS hnsw_size;\n\n hnsw_size\n-----------\n 193 MB\n<\/pre><\/div>\n\n\n
29 seconds to build, 193 MB for 25,000 articles at 3,072 dimensions. That’s roughly 8 KB per row<\/strong> in the index alone.<\/p>\n\n\n\n
\n
Important:<\/strong> halfvec<\/code> stores each dimension in 2 bytes instead of 4. You lose some floating-point precision, but in practice the recall difference is negligible for similarity search. The storage savings are real: your halfvec column is 6 KB per row vs 12 KB for the full vector.<\/p>\n\n\n\n
Alternative:<\/strong> Instead of a separate column, you can create an expression index that casts on the fly: CREATE INDEX ... ON articles USING hnsw ((content_vector::halfvec(3072)) halfvec_cosine_ops);<\/code> The trade-off is that your queries must use the matching expression (content_vector::halfvec(3072) <=> ...<\/code>) for the planner to pick it up, which is harder to read in application code. The separate column approach gives cleaner queries.<\/p>\n<\/blockquote>\n\n\n\n
Tuning ef_search: The Recall vs Speed Dial<\/h3>\n\n\n\n
ef_search<\/code> controls how many candidates the HNSW algorithm considers during search. Higher values mean more candidates examined, better recall, but more work. The default is 40.<\/p>\n\n\n
\n-- ef_search = 40 (default)\nSET hnsw.ef_search = 40;\nEXPLAIN ANALYZE\nSELECT id, title, content_halfvec <=> (\n    SELECT content_halfvec FROM articles WHERE id = 1\n) AS distance\nFROM articles\nORDER BY content_halfvec <=> (\n    SELECT content_halfvec FROM articles WHERE id = 1\n)\nLIMIT 10;\n\nIndex Scan using idx_content_hnsw_halfvec on articles\n  Order By: (content_halfvec <=> (InitPlan 1).col1)\n  Index Searches: 1\n  Buffers: shared hit=551\nExecution Time: 6.004 ms\n\n-- ef_search = 100\nSET hnsw.ef_search = 100;\n\nIndex Scan using idx_content_hnsw_halfvec on articles\n  Buffers: shared hit=716\nExecution Time: 2.365 ms\n\n-- ef_search = 200\nSET hnsw.ef_search = 200;\n\nIndex Scan using idx_content_hnsw_halfvec on articles\n  Buffers: shared hit=883\nExecution Time: 2.542 ms\n<\/pre><\/div>\n\n\n
Wait — ef_search=100 was faster<\/em> than ef_search=40? Not really. Those numbers came from a warm cache (shared hit=551<\/code>, zero disk reads). The apparent speedup is a cache warming effect<\/strong>, not a property of the algorithm. To prove this, I restarted PostgreSQL and ran the full sweep from cold:<\/p>\n\n\n\n
ef_search<\/th>Execution Time<\/th>Buffers<\/th>read (disk)<\/th><\/tr><\/thead>
40 (cold)<\/td>91 ms<\/td>hit=189, read=362<\/strong><\/td>362 pages from disk<\/td><\/tr>
100<\/td>33 ms<\/td>hit=616, read=132<\/strong><\/td>fewer cold pages<\/td><\/tr>
200<\/td>22 ms<\/td>hit=850, read=65<\/strong><\/td>even fewer<\/td><\/tr>
40 (warm)<\/td>0.8 ms<\/strong><\/td>hit=551, read=0<\/strong><\/td>all cached<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
The second run at ef_search=40 clocked 0.8 ms — faster than any ef_search=100 or 200 run. On a warm cache, all three (40\/100\/200) land in the 0.8-5 ms range. The variance is cache state, not algorithmic shortcuts. The real cost cliff is at ef_search=400<\/strong> where the optimizer switches plans entirely:<\/p>\n\n\n
\n-- ef_search = 400\nSET hnsw.ef_search = 400;\n\nSort  (actual time=364.693..364.695 rows=10.00 loops=1)\n  Sort Key: ((articles.content_halfvec <=> (InitPlan 1).col1))\n  Sort Method: top-N heapsort  Memory: 25kB\n  ->  Seq Scan on articles  (actual time=0.143..356.482 rows=24700.00 loops=1)\n        Filter: (content_halfvec IS NOT NULL)\n        Rows Removed by Filter: 300\nExecution Time: 364.724 ms\n<\/pre><\/div>\n\n\n
The planner chose a Seq Scan + Sort path.<\/strong> At ef_search=400, PostgreSQL’s cost model estimated the index scan would be more expensive than reading every row sequentially, and the query went from 2.5 ms to 365 ms.<\/p>\n\n\n\n
This is your optimizer flip-flop<\/strong>. It’s not a bug — it’s the planner doing its job. But it means you need to be aware of the threshold.<\/p>\n\n\n\n
ef_search<\/th>Execution Time<\/th>Plan<\/th>Buffers<\/th><\/tr><\/thead>
40<\/td>6.0 ms<\/td>Index Scan<\/td>551<\/td><\/tr>
100<\/td>2.4 ms<\/td>Index Scan<\/td>716<\/td><\/tr>
200<\/td>2.5 ms<\/td>Index Scan<\/td>883<\/td><\/tr>
400<\/td>365 ms<\/strong><\/td>Seq Scan<\/strong><\/td>209,192<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n
DBA Takeaway:<\/strong> For HNSW on halfvec(3072), stay in the ef_search 40-200 range. Past that, you’re fighting the optimizer. If you need ef_search > 200 for recall, you probably need a bigger m<\/code> parameter at build time.<\/p>\n<\/blockquote>\n\n\n\n
Build Parameters: m and ef_construction<\/h3>\n\n\n\n
m<\/code> is the number of connections per node in the graph. ef_construction<\/code> is the candidate list size during build. Higher values = better graph quality but slower builds and (potentially) larger indexes.<\/p>\n\n\n
\n-- m=16, ef_construction=64 (default-ish)\nTime: 28,974 ms    Size: 193 MB\n\n-- m=32, ef_construction=128\nTime: 54,077 ms    Size: 193 MB\n<\/pre><\/div>\n\n\n
At 25K rows, doubling m<\/code> doubled the build time but didn’t change the index size. The size effect becomes more visible at larger scales. In general: start with m=16, ef_construction=64 and only increase if recall is insufficient after tuning ef_search<\/strong>.<\/p>\n\n\n\n
\n\n\n\n
IVFFlat: Fast Build<\/h2>\n\n\n\n
IVFFlat (Inverted File with Flat quantization) partitions the vector space into Voronoi cells using k-means clustering, then searches only the cells closest to the query vector.<\/p>\n\n\n\n
Same Dimension Limit<\/h3>\n\n\n\n
IVFFlat has the same 2,000-dimension limit<\/strong> as HNSW for the vector<\/code> type. Same workaround:<\/p>\n\n\n
\nCREATE INDEX idx_content_ivfflat\nON articles USING ivfflat (content_halfvec halfvec_cosine_ops)\nWITH (lists = 25);\n\nTime: 5008.765 ms (00:05.009)\n\nSELECT pg_size_pretty(pg_relation_size('idx_content_ivfflat'));\n-- 193 MB\n<\/pre><\/div>\n\n\n
5 seconds to build vs 29 for HNSW. The index size is nearly identical (193 MB vs 193 MB), but IVFFlat builds 5.8x faster<\/strong>.<\/p>\n\n\n\n
The lists<\/code> parameter controls the number of Voronoi cells. The pgvector documentation recommends: rows \/ 1000<\/code> for tables up to 1M rows<\/strong>, sqrt(rows)<\/code> for larger tables. For 25,000 articles: 25000 \/ 1000 = 25 lists, giving roughly 1,000 rows per cell. A common mistake is applying sqrt(rows)<\/code> to small tables — that gives 158 lists here, creating cells with only ~167 rows each, which fragments the index and causes the optimizer to flip to sequential scan at surprisingly low probe counts.<\/p>\n\n\n\n
Tuning Probes: How Many Cells to Search<\/h3>\n\n\n\n
probes<\/code> controls how many Voronoi cells are searched at query time. Default is 1 — fast but low recall. A good starting point is sqrt(lists)<\/code>. Here’s the sweep with lists=25:<\/p>\n\n\n
\nSET ivfflat.probes = 1;\n-- Index Scan, Execution Time: 1.0 ms, Buffers: 571\n\nSET ivfflat.probes = 2;\n-- Index Scan, Execution Time: 3.7 ms, Buffers: 1,944\n\nSET ivfflat.probes = 3;\n-- Index Scan, Execution Time: 4.4 ms, Buffers: 2,793\n\nSET ivfflat.probes = 4;\n-- Index Scan, Execution Time: 5.9 ms, Buffers: 3,937\n<\/pre><\/div>\n\n\n
And then:<\/p>\n\n\n
\nSET ivfflat.probes = 5;\n\nSort  (actual time=152.548..152.549 rows=10.00 loops=1)\n  Sort Key: ((articles.content_halfvec <=> (InitPlan 1).col1))\n  ->  Seq Scan on articles  (actual time=0.144..148.844 rows=24700.00 loops=1)\n        Filter: (content_halfvec IS NOT NULL)\nExecution Time: 152.584 ms\n<\/pre><\/div>\n\n\n
Same story as HNSW. At probes=5 (searching 5 of 25 cells = 20%), the optimizer decided a sequential scan was cheaper. The query went from 6 ms to 153 ms.<\/p>\n\n\n\n
probes<\/th>Execution Time<\/th>Plan<\/th><\/tr><\/thead>
1<\/td>1.0 ms<\/td>Index Scan<\/td><\/tr>
2<\/td>3.7 ms<\/td>Index Scan<\/td><\/tr>
3<\/td>4.4 ms<\/td>Index Scan<\/td><\/tr>
4<\/td>5.9 ms<\/td>Index Scan<\/td><\/tr>
5<\/td>153 ms<\/strong><\/td>Seq Scan<\/strong><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n
Takeaway:<\/strong> For IVFFlat with 25 lists, the optimizer flips between probes=4 and probes=5. With sqrt(25) = 5<\/code>, you’re right at the tipping point. Use SET LOCAL ivfflat.probes = 3<\/code> or 4<\/code> for a good recall\/speed balance. On larger tables (100K+ rows), the flip happens at much higher probe counts because sequential scans become proportionally more expensive.<\/p>\n<\/blockquote>\n\n\n\n
SET LOCAL: The Production Pattern<\/h3>\n\n\n\n
Never set ivfflat.probes<\/code> at the session level in production. Use SET LOCAL<\/code> inside a transaction:<\/p>\n\n\n
\nBEGIN;\n    SET LOCAL ivfflat.probes = 3;\n    SELECT id, title, content_halfvec <=> (\n        SELECT content_halfvec FROM articles WHERE id = 1\n    ) AS distance\n    FROM articles\n    ORDER BY content_halfvec <=> (\n        SELECT content_halfvec FROM articles WHERE id = 1\n    )\n    LIMIT 10;\nCOMMIT;\n\n-- Verify: probes reverted to default\nSHOW ivfflat.probes;  -- 1\n<\/pre><\/div>\n\n\n
The setting reverts automatically after COMMIT\/ROLLBACK. No global state leakage. Do the same for hnsw.ef_search<\/code>.<\/strong><\/p>\n\n\n\n
\n
DO NOT PLAY AROUND WITH SESSION PARAMETERS ON PRODUCTION.<\/strong> Use SET LOCAL<\/code> inside a transaction, or set them at the function\/procedure level. Session-level changes persist until disconnect and can affect every query on that connection, including connection pooler reuse. Hinting is not a strategy. <\/p>\n<\/blockquote>\n\n\n\n
\n\n\n\n
DiskANN: Leveraging B-TREE index principle <\/h2>\n\n\n\n
DiskANN is provided by the pgvectorscale<\/a> project from Timescale (SQL extension name: vectorscale<\/code>). It implements the DiskANN algorithm with Statistical Binary Quantization (SBQ)<\/strong> compression built in. <\/p>\n\n\n\n
No 2,000-Dimension Wall<\/h3>\n\n\n\n
Unlike HNSW and IVFFlat, DiskANN supports the vector<\/code> type natively up to 16,000 dimensions<\/strong> — no halfvec workaround needed:<\/p>\n\n\n
\nCREATE INDEX idx_content_diskann ON articles USING diskann (content_vector)\nWITH (storage_layout = memory_optimized);\n\nNOTICE:  Starting index build with num_neighbors=50, search_list_size=100,\n         max_alpha=1.2, storage_layout=SbqCompression.  -- memory_optimized maps to SBQ\nNOTICE:  Indexed 24700 tuples\nTime: 49140.736 ms (00:49.141)\n\nSELECT pg_size_pretty(pg_relation_size('idx_content_diskann'));\n-- 21 MB\n<\/pre><\/div>\n\n\n
49 seconds to build, but 21 MB<\/strong>. Compare:<\/p>\n\n\n
\n        indexname         |  size  | size_bytes\n--------------------------+--------+------------\n idx_content_diskann      | 21 MB  |   22,511,616\n idx_content_hnsw_halfvec | 193 MB |  202,350,592\n idx_content_ivfflat      | 193 MB |  202,522,624\n<\/pre><\/div>\n\n\n
DiskANN is 9x smaller<\/strong> than HNSW and IVFFlat on the same data. SBQ compression is the default — storage_layout = memory_optimized<\/code> is what you get if you don’t specify a layout. Specifying it explicitly (as in the CREATE INDEX<\/code> above) is good practice for readability. The alternative plain<\/code> layout stores full vectors in the index and does not compress.<\/p>\n\n\n\n
Query performance is competitive:<\/p>\n\n\n
\nIndex Scan using idx_content_diskann on articles\n  Order By: (content_vector <=> (InitPlan 1).col1)\n  Buffers: shared hit=1437 read=132\nExecution Time: 2.915 ms\n<\/pre><\/div>\n\n\n
3 ms for a 3,072-dimension nearest neighbor search on 25K articles. On the same data, HNSW does it in 2-6 ms and IVFFlat in 2-10 ms. All three are in the same ballpark for query speed.<\/p>\n\n\n\n
\n
Takeaway:<\/strong> DiskANN is the right choice when your HNSW index outgrows shared_buffers<\/code>. At 193 MB for 25K rows, HNSW on halfvec(3072) would reach ~77 GB at 10 million rows, this is well beyond what most buffer pools can keep hot. DiskANN’s 9x compression keeps the navigational structure cached while full vectors stay in the heap, fetched only for the final rescore of a few dozen candidates. Same access pattern as a B-tree: compact index in memory, selective heap lookups on demand. The trade-off is longer build times and fewer operator class options (vector type only, no halfvec\/bit\/sparsevec).<\/p>\n<\/blockquote>\n\n\n\n
CREATE INDEX CONCURRENTLY<\/h3>\n\n\n\n
As of pgvectorscale 0.9.0, DiskANN supports CONCURRENTLY<\/code>:<\/p>\n\n\n
\nCREATE INDEX CONCURRENTLY idx_content_diskann\nON articles USING diskann (content_vector)\nWITH (storage_layout = memory_optimized);\n<\/pre><\/div>\n\n\n
This is critical for production — you can build the index without locking the table. HNSW and IVFFlat also support CREATE INDEX CONCURRENTLY<\/code>.<\/p>\n\n\n\n
Why These Dimension Limits Exist: Buffer Pages and Bits<\/h3>\n\n\n\n
If the 2,000 \/ 4,000 \/ 16,000 dimension limits seem arbitrary, they’re not. The intuition<\/strong> comes from how PostgreSQL stores data: in 8 KB buffer pages<\/strong> (8,192 bytes). Every index tuple — including the vector representation in a vector index — has to fit within a page. The fewer bytes per dimension, the more dimensions you can pack.<\/p>\n\n\n\n
Here’s the back-of-the-envelope arithmetic:<\/p>\n\n\n\n
Encoding<\/th>Bytes per dimension<\/th>Theoretical max in 8 KB<\/th><\/tr><\/thead>
vector<\/code> (float32)<\/td>4 bytes<\/td>8,192 \/ 4 = 2,048<\/strong><\/td><\/tr>
halfvec<\/code> (float16)<\/td>2 bytes<\/td>8,192 \/ 2 = 4,096<\/strong><\/td><\/tr>
4-bit quantized (PQ)<\/td>0.5 bytes<\/td>8,192 * 2 = 16,384<\/strong><\/td><\/tr>
1-bit binary (SBQ)<\/td>0.125 bytes<\/td>8,192 * 8 = 65,536<\/strong><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
The theoretical numbers explain the intuition<\/strong> — why halfvec doubles the limit and quantized encodings push it further. The actual limits<\/strong> are slightly lower because of page headers, tuple overhead, and the index metadata stored alongside each vector. For HNSW, each page also stores neighbor connection lists<\/strong> (up to m * 2<\/code> neighbor IDs per node); for IVFFlat, each page carries centroid references and list pointers<\/strong>. These eat into the available space, which is why pgvector’s HNSW sets 2,000 (not 2,048) and pgvectorscale’s DiskANN sets 16,000 (not 16,384). But the pattern is unmistakable: the fewer bits per dimension, the more dimensions you can fit in a page<\/strong>.<\/p>\n\n\n\n
This is why DiskANN can handle 16,000 dimensions where HNSW on vector<\/code> tops out at 2,000 — DiskANN stores a compressed representation in the index page, not the full vector. And why halfvec doubled the HNSW\/IVFFlat limit from 2,000 to 4,000: half the bytes per dimension, twice the capacity.
This is more than enough for the vast majority of use cases. Most embedding models in production today default to 768\u20131536 dimensions, well within the 2,000-dimension limit. This also proves how future proof the curent vector store implementation is on PostgreSQL. <\/p>\n\n\n\n
How pgvectorscale Compresses: Statistical Binary Quantization (SBQ)<\/h3>\n\n\n\n
pgvectorscale’s DiskANN from Tiger Data uses a method called Statistical Binary Quantization<\/strong> (SBQ). The idea is deceptively simple: for each dimension, replace the float value with a 1 or 2-bit code.<\/p>\n\n\n\n
1-bit mode<\/strong> (default for dimensions >= 900): Each dimension is compressed to a single bit. But not naively — standard binary quantization uses 0.0 as the threshold (positive = 1, negative = 0), which works poorly because real embedding distributions are rarely centered on zero. SBQ instead computes the per-dimension mean<\/strong> across all vectors during index build and uses that as the threshold:<\/p>\n\n\n
\nif value &gt; mean_of_this_dimension \u2192 1\nelse \u2192 0\n<\/pre><\/div>\n\n\n
A 3,072-dimension float32 vector (12,288 bytes) becomes a 3,072-bit string (384 bytes). That’s 32x compression<\/strong>. During search, the query vector is also SBQ-encoded, and distances are computed using XOR + popcount<\/strong> on the bit strings — which modern CPUs execute in a single instruction.<\/p>\n\n\n\n
2-bit mode<\/strong> (default for dimensions < 900): Each dimension gets two bits, encoding four “zones” based on the z-score (how many standard deviations from the mean). This gives finer granularity at 16x compression instead of 32x.<\/p>\n\n\n\n
The accuracy loss is real but small. On common benchmarks, SBQ achieves 96-99% recall compared to exact search. The rescore step (controlled by diskann.query_rescore<\/code>, default 50) compensates: after the graph traversal finds the top-50 candidates using quantized distances, pgvectorscale fetches the full-precision vectors from the heap<\/strong> and re-computes exact distances to produce the final top-10.<\/p>\n\n\n\n
What’s stored where:<\/strong><\/p>\n\n\n\n
Location<\/th>What’s stored<\/th>Accessed when<\/th><\/tr><\/thead>
Index pages<\/strong><\/td>SBQ-compressed vectors + graph edges<\/td>Every query (graph traversal)<\/td><\/tr>
Heap (table)<\/strong><\/td>Full float32 vectors<\/td>Only during rescore (top-N candidates)<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
This two-tier architecture is why DiskANN achieves 21 MB index size: the index only stores 384-byte compressed vectors, not 12 KB originals. The full vectors stay in the table, fetched only for the final re-ranking of a few dozen candidates.<\/p>\n\n\n\n
Microsoft’s pg_diskann: A Different Approach<\/h3>\n\n\n\n
There’s a second DiskANN implementation for PostgreSQL: Microsoft’s pg_diskann<\/strong>, currently documented and distributed for Azure Database for PostgreSQL Flexible Server<\/a>. It uses the same Vamana graph algorithm but a fundamentally different compression strategy: Product Quantization (PQ)<\/strong>.<\/p>\n\n\n\n
Where SBQ asks “is this dimension above or below the mean?”, Product Quantization asks “which of 16 codewords best represents this group of dimensions?”<\/p>\n\n\n\n
Here’s how PQ works, step by step:<\/p>\n\n\n\n
    \n
  1. Divide the vector into chunks.<\/strong> A 3,072-dimension vector is split into, say, 1,024 chunks of 3 dimensions each.<\/li>\n\n\n\n
  2. Train a codebook per chunk.<\/strong> For each chunk, k-means clustering finds 16 representative codewords (centroids). Why 16? Because 16 values fit in 4 bits<\/strong> — a single hex digit.<\/li>\n\n\n\n
  3. Encode each chunk as a 4-bit symbol.<\/strong> During index build, each chunk of 3 dimensions is replaced by the index (0-15) of its closest codeword. The entire 3,072-dimension vector becomes 1,024 symbols of 4 bits each = 512 bytes<\/strong>.<\/li>\n\n\n\n
  4. Decode via lookup table at query time.<\/strong> To compute the distance to a query vector, you pre-compute the distance from the query to all 16 codewords for each chunk, creating a 16-row x 1,024-column lookup table<\/strong>. Then for each stored vector, you sum up the table entries corresponding to its symbols. No floating-point multiplication needed — just table lookups and additions.<\/li>\n<\/ol>\n\n\n\n
    The compression is dramatic: 3,072 dimensions * 4 bytes = 12,288 bytes \u2192 512 bytes with PQ. That’s 24x compression<\/strong>, in the same ballpark as SBQ’s 32x.<\/p>\n\n\n\n
    Comparing the Two DiskANN Implementations<\/h3>\n\n\n\n
    <\/th>pgvectorscale (Timescale)<\/th>pg_diskann (Microsoft)<\/th><\/tr><\/thead>
    Compression<\/strong><\/td>SBQ (1-2 bits\/dim)<\/td>Product Quantization (4 bits\/chunk)<\/td><\/tr>
    Compression ratio<\/strong><\/td>32x (1-bit) or 16x (2-bit)<\/td>~24x (depends on chunks)<\/td><\/tr>
    How it works<\/strong><\/td>Per-dimension thresholding<\/td>Codebook lookup per chunk<\/td><\/tr>
    Distance computation<\/strong><\/td>XOR + popcount (very fast)<\/td>Table lookup + sum<\/td><\/tr>
    Trainable<\/strong><\/td>Minimal (just means + stddev)<\/td>Heavy (k-means per chunk)<\/td><\/tr>
    Max dimensions<\/strong><\/td>16,000<\/td>16,000<\/td><\/tr>
    Availability<\/strong><\/td>Open source, self-hosted<\/td>Azure Database for PostgreSQL<\/td><\/tr>
    License<\/strong><\/td>PostgreSQL License<\/td>Distributed via Azure<\/td><\/tr>
    Iterative scan<\/strong><\/td>No<\/td>Yes (relaxed\/strict, ON by default)<\/td><\/tr>
    PG version<\/strong><\/td>PG 14-18 (self-hosted)<\/td>Azure DB for PostgreSQL<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
    Both implementations share the core DiskANN algorithm (Vamana graph) and the two-phase search pattern (compressed scan + full-precision rescore). The difference is how<\/em> they compress:<\/p>\n\n\n\n