--- title: "Turning PostgreSQL Into a Vector Database With pgvector" description: "The complete guide to pgvector — install, index, and query vectors in PostgreSQL. Covers HNSW, IVFFlat, embeddings, and similarity search with examples." section: "Postgres extensions" published: 2024-03-06T15:52:28.205Z updated: 2026-08-31T00:00:00.000Z --- *Updated at Aug 31, 2026* > **TimescaleDB is now Tiger Data.** *Originally published on March 6, 2024 -* pgvector is a PostgreSQL extension that gives the database a new data type built specifically for high-dimensional vectors, along with the operators and functions needed to work with them. In practice, this means you can store, search, and analyze vector data directly inside PostgreSQL. If you need vector database capabilities, PostgreSQL with pgvector already covers it, so there's no need to bolt on a separate system. The [Tiger Cloud platform](https://console.cloud.tigerdata.com/signup?utm_campaign=vectorlaunch&utm_source=pgv-learn&utm_medium=direct) includes two open-source [PostgreSQL extensions](https://www.tigerdata.com/blog/top-8-postgresql-extensions) built by the Tiger Data  team that pair naturally with pgvector. [pg_textsearch](https://github.com/timescale/pg_textsearch) brings a native BM25 search index to PostgreSQL, and [pgvectorscale](https://github.com/timescale/pgvectorscale/) helps developers build scalable AI applications with faster embedding search and more cost-efficient storage.  The numbers back this up: in benchmarks on 50 million 768-dimensional vectors at 99% recall,[ pgvectorscale's StreamingDiskANN index hit 471 queries per second](https://www.tigerdata.com/blog/how-we-made-postgresql-as-fast-as-pinecone-for-vector-data), putting PostgreSQL on par with dedicated vector databases at production scale. [You can dig deeper into both extensions in this article](https://www.tigerdata.com/blog/making-postgresql-a-better-ai-database). This article walks through pgvector's main features and use cases, with examples that show it analyzing relational and time-series data alongside TimescaleDB. ## **What pgvector does and how to enable it** pgvector lets you store, modify, and query vectors inside PostgreSQL. This allows similarity search, semantic search, retrieval-augmented generation (RAG), image search, recommendation systems, natural language processing (NLP), and computer vision, all from within your existing database. To get started, you need to understand what vectors are and how pgvector exposes them. ### **What are vectors?** Vectors are mathematical representations of data points in multidimensional space. In practice, they're arrays of numbers that capture the essential features of something, whether that's a word, an image, or a sensor reading. In machine learning and data science, this numerical format is what makes efficient computation and analysis possible in the first place. Words, sentences, and documents can all be turned into vectors, which is what makes semantic search possible. Instead of matching exact keywords, you can find content with similar meaning even when the wording is completely different. This is the mechanism behind finding documents with related content or answering queries based on the context of the text rather than a literal string match. The same idea applies to images and video. Once visual data is converted into vectors, pgvector can search for similar images or clips, which powers use cases like facial recognition, object detection, and content-based image retrieval. Vectors are just as useful outside of text and images. In time-series data, vectors can capture what "normal" behavior looks like, so anomalies show up as vectors that drift noticeably from that baseline. Key features and use cases of pgvector include: - **Vector storage:** pgvector lets you store high-dimensional vectors directly in PostgreSQL tables, with a dedicated data type for efficient storage and retrieval. - **Similarity search:** pgvector supports [similarity searches](https://www.tigerdata.com/learn/vector-search-vs-semantic-search) based on cosine similarity or Euclidean distance, which powers content-based recommendation systems, k nearest-neighbor search, and clustering. - **Semantic search:** Large language model (LLM) embeddings create vectors from text, images, and other data types. These embeddings represent the meaning of the underlying data, so similarity search over them returns results with similar semantic meaning regardless of original format. - **Natural Language Processing and text analysis: **[Vector embeddings](https://www.tigerdata.com/blog/a-beginners-guide-to-vector-embeddings) capture the underlying meaning of text, enabling vector-based operations like similarity search, clustering, or classification on textual data. - **Computer vision:** pgvector handles vector representations of images and enables similarity-based image search. You can convert images to vector representations using convolutional neural networks (CNN) or image embeddings, then perform content-based image retrieval, image similarity matching, object identification, and image clustering within the database. *pgvector stores vectors that represent semantic meaning. Things that are meaningfully similar are "closer" together in the vector space. This allows people searching for SUVs to find cars and trucks instead of watermelons and apples, even though SUVs and trucks are different words.* ## Using pgvector for Vector Data pgvector lets developers store and query vector data within PostgreSQL, making it practical to build generative AI applications with LLMs, as well as AI applications that require similarity search, recommendation systems, NLP, and other tasks that involve working with vectors. [RAG now accounts for 51% of enterprise AI implementations](https://www.tigerdata.com/blog/rag-is-more-than-just-vector-search), making pgvector's production-readiness more important than ever. **Installation** options depend on whether you're using a managed cloud service or a self-hosted PostgreSQL instance. Tiger Cloud's database instances come with pgvector pre-installed. All you have to do is run CREATE EXTENSION or connect to the PostgreSQL service with TimescaleDB's [Python client library](https://github.com/timescale/python-vector). [Get started with pgvector on Tiger Cloud](https://console.cloud.tigerdata.com/signup?utm_campaign=vectorlaunch&utm_source=pgv-learn&utm_medium=direct). For self-hosting, install the pgvector extension on your server by following the [pgvector installation instructions for Linux, MacOS, and Windows](https://github.com/pgvector/pgvector?tab=readme-ov-file#installation). Once installed, enable the extension in your database: `CREATE EXTENSION vector; ` ` `**Vector data type.** pgvector introduces a vector data type representing a high-dimensional vector. You can define vector-type columns in your database tables to store vector data. For example, a documents table with an embedding column of type vector stores LLM embeddings for each document. pgvector 0.8.0 also introduced a `halfvec` type that stores half-precision (16-bit) floats, cutting storage requirements roughly in half with minimal impact on search quality. `CREATE TABLE documents ( id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, created_at TIMESTAMPTZ, author_id BIGINT, content TEXT, embedding HALFVEC(1536) );` **Indexing and vector search.** pgvector provides indexing mechanisms optimized for approximate nearest neighbor (ANN) search over vector data. Two index types are available, HNSW (Hierarchical Navigable Small World, a graph-based vector indexing method that connects similar vectors in layers to enable fast approximate similarity search) and IVFFlat (Inverted File with Flat compression, a vector indexing method that groups vectors into clusters to speed up approximate similarity search). HNSW is the recommended default for most workloads, offering higher recall and lower query latency, and it can be built on an empty table, which is useful for CI/CD workflows. You can create an HNSW index using the CREATE INDEX command with the halfvec_cosine_ops operator class, then use the <=> operator to perform [similarity searches](https://www.tigerdata.com/learn/vector-search-vs-semantic-search): `CREATE INDEX ON documents USING hnsw (embedding halfvec_cosine_ops); --find the closest 5 elements to a given query SELECT * FROM documents ORDER BY embedding <=> '[10.5, 11.0,...]'::halfvec(1536) LIMIT 5;` pgvector 0.8.0 introduced iterative scans for filtered queries, which resolves a previous limitation where WHERE clause filters applied after the index scan could return fewer results than expected. Filtered searches combining vector similarity with SQL conditions are now reliable in production. **Vector functions.** pgvector includes built-in functions to manipulate and perform operations on vector data. You can use the cosine_distance function to calculate the cosine similarity between two vectors, vector_norm() to get the Euclidean norm, and vector_dims() to determine how many dimensions a vector contains. **Vector aggregates.** pgvector provides avg(vector) and sum(vector) aggregate functions for calculating analytics on vectors. **Integration with other PostgreSQL features.** pgvector integrates with PostgreSQL's transaction management, [query optimization](https://www.tigerdata.com/blog/best-practices-for-query-optimization-in-postgresql), and security. Joining pgvector data with relational, time-series, and geospatial data is a practical way to enrich your vector queries. As a simple example, you could return author information for documents found via similarity search: `WITH matching_docs as ( --find 5 closest matches SELECT * FROM documents ORDER BY embedding <=> '[10.5, 11.0,...]'::halfvec(1536) LIMIT 5 ) SELECT d.content, a.first_name, a.last_name FROM matching_docs d INNER JOIN author a ON (a.id = d.author_id);` ## **Why use pgvector with TimescaleDB** TimescaleDB is PostgreSQL engineered for time-series data, events, and analytics. It provides a foundation for storing, retrieving, and [analyzing large volumes of time-series data](https://www.tigerdata.com/blog/time-series-analysis-what-is-it-how-to-use-it) through time-based aggregations, [data retention policies](https://www.tigerdata.com/docs/build/data-management/data-retention/create-a-retention-policy), and features like [hypertables](https://www.tigerdata.com/docs/learn/hypertables/understand-hypertables) and [continuous aggregates](https://www.tigerdata.com/learn/postgresql-materialized-views-and-where-to-find-them) (materialized views). Many real-world AI applications have retrieval or analytical requirements that include both vector data and a temporal aspect to similarity searches, covering embeddings of news, legal documents, and financial statements. In such cases, users want to find similar documents within a specific time frame. Another common use is time-weighting, where users may not apply a strict time filter but still prefer more recent or older data. For such workloads, TimescaleDB can markedly improve data ingestion and query speeds through [hypertables](https://www.tigerdata.com/learn/is-postgres-partitioning-really-that-hard-introducing-hypertables), which provide automatic time-based partitioning of tables. TimescaleDB is particularly good at optimizing queries that filter data based on time, enabling those queries to run more efficiently. For workloads exceeding tens of millions of vectors, the [pgvectorscale](https://github.com/timescale/pgvectorscale/) extension adds a StreamingDiskANN index that stores the full index on disk, bypassing the memory constraints of HNSW and allowing you to store larger datasets within PostgreSQL. [According to the 2025 Stack Overflow Developer Survey](https://survey.stackoverflow.co/2025/technology#1-databases), **PostgreSQL commands 55.6% developer adoption**, making it the most widely used database for three consecutive years, and staying on PostgreSQL for vector workloads avoids the operational overhead of a separate vector database. Another common use-case is storing telemetry, machine data, or other measurements in TimescaleDB and storing documentation about that data in the same database indexed with pgvector. That allows the databases to become “self-documenting” and answer questions both about the data and about what generated the data. ## **How to use pgvector with TimescaleDB** ### **Step 1: Set up pgvector and TimescaleDB on a PostgreSQL instance** You can do this either with a cloud-hosted database on Tiger Cloud (recommended) or a self-hosted instance. Tiger Cloud is the easiest path to a managed database instance, and Tiger Data comes with pgvector and TimescaleDB already pre-installed. [Get started for free](https://console.cloud.tigerdata.com/signup?utm_campaign=vectorlaunch&utm_source=timescale-blog&utm_medium=direct&utm_content=how-we-made-postgresql-the-best-vector-database) with Tiger Cloud for 90 days. The only thing you need to do is execute: `CREATE EXTENSION vector;` For self-hosted setups, [follow the TimescaleDB installation guide](https://www.tigerdata.com/docs/get-started/choose-your-path/install-timescaledb) based on your PostgreSQL version, then [follow the pgvector installation instructions](https://github.com/pgvector/pgvector?tab=readme-ov-file#installation) for Linux, MacOS, and Windows. You then need to execute the following: `CREATE EXTENSION vector; CREATE EXTENSION timescaledb;` ### **Step 2: Create a TimescaleDB hypertable with a vector column** Create a regular table in your database that will form the basis for the [hypertable](https://www.tigerdata.com/blog/database-indexes-in-postgresql-and-timescale-cloud-your-questions-answered). Convert the table into a hypertable using the create_hypertable function provided by TimescaleDB, specifying the time column and other relevant options. `CREATE TABLE documents ( id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, created_at TIMESTAMPTZ, author_id BIGINT, content TEXT, embedding HALFVEC(1536) ); SELECT create_hypertable('documents', 'created_at');` ### **Step 3: Insert vector data into the hypertable** Use regular SQL INSERT statements to insert data into the hypertable, including the vector data in the designated vector column. Ensure that the vector data is in the correct format expected by pgvector. You'll need to get the vector representation from an embedding model (e.g., [OpenAI text-embedding-3 models](https://developers.openai.com/api/docs/guides/embeddings) or open-source [sentence transformers](https://www.sbert.net)). `INSERT INTO documents (created_at, content, embedding) VALUES ('2023-06-01 00:00:00', 'the quick', '[1,2,3,...]'), ('2023-06-02 00:00:00', 'brown fox', '[4,5,6,...]'), ('2023-06-03 00:00:00', 'jumped over', '[7,8,9,...]');` ### **Step 4: Query and analyze vector data** SQL queries allow you to perform various operations on the vector data stored in the hypertable. You can combine TimescaleDB's time-series functions with pgvector's vector functions for analysis and querying. Operations you can perform include similarity search (finding vectors similar to a given query vector using the <=> operator, combined with filters on other columns such as time), aggregation and grouping (using TimescaleDB's time-series aggregation functions to aggregate vector data over time intervals or other dimensions), and filtering and selection (using regular SQL filters and conditions to select specific vector data based on certain criteria). `-- Similarity search SELECT * FROM documents ORDER BY embedding <=> '[1,2,3,...]'::halfvec(1536) LIMIT 5; -- Similarity search filtered by time SELECT * FROM documents WHERE created_at >= '2023-06-02 00:00:00' AND created_at < '2023-06-03 00:00:00' ORDER BY embedding <=> '[1,2,3,...]'::halfvec(1536) LIMIT 5; -- Aggregation and grouping SELECT time_bucket('30 day', created_at) AS day, count(*) AS count_documents FROM documents GROUP BY day ORDER BY day; -- Filtering and selection SELECT * FROM documents WHERE created_at >= '2023-06-02 00:00:00' AND created_at < '2023-06-03 00:00:00';` ### **Step 5: Optimize for performance** Once your data is loaded, create an index on the vector column. Index after bulk loading initial data — building on an already-populated table gives much better build performance than indexing row-by-row on insert. Use the [HNSW index type](https://www.tigerdata.com/blog/vector-database-basics-hnsw) as your default — it has a superior speed/recall tradeoff, needs no training step, and can even be built on an empty table. Store and index your embeddings as `halfvec` rather than `vector`: it cuts storage and index size roughly in half with minimal recall loss, so it should be your default type, not just a fallback for when memory gets tight. `CREATE INDEX CONCURRENTLY ON documents USING hnsw (embedding halfvec_cosine_ops);` A few rules worth following closely: - **Match your operator class to your distance function.** An index built with `halfvec_cosine_ops` only gets used by queries using the `<=>` operator — mismatch the two and Postgres silently falls back to a sequential scan. - **Cast query vectors explicitly** (e.g. `$1::halfvec(1536)`) to avoid implicit-cast failures in prepared statements. - **Build concurrently in production** (`CREATE INDEX CONCURRENTLY`) to avoid locking writes. **Tuning parameters.** HNSW exposes both build-time and query-time knobs: | **Parameter** | **Default** | **Effect** | | --- | --- | --- | | `m` | 16 | max connections per layer — higher improves recall at the cost of memory | | `ef_construction` | 64 | build-time candidate list — higher improves graph quality at the cost of build time | | `hnsw.ef_search` | 40 | query-time candidate list — higher improves recall at the cost of latency | The defaults (`m=16`, `ef_construction=64`) are a good starting point for most workloads. At query time, start with `SET hnsw.ef_search = 100` and increase further if you need higher recall — each step up roughly doubles query latency, so validate against your own p95/p99 rather than assuming. pgvector 0.8.0 also introduced parallel HNSW index builds, which reduce build times significantly on multi-core machines (typically 30–50% faster on large datasets); set `max_parallel_maintenance_workers` accordingly. **Scaling beyond memory.** HNSW's main constraint is that the index needs to stay resident in memory. As a rough guide, here's approximate `halfvec` capacity at `m=16` for 1536-dim vectors: | **RAM** | **Approx max halfvec vectors** | | --- | --- | | 16 GB | ~2–3M vectors | | 32 GB | ~4–6M vectors | | 64 GB | ~8–12M vectors | | 128 GB | ~16–25M vectors | For 3072-dim embeddings, divide these numbers by roughly 2. For `m=32`, divide again by roughly 2. These are directional ranges, not guarantees — validate against your own cache residency and p95/p99 latency under realistic load. If p95/p99 latency climbs while CPU stays idle, that's usually a sign the index no longer fits in memory. HNSW is the recommended default for most workloads up to around 10 million vectors. Beyond that point, rather than reaching for binary quantization, use **pgvectorscale's StreamingDiskANN index**, which stores the full index on disk with no memory ceiling — this avoids the accuracy tradeoffs of quantization-plus-reranking while still scaling to much larger datasets. It's also the better choice if you need label-based filtered search at scale, since StreamingDiskANN supports filtered indexes on `smallint[]` columns natively. Finally, tune the configuration parameters of TimescaleDB and PostgreSQL based on your workload and resource requirements to get optimal performance. ### **Step 6: Add hybrid search** Semantic search alone can miss exact terms — product names, error codes, SKUs — that matter as much as meaning. Hybrid search combines keyword search (BM25) with semantic vector search and merges the two rankings using Reciprocal Rank Fusion (RRF), improving recall over either method alone. Use hybrid search when queries mix specific terms with conceptual intent. Semantic-only search is fine when meaning matters more than exact wording (e.g., "how to fix slow queries" should still match "query optimization"); keyword-only is fine when exact matches are critical (error codes, legal citations). For most user-facing search, hybrid is the safer default. This combines [pg_textsearch](https://github.com/timescale/pg_textsearch) (BM25) with pgvector. Both extensions are required, and both indexes run against the same chunked content so the two rankings stay comparable: `CREATE EXTENSION IF NOT EXISTS vector; CREATE EXTENSION IF NOT EXISTS pg_textsearch; CREATE TABLE documents ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, content TEXT NOT NULL, embedding HALFVEC(1536) NOT NULL ); -- BM25 index for keyword search CREATE INDEX ON documents USING bm25 (content) WITH (text_config = 'english'); -- HNSW index for semantic search CREATE INDEX ON documents USING hnsw (embedding halfvec_cosine_ops);` Run the keyword and semantic queries in parallel from your application rather than sequentially in SQL — this keeps latency down and keeps the fusion logic simple:` -- Keyword search (BM25) — lower <@> score is a better match SELECT id, content FROM documents ORDER BY content <@> $1 LIMIT 50; -- Semantic search (run in parallel with the query above) SELECT id, content FROM documents ORDER BY embedding <=> $1::halfvec(1536) LIMIT 50;` Then fuse the two result sets client-side. RRF scores each row `1 / (k + rank)` and sums the score across both searches, so a document that ranks well on both counts more than one that only ranks well on one: `def rrf_fusion(keyword_results, semantic_results, k=60, limit=10): scores = {} content_map = {} for rank, row in enumerate(keyword_results, start=1): scores[row['id']] = scores.get(row['id'], 0) + 1 / (k + rank) content_map[row['id']] = row['content'] for rank, row in enumerate(semantic_results, start=1): scores[row['id']] = scores.get(row['id'], 0) + 1 / (k + rank) content_map[row['id']] = row['content'] sorted_ids = sorted(scores, key=scores.get, reverse=True)[:limit] return [{'id': i, 'content': content_map[i], 'score': scores[i]} for i in sorted_ids]` `k=60` is the standard smoothing constant and rarely needs tuning; 50 candidates per method is usually enough, and increasing that helps if relevant results are being missed. If one method should carry more weight — for example, favoring semantic matches for conceptual queries — multiply its contribution before summing (e.g. `semantic_weight = 2.0`) rather than changing k. For highest-quality results, add a reranking step on top of the fused candidates using a cross-encoder model (e.g. `cross-encoder/ms-marco-MiniLM-L-6-v2`, or a hosted reranker like Cohere's). Cross-encoders are too slow to run over the full table, so run RRF first with a larger limit (e.g. 100), then rerank just that candidate set and return the top 10. Reranking is optional — hybrid RRF alone is already a substantial improvement over single-method search. At scale, the same StreamingDiskANN guidance from pgvectorscale applies to the semantic half of a hybrid setup — swap in a `diskann` index in place of `hnsw` once you're past the memory ceiling or need label-based filtering, while keeping the BM25 index unchanged. ## **Next steps** Want to learn more about pgvector and how to use it? Read the following resources: - [For production hybrid search combining pgvector with BM25, see Tiger Data Search](https://www.tigerdata.com/search) - [Explore our documentation about working with pgvector for your AI application](https://www.tigerdata.com/docs/ai/latest) - How to build with pgvector: [PostgreSQL as a vector database: a pgvector tutorial](https://www.tigerdata.com/blog/postgresql-as-a-vector-database-using-pgvector) - Focus on performance: [How we made PostgreSQL as fast as Pinecone for vector data](https://www.tigerdata.com/blog/how-we-made-postgresql-as-fast-as-pinecone-for-vector-data) - [PostgreSQL and pgvector: now faster than Pinecone, 75% cheaper, and 100% open source](https://www.tigerdata.com/blog/pgvector-is-now-as-fast-as-pinecone-at-75-less-cost) Get started with pgvector on a production-ready cloud PostgreSQL platform. [Sign up for Tiger Cloud today](https://console.cloud.tigerdata.com/signup?utm_campaign=vectorlaunch&utm_source=pgv-learn&utm_medium=direct). With Tiger Cloud, you get pgvector, [pgvectorscale](https://github.com/timescale/pgvectorscale/), and [pgai](https://www.tigerdata.com/blog/pgai-giving-postgresql-developers-ai-engineering-superpowers) in a fully managed cloud database, turning PostgreSQL into a high-performance vector database without the operational overhead of running it yourself. ## **Frequently asked questions about pgvector** **What is pgvector and what does it add to PostgreSQL?** pgvector is a PostgreSQL extension that adds a dedicated vector data type, distance operators, and indexing support for high-dimensional vectors. It enables similarity search, semantic search, and embedding storage directly inside PostgreSQL without requiring a separate vector database system. **Which index type should I use with pgvector, HNSW or IVFFlat?** HNSW (Hierarchical Navigable Small World, a graph-based vector indexing method that connects similar vectors in layers to enable fast approximate similarity search) is the recommended default for most workloads. It delivers higher recall and lower query latency than IVFFlat (Inverted File with Flat compression, a vector indexing method that groups vectors into clusters to speed up approximate similarity search), and it can be built on an empty table, which makes it compatible with CI/CD workflows. IVFFlat requires a populated table before index creation and is generally less preferred for new deployments. **How does pgvector handle filtered similarity searches?** pgvector 0.8.0 introduced iterative scans for filtered queries, resolving a previous limitation where WHERE clause filters applied after the index scan could return fewer results than requested. Filtered searches combining vector similarity with standard SQL conditions are now reliable in production. **What is the halfvec type and when should I use it?** halfvec is a pgvector 0.8.0 data type that stores vectors using half-precision (16-bit) floats instead of 32-bit floats. It cuts storage requirements roughly in half with minimal impact on search quality, making it a practical choice when memory or disk costs are a concern on large embedding datasets. **How does TimescaleDB improve pgvector performance for time-series workloads?** TimescaleDB's hypertable feature automatically partitions tables by time, which allows PostgreSQL to skip irrelevant time partitions during query execution. For AI applications that filter embeddings by recency or time range (such as news, legal documents, or financial statements), this partition pruning can significantly reduce query latency compared to a standard PostgreSQL table. **When should I use pgvectorscale's StreamingDiskANN index instead of HNSW?** StreamingDiskANN stores the full index on disk rather than in memory, so it has no memory ceiling. For workloads exceeding tens of millions of vectors where HNSW's memory requirements become prohibitive, StreamingDiskANN is the practical alternative. In benchmarks on 50 million 768-dimensional vectors at 99% recall, it achieved 471 queries per second. **Can pgvector join vector search results with relational or time-series data?** Yes. Because pgvector runs inside PostgreSQL, you can join vector similarity results with any other table using standard SQL. A common pattern is to run a similarity search in a CTE, then join the results against an authors, products, or events table to enrich the output with relational metadata. **Does pgvector work with standard PostgreSQL features like transactions and access control?** pgvector integrates fully with PostgreSQL's transaction management, query optimization, and role-based access control. Vector columns behave like any other column type, so existing PostgreSQL tooling, backup strategies, and security configurations apply without modification.