Category: All posts
Jun 13, 2024
Pinecone and PostgreSQL with the pgvector extension are two of the most popular vector databases to use when developing AI applications. On one hand, you have Pinecone, which is a proprietary managed vector database, specifically designed for vector workloads. On the other hand, there’s PostgreSQL, the popular and robust general-purpose relational database with the pgvector extension, which adds support for vector storage and search.
Both PostgreSQL and pgvector are open source, with the flexibility to deploy locally, self-manage, or from several managed database providers (Timescale included). PostgreSQL is a mature database with advanced production-necessary features for high availability, streaming replication, point-in-time recovery, and observability. Pinecone’s development concentrated on performing fast vector search but is noticeably less mature in operational features.
So the question is: when building an AI application, do you need a standalone vector database like Pinecone, or can you use PostgreSQL, a database you might already be familiar with and know how to operationalize (and already deploy in your data stack)? And what’s more, which of the two is the better choice for large-scale vector workloads that are common in production AI applications like RAG (retrieval-augmented generation), search, and AI agents?
In this blog post, we compare the performance, cost, and ease of use of both Pinecone and PostgreSQL with pgvector, but with an added twist. We also factor in pgvectorscale, a new open-source PostgreSQL extension that builds on pgvector for greater performance and scalability, making PostgreSQL a better database for AI applications.
Pgvectorscale adds specialized data structures and algorithms for large-scale vector search and storage to PostgreSQL as an extension.
Pgvectorscale complements pgvector by building on its data type and distance functions with a new search index, StreamingDiskANN, which is purpose-built for high performance and cost-efficient scalability. StreamingDiskANN overcomes limitations of in-memory indexes like HNSW (hierarchical navigable small world) by storing the index on disk, making it more cost-efficient to run and scale as vector workloads grow.
Pgvectorscale also adds Statistical Binary Quantization, a compression technique, developed by researchers at Timescale, which improves upon standard binary quantization techniques by improving accuracy when using quantization to reduce the space needed for vector storage. This makes index traversal faster without sacrificing high accuracy.
Before we start benchmarking, check the pgvectorscale GitHub repository to learn more about pgvectorscale. For more information about pgvectorscale, see this post about why we built it. For those looking for in-depth technical details, our Engineering team goes into heavy detail in this article.
Before we “delve” into the methodology of how we compared Pinecone vs. PostgreSQL with pgvector and pgvectorscale, let’s summarize what we found for those looking for a TL;DR:
Thanks to pgvectorscale and pgvector, developers can now use the open-source general-purpose PostgreSQL database to achieve comparable (and often superior) performance to specialized vector databases like Pinecone.
And in conclusion, we show that even with large scale vector workloads common in production AI applications, PostgreSQL is all you need. And that developers can resist the temptation to go after the shiny new database, and instead rely on the PostgreSQL foundation they know and love, with extensions for purpose-built performance and scalability. This is also a testament to the commitment and consistent efforts of the PostgreSQL community, of which we are proud members.
Now that you know the destination, let’s begin the benchmarking journey.
Benchmarking tool: We used a fork of the industry standard, the open-source ANN-Benchmarks tool, to benchmark both Pinecone and PostgreSQL with pgvectorscale. Before testing performance, we modified it to correctly measure queries per second (QPS) when using multiple threads and to run different queries to warm up (versus test) the index. You can find all of our modifications in this tag of our fork of ANN-Benchmarks.
Dataset: 50 million Cohere embeddings of 768 dimensions each. The dataset was created by concatenating multiple Cohere Wikipedia datasets until we had 50 million vectors of 768 dimensions in our training dataset and 1,000 in our test dataset. Links to datasets are publicly available on HuggingFace here:
Client machine details: A standalone client machine ran the ANN-Benchmarks tool. This client machine was in the same region as the Pinecone index and PostgreSQL machine to ensure we had an even playing field regarding network latency. This client machine had 16 CPUs and 64 GB of RAM. We downloaded the dataset before the benchmarking started; it was not streamed during the runs. We used the gRPC version of the Python client for Pinecone, which gave us better results than the HTTP(S) version.
Testing methodology: The client ran 29,000 queries in each benchmark using training vectors to “pre-warm” the system. Then, the client used the 1,000 “real” test vectors, which were different from the pre-warm set, to query. We only used the figures from the test vectors for the results.
Pod-based indexes: We benchmarked three Pinecone pod-based indexes, p1, p2, and s1. We did not benchmark the Pinecone serverless index as queries were rate-limited. Pinecone suggests using its serveless offering only for workloads of less than 5 QPS and is thus unsuitable for the scale of our benchmark at the time of testing. See the table below for an explanation of Pinecone’s pod-based indexes.
General approach: We benchmarked many different Pinecone pod-based index configurations. We tried storage-optimized (S1) and performance-optimized (P1, P2) pod types with various pod sizes and replica settings. With a limited time and budget, a real-world struggle faced by many developers, we did not explore every combination possible but did our best to explore the space.
Best performing configurations: the best configurations we found are listed below.
Storage-optimized (s1)
Performance-optimized (s1)
Performance-optimized (p2)
General approach: We experimented with various PostgreSQL machine, database, and index configurations. We self-hosted the PostgreSQL instance on AWS EC2 to accurately reflect the experience of running fully open-source software for developers.
Machine details:
PostgreSQL settings
StreamingDiskANN index: Rather than using the (HNSW or IVFFlat indexes in pgvector, we used the StreamingDiskANN index for large-scale approximate nearest neighbor search. The StreamingDiskANN index for pgvector is a key innovation introduced by the pgvectorscale extension.
StreamingDiskANN index parameters: Unlike Pinecone, pgvectorscale’s StreamingDiskANN index exposes parameters to users to configure search performance for their specific workload. We used the following index parameters; most are default values, and non-default parameters are marked with an asterisk (*):
Overview: Pinecone’s s1 index is the most “apples to apples'' comparison to PostgreSQL with pgvector and pgvectorscale because (1) it is optimized for storage, making it suitable for the 50 million vector dataset, and (2) it is the only pod-based index type which keeps raw vectors on disk rather than in fully in memory, mirroring pgvectorscale’s StreamingDiskANN index storage architecture. The s1 pod was Pinecone’s highest accuracy pod, reaching 99 % recall, albeit at the cost of slower query speeds.
A results summary of PostgreSQL with pgvectorscale vs. Pinecone s1:
Overview: Pinecone’s p2 index is Pinecone’s highest-performance offering. It uses a graph-based index and stores raw vectors in memory, unlike pgvectorscale’s StreamingDiskANN, which combines memory and disk. It also trades off accuracy for speed, achieving a maximum of 90 % recall. We found that with pgvectorscale, PostgreSQL is comparable and actually outperformed Pinecone’s p2 performance-optimized index.
A results summary of PostgreSQL with pgvectorscale vs. Pinecone p2:
Pgvectorscale and pgvector have several operational advantages compared to Pinecone due to its PostgreSQL foundation:
Pgvectorscale is a new open-source extension that enables developers to build more scalable AI applications using PostgreSQL, with higher-performance embedding search and cost-efficient storage.
To test the performance impact of pgvectorscale, we created a fork of the ANN benchmarks tool to compare the performance of PostgreSQL and Pinecone on a dataset of 50 million Cohere embeddings.
Compared to Pinecone’s storage optimized index (s1), PostgreSQL with pgvector and pgvectorscale achieves 28x lower p95 latency and 16x higher query throughput for approximate nearest neighbor queries at 99 % recall, all at 25 % the monthly cost.
Compared to Pinecone’s performance-optimized index (p2), PostgreSQL with pgvector and pgvectorscale achieves 1.4x lower p95 latency and 1.5x higher query throughput for approximate nearest neighbor queries at 90 % recall, all at 21 % of the monthly cost.
Thanks to pgvectorscale, developers can now use the open-source general-purpose PostgreSQL database to achieve comparable (and often superior) performance to specialized vector databases like Pinecone.
Pgvector and pgvectorscale are both open source under the PostgreSQL License and are available for you to use in your AI projects today.
You can find installation instructions on the pgvector and pgvectorscale GitHub repositories, respectively. You can also access both pgvector and pgvectorscale on any database service on Timescale’s cloud PostgreSQL platform.
For production vector workloads, we’re offering private beta access to vector-optimized databases with pgvector and pgvectorscale on Timescale. Sign up here for priority access.
Both pgvector and pgvectorscale are open-source community projects. Here’s how you can get involved:
