Building An Integrated Vector Database System in PostgreSQL

Building An Integrated Vector Database System in PostgreSQL Jiayi Liu

Te Guo

Jianguo Wang

Purdue University {liu4127; guo777; csjgwang}@purdue.edu

arXiv:2608.15994v1 [cs.DB] 17 Aug 2026

ABSTRACT This paper presents PostgreSQL-V 2.0, a scalable integrated vector database system inside PostgreSQL. Existing PostgreSQL-based vector search systems such as pgvector, embed vector indexes into PostgreSQL’s page-oriented storage engine, incurring significant overhead that leads to a huge performance gap with specialized vector databases. In our earlier work, we introduced PostgreSQL-V 1.0, which addresses this issue by separating vector index structures from PostgreSQL’s storage engine, enabling vector search performance close to that of native vector index libraries while preserving SQL compatibility. However, we find that PostgreSQL-V 1.0 has three limitations that matter for real-world workloads: it only supports a single connection (without concurrency), recovery time grows with index size, and physical replication is unsupported. We further present PostgreSQL-V 2.0, which closes all three gaps. PostgreSQL-V 2.0’s concurrency support enables fully concurrent vector searches and updates across PostgreSQL’s multi-process backends, delivering up to 36.4× the throughput of PostgreSQLV 1.0 while serving 32 concurrent clients. PostgreSQL-V 2.0’s fast crash recovery keeps cost independent of total index size, remaining near 20 ms while PostgreSQL-V 1.0’s grows into seconds-scale. PostgreSQL-V 2.0’s physical replication support extends physical replication to the decoupled index, preserving index consistency on standbys without burdening the primary node. Together, these advances make PostgreSQL-V 2.0 a fully concurrent, crash-resilient, and replication-ready vector database inside PostgreSQL.

1

INTRODUCTION

Vector similarity search, which retrieves the top-𝑘 nearest vectors of a query vector under a given distance metric, has become a critical primitive in modern data infrastructure for AI, powering retrieval-augmented generation (RAG), semantic search, and recommendation systems [17, 19, 22, 25, 31, 32, 35, 38]. It must be served at low latency, under concurrent load, and with the reliability guarantees that production deployments demand. Two architectural approaches for vector databases have emerged. Specialized vector databases (e.g., Faiss [6], Milvus [39], Pinecone [14]) are purpose-built for vector workloads and deliver high search performance by designing their storage and execution engine around vector search from the ground up. Integrated systems (e.g., pgvector [12], PASE [41], AlloyDB AI [1], GaussDB-Vector [37], and PostgreSQL-V 1.0 [26]) add vector search capabilities to an existing database engine and are attractive in practice because vector data can be stored and queried alongside relational attributes in a single system, under a unified transaction and consistency model, without data migration or introducing a separate operational tier. Integrated systems, however, have struggled to match the search performance of specialized ones. The dominant reason is architectural: existing implementations embed vector indexes into the

host’s storage engine, inheriting constraints that are a poor fit for vector search. PostgreSQL is a representative case: its vector search extensions (e.g., pgvector [12] and PASE [41]) must express vector indexes in a page-oriented format, which introduces significant overhead relative to native vector index libraries [42] and forecloses optimizations that specialized vector databases readily adopt. In our earlier work, published at CIDR 2026 [26], we introduced PostgreSQL-V 1.0, a system that addresses this gap by decoupling the vector index from PostgreSQL’s storage engine, allowing it to be backed by native libraries while remaining compatible with PostgreSQL’s query engine. The decoupled index adopts an LSMstyle framework for efficient updates and a lightweight consistency mechanism that keeps the index crash-safe without reintroducing the page-oriented format that decoupling was meant to escape. PostgreSQL-V 1.0 demonstrated the feasibility of the approach and achieved performance on par with native libraries. However, there are three key limitations in PostgreSQL-V 1.0: it serves only a single connection (i.e., concurrency is unsupported), crash recovery time grows linearly with index size, and physical replication is unsupported. Section 2.3 details each gap.

Contributions. Based on PostgreSQL-V 1.0, we present PostgreSQLV 2.0 (Scalable Decoupled Vector Database), an integrated vector database within PostgreSQL that closes the above three gaps. (1) Concurrency Support (Section 4). PostgreSQL-V 2.0 converts index access from PostgreSQL’s multi-process model to a multi-thread model within a single process, enabling fully concurrent vector search and update across PostgreSQL’s backends while preserving pluggable use of native vector index libraries. It further minimizes the cold-start window after a restart through an mmap-based mode that shifts the bulk of the segment loading cost off the critical path, dramatically reducing the time before search can resume. (2) Fast Crash Recovery (Section 5). PostgreSQL-V 2.0 guarantees crash consistency for the decoupled index, just as PostgreSQL-V 1.0 does, but at significantly lower cost that, unlike PostgreSQL-V 1.0’s, stays constant as the index grows. It redesigns how crash-recovery state is tracked so that recovery touches only a small portion of the index that may be inconsistent upon a crash, leaving the far larger portion of the index untouched. (3) Physical Replication (Section 6). PostgreSQL-V 2.0 supports physical replication of the decoupled index via a novel two-channel design: a consistency-critical channel preserves index consistency on the standby through custom WAL records, while a performance-critical channel propagates index binaries asynchronously to sustain search efficiency on standbys.

Experimental Overview. We evaluate PostgreSQL-V 2.0 with up to 32 concurrent clients (Section 7.1), demonstrating the benefit of concurrency support: for pure vector search, it delivers up to 36.4× the throughput of PostgreSQL-V 1.0. PostgreSQL-V 2.0 also sustains up to 4.2× the throughput of pgvector on in-memory indexes and up to 19.4× the throughput of pgvectorscale on disk-based indexes. Under a dynamic workload of concurrent inserts, deletes, and searches (Section 7.2), PostgreSQL-V 2.0 sustains roughly 5.1× the throughput of pgvector while maintaining stable recall as pgvector’s degrades over time. Turning to failure resilience, we show that the recovery overhead stays independent of index size, remaining near 20 ms even as PostgreSQL-V 1.0’s grows to over 4 seconds at comparable scale (Section 7.3), and that the mmap cold-start mode reduces the post-restart unavailability window by nearly two orders of magnitude (Section 7.4). Finally, for physical replication, we demonstrate that serving standbys imposes negligible overhead on the primary while the standby preserves search quality identical to the primary’s (Section 7.5). Overall, we believe this work represents a significant milestone toward enabling efficient vector search in relational databases.

predicates (e.g., a WHERE filter over scalar attributes such as a category or time range), so the database can further restrict the search results within the same statement. These predicates are evaluated by PostgreSQL’s executor on the heap tuples fetched after the index scan, that is, hybrid search uses post-filtering (e.g., in pgvector [12]) without any involvement of the vector index. Combining vector similarity with relational operations in a single query is a practical advantage of building vector search into a relational engine. Reuse of PostgreSQL architecture. Because these extensions store their index data in PostgreSQL’s storage engine and route all accesses through its buffer manager, the vector index is managed along with the heap data pages and inherits PostgreSQL’s mature infrastructure. In particular, a vector index update and its corresponding heap update within a transaction both emit write-ahead log (WAL) records that are force flushed at commit, so both are recovered together after a crash via log replay, keeping the index consistent with the heap. The same WAL mechanism also supports replication: a standby replays the WAL records, reproducing the heap’s and index’s modifications and staying up to date with the primary. By conforming to the page-oriented index framework, these extensions obtain durability, index consistency, and support for replication without reimplementing them.

Open-Source. The source code of PostgreSQL-V 2.0 is available at: https://github.com/purduedb/PostgreSQL-V.git.

2 BACKGROUND 2.1 Implementing Vector Search in PostgreSQL

2.2

Limitations of Vector Search in PostgreSQL

The convenience of reusing PostgreSQL’s architecture comes at a cost. These extensions must integrate the vector index into PostgreSQL’s storage engine, which requires expressing it in a pageoriented format. This requirement is a poor match for vector indexes, and we highlight three resulting limitations.

PostgreSQL has been extended to support vector search by several extensions, including pgvector [12], PASE [41], and pgvectorscale [13]. We describe the major components of their design. Storing embeddings. Embeddings are stored as values of a dedicated column data type (e.g., the vector type introduced by pgvector) that holds a fixed-dimensional vector. A table therefore keeps each vector alongside its relational attributes in the same row, so vector and non-vector data are managed under one schema.

Performance degradation. Modern vector indexes (e.g., HNSW [28], IVF_FLAT [23], IVF_PQ [23], and NSG [21]) are memory-centric and traverse their structures through raw pointers. Implementing them within the page storage framework forces every access through the page buffer, introducing page indirection, buffering overhead, and CPU cost a native library avoids. This overhead affects both search and update operations alike. A recent case study [42] measured this gap: a PostgreSQL-based implementation is several times slower than a native library under identical index structures and parameters. This page-versus-pointer mismatch persists even for disk-oriented indexes such as DiskANN [36]: within PostgreSQL’s storage engine, their in-memory components are still reached through the page buffer rather than directly, and can be evicted by heap pages that share the buffer pool. Index updates face a further difficulty: vector indexes such as HNSW are inefficient to update in place (i.e., modifying the index structure directly), since in-place updates degrade index quality and are slow [34, 40]. They are better maintained out-of-place, as in an LSM-style design [27, 30, 39]. However, PostgreSQL’s storage engine makes out-of-place updates hard to adopt.

Vector indexes via IndexAmRoutine. A vector index is registered as a new index access method through PostgreSQL’s IndexAmRoutine interface, the same extension point used by built-in indexes such as B-tree. The interface defines the callbacks that PostgreSQL’s core engine invokes over the lifetime of an index: the build callback that constructs an index over a vector column, the insert callback that adds an entry for a new tuple (the storage format of a table row), the scan callbacks that return the matching tuple identifiers (TIDs) in order, and the vacuum callbacks that remove index entries for dead tuples. By implementing this interface, an extension exposes a vector index that the executor can use transparently. A top-𝑘 similarity query is then written in plain SQL as an ORDER BY on a distance operator (e.g., < column > <-> < query >) together with a LIMIT 𝑘 clause. When a vector index is available on the column, PostgreSQL answers the top-𝑘 query with an index scan, invoking the scan callbacks described above to return the 𝑘 nearest vectors as TIDs. The executor then fetches the corresponding rows from the heap (PostgreSQL’s table storage) and applies a visibility check under PostgreSQL’s MVCC (multi-version concurrency control), so that only rows visible to the current transaction are returned.

Engineering overhead. Beyond runtime cost, re-engineering a memory-centric index into a fixed-size page layout demands substantial engineering effort [41]. Pointer-based structures must be re-expressed as page and offset references, large vectors may span multiple pages, and the index logic must interact correctly with the buffer manager to ensure crash safety. This complexity increases

Hybrid search. Because vectors live in ordinary tables, a query may combine an ORDER BY/LIMIT top-𝑘 clause with other relational 2

implementation and maintenance burden and makes it harder to track and adopt the rapidly evolving landscape of vector indexes.

pages reattach the decoupled index to PostgreSQL’s crash-recovery guarantees without forcing the index itself into the page-oriented format.

Difficulty exploiting emerging hardware. Finally, binding the index to PostgreSQL’s page structure and buffer manager makes it difficult to exploit emerging hardware. GPU-accelerated search [11, 29], for example, requires the index to reside in GPU memory using a layout optimized for GPU kernels. In contrast, a page-oriented index requires deep integration with PostgreSQL, whose queryprocessing pipeline currently lacks mature GPU support.

2.3

Limitations of PostgreSQL-V 1.0. The decoupling principle is elegant, and PostgreSQL-V 1.0 demonstrates the feasibility of the design with promising performance results. However, it remains a prototype with three important gaps. First, the decoupled index is not designed for PostgreSQL’s multi-process execution model: because the index cannot be shared across processes, PostgreSQL-V 1.0 lacks concurrency support and is confined to a single connection. Second, because the status pages record every active entry in a flat layout, crash recovery must process all of them arbitrarily, so recovery time and the resulting unavailability window grow with the index size. Third, the design provides no support for replication, on which production deployments rely for read scaling.

Decoupling the Vector Index

In our earlier work [26], we introduced PostgreSQL-V 1.0, which addresses the limitations above via a novel decoupling principle. Rather than fitting the index into a page-oriented layout, it implements the index as an independent component outside PostgreSQL’s storage engine. It still conforms to IndexAmRoutine, so the query engine invokes it transparently. Decoupling lets the index be backed by a native library (e.g., Faiss or hnswlib) with direct memory access, eliminating overhead from the page buffer. It also makes the index pluggable, so that new index implementations can be substituted as the field advances. Decoupling further enables the index to adopt an LSM-style framework, the out-of-place update scheme favored by many specialized vector databases [18, 39]: new vectors are appended to an in-memory mutable memtable that is later sealed and built asynchronously into an immutable index segment, with smaller index segments periodically merged. Because updates modify only in-memory memtables and never touch existing segments, this scheme avoids the in-place updates that vector indexes handle poorly. For a top-𝑘 query, the similarity search runs on every memtable and segment, and their top-𝑘 results are merged into the final answer. Decoupling, however, leaves the index without crash consistency. For a normal page-based index, PostgreSQL logs its modifications together with the heap’s as WAL records and replays them after a crash, recovering the index to a state consistent with the heap. In contrast, as the decoupled index lives outside the data page buffer, its updates generate no WAL records, so a crash can leave it out of sync with the heap: vectors in the memtable will be lost even if the heap committed them, while flushed segments may retain vectors the heap never committed. PostgreSQL-V 1.0 resolves this by separating index metadata from index contents, tracking the metadata in a newly introduced structure called status pages. The metadata is small: for each active index entry, it records the entry’s identifier together with the TID of the corresponding heap tuple. PostgreSQL-V 1.0 implements status pages as ordinary PostgreSQL pages, so they reside in the data page buffer and are logged and recovered by PostgreSQL’s WAL mechanism, which guarantees that they stay consistent with the heap across crashes. The actual vector index structures remain in the decoupled component and are not logged. After a crash, PostgreSQL conducts its recovery procedure, which restores the status pages to a consistent state. They then serve as a single source of truth: by comparing the recovered metadata against the decoupled index, the system repairs the index, fetching missing vectors from the heap via their recorded TIDs and appending them to memtables, and invalidating entries that should not exist. In effect, the status

3

SYSTEM OVERVIEW

Figure 1 shows the architecture of PostgreSQL-V 2.0, a vector database built inside PostgreSQL that can run in both single-node and multi-node settings. The figure illustrates the multi-node setting with a primary and a standby node. PostgreSQL-V 2.0 is fully compatible with existing PostgreSQL-based vector search systems such as pgvector [12], pgvectorscale [13], and PostgreSQL-V 1.0 [26], sharing the same embedding storage, vector index registration via IndexAmRoutine, and hybrid search interface (Section 2.1). As an integrated vector database, PostgreSQL-V 2.0 builds heavily on PostgreSQL’s existing architecture, while extending it with new components to overcome the performance degradation identified in Section 2.2 and address the limitations in PostgreSQL-V 1.0 (Section 2.3). Normal Processing. PostgreSQL-V 2.0 fits into PostgreSQL’s architecture, where each client connection is served by a backend process under the postmaster. While heap tables remain in PostgreSQL’s data page buffer, PostgreSQL-V 2.0 follows PostgreSQL-V 1.0 in adopting the decoupling approach and LSM-style index framework, managing vector indexes as memtables and immutable segments allocated outside the page buffer. As shown by the orange arrows in Figure 1, a backend inserts a vector into the mutable memtable; once the memtable fills, it is sealed into an immutable memtable; a background flusher builds an index over the immutable memtable and persists it as an on-disk index segment; the VISP (Vector Index Service Process) loads the resulting segment into its local memory. PostgreSQL-V 2.0 removes PostgreSQL-V 1.0’s single-connection limitation by introducing VISP, a dedicated process that exclusively owns all index segments, each consisting of an index, a TID array, and a bitmap. VISP serves search requests from all backends over a shared ring buffer, making the decoupled index fully concurrent for the first time (Section 4). Crash Recovery. PostgreSQL-V 2.0 leverages PostgreSQL’s existing WAL and recovery infrastructure, shown in Figure 1 (the WAL buffer and the startup process that conducts log replay), by storing status pages in the data page buffer so they are logged and recovered alongside heap pages. PostgreSQL-V 2.0 then layers a customized recovery procedure on top to restore the decoupled index, as PostgreSQL-V 1.0 also does. However, PostgreSQL-V 2.0 3

Figure 1: System Overview of PostgreSQL-V 2.0 redesigns the status pages and the recovery mechanism so that recovery time stays minimal and constant, in contrast to PostgreSQLV 1.0, whose recovery cost grows with the index size, becoming substantial at scale (Section 5).

the flexibility benefits of decoupling, since it disables the modular reuse of existing vector indexing libraries. Thus, we propose a new design that addresses this challenge at its root. We introduce the Vector Index Service Process (VISP), a dedicated process that exclusively owns all vector index instances in its local memory and provides index service to all PostgreSQL processes. The VISP manages every index segment, each consisting of a vector index, an array of TIDs that maps each indexed vector back to its heap tuple, and a deletion bitmap. Deletions within a segment are applied softly by flipping the corresponding bit in the bitmap rather than modifying its index structure directly. With these structures, the VISP executes vector searches on behalf of all backend processes. A vector search request flows from the issuing backend to the VISP and back. The backend enqueues its request into a shared ring buffer, the communication channel to the VISP. VISP drains the buffer and serves requests with a thread pool. This arrangement gives two levels of parallelism. Across requests, the ring buffer collects searches from all backends and the thread pool serves them concurrently, letting a single VISP handle many connections at once. Within a request, the search fans out across LSM segments in parallel, and their per-segment top-𝑘 results are merged into the top-𝑘 across segments before the issuing backend receives them. A search must also cover the memtables, which hold the most recently inserted vectors (Section 3). Because memtables store raw vectors contiguously as arrays rather than relying on pointer-based index structures, they reside in PostgreSQL’s static shared memory and are directly accessible by all backend processes. Thus, while the VISP searches the flushed segments, the requesting backend concurrently scans the memtables for their top-𝑘 candidates, then merges the memtable and segment results into the final output. The cost of scanning the memtables is therefore absorbed into the segment search rather than adding extra latency. The VISP also runs maintenance tasks over flushed segments: loading newly flushed ones, merging smaller segments into larger ones [24], and rebuilding a segment when the deletion ratio in the bitmap exceeds the threshold. These run in the background, off the critical path of foreground searches.

Physical Replication. PostgreSQL-V 2.0 supports physical replication in two paths, shown in Figure 1: it extends PostgreSQL’s replication mechanism with customized WAL log records and callback functions invoked during replay (green arrows) to propagate consistency-critical index operations to the standby, and introduces a file server and fetcher workers (blue arrows) to propagate index segments asynchronously. This gives PostgreSQL-V 2.0 physical replication support that PostgreSQL-V 1.0 lacks entirely (Section 6).

4

CONCURRENCY SUPPORT

The first gap in PostgreSQL-V 1.0 [26] is the decoupled index’s inability to be shared across PostgreSQL’s processes (identified in Section 2.3), which confines it to a single connection. PostgreSQLV 2.0 removes this limitation and makes the decoupled index fully compatible with PostgreSQL’s concurrency guarantees. Two properties are required: multiple connections must be able to access the same index simultaneously (Section 4.1 and 4.2), and concurrent transactions must remain correct under PostgreSQL’s isolation levels (Section 4.3).

4.1

Converting Multi-Process Concurrency to Multi-Thread

Supporting multiple connections in PostgreSQL-V 2.0 is non-trivial due to PostgreSQL’s multi-process execution model, where each connection runs in its own process (see Section 3). This execution model poses fundamental challenges to integrating vector indexes in a decoupled, plug-in fashion. All existing vector index libraries (e.g., Faiss [6], HNSWlib [8], Knowhere [9]) assume a multi-threaded concurrency model and rely on raw pointers within a virtual address space, which prevents direct sharing across PostgreSQL processes. A straightforward approach is to implement a vector index that adopts a multi-process execution model. However, doing so not only introduces substantial engineering complexity but also undermines 4

4.2

More on The Vector Index Service Process

full in-memory residency in the background, loading entire segments into VISP’s local memory. This shifts the bulk of the loading cost off the critical path: early queries pay only the cost of page faults as the OS loads segment data on demand, so the index becomes searchable in nearly two orders of magnitude less time than loading entire segments upfront. Once the background promotion completes, subsequent queries are served from the pure in-memory index for better performance, and the memory mapping is released. We are aware of the concerns raised by Crotty et al. [20], who caution against using mmap for DBMS file I/O due to correctness and performance issues. The correctness concern, namely transactional safety risk, arises because the OS can flush dirty pages before a transaction commits, which stems from the DBMS having no control over how and when mapped pages are written back to disk. However, PostgreSQL-V 2.0 maps only immutable, read-only segment files that are fully persisted before mapping, so no writes go through the mapping and no dirty pages or write-ordering races can arise. The correctness issue therefore does not apply to PostgreSQLV 2.0’s use of mmap. Beyond correctness, Crotty et al. also identify two performance hazards: I/O stalls from blocking page faults on pages not yet resident in the page cache, and TLB shootdowns, where releasing a memory mapping forces the OS to interrupt every core to invalidate cached address translations. In PostgreSQL-V 2.0’s cold-start use, the I/O stalls from page faults are intentional: they replace the far worse outcome of complete search unavailability after a restart. The remaining hazard, TLB shootdowns from releasing mappings, is bounded because PostgreSQL-V 2.0 releases the cold-start mapping only once, when background promotion to full in-memory residency completes. Importantly, the authors’ guidance [20] permits mmap for read-only data used transiently rather than as a permanent buffer pool replacement, which matches PostgreSQL-V 2.0’s design precisely. PostgreSQL-V 2.0 therefore scopes its use of mmap deliberately to avoid the risks above.

Centralizing all vector-index operations in a single VISP may raise two natural concerns: that it is a single point of failure, and that it is a performance bottleneck. We address both. 4.2.1 Single Point of Failure One might worry that a VISP crash leaves the index in an inconsistent state or renders vector search unavailable to all backends. We argue that the impact of such a crash is minimal, for two reasons. First, a VISP crash never corrupts existing index segments. Flushed segments are immutable once written: VISP’s foreground work only searches them, and its background merges and rebuilds produce new segments rather than modifying existing ones in place. All insertions, meanwhile, are confined to memtables managed by backend processes, not VISP. Second, although a VISP crash also causes a termination and restart of all PostgreSQL processes and a subsequent crash recovery, this is general PostgreSQL behavior, triggered by the abnormal exit of any process attached to shared memory, not something specific to VISP: an ordinary process (e.g., a backend) crashing would cause the identical restart. However, PostgreSQL-V 2.0 minimizes the resulting downtime by addressing its two costs separately: a lightweight crash-recovery mechanism (Section 5) restores the index to a consistent state with negligible overhead, while the remaining cost, making the index searchable again, is the cold-start problem we address next. Cold-start availability. PostgreSQL-V 2.0’s LSM-based framework answers every query by searching all index segments, which must reside in memory to be searched for indexes such as HNSW. After a restart, no segments are in memory. In the default mode, all segments are loaded in bulk, triggered by the first query against the index. The index is not searchable until the entire load finishes. This is a sharper problem than slow queries: it is a window of search unavailability, not just increased latency. It also distinguishes PostgreSQL-V 2.0 from page-oriented vector indexes within PostgreSQL’s storage engine (e.g., pgvector), which fault in only the index pages they visit, amortizing the cost across queries so that search stays available, albeit slower at first. We emphasize that this window is not specific to PostgreSQL-V 2.0, but common to any vector database that adopts native vector index libraries, since they require in-memory state before they can be searched. A memory-centric index such as HNSW [28] resides entirely in memory, and even a disk-based index such as DiskANN [36] has in-memory components that must be loaded first. The window can be substantial (we measured 5s for an HNSW index of 10M vectors and 43s for 100M, see Section 7.4). We close this window with an mmap-based cold-start mode, exploiting the mmap support in many vector index libraries (e.g., Faiss [6], Knowhere [9]). mmap is an OS-provided mechanism that maps file contents directly into a process’s virtual address space, allowing the file to be accessed through ordinary pointers. Pages load lazily on first access: touching a page not yet resident in memory triggers a page fault, causing the OS to read it from disk into the system page cache. In the mmap-based cold-start mode, the index segments are mapped into the address space via mmap on the first query and become searchable right away, while VISP promotes segments to

4.2.2 Performance Bottleneck A second concern is that a single VISP could become a performance bottleneck. Although there is only one VISP process, it employs multiple threads that collectively leverage the server’s CPU resources. Our experiments in Section 7.1.1 show that throughput scales with increasing concurrency rather than saturating, indicating that the VISP is not the limiting factor. The actual bottleneck lies in the server’s CPU and memory resources, which we address by adding support for physical replication (Section 6).

4.3

Concurrency Control in PostgreSQL-V 2.0

The second goal of PostgreSQL-V 2.0 is to ensure the correctness of concurrent transactions under different isolation levels in PostgreSQL when the decoupled vector index is involved. This requires two properties: visibility correctness, guaranteed entirely at the heap level and unaffected by the index, and physical correctness, guaranteed by the index’s own concurrency control over its structures. Visibility correctness. PostgreSQL employs MVCC, allowing multiple physical versions of a row (heap tuples) to coexist, with visibility determined by transaction-specific snapshots. Index scans, whether on built-in or decoupled ones, may return TIDs referencing dead or invisible versions. PostgreSQL-V 2.0 preserves correctness 5

by enforcing visibility checks at the heap level after fetching the corresponding tuples. Consequently, decoupling the vector index does not affect visibility correctness, which remains guaranteed by the heap. Physical correctness. Beyond visibility, the decoupled vector index ensures physical correctness under concurrent access. In PostgreSQL-V 2.0, concurrent reads and writes occur only on the mutable memtable given the properties of the LSM. To support scalable concurrency, we adopt a lock-minimal, append-only design. Writers reserve insertion slots in the memtable using lightweight synchronization primitives and write vector data without holding global locks. Once a write completes, a ready bit is atomically set. Concurrent scans check this bit and skip unready entries, preventing access to partially written data. This design guarantees correctness while keeping concurrent access efficient.

5

Figure 2: Status Page Design

CRASH RECOVERY

5.2

The second gap in PostgreSQL-V 1.0 [26] is recovery scalability: its crash recovery mechanism (as described in Section 2.3) ties the recovery overhead to the total size of the index, so the larger the data grows, the longer the system stays unavailable after a crash. PostgreSQL-V 2.0 addresses this gap by redesigning the status pages to exploit properties of the LSM framework and revising the recovery mechanism accordingly, keeping recovery cost bounded and independent of index size. We first identify why the original layout scales poorly (Section 5.1), then present the redesigned status pages (Section 5.2), how they are maintained during normal processing (Section 5.3), and how the index is recovered (Section 5.4).

5.1

Redesigning the Status Pages

To exploit the LSM structure, PostgreSQL-V 2.0 replaces the original flat status-page layout with one organized by SegmentID. SegmentID is the identifier the LSM framework assigns to each memtable upon its creation, in monotonically increasing order. It continues to track the memtable’s vectors throughout their lifetime, including across flushes, merges, and rebuilds. Figure 2 shows the resulting structure. Status pages record the TID of every active vector in the index, and organize these TID records by SegmentID. Since all vectors in a memtable share one SegmentID, their TIDs are stored contiguously across a page list of status pages, with the pointer to the next page kept in each page’s opaque-data field (the fixed-size region at the end of a PostgreSQL page reserved for customized metadata). The SegmentID and its page list are recorded together in a dedicated memtable array page: each tuple stores its SegmentID along with a PageHead and PageTail pointer locating the first and last page of that list, so new TIDs can be appended to the tail without traversing it. Because flushed segments are immutable and durable, their entries no longer need tracking, so PostgreSQL-V 2.0 removes the corresponding page list from the status pages once a memtable is flushed. Without reusing those pages, the status pages would grow without bound. Therefore, PostgreSQL-V 2.0 adopts the free-page list design used by some built-in indexes in PostgreSQL, such as GiST [7]: the free-page list tracks reclaimed status pages so they can be reused by subsequent memtables, and its metadata is stored in the status metadata page, as shown in the upper part of Figure 2. This page also holds MaxMemtableId, the SegmentID of the current mutable memtable.

Why the Original Layout Fails to Scale

The recovery cost of the original status pages in PostgreSQL-V 1.0 [26] stems from the following two design choices. Flat, segment-oblivious layout. PostgreSQL-V 1.0’s design does not exploit the properties of the LSM framework of the decoupled vector index. The original status pages record all index entries in a flat layout. However, tracking entries of flushed index segments serves no purpose, as they are immutable and durable on disk. Besides, the flat layout does not separate segment entries from volatile memtable entries, so recovery is forced to process the entire index rather than only the volatile portion that can actually be lost upon a crash. Recording vacuum on durable segments. The original status pages in PostgreSQL-V 1.0 also record modifications introduced by the vacuum procedure, which cleans up dead tuples left behind by MVCC, by physically removing their metadata from status pages. Since vacuum sweeps the whole index, including flushed segments, recovery must in turn process the whole index for those removals. We observe that this work is avoidable. Status pages exist to provide transactional durability: changes must be recoverable upon transaction commit. Vacuum, however, runs asynchronously outside any transaction, so its durability is independent of transaction boundaries. It suffices to flush the index vacuum changes to durable storage before returning to the index vacuum caller, which guarantees the correct durability ordering without routing vacuum through the status pages at all.

5.3

Normal Processing

Status pages are updated together with the decoupled vector index whenever the index update operations defined in IndexAmRoutine are invoked by the PostgreSQL core engine. Index insertions add entries and vacuum removes them. We describe how insertions and vacuum maintain the new status pages. When a vector is inserted, the core engine writes the tuple to heap pages, logs it to the WAL, and invokes the index’s insert operation, which appends the vector to the mutable memtable. 6

PostgreSQL-V 2.0 then appends the corresponding TID to the mutable memtable’s page list in status pages. If the current memtable is full, the insert first seals it and creates a new mutable memtable to hold the vector. The status pages follow this by advancing MaxMemtableId in the status metadata page and registering the new memtable in the memtable array page. A new page list is assigned to the new SegmentID, taken from the free-page list when reclaimed pages are available or allocated fresh otherwise. Finally, when PostgreSQL-V 2.0 detects during an insert that an immutable memtable has just been flushed, it removes that memtable from the status pages by clearing its tuple in the memtable array page and moving its page list to the free-page list, keeping the status pages bounded to the entries of unflushed memtables. All of these status page updates generate WAL records, and both the heap’s and the status pages’ WAL records are flushed upon transaction commit, guaranteeing their durability and consistency after recovery. Deletion in PostgreSQL is lazy: deleting a heap tuple only updates the MVCC metadata in its header to record the deletion, rather than removing the tuple. Query correctness is preserved by the heap-level visibility check applied after each search (Section 4.3). Accordingly, IndexAmRoutine exposes no direct delete. Deleted tuples are removed once they are dead, that is, invisible to all active transactions. PostgreSQL’s vacuum process periodically scans heap pages to collect all dead tuples and triggers index vacuum to clear their index entries before it removes the heap tuples. PostgreSQLV 2.0 clears each index entry depending on where it resides. If it lies in a memtable, PostgreSQL-V 2.0 removes its TID from the status pages, generating a WAL record, and marks the entry in the memtable’s in-memory bitmap. If it lies in a flushed segment, PostgreSQL-V 2.0 marks that segment’s bitmap. At the end of the index vacuum, the affected segment bitmaps are force-flushed to disk, making them durable without involving status pages. A crash that occurs before the force flush completes may cause a bitmap update to be lost, leaving some index entries that vacuum was meant to remove still marked valid. However, this causes no inconsistency or correctness issue: heap vacuum only removes a tuple after its index vacuum completes, so no dangling entries can result, and these remaining entries, though pointing to dead tuples, are still filtered out by the heap-level visibility check (Section 4.3), keeping search correct.

5.4

(SegmentID 102). If PostgreSQL-V 2.0 crashes before this transaction commits, its WAL records may never reach disk, so after recovery the heap contains none of these vectors—neither the final 1K of memtable 100 nor any of memtables 101 and 102. The recovered status pages likewise keep only the first 9K vectors in memtable 100, omit memtables 101 and 102, and still record MaxMemtableId at 100. Yet segments 100 and 101 were already flushed to disk by the background flusher, making them durable. These flushed segments are therefore inconsistent with the heap and must be reconciled during recovery. PostgreSQL-V 2.0 begins by reading MaxMemtableId and dropping any flushed segment whose SegmentID exceeds it (segment 101 in the example above). It then iterates over the memtable array page, and for each SegmentID, scans its page list to recover the corresponding segment or memtable. If the SegmentID corresponds to a flushed segment (segment 100), PostgreSQL-V 2.0 reconstructs that segment’s bitmap, marking a vector valid if its TID appears in the status pages and invalid otherwise. For a merged segment spanning several memtables, only the entries for the given SegmentID are touched. If the SegmentID corresponds to no flushed segment, the memtable’s vectors were never persisted and are lost, so PostgreSQL-V 2.0 rebuilds the memtable by scanning the status pages for all TIDs of that SegmentID and re-fetching vectors from the heap, reinserting each with its TID. Correctness of the recovery process. PostgreSQL-V 2.0 recovery relies on two distinct durability boundaries: one marked by the recovered status pages, the other by which segments the LSM framework had flushed at crash time. The status page boundary is authoritative, since the status pages are durable through PostgreSQL’s WAL mechanism and thus consistent with the heap. Because the two boundaries can lead or lag one another, matching the index to the status pages requires one of two corrections: PostgreSQL-V 2.0 reconstructs the corresponding memtable from the heap where status page entries exceed flushed segments, or trims or discards flushed segments where they exceed the status page boundary. PostgreSQL-V 2.0 performs exactly this correction at recovery time, so the resulting index reflects precisely the vectors present in the heap after recovery, no more and no less. This work is bounded by the small number of entries recorded in the status pages at the time of the crash, so the cost stays low and constant regardless of how large the index has grown.

Index Recovery 6

After a restart, PostgreSQL’s startup process first recovers the status pages by replaying WAL records, making them consistent with the heap. The decoupled vector index is then recovered based on the restored status pages. Because flushed memtables are removed from the status pages during normal processing, the recovered status pages describe only memtables that may not have been persisted before the crash, so, in the common case, the work of restoring the decoupled index reduces to reconstructing those memtables. Flushed segments are involved only in certain cases, illustrated by the example below. Consider the following example. Suppose the memtable capacity is 10K vectors and the current mutable memtable (SegmentID 100) already holds 9K. A transaction then inserts 15K vectors, sealing and flushing memtables 100 and 101 and allocating a new memtable

PHYSICAL REPLICATION

Vector search in production is a read-heavy, high-volume workload: services such as RAG, semantic search, and recommendation issue a large number of vector similarity queries, each far more compute-intensive than a traditional point lookup. Physical replication serves exactly this setting: a primary node streams its WAL to read-only standbys that replay it to track the primary while remaining queryable, letting them absorb the query fan-out of costly vector search. For a built-in PostgreSQL index, this support is free: every index change passes through the data page buffer and is logged to the WAL, so replaying that log on the standby reapplies the same page-level changes and catches up the index. The decoupled index sacrifices this for the same reason it forfeits crash consistency: its contents live outside the data page buffer and generate no WAL. 7

Blindly replaying the WAL on a standby in PostgreSQL-V 2.0 only reproduces the index’s status pages and the corresponding heap pages. The unique challenge in PostgreSQL-V 2.0 is that, the index structures are never logged, leaving the standby with no searchable index to answer queries. PostgreSQL-V 2.0 addresses this gap with a replication mechanism designed specifically for the decoupled index, described next.

6.1

The WAL record generated when an insertion writes a TID to status pages (Section 5.3) carries an insert record type and both the TID and vector embedding as payload. Carrying the embedding with the TID lets the standby append the vector into its memtable directly from the WAL, without a heap lookup. Similarly, the WAL record generated when a new memtable is registered carries a memtable_creation type and the new memtable’s SegmentID, so the standby can open a matching memtable of its own. Vacuum on a memtable entry removes a TID from a status page, and the resulting WAL record carries a vacuum record type with that TID as payload. Vacuum on a flushed-segment entry, however, follows a different path: it only flips a bit in the segment’s bitmap, leaving status pages unchanged and generating no WAL record to carry a type. Single-node recovery tolerates this because it relies on the forced bitmap flush rather than status pages (Section 5.3), but a remote standby never observes that flush. PostgreSQL-V 2.0 therefore logs a standalone vacuum record carrying the TID, independent of any status-page write, so the deletion still reaches the standby.

Two-Channel Design

Replication must give the standby the same guarantee the decoupled design already gives on a single node: the index stays consistent with the heap. To meet this requirement, PostgreSQL-V 2.0 splits the operations of the decoupled index by their effect on consistency. Our observation is that insertion and vacuum operations change which entries the index holds relative to the heap, so they are critical to consistency. In contrast, flushing, merging, and rebuilding operations only reorganize vectors already in the index into a different physical representation (i.e., the number of segments and the index structure of each segment), so they govern how the index is searched rather than which entries it holds, thus affecting performance but not correctness. We call the first class consistency-critical operations and the second performance-critical operations. The two classes therefore impose very different replication requirements. Consistency-critical operations carry a correctness requirement: they must be durably replicated to the standby, and applied there in the same order as the heap changes they accompany on the primary, or the index will disagree with the heap. Performance-critical operations carry only a best-effort requirement: missing or reordering the propagation of index segment files generated by flushing, merging, and rebuilding is acceptable. PostgreSQL-V 2.0 meets these with two different replication paths (Figure 1): a consistency-critical channel (the green arrows) for consistency-critical operations and a performance-critical channel (the blue arrows) for the segment files.

Performance-critical channel. A potential approach is that, index segment files could likewise be embedded in the WAL as payload, riding the same replication transport as the records above, but at a cost that outweighs the convenience. Flushing, merging, and rebuilding are background operations that the LSM framework keeps off the critical path, whereas the WAL is a single ordered stream that every commit must flush through and every replica must replay in order. Injecting large segment writes into this stream risks stalling commits on the primary, since a commit’s durability requires flushing the WAL up to its own position, and any large record ahead of it must be flushed first. On the standby, replay faces the same ordering constraint: a large segment record blocks every record queued behind it, increasing replication lag. Besides, these records would also be replayed during PostgreSQL’s recovery procedure after a crash, which inflates recovery overhead. PostgreSQL-V 2.0 therefore moves segment files onto a separate channel, while still emitting a small segment_create WAL record for each segment through the WAL stream to notify the standby that a new segment exists, carrying only the segment’s metadata as payload. This channel is served by fetcher workers on each standby, which extract the metadata from replayed segment_create records and asynchronously send pull requests for the corresponding segment files to a lightweight file server on the primary. The file server, in turn, retrieves the requested segments from local storage and transfers them to the standby (Figure 1). In this way, the heavy bytes stay off both the commit path and the replication stream. Moving segments out of band opens architectural choices that routing them through the WAL would prevent. One such choice is running file servers independently of the primary node. A file server accesses only on-disk segment files and communicates only with fetcher workers, with no attachment to any other PostgreSQL component or process. This independence means that if segment files are also stored on other servers, file servers can be deployed there to serve segment requests from those locations, avoiding the network bottleneck that arises when multiple standbys pull large segment files from the primary simultaneously. Going further, VISP can be deployed on dedicated servers separate from PostgreSQL

Consistency-critical channel. PostgreSQL-V 2.0 replicates insertion and vacuum operations based on PostgreSQL’s extensibility framework. By default, WAL records capture only physical changes to PostgreSQL’s pages, so replaying them recovers nothing beyond those pages. However, the extension framework allows attaching a custom record type and arbitrary payloads to WAL records, and registering a callback for each record type that runs on replay. This callback can operate on components other than PostgreSQL’s pages, such as the decoupled index, which PostgreSQL does not otherwise manage. PostgreSQL-V 2.0 uses this framework to define its own record types for the decoupled vector index, each carrying the payload and callback it needs. These WAL records are generated, shipped, and replayed alongside all others in a single ordered stream, preserving their order relative to the heap changes they accompany. PostgreSQL-V 2.0 defines three customized record types that together cover every consistency-critical change to the decoupled index: insert, memtable_creation, and vacuum. Since each of these changes modifies status pages through the data page buffer, PostgreSQL already generates WAL records for them. PostgreSQLV 2.0 extends these records with a custom type, payload, and replay callback tailored to each case. 8

7.1

on both the primary and standby sides, so that index segment propagation proceeds directly between VISP nodes. None of this is possible if segment files instead travel through the WAL, which would require the PostgreSQL server to participate in every segment propagation, reintroducing the primary and standby PostgreSQL nodes into the transfer path and adding redundant network traffic. The two channels therefore carry different weight: correctness rests entirely on the consistency-critical channel, which replays consistency-critical operations in strict order, while the performancecritical channel governs search efficiency by fetching new index segment files asynchronously.

6.2

7.1.1 In-memory Vector Search We begin with the memory-resident indexes, comparing PostgreSQLV 2.0 against pgvector [12] configured with HNSW and IVF_FLAT, as well as the PostgreSQL-V 1.0 prototype. To position PostgreSQLV 2.0 relative to specialized vector search systems, we additionally run it against the native libraries hnswlib [8] and Faiss [6]. PostgreSQL-V 2.0 also adopts these libraries as its own decoupled index for HNSW and IVF_FLAT respectively. Every system is evaluated with matching index types and identical build-time parameters (M=16, efConstruction=40 for HNSW; nlist=3162 for IVF_FLAT), so any performance difference reflects how the index is integrated rather than the index itself. For pgvector, we configure the buffer pool large enough to hold both the heap and its index in memory, and we warm up the cache before the evaluation. We report single-client search performance first and then turn to the concurrent, multi-client setting. Under a single client, we evaluate the recall-latency trade-off on SIFT10M [15] and DEEP10M [16], each holding 10 million vectors and 10,000 queries (Figure 3). Backed by hnswlib, PostgreSQL-V 2.0 is 3.2–4.4× faster than pgvector’s HNSW on SIFT10M and 3.2–5.0× faster on DEEP10M; backed by Faiss’s IVF_FLAT, the gap widens to 6.5–8.6× over pgvector’s IVF_FLAT on SIFT10M and 4.9–10.4× on DEEP10M. Across both index types, PostgreSQL-V 2.0 tracks the search performance of the standalone library it wraps, separated only by a small constant gap of roughly 0.8 ms spent parsing the SQL statement and fetching heap tuples for the TIDs the index returns, consistent with the gap reported by PostgreSQL-V 1.0. Compared to the PostgreSQL-V 1.0, PostgreSQL-V 2.0 achieves comparable single-client latency on both datasets for IVF_FLAT. For HNSW, PostgreSQL-V 2.0 is slightly faster, as it adopts a newer version of the underlying HNSW library. These results confirm that PostgreSQL-V 2.0 preserves the decoupling benefit: the vector index is searched via direct memory access, avoiding the overhead that a page-oriented index incurs during search. We then raise the client count to 32 to assess scalability, exercising concurrency that the PostgreSQL-V 1.0 could not support. Figure 4 reports recall versus aggregate throughput. Scaling from one client to 32, PostgreSQL-V 2.0’s HNSW throughput increases by 18.3× to 39.0× (recall spanning 0.96–0.99) and IVF_FLAT throughput by 10.7× to 13.7× (recall spanning 0.87–0.98). At 32 clients, throughput for both indexes matches the native libraries. IVF_FLAT’s scaling is weaker than HNSW’s because memory bandwidth becomes the bottleneck as concurrency increases, particularly at higher recall, where larger nprobe values raise memory traffic. This limitation comes from the index structure itself rather than PostgreSQL-V 2.0’s design. PostgreSQL-V 2.0 also remains well

Replaying the Vector Index on the Standby

To derive a searchable index from replication, PostgreSQL-V 2.0 registers custom redo callbacks for both channels: handlers for the consistency-critical channel rebuild memtables and deletion bitmaps, while the segment_create handler schedules an asynchronous fetch of the segment file over the performance-critical channel. Reconstructing standby memtables. An insertion record’s callback appends the inline embedding payload directly to the standby memtable, while a memtable_creation record’s handler opens a new one. The standby never flushes, so these memtables accumulate, each serving as the correctness fallback for its vectors until the segment that covers it has been fetched and adopted. Fetching and adopting segments. Replaying a segment_create record does not fetch the segment inline, instead, it schedules an asynchronous fetch in the fetcher process and lets replay continue. Once the segment’s files arrive, the standby adopts it into the searchable index: any standby memtables it covers are freed, since their vectors are now durably represented in the segment, and any older segments it supersedes, from a merge or rebuild, are replaced. A late or out-of-order transfer may deliver an already-stale segment, identified by an outdated version number for a rebuild or a superseded SegmentID range for a merge, and discarded rather than adopted. Tracking deletion. Every vacuum record marks the deleted vector’s bit in the relevant memtable’s or segment’s bitmap upon replay. Upon segment adoption, it is tempting to let the fetched segment carry its deletion bitmap and adopt that directly. But the side-channel is asynchronous, so the bitmap can lag the vacuums the logical channel has already applied and miss the latest deletions. Adopting it would silently resurrect deleted vectors. The standby therefore maintains its own deletion bitmaps from replayed vacuum records alone, never trusting the side-channel. When it adopts a new segment, it builds that segment’s bitmap from the bitmaps already held in the memtables and segments the new segment supersedes.

7

Static Vector Search

Static vector search measures latency and throughput for pure queries against a built index with no updates. We evaluate PostgreSQLV 2.0 first over memory-resident indexes (Section 7.1.1) and then over disk-resident indexes (Section 7.1.2). In both settings, we confirm that PostgreSQL-V 2.0 preserves the latency advantage of a decoupled index over the page-oriented index, an advantage PostgreSQL-V 1.0 [26] established. We then go further and show that this advantage extends to throughput under concurrency, a setting the PostgreSQL-V 1.0 prototype could not support at all.

EXPERIMENTS

All experiments are conducted on Linux servers (Ubuntu 22.04, kernel 5.15) with two Intel Xeon sockets (112 cores total, 2 NUMA nodes) running at 2.0 GHz, 251 GB of DRAM, a 1.8 TB SSD, and a 10 Gb Ethernet NIC. 9

Figure 3: Recall-Latency Trade-off (In-memory)

Figure 4: Recall-Throughput Trade-off Under Concurrency (In-memory) PostgreSQL-V 1.0, likewise backed by DiskANN, and additionally run PostgreSQL-V 2.0 against the native DiskANN library to bound the achievable performance. To keep the comparison fair, every system is capped at 5 GB of memory. Each index uses its default parameters: R=68, L=75, 𝛼=1.2 for DiskANN, and search_list_size=100, num_neighbors=50, 𝛼=1.2 for pgvectorscale. For DiskANN’s beamwidth, each system uses the value that gives its best performance on each run. We report single-client search latency and, separately, peak throughput driven to the point where disk bandwidth saturates. On the Cohere10M dataset [3] of 768-dimensional vectors, PostgreSQL-V 2.0 (DiskANN) outperforms pgvectorscale by 7.3×– 8.0× in query latency (Figure 5a) and 7.7×–19.4× in peak throughput (Figure 6a), primarily attributable to the direct control over which data resides in memory allowed by decoupling. PostgreSQLV 2.0 also tracks the native DiskANN library closely in both metrics. Against PostgreSQL-V 1.0, PostgreSQL-V 2.0 leads in latency by a narrow margin, a gap due to the different DiskANN library versions the two systems build on; in throughput, PostgreSQL-V 1.0 is measured at the single client it supports, as in the in-memory evaluation, and against that baseline PostgreSQL-V 2.0’s peak throughput is 10.5×–65.3× higher. We further scale to SIFT100M [2], of 100 million vectors. Here index construction in pgvectorscale exceeds a week and is no longer a practical baseline, so we compare PostgreSQL-V 2.0 against the native DiskANN library and against PostgreSQL-V 1.0. As Figures 5b and 6b show, PostgreSQL-V 2.0 (DiskANN) stays close to the native library in both latency and throughput. Against PostgreSQL-V 1.0, PostgreSQL-V 2.0’s single-client latency nearly matches and its peak throughput is 3.2×–3.4× higher, a smaller gap than on Cohere10M, since DiskANN-backed systems saturate disk bandwidth at lower concurrency on SIFT100M, leaving PostgreSQL-V 2.0’s concurrency support less headroom to exploit.

Figure 5: Recall-Latency Trade-off (Disk-based)

Figure 6: Recall-Throughput Trade-off Under Concurrency (Disk-based) ahead of pgvector: it delivers 3.2× and 3.6× higher throughput than pgvector’s HNSW and 3.4× and 4.2× higher than its IVF_FLAT across the two datasets. Because PostgreSQL-V 1.0 restricts search to a single client, we compare against PostgreSQL-V 1.0 at its singleclient maximum: PostgreSQL-V 2.0 improves HNSW throughput by 23.4× to 36.4× and IVF_FLAT throughput by 11.1× to 16.2×, demonstrating the practical benefit of PostgreSQL-V 2.0’s concurrency support. 7.1.2 Disk-based Vector Search For the disk-resident indexes, PostgreSQL-V 2.0 backs its decoupled index with the native DiskANN library [5]. pgvector offers no disk-optimized index algorithm, so the baseline is pgvectorscale [13], which ports a DiskANN-style index into PostgreSQL’s page-oriented storage engine. We also compare against 10

pgvector throughout the run, averaging about 13.0K ops/s against 2.5K ops/s for pgvector, roughly a 5.1× gap overall, ranging from 3.4× to 8.5× across the run. On recall, the two diverge over the run. PostgreSQL-V 2.0’s recall starts about 2% below pgvector’s but quickly catches up within 50 steps; pgvector then degrades steadily from 0.9298 to 0.8407, whereas PostgreSQL-V 2.0 climbs from 0.9106 to 0.9422. pgvector’s recall declines over successive delete-insert cycles, a known difficulty for in-place HNSW updates, where continuous deletions gradually degrade graph connectivity [4, 34]. PostgreSQL-V 2.0 avoids this because it never mutates a built index in place: deletions are recorded as soft deletes in per-segment bitmaps, and each segment’s index is built once over an immutable vector set. Together, these results show that PostgreSQL-V 2.0’s out-of-place, LSM-style design sustains high throughput under continuous updates while avoiding the recall degradation that in-place index maintenance incurs.

7.3

We first test crash consistency, following the crash-injection methodology of [26]. During a mixed-mode runbook execution, we kill the PostgreSQL process at a random point and clear the system page cache, then restart and compare the heap table against the recovered vector index to verify index consistency. Across more than 1,000 such trials, the recovered index agreed with the heap in every case, with no inconsistency detected. More detailed correctness analysis can be found in Section 5.4. We then measure recovery overhead, comparing PostgreSQLV 2.0 against pgvector and PostgreSQL-V 1.0. The recovery overhead comprises the elapsed time of PostgreSQL’s built-in recovery procedure – during which the system scans and replays WAL records – as well as the overhead introduced by our customized recovery mechanism for restoring the decoupled vector index. To substantiate the claim that the recovery time of the decoupled vector index is independent of the number of vectors stored, we configure the runbook sliding window to two different sizes: 10M and 100M. PostgreSQL’s built-in recovery cost scales with the volume of WAL replayed, which is affected by PostgreSQL configurations and the operation rate. Therefore, across all three systems, we fix PostgreSQL configurations that affect WAL volume identically, including max_wal_size (1 GB), min_wal_size (80 MB), wal_level (replica), full_page_writes (on), and wal_compression (off). To control for operation rate, we drive PostgreSQL-V 2.0 and pgvector with 16 threads each throttled to roughly 50 operations per second. PostgreSQL-V 1.0 supports neither concurrency nor full index update support, so it cannot be driven at this same controlled rate, making its PostgreSQL-level recovery cost incomparable to the other two systems; we therefore exclude it and compare only the overhead of its customized index recovery. The results are presented in Figure 8. PostgreSQL-V 2.0’s customized recovery mechanism averages 22.43 ms and 19.76 ms for sliding window sizes of 10M and 100M, respectively, negligible compared with its PostgreSQL built-in recovery overhead of approximately 900 ms. Moreover, as shown in Figure 8a, PostgreSQL’s recovery overhead in PostgreSQL-V 2.0 is only about half of that in pgvector. This is because PostgreSQL-V 2.0 generates less WAL

Figure 7: Dynamic Vector Search Results

7.2

Crash Consistency and Recovery Overhead

Dynamic Vector Search

PostgreSQL-V 2.0 fully implements the LSM-style framework of the decoupled index, including background segment merge and rebuild, which the PostgreSQL-V 1.0 prototype [26] leaves incomplete. This lets us evaluate PostgreSQL-V 2.0’s dynamic vector search performance against pgvector, an evaluation PostgreSQL-V 1.0 could not support. We follow the evaluation methodology of the NeurIPS 2023 BigANN Competition [33] to assess dynamic vector search performance using runbooks [10]. A runbook is an ordered sequence of batched insert, delete, and search operations executed step by step to emulate a streaming workload in a vector database. Specifically, we use the msturing-10M_slidingwindow runbook [10], which consists of 400 steps. The first 100 steps insert half the data points (5M) in batches. The remaining 300 steps form 100 repeating three-step cycles: each cycle performs 10K vector searches, then deletes the oldest 50K vectors in the dataset, then inserts 50K new vectors, so the total dataset size stays constant throughout. Note that these steps can be executed sequentially, with concurrency only within each step. However, in practice, queries, insertions, and deletions often occur simultaneously. Therefore, we execute the runbook in mixed mode, interleaving operations in three consecutive steps and issuing all operation types concurrently. In this setting, recall is difficult to compute because the ground truth is not well defined [43]. Thus, we also conduct experiments in non-mixed mode and report recall results based on that setting. We perform experiments with 32 clients and measure throughput and the recall. Since the vector index is built after completing step 100, our evaluation begins at step 101. Both PostgreSQL-V 2.0 and pgvector use HNSW, and as in the in-memory evaluation (Section 7.1.1), pgvector’s buffer pool is sized to hold both the heap and its index. Figure 7 plots throughput and recall across the 300 steps. PostgreSQL-V 2.0 sustains substantially higher throughput than 11

the initial queries pay the cost of cold page-cache misses and their latency is visibly elevated until the working set warms up. PostgreSQL-V 2.0 without the mmap-based cold-start mode (Figure 9b) recovers in ∼2.4 s (crashed-to-recovered), but its decoupled index bypasses the page buffer and must be fully loaded into VISP’s private memory before search resumes: all segments load on the first search after recovery, so search stays unavailable until that load completes. This adds a substantial unavailability window of ∼4.8 s, bringing the total crashed-to-searchable time to ∼7.2 s. PostgreSQL-V 2.0 with the mmap-based cold-start mode (Figure 9c) recovers in ∼2.5 s (crashed-to-recovered), and instead of loading segments eagerly, it maps them into the VISP’s address space on the first query, making the index searchable roughly 100 ms after recovery, compared to ∼4.8 s without mmap (about 2% of the no-mmap loading time). This brings the total crashed-to-searchable time to roughly 2.6 s. Background promotion to full in-memory residency completes in roughly 4.9 s, after which queries are served from the pure in-memory index and all mmap mappings are released. To check whether the mmap cold-start mode’s benefit holds at larger scale, we repeat the experiment on SIFT100M. At this scale, the recovered-to-searchable time is ∼43 s without mmap, while mmap reaches searchable in ∼670 ms.

Figure 8: Recovery Overhead Evaluation Results

volume during normal processing: PostgreSQL-V 2.0 never logs modifications to its native index structures in the WAL. Its WAL consists solely of status-page WAL records, extended with insertion and vacuum payloads (Section 5.3); in contrast, pgvector logs every modification to its vector index pages. Consequently, the WAL scanned during recovery is far smaller for PostgreSQL-V 2.0 than for pgvector: approximately 200 MB against 900 MB. Figure 8b isolates PostgreSQL-V 2.0’s customized index-recovery cost against that of PostgreSQL-V 1.0. PostgreSQL-V 1.0’s indexrecovery time tracks index size, rising from 495.3 ms at 10M to 4,099 ms at 100M, since that design scans every status page at recovery. The redesigned status pages remove this dependence: PostgreSQL-V 2.0’s index-recovery time stays near 20 ms at both scales.

7.4

7.5

Physical Replication

We evaluate physical replication along two axes: the overhead that serving standbys imposes on the primary, and whether the standby answers queries as accurately as the primary. We deploy one primary and three standbys, driving both with the same slidingwindow runbook used in Section 7.2 at a concurrency of 32. We first measure the overhead that serving the three standbys imposes on the primary, executing the runbook in mixed mode both with and without the standbys attached. Serving the standbys leaves throughput essentially unchanged: the primary sustains about 12.9 K ops/s in both cases, whether running on its own or streaming WAL to all three standbys. We next evaluate the standby’s search accuracy relative to the primary. For this comparison we run the runbook in non-mixed mode, where recall is well defined, and before issuing queries on the standby we wait for it to catch up to the primary, so the two nodes are compared at the same logical point in the workload rather than across replication lag. Figure 10 reports the result on twin axes: the left axis carries two line series tracking Recall@100 on the primary and standby, and the right axis carries bars giving the absolute recall gap between them at each step, colored to distinguish the steps where the primary leads from those where the standby leads. The two line series are nearly indistinguishable. Through the early phase recall is identical on both nodes, around 0.91–0.92; once the curves begin to diverge the gap stays negligible, with a maximum of 0.0004 at any single step and a mean of about 0.00005 across the run. The standby is never systematically worse than the primary, so replication introduces no degradation in search quality, and both nodes trace the same recall trend as the standalone dynamic search results (Section 7.2). A small recall gap remains even with non-mixed mode and standby catch-up, because "catch-up" only means the standby’s WAL replay has reached the primary’s logical point, not that it

Cold-Start Availability

This experiment evaluates the cold-start availability concern from Section 4.2: after a restart, PostgreSQL-V 2.0 must reload all segments of an index into the VISP before search can resume, creating a window of unavailability absent from page-oriented systems. We quantify this window against pgvector, and show that PostgreSQLV 2.0’s mmap cold-start mode shrinks it dramatically relative to PostgreSQL-V 2.0 without it. In our setup, both systems use HNSW, and 16 clients issue vector search queries continuously against the SIFT10M dataset [15], each at a rate of one query per 100 ms with a 100 ms timeout. We tune ef_search for recall@100 of roughly 0.95. During the run, the process under load is killed with SIGKILL: a backend process for pgvector, the VISP for PostgreSQL-V 2.0. In both cases, PostgreSQL’s postmaster detects the abnormal exit of a process attached to shared memory, restarts the cluster, and triggers the crash recovery procedure. The recovery procedure is evaluated in Section 7.3, and this experiment focuses on what happens after recovery completes, before search can resume. Figures 9a–9c show per-query latency over a 30-second window centered on the crash (crash at 𝑇𝑖𝑚𝑒 = 10 s). Each plot marks the crash, the moment connections are re-accepted (recovered), and the moment search resumes (searchable). The result on pgvector (Figure 9a) is as expected: pgvector recovers in ∼2.8 s (crashed-to-recovered) and becomes searchable immediately. This is because it stores the index in PostgreSQL’s pages and accesses them through the page buffer, so index pages are loaded into the buffer on demand and no upfront load is required. The total crashed-to-searchable time therefore remains ∼2.8 s, though 12

causing a recall difference. The effect is small and bounded, so reads can be offloaded to the replica without sacrificing search accuracy.

8

CONCLUSION

This paper presents PostgreSQL-V 2.0, an efficient, scalable, crashresilient integrated vector database inside PostgreSQL. Building on the decoupled index architecture of our prior PostgreSQL-V 1.0 prototype [26], PostgreSQL-V 2.0 delivers three advances that close critical limitations in the prototype: fully concurrent vector search and updates across multiple clients, fast restart and recovery after a crash, and physical replication support that scales query throughput across standbys. This work demonstrates that the decoupling principle extends beyond an initial proof of concept and, with carefully crafted designs, can be realized as a fully capable system within a mature relational engine. We hope that PostgreSQL-V 2.0 serves as a reference point for future integrated vector database systems, demonstrating that high-performance, scalable, and highly available vector search can be achieved without sacrificing the transactional guarantees and operational simplicity of a relational engine.

Figure 9: Query Latency During Server Restart

Figure 10: Recall on the Primary vs. the Standby holds the same segments: segments propagate over a separate, asynchronous side channel with no timing guarantee relative to WAL replay (Section 6.1). A standby that has not yet adopted a freshly flushed segment instead searches the older segments or memtables, 13

REFERENCES

[36] Suhas Jayaram Subramanya, Fnu Devvrit, Harsha Vardhan Simhadri, Ravishankar Krishnawamy, and Rohan Kadekodi. 2019. Rand-NSG: Fast Accurate Billionpoint Nearest Neighbor Search on a Single Node. In Annual Conference on Neural Information Processing Systems (NeurIPS). 13748–13758. [37] Ji Sun, Guoliang Li, James Pan, Jiang Wang, Yongqing Xie, Ruicheng Liu, and Wen Nie. 2025. GaussDB-Vector: A Large-Scale Persistent Real-Time Vector Database for LLM Applications. Proc. VLDB Endow. 18, 12 (Aug. 2025), 4951–4963. doi:10.14778/3750601.3750619 [38] Jianguo Wang, Shasank Chavan, Guoliang Li, Yannis Papakonstantinou, and Charles Xie. 2024. Vector Databases: What’s Really New and What’s Next? PVLDB 17, 12 (2024), 4505–4506. [39] Jianguo Wang, Xiaomeng Yi, Rentong Guo, Hai Jin, Peng Xu, Shengjun Li, Xiangyu Wang, Xiangzhou Guo, Chengming Li, Xiaohai Xu, Kun Yu, Yuxing Yuan, Yinghao Zou, Jiquan Long, Yudong Cai, Zhenxiang Li, Zhifeng Zhang, Yihua Mo, Jun Gu, Ruiyi Jiang, Yi Wei, and Charles Xie. 2021. Milvus: A Purpose-Built Vector Data Management System. In SIGMOD. 2614–2627. [40] Yuming Xu, Hengyu Liang, Jin Li, Shuotao Xu, Qi Chen, Qianxi Zhang, Cheng Li, Ziyue Yang, Fan Yang, Yuqing Yang, Peng Cheng, and Mao Yang. 2023. SPFresh: Incremental In-Place Update for Billion-Scale Vector Search. In Proceedings of the Symposium on Operating Systems Principles (SOSP). 545–561. [41] Wen Yang, Tao Li, Gai Fang, and Hong Wei. 2020. PASE: PostgreSQL UltraHigh-Dimensional Approximate Nearest Neighbor Search Extension. In SIGMOD. 2241–2253. [42] Yunan Zhang, Shige Liu, and Jianguo Wang. 2024. Are There Fundamental Limitations in Supporting Vector Data Management in Relational Databases? A Case Study of PostgreSQL. In ICDE. 2835–2849. [43] Ziyu Zhang, Yuanhao Wei, Joshua Engels, and Julian Shun. 2025. CleANN: Efficient Full Dynamism in Graph-based Approximate Nearest Neighbor Search. CoRR abs/2507.19802 (2025).

[1] [n. d.]. AlloyDB AI (https://cloud.google.com/alloydb/ai). [2] [n. d.]. Billion-Scale Approximate Nearest Neighbor Search Challenge (https: //big-ann-benchmarks.com). [3] [n. d.]. Cohere (https://huggingface.co/datasets/Cohere/beir-embed-english-v3). [4] [n. d.]. Dagstuhl Seminar 26161: Managing Vector Data for Retrieval Augmented Generation: Systems and Algorithms (https://www.dagstuhl.de/26161). [5] [n. d.]. DiskANN (https://github.com/microsoft/DiskANN). [6] [n. d.]. Facebook Faiss (https://github.com/facebookresearch/faiss). [7] [n. d.]. GiST (https://www.postgresql.org/docs/8.1/gist.html). [8] [n. d.]. HNSWlib (https://github.com/nmslib/hnswlib). [9] [n. d.]. Knowhere (https://github.com/zilliztech/knowhere). [10] [n. d.]. NeurIPS 2023 Streaming Challenge and Beyond. (https://github.com/ harsha-simhadri/big-ann-benchmarks/tree/main/neurips23/streaming.). [11] [n. d.]. NVIDIA cuVS (https://developer.nvidia.com/cuvs). [12] [n. d.]. pgvector (https://github.com/pgvector/pgvector). [13] [n. d.]. pgvectorscale (https://github.com/timescale/pgvectorscale). [14] [n. d.]. Pinecone (https://www.pinecone.io/). [15] [n. d.]. SIFTData (http://corpus-texmex.irisa.fr/). [16] Artem Babenko and Victor S. Lempitsky. 2016. Efficient Indexing of Billion-Scale Datasets of Deep Descriptors. In CVPR. 2055–2063. [17] Sebastian Bruch. 2024. Foundations of Vector Retrieval. Springer. [18] Cheng Chen, Chenzhe Jin, Yunan Zhang, Sasha Podolsky, Chun Wu, Szu-Po Wang, Eric Hanson, Zhou Sun, Robert Walzer, and Jianguo Wang. 2024. SingleStoreV: An Integrated Vector Database System in SingleStore. PVLDB 17, 12 (2024), 3772–3785. [19] Paul Covington, Jay Adams, and Emre Sargin. 2016. Deep neural networks for youtube recommendations. In Proceedings of the 10th ACM conference on recommender systems. 191–198. [20] Andrew Crotty, Viktor Leis, and Andrew Pavlo. 2022. Are you sure you want to use mmap in your database management system?. In CIDR. [21] Cong Fu, Chao Xiang, Changxu Wang, and Deng Cai. 2019. Fast Approximate Nearest Neighbor Search With The Navigating Spreading-out Graph. Proceedings of the VLDB Endowment (PVLDB) 12, 5 (2019), 461–474. [22] Yunfan Gao, Yun Xiong, Xinyu Gao, Kangxiang Jia, Jinliu Pan, Yuxi Bi, Yi Dai, Jiawei Sun, Meng Wang, and Haofen Wang. 2023. Retrieval-augmented generation for large language models: A survey. arXiv preprint arXiv:2312.10997 (2023). [23] Hervé Jégou, Matthijs Douze, and Cordelia Schmid. 2011. Product Quantization for Nearest Neighbor Search. IEEE Transactions on Pattern Analysis and Machine Intelligence (TPAMI) 33, 1 (2011), 117–128. [24] Chenzhe Jin, Yunan Zhang, Jiayi Liu, and Jianguo Wang. 2026. Efficient Vector Index Merging in Vector Databases. Proceedings of the ACM on Management of Data 4, 1 (SIGMOD (2026), 1–26. [25] Vladimir Karpukhin, Barlas Oguz, Sewon Min, Patrick Lewis, Ledell Wu, Sergey Edunov, Danqi Chen, and Wen-tau Yih. 2020. Dense passage retrieval for opendomain question answering. In Proceedings of the 2020 conference on empirical methods in natural language processing (EMNLP). 6769–6781. [26] Jiayi Liu, Yunan Zhang, Chenzhe Jin, Aditya Gupta, Shige Liu, and Jianguo Wang. 2026. Fast Vector Search in PostgreSQL: A Decoupled Approach. In Conference on Innovative Data Systems Research (CIDR). [27] Chen Luo and Michael J. Carey. 2020. LSM-based Storage Techniques: A Survey. VLDB Journal 29, 1 (2020), 393–418. [28] Yury A. Malkov and Dmitry A. Yashunin. 2018. Efficient and Robust Approximate Nearest Neighbor Search Using Hierarchical Navigable Small World Graphs. IEEE Transactions on Pattern Analysis and Machine Intelligence (TPAMI) 42, 4 (2018), 824–836. [29] Hiroyuki Ootomo, Akira Naruse, Corey Nolet, Ray Wang, Tamas Feher, and Yong Wang. 2024. CAGRA: Highly Parallel Graph Construction and Approximate Nearest Neighbor Search for GPUs. In ICDE. 4236–4247. [30] Patrick O’Neil, Edward Cheng, Dieter Gawlick, and Elizabeth O’Neil. 1996. The log-structured merge-tree (LSM-tree). Acta informatica 33, 4 (1996), 351–385. [31] James Pan, Jianguo Wang, and Guoliang Li. 2024. Survey of Vector Database Management Systems. VLDB Journal (VLDBJ) 33, 5 (2024), 1591–1615. [32] James Pan, Jianguo Wang, and Guoliang Li. 2024. Vector Database Management Techniques and Systems. In SIGMOD. 597–604. [33] Harsha Vardhan Simhadri, Martin Aumüller, Amir Ingber, Matthijs Douze, George Williams, Magdalen Dobson Manohar, Dmitry Baranchuk, Edo Liberty, Frank Liu, Benjamin Landrum, Mazin Karjikar, Laxman Dhulipala, Meng Chen, Yue Chen, Rui Ma, Kai Zhang, Yuzheng Cai, Jiayang Shi, Yizhuo Chen, Weiguo Zheng, Zihao Wan, Jie Yin, and Ben Huang. 2024. Results of the Big ANN: NeurIPS’23 Competition. CoRR abs/2409.17424 (2024). [34] Aditi Singh, Suhas Jayaram Subramanya, Ravishankar Krishnaswamy, and Harsha Vardhan Simhadri. 2021. FreshDiskANN: A Fast and Accurate Graph-Based ANN Index for Streaming Similarity Search. CoRR abs/2105.09613 (2021). [35] Michael Stonebraker and Andrew Pavlo. 2024. What Goes Around Comes Around... And Around.. SIGMOD Record 53, 2 (2024), 21–37.

14

Related documents

• Building An Integrated Vector Database System in PostgreSQL#617669
• Building An Integrated Vector Database System in PostgreSQL#617169
• QUERY PROCESSING STRATEGIES IN THE PASCAL/R RELATIONAL DATABASE MANAGEMENT SYSTEM#933692
• Integrating customer relationship management with business intelligence platform design: a design science approach.#1091551
• Flamingo: On Load Balancing in DAG-based Consensus Protocols#1108881