Adding pgvector vector search to a PostgreSQL database you already run
pgvector runs inside PostgreSQL as an extension, so a team that already operates a cluster can add similarity search over embeddings without introducing a second database. The decisions that shape the work are the index type, the distance function, the memory and build time the index needs, and the backup scope that must cover the vectors. Teams usually reach for this setup for retrieval in language-model applications, product recommendations, or deduplication of records and documents, and they often find that the embedding table grows faster than the rest of the schema.
PostgreSQL is released under the PostgreSQL License, a permissive license similar to the BSD and MIT licenses, so the database server itself carries no copyleft obligations for a self-hosted deployment. pgvector is a separate open-source project with its own license, release history and compatibility statements, and you must confirm all three against the pgvector project before you adopt a version. Per the PostgreSQL versioning policy, the currently supported major versions are 14 through 18, with 14 leaving support on November 12, 2026 and 18 supported until November 14, 2030. PostgreSQL 13 and earlier are no longer supported, so a cluster on those versions needs an upgrade before the extension work begins.
Platform engineering or database teams usually implement pgvector, often with an application team that owns the embedding pipeline and the query patterns. A partner is worth considering when the team has not yet settled index parameters for its expected row count and dimensions, when the vectors fall under access control or audit requirements, or when the backup and upgrade routines must change to cover the new objects. DeployOpen plans and builds this work inside your own infrastructure under a written scope, so the decisions above are recorded before any build starts.
Before scoping, collect the embedding dimension, the expected row count and growth, the latency your queries must meet, and whether vectors are inserted once or updated often, because these facts drive the index choice. Confirm in the pgvector release notes which index types, dimension limits and build options that version supports, and confirm how your backup tooling restores the extension objects into the target PostgreSQL major. Test a restore on a copy of production data before go-live, and plan the memory and build window for the index on a staging cluster that matches your production sizing.
What pgvector adds to PostgreSQL
pgvector is an extension that adds vector storage, distance operators and approximate nearest neighbor indexes to a PostgreSQL database. The cards below separate these capabilities so you can decide how each one shapes your schema, your application queries and your index build plan.
Vector data type column
pgvector adds a vector column type that stores embeddings in the same table as your existing relational data. For the schema, the embedding dimension becomes part of the column definition, so moving to a different embedding model usually means a new column or table and a re-embedding job. Check the type's dimension limit and any element-type variants in the pgvector documentation for your chosen version before you fix the design.
Distance operators in queries
The extension provides distance operators that you call in ordinary SQL to order rows by similarity to a query vector. The application query typically pairs an ORDER BY on the operator with a LIMIT, and it can add WHERE clauses on tenant, status or date columns in the same statement. Confirm the exact operator symbols, the supported distance metrics and whether each operator can use an index in the documentation for your version. The metric should match what your embedding model's documentation recommends.
HNSW approximate nearest neighbor index
HNSW is an approximate nearest neighbor index type that pgvector can build on a vector column. It trades build time, memory use and index size for query recall, and its options control where that balance falls. Confirm the build parameters, the query-time search setting and the operator classes that match each distance metric in the documentation for your version. Test recall on your own data with representative queries before you settle on index settings.
IVFFlat approximate nearest neighbor index
IVFFlat is the second approximate index type in pgvector. It groups vectors into lists during the build, so the index works best when it is built after the table holds representative data, and rebuilding after large data changes belongs in your operations plan. Confirm the list count guidance, the query-time probes setting and how the index handles rows added after the build in the documentation for your version. Compare its recall and latency with HNSW on your own data.
Exact search without an index
Queries that use distance operators run without any vector index and return exact results by comparing candidate rows directly. For small tables, or for queries narrowed by selective filters, this may be acceptable, but the cost grows as row count grows. Measure the query plan with realistic data volume before you decide an index is unnecessary. Ask whether your ordinary filter columns can use their own indexes in the same plan.
Operations tied to PostgreSQL
Vectors live in ordinary PostgreSQL tables, so your existing backup, point-in-time recovery, replication and access control apply to them. Index builds consume memory and CPU on the primary, and index data adds to backup and restore volumes. Confirm the extension is installed and compatible on every replica and standby, and that it is available for the server major version you plan to run after a PostgreSQL upgrade.
Which index, distance function and storage layout fit your workload
This table compares the index types, distance functions and storage layouts pgvector offers, and how each choice affects build time, memory, recall and writes. Read each row as a decision: the first column names the option, the middle column says when it fits, and the last column lists what to measure on your own data. Confirm every index behavior for the pgvector version you will run, since build, memory and update characteristics can change between releases.
| Option | When it fits | What to test before deciding |
|---|---|---|
| HNSW index | Read-heavy search where recall and query latency matter most and you can accept slower builds and higher memory use. | Build time on a full-size sample, recall at your chosen search setting, and index size relative to table size. |
| IVFFlat index | Large bulk loads where build time and memory must stay lower and some loss of recall is acceptable. | Recall after your expected insert volume, and whether results degrade until the index is rebuilt. |
| Inserts and updates after indexing | Tables that change often, where the cost of keeping the index current decides which index type you can run. | Write latency and recall under live inserts and updates for each index type, on the pgvector version in production. |
| Cosine distance | Embedding models trained for cosine similarity, which is the setting the model documentation names. | Confirm the metric in the model documentation, then compare ranking on a labeled set of queries. |
| Inner product and Euclidean (L2) | Inner product fits models trained for dot product scoring. L2 fits models documented with Euclidean distance. | The index operator class must match the query operator. Compare ranking on a labeled sample before you commit. |
| Exact search without an index | Small tables where a sequential scan meets your latency target and you need perfect recall. | Query latency at your expected row count, and the row count where scans stop meeting the target. |
| Embeddings in the same table as business data | Smaller datasets with frequent joins to business columns and access rules already applied to that table. | Table size and index size from wide rows, and whether existing row-level access rules cover the vector column. |
| Embeddings in a separate table | Large vector sets, different retention or access needs, or re-embedding that runs on its own schedule. | Join cost to business data at query time, and the process needed to keep both tables consistent during updates. |
How pgvector indexes fit into PostgreSQL backups and replication
A pgvector index is stored in the same database cluster as the tables it serves, so the backup, point-in-time recovery and replication decisions you make for PostgreSQL also cover your embeddings and their indexes. There is no separate vector store to back up. PostgreSQL's continuous archiving combines a base backup with archived WAL files, and that pair is what allows recovery to a chosen point in time. Decide your base backup schedule, WAL retention and restore targets before the first embedding is loaded. Where your data volume is large, check whether incremental backups with pg_basebackup and pg_combinebackup fit your recovery procedure.
The restore question for vector search is whether the embeddings and the source rows they describe come back at the same point in time. If embeddings are generated by a pipeline outside the database, a restore can leave vectors that no longer match their source text, or the reverse. Run a restore test to a target time you choose, then compare row counts and checksums for the embedding table against the source. Confirm with the pgvector documentation whether an index is restored with the cluster or must be recreated after restore. Measure the rebuild time in your own environment instead of assuming it is short.
Hot standby servers accept read-only queries, so similarity search can be routed to a replica to spread read load. Replication mode changes what those queries see. With asynchronous replication, a standby can lag the primary, so an embedding inserted a moment earlier may be absent from results served by the replica. With synchronous replication, a commit waits for the standby to confirm, which protects recent writes but adds latency to every write that updates embeddings. Decide per workload: a product recommendation feed may accept a short lag, while a fraud or compliance lookup may not. Measure replica lag in your environment and set an acceptable limit in writing.
PostgreSQL does not detect a primary failure or notify a standby, so automatic failover depends on external tooling that you select and operate. After promotion, the new primary must serve the same vector queries correctly. Plan a post-failover check that runs a known similarity query, confirms the index is used through the query plan, and verifies the expected row counts. If PgBouncer or another connection layer sits in front of the cluster, confirm that it points at the promoted primary. Record these checks in your runbook so they run the same way during a real incident and during scheduled failover tests.
How a pgvector implementation runs from requirements to production
This six-stage sequence moves a vector search workload from agreed requirements to a monitored production cutover. Each stage produces a written artifact, so your team can see what has been decided, tested and handed over before traffic moves.
Define requirements and query patterns
Agree with your application and data owners which similarity questions the search must answer, how many vectors you expect to store, how often they change, and which metadata filters appear alongside each query. DeployOpen works through these with your teams, and the output is a written requirements note covering query shapes, filter columns, recall expectations, latency targets and retention. The step is complete when the business owner and the application team have both approved the note, because the index choices in later stages depend on it.
Check PostgreSQL and pgvector compatibility
Confirm that the PostgreSQL major version you run is still within its support window, using the PostgreSQL versioning policy. For example, PostgreSQL 14 is supported through November 12, 2026, so a cluster on that version may need its upgrade planned alongside this work. Check that the pgvector release you intend to use builds and loads on your exact PostgreSQL version and packaging, including any managed service or Kubernetes operator image. The output is a compatibility record and a successful test install on a non-production cluster.
Design schema and indexes on a test corpus
Create the table, the vector column with its dimension, and the metadata columns that your filters use. Load a representative sample of your embeddings, choose the distance function that matches how your embedding model was trained, and build candidate HNSW and IVFFlat indexes on the sample. Record build time and memory use for each candidate. The outputs are a schema definition, the index definitions with the parameters tested, and a test corpus with a note on where the data came from. Confirm the available index options in the pgvector documentation for the version you run.
Measure recall and latency against exact search
Run the query set from the requirements note twice against the test corpus. The first run uses exact search with the index disabled, which gives the true nearest neighbors. The second run uses the index at the planned settings. Compare how many approximate results appear in the exact top results for each query class, and measure latency at the concurrency your application expects. The output is a test report with recall, latency percentiles, index build time and the settings that met the requirements. If recall falls short, adjust index parameters or filter design and repeat the run.
Test backup and restore including the index
Take a base backup with archived WAL, then restore it to a separate host and recover to a chosen point in time. Check that the extension, the table, the index and the row counts are all present after recovery. Re-run the recall test queries and confirm that results match the pre-restore run. Record how long the restore and any index rebuild took, and compare those figures with the recovery objectives in your requirements note. The outputs are a restore test report and a backup runbook that names the restore steps and owners.
Cut over in stages with monitoring
Move the application in stages, beginning with shadow queries against the new index, then a limited share of production traffic, then full traffic. Keep the previous search path available for rollback at each stage. Configure monitoring for query latency, index size, memory use, replication lag and connection counts, and agree alert thresholds with your operations team before the first stage. The outputs are a cutover runbook with go and rollback criteria, a monitoring dashboard with alerts, and a post-cutover review that compares production recall samples with the test report.
What drives the cost and effort of a pgvector implementation
This table lists the main drivers of pgvector work, what raises effort for each, what keeps it lower, and what to ask a partner to price in writing. Read it before requesting a quote, because the same PostgreSQL platform can need very different work depending on how these drivers combine. Most scope disputes come from drivers that were never written down.
| Driver | What raises effort | What lowers effort | What to price in a written scope |
|---|---|---|---|
| Corpus size and growth | Large corpora, frequent re-indexing, and expected growth that outpaces the index build plan | A bounded corpus, a defined refresh cycle, and an index rebuild window agreed early | Row counts at go-live, expected growth, and index build time measured on a representative copy |
| Embedding dimension and model count | High-dimensional vectors, several embedding models, or models that change often, each needing its own column and index | One embedding model with a documented version and a fixed dimension set before schema design | Number of embedding models, dimension per column, and the re-embedding procedure for model changes |
| Filtered query patterns | Many queries that combine vector ordering with tenant, date, or status filters, each needing plan testing | A short list of filter combinations, with query plans reviewed against realistic data volumes | Each query pattern in writing, including its filters, sort order, and result limit |
| Recall targets | Strict recall targets across many filter combinations, which usually means more index tuning and test runs | One agreed recall measure, a ground-truth test set, and a tolerance set per use case | How recall is measured, who builds the ground-truth set, and how many tuning cycles are included |
| Memory on the database host | Index sizes that do not fit in memory, forcing disk-bound search and slower index builds | Host sizing based on measured index size and planned query concurrency before the schema is final | The sizing basis, any memory or storage changes in scope, and who runs the capacity test |
| Need for replicas | Read replicas serving search traffic, failover requirements, or replicas that must rebuild indexes after promotion | Reusing the PostgreSQL high availability design already in place, with replica lag tested | Replica count, whether replicas serve search queries, and the failover tooling and test included in scope |
| Backup and restore test scope | Restore tests that must cover embeddings, indexes, and source keys, plus point-in-time recovery drills | Restore tests limited to the tables the application depends on, run on a fixed schedule | Whether restores include indexes or rebuild them, the recovery goals, and the number of restore drills |
| Source systems feeding embeddings | Several source systems with different update rules, deletes, and access controls that must carry into search results | One source of truth, a single ingestion path, and a documented rule for updates and deletes | The source list, change frequency of each, the pipeline owner, and how permissions map to search results |
When a dedicated vector database is the better fit
A separate vector engine is worth its extra operational cost when the vector workload would otherwise compete with transactional data for the same resources. The clearest cases are very large corpora, where index memory and build time begin to dominate the database, and heavy vector-only query traffic that should not share CPU, memory and disk I/O with order processing or reporting tables. A third case is a need for a managed or distributed vector service that your team does not want to run inside its core database. None of these is a fixed threshold. Before you decide, measure index size, build time, query latency under concurrent transactional load, and recall on a realistic sample of your own data in pgvector.
Qdrant is a useful reference point for this comparison. Qdrant is licensed under Apache-2.0, and the open-source engine is free to self-host, so the cost of a dedicated engine is the infrastructure and the people who run it. Its storage requirements are also specific. Qdrant needs block-level storage with a POSIX-compatible file system and cannot run on network file systems such as NFS or on object storage such as S3, with SSD or NVMe drives recommended. Check this against your platform team's storage classes early, because a design built on shared network storage or object storage will fail for Qdrant while it may serve PostgreSQL well.
Running Qdrant in production means planning a cluster, not a single container. Qdrant's distributed mode uses the Raft consensus protocol for cluster topology and collection structure, splits collections into shards across nodes, and keeps extra copies through a replication factor. The documentation recommends at least three nodes for production so that quorum survives a node failure, and a shard count that is a multiple of the node count. The replication factor has a direct storage cost: at a factor of 2, storage doubles. Size disks, network and memory for the replicated footprint and the growth you expect, and test a node failure before go-live.
Qdrant documents three production paths: managed Qdrant Cloud, a community-supported Kubernetes Helm chart, and an enterprise Kubernetes operator offered as part of Qdrant Private Cloud. The Helm chart lacks enterprise features such as zero-downtime upgrades and automatic shard rebalancing. If your team picks the chart, it owns the upgrade windows, shard placement and recovery steps those features would otherwise handle. Ask Qdrant which path supports your hosting, data residency and support requirements, and get the answer in writing before you design around one of them.
The main trade-off is operational. A dedicated engine adds components to back up, monitor, secure and patch, and it adds a synchronization path between PostgreSQL rows and vector points. Qdrant's security documentation states that self-deployed instances listen on all network interfaces and have no authentication configured by default, so access controls must be set before any service reaches the cluster. Qdrant documents a migration tool, which helps with moving data but does not keep embeddings current after cutover. Compare a dedicated design with keeping vectors in PostgreSQL, where one backup scope, one access model and joins to transactional data remain available, and record who owns the synchronization.
How pgvector connects to the rest of the PostgreSQL platform
pgvector inherits the operating model of the PostgreSQL cluster it runs in, so vector search is only as available, pooled, authenticated and auditable as the platform around it. The cards below cover the links that change design decisions, and the team that owns each one should confirm its choices before build starts.
High availability and failover
Vector indexes live in ordinary PostgreSQL tables, so they replicate with the cluster through hot standbys and streaming replication. PostgreSQL does not detect a failed primary or promote a standby on its own, so an external failover tool must be chosen and tested against the vector workload. Decide whether embedding writes wait for synchronous standbys, since that adds commit latency during bulk loads. Confirm with the database team that the vector tables sit inside the same failover scope as the application that queries them.
Connection pooling with PgBouncer
Application tiers that open many short connections can exhaust PostgreSQL backends, and similarity queries often run longer than simple lookups. PgBouncer is a lightweight pooler that can sit in front of the cluster. Decide the pool mode, because session-level features such as prepared statements and session settings behave differently under transaction pooling. Confirm that any per-session settings used for search tuning survive pooling, and check that the deployed PgBouncer release includes current security fixes, such as version 1.26.1 released October 14, 2026.
Client authentication methods
PostgreSQL documents several client authentication methods, including password, GSSAPI, SSPI, LDAP, RADIUS, certificate, PAM and OAuth. Decide which method each application and service role uses, and whether a central identity provider or directory is the source of truth for those identities. Password logins should use SCRAM, since PostgreSQL 18 deprecated MD5 password authentication in favor of SCRAM. Confirm with the identity team which roles may read or write embedding columns, and whether ingestion jobs run under separate service roles with narrow grants.
Audit logging with pgAudit
Vector tables often hold embeddings derived from customer or employee text, so access to them may need an audit trail. pgAudit adds session and object audit logging on top of the standard PostgreSQL logging facilities. Decide which statement classes to log, because recording every read against a busy search table produces large log volumes. Match the pgAudit version to the PostgreSQL major version in use, and confirm with the security team where logs ship and how long they are retained.
Embedding pipeline writes
Vectors reach PostgreSQL from an embedding job, a trigger or application code that calls an embedding model and writes the result. Decide where embedding generation runs, how new and changed rows are queued, and whether the job is idempotent so retries do not duplicate vectors. Changing the embedding model means stored vectors must be regenerated, so plan how dimension changes and backfills are scheduled. Confirm with the data platform team who owns the scheduler, the model endpoint, its credentials and its rate limits.
Backups and point-in-time recovery
Base backups and archived WAL files restore vector tables along with the rest of the cluster, and incremental backups can reduce backup volume. Decide whether embeddings must be restored from backup or can be regenerated from source text. Regeneration shrinks stored data but moves time into the embedding job during recovery. Confirm recovery time expectations with the backup owner, and test a restore that includes the vector indexes and the installed extension version.
Questions teams ask before hiring help with pgvector
How much does a pgvector implementation cost?
Cost follows the scope you approve. The main drivers are the number of tables and embedding columns, the size of the corpus, whether an embedding pipeline already exists or must be built, the query patterns and filters you need to support, how much recall and latency testing you require, and whether the database runs as a single node or as part of a replicated cluster. One-time work includes design, index selection, build tuning, load testing and documentation. Recurring work includes index maintenance during PostgreSQL upgrades, monitoring, and retesting when data or embedding models change. DeployOpen does not publish prices. A written quote follows from the agreed scope, so ask which items are fixed-scope, which are retainer items, and which assumptions about your test data and team availability are written into the scope.
How long does it take to set up vector search in PostgreSQL?
The schedule is driven by data preparation, testing and change approval more than by the extension install itself. The main inputs are how many tables hold embeddings, how clean the source data is, whether the embedding model and vector dimensions are settled, how many query patterns must be benchmarked against recall and latency targets, and how long your production change process takes. Index build time grows with row count and memory settings, so a representative test corpus is needed before any timeline is credible. Ask for a phased plan with discovery, a build stage that includes load testing, and a cutover stage, and check which steps depend on your database owners and data engineers being available. No duration is given here because it depends on those inputs.
What does our team need to provide for a pgvector project?
The work needs named people as much as tools. Database owners approve schema changes, extension installation, memory settings and maintenance windows. Data engineers supply the source tables, the embedding pipeline and its refresh schedule. Query owners describe the search and filter patterns and define what a good result looks like. You also need a test corpus: a representative sample of vectors and source records, produced with the same embedding model and dimensions that production will use, along with expected results for a set of queries. A non-production PostgreSQL instance on the same major version is needed, plus monitoring data from the current workload. Confirm who signs off on acceptance criteria before work starts, since recall and latency targets must be set by your team.
Can our in-house team implement pgvector without outside help?
Often yes, if the team already runs PostgreSQL in production and includes people who can read query plans, memory settings and index behavior. The gaps are usually vector-specific: choosing between HNSW and IVFFlat indexes, understanding how the distance function relates to the embedding model, measuring recall against exact search, and planning index builds on large tables without disrupting writes. A useful test is whether your team can produce a recall and latency report on a representative corpus through your normal change process. If they can, outside help may be limited to review or specific tuning. If they cannot, a fixed-scope engagement can cover the gap, and the team keeps the runbooks and results afterward. Check capacity for benchmarking and for the upgrade planning that follows, since both recur.
Is pgvector or a dedicated vector database the better choice?
It depends on the workload, the data governance model and how much operational surface you want to own. Keeping vectors in PostgreSQL places embeddings beside operational data, under the same backup, access and replication controls, and lets you combine similarity search with relational filters in one query. A dedicated engine such as Qdrant may fit better when vector volume, query throughput or filtering needs outgrow the PostgreSQL capacity you can allocate, or when the team wants a vector-specific API. Qdrant's engine is open source and free to self-host, but its production guidance recommends block storage, at least three nodes for a quorum-based cluster, and replication that multiplies storage. Test both options on the same corpus, and include the cost of a second data store with its own backups, upgrades and access reviews.
How do PostgreSQL major upgrades affect a pgvector index?
The effect depends on the upgrade method and the extension version, so confirm both in a rehearsal before relying on either. PostgreSQL minor releases do not change the internal storage format, so they mean replacing executables and restarting. Major upgrades use dump and restore, pg_upgrade, or logical replication. Logical replication works across major versions and can limit the switchover to several seconds of downtime. With pg_upgrade, the target server needs the matching extension installed, and you should check whether vector indexes must be rebuilt for the new version. Run a test upgrade on a copy of production data, then measure index build time and query latency afterward. Supported PostgreSQL major versions currently run from 14 to 18, with 14 supported until November 12, 2026, so plan the upgrade calendar early.
How is a pgvector implementation quoted and scoped?
DeployOpen quotes from a written scope. Most engagements are fixed-scope, so scope, deliverables and timelines are agreed in writing before work starts. A useful quote states the environments covered, the number of tables and embedding columns in scope, the index types and distance functions to be evaluated, the benchmark and load test method, the documentation handed over, and what is excluded, such as the embedding pipeline or application changes. Ask whether post-go-live tuning, upgrade support and re-benchmarking are included or offered as a retainer, since those costs recur. DeployOpen does not publish a price list. Request line items so you can see which parts depend on your inputs, and confirm that assumptions about the test corpus and your team's availability appear in the scope.
How DeployOpen scopes and delivers a pgvector implementation
DeployOpen plans, builds and supports pgvector inside the PostgreSQL estate your team already operates, so embeddings stay in infrastructure you control. Every engagement starts from a written, fixed scope that names the indexes, workloads and deliverables before build work begins. Where a dedicated vector database fits the workload better, the scoping stage is where that decision is recorded.
How we scope
- Inventory the PostgreSQL major versions, hosting model and extension packaging you run today
- Define embedding dimensions, distance functions and query patterns the workload needs
- Agree the written, fixed-scope deliverables and schedule before build work starts
How we build
- Set up pgvector in a test cluster, then compare HNSW and IVFFlat indexes on your data
- Tune index build parameters and memory settings against measured recall and build time
- Test filtered queries, backups, replication and upgrades alongside the vector workload
How we hand over
- Deliver index definitions, extension version records and configuration documented for your operators
- Provide runbooks for index rebuilds, reindexing after large loads and capacity checks
- Walk your team through restore tests and the decision record for pgvector versus a dedicated database
How we support
- Plan pgvector upgrades alongside your PostgreSQL minor and major release schedule
- Confirm supported extension versions for each PostgreSQL major before scheduling any upgrade
- Offer ongoing support, upgrades and testing as a retainer after handover, if you choose
