With the widespread adoption of Postgres among developers, pgvector has emerged as a popular extension for vector similarity searches, which are essential in AI-powered applications. But as datasets grow larger, ensuring efficient and accurate searches becomes a challenge.

Is pgvector alone enough? Or should you combine it with IVFFlat indexing? This article unpacks the specifics of using IVFFlat indexing with pgvector and guides you through the decision-making process.

Read full article: Optimizing vector search performance with pgvector.

Sequential Scans with pgvector: A Starting Point

Out of the box, pgvector performs sequential scans to execute exact similarity searches. This approach guarantees 100% recall but may become inefficient with larger datasets.

EXPLAIN ANALYZE SELECT * FROM documents ORDER BY embedding <-> '[0.011699999682605267,..., 0.008700000122189522]' LIMIT 100;

This query compares the input vector with all vectors in the table, which can be expensive in terms of performance.

IVFFlat Indexing: A Leap Towards Efficiency

IVFFlat indexing comes into the picture as a more scalable solution. By leveraging the Inverted File Index (IVFFlat) for Approximate Nearest Neighbor (ANN) searches, pgvector can optimize the vector search process. It partitions the dataset into k-means clusters, thus performing the search on a smaller subset of the data.

Creating an IVFFlat index is as simple as executing the following query:

CREATE INDEX documents_embedding_cosine_idx ON documents USING ivfflat (embedding vector_l2_ops) WITH (lists = 1000);

However, to effectively use IVFFlat, you need to fine-tune two parameters: lists and probes.

• Lists: Denotes the number of k-means clusters.
• Probes: Indicates the number of clusters to be explored during the search.

SET ivfflat.probes = 100;
SET enable_seqscan=off;
SELECT * FROM documents ORDER BY embedding <-> '[0.011699999682605267,..., 0.008700000122189522]' LIMIT 100;

As the number of probes increases, the recall improves. However, beyond a certain point, there is a diminishing return on recall, and the execution time begins to rise.

Finding the Sweet Spot

Experimentation is key. Vary the number of lists and probes to find an optimal combination for your dataset. A good starting point is:

• For tables with up to 1 million rows, use a list size equal to rows/1000.
• For tables larger than 1 million rows, use a list size equal to the square root of the number of rows.
• For probes, start with a value equal to lists/10 for tables up to 1 million rows and the square root of the number of lists for larger datasets.

Making the Choice: pgvector with IVFFlat Indexing?

The decision to use IVFFlat indexing with pgvector should be guided by the scale of your dataset and the desired balance between speed and recall. For small datasets, sequential scans may suffice. However, as your data grows, IVFFlat indexing becomes increasingly attractive due to its efficiency and scalability.

In summary, if you're dealing with large datasets and performance is critical, IVFFlat indexing with pgvector is a powerful combination that can significantly optimize vector similarity searches in Postgres. The key is to fine-tune the parameters to strike the perfect balance for your specific use case.

Happy optimizing! 🚀