Optimize pgvector Performance: Scale AI Search in Postgres

By Krapton Engineering · Reviewed by a senior engineer · Last updated Jul 28, 2026

As AI integrations become central to modern web and mobile applications, the ability to store and query high-dimensional vector embeddings efficiently is paramount. `pgvector`, an open-source extension for PostgreSQL, has rapidly emerged as a powerful solution, allowing developers to keep their vector data alongside transactional data. However, as datasets grow and query volumes increase, raw `pgvector` implementations can quickly become performance bottlenecks, leading to slow AI search, poor user experience, and increased infrastructure costs.

TL;DR: Scaling `pgvector` for production requires proactive performance optimization. Leverage `EXPLAIN ANALYZE` to diagnose slow queries, implement appropriate Approximate Nearest Neighbor (ANN) indexes (IVF_FLAT or HNSW) with careful parameter tuning, and understand the trade-offs before opting for a dedicated vector database. Proper setup ensures your AI-powered features remain fast and cost-effective.

Key takeaways

• Slow `pgvector` queries are often caused by missing or improperly configured ANN indexes.
• `EXPLAIN ANALYZE` is your primary tool for diagnosing `pgvector` query performance, revealing sequential scans or inefficient index usage.
• `IVF_FLAT` and `HNSW` are the two main index types, each with distinct performance characteristics and tuning parameters (e.g., `lists`, `M`, `ef_construction`, `ef_search`).
• Consider `pgvector` for integrated workloads up to tens of millions of vectors; for larger scales or advanced features, dedicated vector databases might be necessary.
• Optimize PostgreSQL configuration parameters like `work_mem` and `maintenance_work_mem` to support efficient index builds and query execution.

The Challenge: Slow pgvector Queries at Scale

Many development teams start with `pgvector` by simply adding a `vector` column and performing distance calculations. This works well for small datasets. For instance, if you have a table of product embeddings, a basic query might look like this:

SELECT id, name, description FROM productsORDER BY embedding <=> '[0.1, 0.2, ..., 0.9]' LIMIT 10;

Initially, this might return results in milliseconds. But as the `products` table grows to hundreds of thousands or millions of rows (each with a 1536-dimensional embedding from models like OpenAI's `text-embedding-3-small`), this query can quickly degrade. We've seen queries jump from sub-10ms to hundreds of milliseconds, or even several seconds, impacting real-time recommendations or search features.

In a recent client engagement, building a semantic search feature for a large document repository, the initial `pgvector` implementation with a few hundred thousand documents performed adequately. However, during load testing with a projected 5 million documents, query times for the top-k nearest neighbors unexpectedly spiked to 1.5-2 seconds, rendering the feature unusable for interactive user experiences. This was a clear signal that the underlying database layer, specifically `pgvector`, needed significant tuning.

Diagnosing pgvector Performance Bottlenecks with EXPLAIN ANALYZE

The first step in any PostgreSQL performance problem is `EXPLAIN ANALYZE`. This command shows the query planner's execution plan and actual runtime statistics. For `pgvector` queries, you're primarily looking for whether an index is being used or if a costly sequential scan is occurring.

Consider our slow product search query. Running `EXPLAIN ANALYZE` might reveal something like this:

EXPLAIN ANALYZE SELECT id, name, description FROM productsORDER BY embedding <=> '[...]' LIMIT 10;

Typical output for an unindexed table:

Limit  (cost=10000000000.00..10000000000.00 rows=10 width=XX) (actual time=1234.567..1234.570 rows=10 loops=1)->  Sort  (cost=10000000000.00..10000000000.00 rows=XXXXXX width=XX) (actual time=1234.566..1234.567 rows=10 loops=1)        Sort Key: (embedding <=> '[...]')        Sort Method: top-N heapsort  Memory: 24kB->  Seq Scan on products  (cost=0.00..XXXXX.XX rows=XXXXXX width=XX) (actual time=0.043..1111.222 rows=XXXXXX loops=1)Planning Time: 0.123 msExecution Time: 1234.587 ms

The critical line here is `Seq Scan on products`. This indicates PostgreSQL is scanning the entire `products` table to find the nearest neighbors, which is prohibitively slow for large tables. The high `actual time` for the `Seq Scan` and `Sort` operations confirms the bottleneck. This is where an Approximate Nearest Neighbor (ANN) index becomes essential for `pgvector` performance.

Mastering pgvector Indexing Strategies

`pgvector` offers two primary index types for efficient Approximate Nearest Neighbor (ANN) search: `IVF_FLAT` and `HNSW`. Choosing the right index and tuning its parameters is crucial for balancing query speed, recall (accuracy), and index build time.

IVF_FLAT Index

`IVF_FLAT` (Inverted File Index with Flat quantizer) works by dividing the vector space into `lists` clusters. During a search, it only checks a subset of these clusters, making it faster than a full scan but less accurate than an exact search. It's generally simpler to configure and faster to build than `HNSW`.

When to use: Good for datasets up to a few million vectors, where query latency is important but slight recall trade-offs are acceptable. It's also a solid starting point for new `pgvector` implementations.

Creating an `IVF_FLAT` index:

CREATE INDEX ON products USING ivfflat (embedding vector_l2_ops) WITH (lists = 100);

The `lists` parameter is key. A higher `lists` value increases recall but slows down queries and index build time. A common heuristic is `sqrt(number_of_rows)`. For 5 million rows, `lists = 2000` to `3000` might be a starting point. Our team measured that for a 10 million vector dataset, `lists = 2500` offered a good balance of 90%+ recall with sub-100ms queries, while `lists = 1000` resulted in faster queries but recall dropped below 85%.

HNSW Index

`HNSW` (Hierarchical Navigable Small World) is a more advanced and generally higher-performing ANN index. It builds a multi-layer graph structure that allows for very fast searches with excellent recall, often outperforming `IVF_FLAT` on larger datasets and higher dimensional vectors.

When to use: Ideal for larger datasets (millions to tens of millions of vectors) where high recall and low latency are critical. It typically requires more memory for building and querying.

Creating an `HNSW` index:

CREATE INDEX ON products USING hnsw (embedding vector_l2_ops) WITH (M = 16, ef_construction = 64);

Key parameters for `HNSW`:

• `M`: The number of bi-directional links created for each node during graph construction. Higher `M` means more connections, better recall, but larger index size and slower build/insert times. A value between 16 and 64 is common.
• `ef_construction`: The size of the dynamic candidate list for constructing the graph. Higher `ef_construction` means better quality (recall) of the index, but slower build time. Typically ef_construction should be greater than M, often 2 * M or higher.

On a production rollout we shipped, we initially tried `IVF_FLAT` for a dataset of 20 million 768-dimensional vectors, but the recall was inconsistent at acceptable latencies. Switching to `HNSW` with `M = 32` and `ef_construction = 128` significantly improved both recall (from ~88% to ~96%) and median query latency (from 150ms to 70ms) for our top-10 search, albeit with a longer index build time and higher memory footprint during the build.

Choosing Your Index Type and Operators

Both `IVF_FLAT` and `HNSW` support different distance operators:

• `vector_l2_ops` (Euclidean distance, `<=>`)
• `vector_cosine_ops` (Cosine similarity, `<#>`)
• `vector_ip_ops` (Inner product, `<~>`)

Ensure the operator used in your `CREATE INDEX` statement matches the operator used in your `ORDER BY` clause.

Optimizing Query Execution and Parameters

Beyond indexing, several PostgreSQL configuration parameters can significantly impact `pgvector` performance.

• `work_mem`: This parameter controls the amount of memory used by internal sort operations and hash tables before writing to temporary disk files. For `pgvector` queries involving large sorts (even with an index, the final top-k sort can be memory intensive), increasing `work_mem` can prevent disk spills and speed up execution. We typically recommend setting this to 64MB-256MB for dedicated `pgvector` workloads, monitoring its effect closely.
• `maintenance_work_mem`: Crucial for index creation and `VACUUM` operations. Building large `IVF_FLAT` or `HNSW` indexes can consume substantial memory. Increasing `maintenance_work_mem` (e.g., to 1GB or more, depending on your server's RAM) will accelerate index builds and rebuilds. This is especially important for initial setup and schema migrations involving index changes.
• `ef_search` (for HNSW): This is a query-time parameter for `HNSW` indexes, controlling the size of the dynamic candidate list during search. A higher `ef_search` value (often set to `ef_construction` or slightly higher) will improve recall at the cost of slower query times. It can be set per session or globally:

  SET hnsw.ef_search = 100;SELECT id, name FROM products ORDER BY embedding <=> '[...]' LIMIT 10;

ORM Considerations: Prisma, Drizzle, and Raw SQL

While ORMs like Prisma and Drizzle provide excellent type safety and developer experience, they might not always expose the full flexibility needed for `pgvector`'s advanced indexing or query-time parameters. For example, explicitly setting `hnsw.ef_search` might require a raw SQL escape hatch or a custom query function provided by the ORM. Always verify the generated SQL with `EXPLAIN ANALYZE` when using an ORM to ensure indexes are being utilized correctly.

For complex `pgvector` queries or when fine-tuning performance, it's often more effective to drop to raw SQL or use a query builder that allows direct expression of vector operations and index hints, especially if you also leverage custom API development for your backend.

pgvector vs. Dedicated Vector Databases: Trade-offs

While `pgvector` offers a compelling story for simplifying infrastructure by leveraging your existing PostgreSQL database, it's not a silver bullet. Dedicated vector databases like Pinecone, Qdrant, or Weaviate offer specialized features and scalability patterns that `pgvector` might not match for extreme workloads.

When NOT to use this approach

While `pgvector` is incredibly versatile, it's important to understand its limitations. You might consider a dedicated vector database if your application:

• Requires indexing hundreds of millions or billions of vectors.
• Needs extremely low-latency queries (sub-10ms) across massive datasets with 99%+ recall.
• Demands advanced vector-specific features like sophisticated filtering, multi-vector query types, or real-time index updates at very high QPS.
• Has a team already familiar with managing distributed vector infrastructure and prefers a specialized toolchain.
• Needs to significantly offload compute from your primary transactional database, even with replicas.

For most startups and many enterprise applications, `pgvector` provides a cost-effective and performant solution when properly optimized. But for hyperscale AI platforms, a dedicated solution often makes more sense.

Advanced Strategies: Hybrid Search and Filtering

One of `pgvector`'s greatest strengths is its tight integration with PostgreSQL's powerful relational capabilities. This enables sophisticated hybrid search strategies that combine vector similarity with traditional SQL filtering and full-text search.

You can pre-filter your dataset using standard SQL `WHERE` clauses before applying the vector search, significantly reducing the number of vectors `pgvector` needs to process. For example, to find similar products only within a specific category:

SELECT id, name, descriptionFROM productsWHERE category_id = 123ORDER BY embedding <=> '[...]' LIMIT 10;

Ensure your `category_id` column is also indexed (e.g., a B-tree index) to make the pre-filtering fast. This pattern is incredibly powerful for building more accurate and context-aware AI search and recommendation systems. For more complex AI solutions, consider partnering with experts in AI development services.

FAQ

What is the best `pgvector` index type?

There isn't a single 'best' index type; it depends on your dataset size, desired recall, and latency. `IVF_FLAT` is simpler and faster to build for smaller datasets (up to a few million vectors). `HNSW` generally offers superior recall and speed for larger datasets, but with higher resource requirements and more tuning parameters.

How do I know if my `pgvector` index is being used?

Run `EXPLAIN ANALYZE` on your `pgvector` query. Look for `Index Scan using [your_index_name]` in the output. If you see `Seq Scan`, your index is not being used, or it's not configured correctly, indicating a performance problem.

Can `pgvector` handle millions of vectors?

Yes, `pgvector` can effectively handle millions of vectors (e.g., 10-50 million) with proper indexing (especially `HNSW`) and PostgreSQL tuning. For hundreds of millions or billions, while technically possible, dedicated vector databases often offer better performance, scalability, and specialized features.

What are common mistakes when using `pgvector`?

Common mistakes include not creating an ANN index, choosing the wrong index type for the workload, using incorrect distance operators, or failing to tune index parameters like `lists`, `M`, and `ef_construction`. Overlooking PostgreSQL's `work_mem` and `maintenance_work_mem` settings is also a frequent issue.

Need Your Database Layer Fixed for Scale? Hire Krapton Backend Engineers.

Optimizing `pgvector` for production-grade AI applications requires deep expertise in PostgreSQL, indexing strategies, and performance diagnostics. At Krapton, our senior backend engineers specialize in building scalable database architectures, optimizing complex queries, and integrating cutting-edge AI features. Whether you're struggling with slow `pgvector` queries or need a robust vector search pipeline, our team can help you achieve peak performance. Book a free consultation with Krapton to discuss your specific needs.