GitHub user yjhjstz added a comment to the discussion: [Proposal] Bundle `pgvector` as a built-in extension in Apache Cloudberry
Thanks @tuhaihe for raising this. I fully agree that vector search should be a first-class, out-of-the-box capability in Cloudberry, and the MCP use case mentioned above makes it more pressing. I'd like to propose a different direction, though: instead of vendoring pgvector as-is, **re-implement vector similarity search natively for Cloudberry** based on the decoupled design from PostgreSQL-V (Purdue DB Group, CIDR 2026: [paper](https://cs.purdue.edu/homes/csjgwang/pubs/CIDR26_PostgreSQLVector.pdf), [code](https://github.com/purduedb/PostgreSQL-V/tree/cidr)). We would keep pgvector's SQL interface so existing applications keep working unchanged. ### Why not just pgvector pgvector builds HNSW/IVFFlat on PostgreSQL's page-oriented storage. Every graph hop goes through the buffer manager, index pages compete with table pages for `shared_buffers`, and every index modification is WAL-logged at page granularity. The PostgreSQL-V paper compares the two approaches with the same index type and the same index parameters. Their decoupled version is 2.9–3.5× faster than pgvector for HNSW and 6.5–8.9× faster for IVF_FLAT, and it also builds faster and produces a smaller index. With DiskANN it is 3.2–4.0× faster than pgvectorscale. In short, it performs about as well as native Faiss, hnswlib and DiskANN. In Cloudberry these costs are paid on every primary, and all of that index WAL is also shipped to the mirrors. If we bundle pgvector now, we will also have to support its on-disk format for a long time. ### The core idea Decouple the vector index from the page-based engine and split it into two parts: - **Content**: an LSM-style structure. Inserts go into an in-memory memtable, which is searched by brute force. When the memtable is full, it is sealed, and a background worker builds an immutable segment from it with a native library (Faiss, hnswlib or DiskANN). Small segments are merged over time, and deletes are recorded in a per-segment bitmap. The graph is never updated in place, and the index content writes no WAL. - **Metadata**: small "status pages" that hold `<VID, TID>` pairs. They are normal, WAL-logged index pages and are the single source of truth. After crash recovery they are consistent with the table, and the index content is rebuilt from them: entries with `VID <= flushed_VID` only need their bitmaps fixed, and newer entries are re-read from the table into the memtable. The paper reports 239 ms to recover 10M indexed vectors plus 100K unflushed ones. The index still plugs in through `IndexAmRoutine`, so the planner and executor work the same way as with pgvector. The SQL surface (the `vector`/`halfvec`/`sparsevec` types, the `<->`/`<#>`/`<=>` operators, and the `hnsw`/`ivfflat` access methods with their opclasses) can stay pgvector-compatible. ### Why it fits Cloudberry well - **AO, AOCS and PAX already work this way.** Append-only segment files plus a visibility map for deletes have the same shape as immutable segments plus delete bitmaps. Most analytical data in Cloudberry lives in these table AMs, which are a poor fit for page-structured, update-in-place index structures. - **Less WAL to mirrors.** On every insert, only the small status-page records go through the WAL stream, instead of graph page updates. - **Pluggable index libraries.** Faiss (MIT), hnswlib (Apache-2.0) and DiskANN (MIT) all use ASF Category A licenses, and we already build C++ for ORCA. New index types (DiskANN, quantization, GPU) can be adopted without re-implementing each one on top of pages. - **Explicit memory control.** Each host runs many segments. A decoupled index can have its own memory budget, tied into resource groups, instead of competing with table pages in `shared_buffers`. ### Why re-implement instead of vendoring PostgreSQL-V The `cidr` branch is explicitly a research prototype: it targets single-node PostgreSQL and heap tables only, uses a hard-coded storage directory, and has fixed-size shared slots for 4 indexes and 100 segments. We should take the design rather than the code, and solve the MPP-specific problems properly: 1. **Mirrors, failover and `gprecoverseg`.** Status pages replicate through WAL for free, but sealed segment files don't. One option is to keep segment files inside the data directory, so that `pg_basebackup` and full recovery copy them, and then either WAL-log each immutable segment once when it is sealed or rebuild it locally on the mirror at promotion. This needs its own design discussion. 2. **Table-AM agnostic.** TIDs and recovery-time fetches must go through the table AM (heap, AO row, AOCS, PAX). That includes AO VACUUM compaction, which moves tuples and changes their TIDs. 3. **Distributed top-k.** Each segment runs an ordered index scan with the LIMIT pushed down, a sorted Gather Motion merges the results, and filtered search is evaluated per segment. Today ORCA falls back to the Postgres planner for any `ORDER BY` with an ordering operator (#1653), so ORCA support for KNN ordering should be part of this work. 4. **Operations.** Background flush/merge workers per segment, resource-group accounting, `gpexpand` and redistribution, segment files tracked in the catalog, and cleanup on DROP/TRUNCATE. ### Proposed path 1. Agree on the pgvector-compatible SQL surface as the stable contract, so that users and MCP integrations don't need to change whatever we ship first. 2. Build the decoupled index AM in-tree (memtable, Faiss/hnswlib segments, status pages and recovery), starting with heap and AO/AOCS. 3. Integrate with MPP: mirrors, ORCA KNN support and resource groups. 4. Add DiskANN and quantization for data larger than memory. I'm glad to write a detailed design doc and start on a prototype. Feedback is very welcome, especially on the mirror replication strategy and on whether pgvector and the native AM should coexist during the transition. GitHub link: https://github.com/apache/cloudberry/discussions/2043#discussioncomment-19361891 ---- This is an automatically sent email for [email protected]. To unsubscribe, please send an email to: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
