skip to content

Should a team keep embeddings in the relational database it already runs, through a vector extension, or adopt a dedicated vector store?

level: principalimportance: should knowfreq 48%

answer

  1. one system or two
  2. a transaction around row and vector
  3. shared memory and CPU
  4. a sync path to build
  5. default plus exit criteria

basics

~20 s

Start in the relational database already in production while vectors fit beside its transactional load: one system, one transaction, existing backups. Move to a dedicated vector store when measured contention, scale or missing features demand it, accepting a sync path.

solid answer

~50 s

Keeping vectors in the database you already run buys a lot: a row, its vector and its permissions commit in one transaction, a delete can remove the vector in the same transaction, filters join existing columns, and backups, failover and on-call already exist. The cost is shared resources: index builds and vector queries compete with the transactional workload for memory and CPU, the write path ends at one primary, and the extension's vector features and versions may trail a dedicated store's. A dedicated store brings scale-out, broader features and its own failure domain, but also a second system to run and a sync path from the system of record, with lag, gaps and duplicated access rules. As a lead I would set the database as the default, write down the exit criteria (transactional p99 hurt by vector load, an index that no longer fits, a needed feature), keep vectors exportable, and revisit on measurements.

go deeper

for a junior

Know that a vector extension stores embeddings inside the existing relational database, while a dedicated store is a separate system fed by a pipeline.

for a middle

Explain the transactional advantage of one database and the resource contention it brings: index builds and vector queries share memory and CPU with transactions.

for a senior

Count the sync path's cost for a dedicated store and name measurable triggers for moving, such as transactional tail latency or an index that outgrows the node.

for a principal

Own the default for the organisation, write the exit criteria down, and make the exit cheap from day one so the decision can be revisited on evidence.

## The two options A **vector extension** adds a vector column type, distance operators and one or more approximate nearest-neighbour index types to a relational database. Embeddings then live in a column beside the rows they describe, queried in the same statement as everything else. A **dedicated vector store** is a separate system, self-run or managed, built for vector search. The relational database stays the system of record, and a pipeline copies vectors and the metadata needed for filtering into the store. There is no universal answer, which is why this is a lead's call: the right choice depends on corpus size and growth, query rate, filter complexity, consistency needs and the team's operating capacity. ## What the database already in production gives you - **One transaction.** A document row, its chunk vectors and its permission rows commit or roll back together. Delete the row and its vectors can go in the same transaction, stored in the same row or removed by a cascading delete, so no orphan vector outlives its source. - **Joins and filters on real columns.** Tenant, permission and status filters reuse the schema and its indexes instead of a copied metadata blob. - **Operations you already have.** Backups, failover, access control, auditing, monitoring and an on-call rotation exist on day one. - **No sync pipeline.** Nothing to lag, drop events or drift. ## What it costs 1. **Shared resources.** Building a graph index is memory- and CPU-heavy and can run for a long time on a large table; vector queries hold index pages in memory that the transactional workload also wants. Contention shows up as transactional tail latency. 2. **A single write path.** Most deployments have one primary; read replicas can absorb vector queries, but writes and index maintenance still land on the primary. 3. **Feature lag.** Quantisation options, hybrid keyword-plus-vector ranking and multi-vector records vary by extension and version, and a managed database service offers only the extension versions it supports. 4. **Scale ceiling.** A very large corpus pushes the index past what one node holds comfortably; fitting an index into memory is its own subject, but it is often the trigger. ## What a dedicated store gives, and what it costs | | Database extension | Dedicated vector store | |---|---|---| | Consistency with source rows | One transaction | Eventual, via a sync path | | Systems to operate | One | Two | | Scale-out of vector search | Limited by one primary | Designed for it | | Vector feature set | Varies, may trail | Usually broader | | Failure domain | Shared with transactions | Independent | | Access control | Existing rules | Duplicated in the store | The hidden cost of the dedicated store is the **sync path**: every insert, update, delete and permission change must reach it, and every gap is a stale or leaked result. Building that pipeline is a subject of its own; here it is a cost to count. ## How a lead makes the call 1. **Measure the fit today.** Corpus size, vectors added per day, peak vector queries per second, and the database's spare memory and CPU. 2. **Pick a default.** For most teams already running a relational database, the default is the extension, because it removes a system and a pipeline. 3. **Write the exit criteria down.** For example: vector load pushes transactional p99 past its target; the index no longer fits the node; a needed retrieval feature is missing; vector traffic needs to scale independently. 4. **Keep the exit cheap.** Store vectors with their model identity, keep IDs stable, and put retrieval behind one internal interface so a move later is a data migration, not a rewrite. 5. **Revisit with measurements.** A projected tenfold growth is a reason to watch the criteria, not to adopt a second system now. A middle path exists: route vector queries to a read replica so they stop competing with transactions, while writes stay transactional. It postpones the decision rather than avoiding it. ## How to answer in an interview Show both sides, then commit: name the default for this team, the measured triggers that would change it, and what you do now to keep the move cheap. Interviewers mark down answers that pick the dedicated store because it is "built for vectors" without counting the sync path, and answers that keep everything in the database without saying when they would stop.

  • What does a read replica for vector queries solve, and what does it not?
    It moves vector query load off the primary, so transactional reads and writes stop competing with similarity searches for memory and CPU. It does not move writes or index maintenance, which still run on the primary and replicate, and replica lag means a just-written vector may not be found there at once.
  • What would you do on day one to keep a later move to a dedicated store cheap?
    Keep stable record IDs, store each vector with its embedding model identity and the metric it assumes, keep filterable metadata in plain columns, and route all retrieval through one internal interface. Then a move is exporting vectors and switching one implementation, not re-embedding and rewriting call sites.

  • Vector search inside a relational database is always too slow for production.
  • A dedicated vector store removes the need to keep anything in sync.
  • Adding vectors to the main database has no effect on its transactional load.
  • Adopt the dedicated store now because the corpus might grow tenfold someday.
  • A vendor benchmark on someone else's data settles the decision.