PostgreSQL Extensions: Turning PostgreSQL Into a Vector Database With pgvector

pgvector is a PostgreSQL extension that provides powerful functionalities for working with vectors in a high-dimensional space. It introduces a dedicated data type, operators, and functions that enable efficient storage, manipulation, and analysis of vector data directly within the PostgreSQL database.

Timescale started supporting the extension in May, and we're already doing pretty cool stuff with it. Using it will allow you to query and analyze vector data alongside other structured data stored in Timescale.

Join us as we walk you through a few examples to improve and expand your time-series data analysis with pgvector.

But first, let's check the extension's main features and use cases.

Understanding pgvector: Enabling Vector Operations

The pgvector extension is a powerful tool for vector similarity search. It allows you to store, manipulate, and search vectors efficiently, enabling various applications such as similarity matching, recommendation systems, natural language processing (NLP), and computer vision.

Here are some of its key features and use cases:

1. Vector storage: The pgvector extension lets you store high-dimensional vectors directly in PostgreSQL tables. It provides a dedicated data type for vector representation, allowing efficient storage and retrieval of vector data.

2. Similarity search: With pgvector, you can perform similarity searches based on vector similarity metrics such as cosine similarity or Euclidean distance. This enables you to find similar vectors to a given query vector, facilitating applications like content-based recommendation systems, nearest-neighbor search, and clustering.

3. Natural Language Processing (NLP) and text analysis: pgvector is particularly useful in NLP applications. It allows you to represent text documents as vectors using techniques like word embeddings or document embeddings. You can then perform vector-based operations like similarity search, clustering, or classification on textual data.

4. Computer vision: The pgvector extension can handle vector representations of images and enable similarity-based image search. By converting images to vector representations using techniques like convolutional neural networks (CNN) or image embeddings, you can perform content-based image retrieval, image similarity matching, and image clustering within the database.

5. Integration with SQL queries: As mentioned, pgvector seamlessly integrates with SQL queries, allowing you to combine vector similarity search with other filtering or aggregation operations. This integration enables complex querying and analysis of vector data alongside other structured data stored in PostgreSQL.

Using pgvector for Vector Data

By utilizing the pgvector extension, developers can efficiently store and query vector data within PostgreSQL, making it easier to build applications that require similarity search, recommendation systems, NLP, and other tasks that involve working with vectors.

1. Installation: To get started, you need to install the pgvector extension. You can do this by downloading the pgvector extension files and compiling them with the PostgreSQL server. Once installed, you can enable the extension in your database using the CREATE EXTENSION command.

2. Vector data type: pgvector introduces a new data type called vector representing a high-dimensional vector. You can define type vector columns in your database tables to store vector data. For example, you can have a table called products with a features column of type vector to store feature vectors for each product.

3. Indexing and search: pgvector provides indexing mechanisms optimized for vector data. You can create an index on a vector column using the CREATE INDEX command, specifying the pgvector operator class. This enables fast similarity searches on vector data using various distance metrics like cosine similarity, Euclidean distance, etc. You can then use the @@ operator to perform similarity searches in your queries.

4. Vector functions: pgvector comes with a set of built-in functions to manipulate and perform operations on vector data. These functions allow you to calculate vector similarities, perform vector arithmetic, and more. For example, you can use the cosine_similarity function to calculate the cosine similarity between two vectors.

5. Feature extraction: pgvector also provides functions to extract features from raw data. For example, you can use the pgvector.get_vector function to convert textual data (e.g., sentences) into vectors using pre-trained models like Word2Vec or GloVe. This feature extraction capability enables you to convert unstructured data into structured vector representations for analysis and similarity searches.

6. Dimensionality reduction: Working with vector data can be computationally expensive. pgvector includes functionality for dimensionality reduction using techniques like Principal Component Analysis (PCA) and Locality-Sensitive Hashing (LSH). These techniques allow you to reduce the dimensionality of your vectors while preserving important characteristics and speeding up computations.

7. Integration with other PostgreSQL features: pgvector seamlessly integrates with other features of PostgreSQL, such as transaction management, query optimization, and security. You can combine pgvector queries with traditional SQL queries and leverage the power of PostgreSQL for complex data processing tasks.

Why Use pgvector With Timescale?

Timescale is PostgreSQL++ engineered for time-series data, events, and analytics. It provides a robust foundation for storing, retrieving, and analyzing large volumes of time-series data thanks to its time-based aggregations, data retention policies, and features like hypertables and continuous aggregates.

By now, you're probably wondering why you should query vector data in a time-series database instead of using a native vector DB. But that's the true beauty of Timescale—as a powerful PostgreSQL data platform, Timescale can handle multiple workload types, allowing you to query and analyze vector data alongside time-series data, event data, historical data, real-time data, or other structured data.

By keeping everything in the same database, you can speed up your workflows and drive faster insights for valuable data analysis.

How to use pgvector with Timescale

1. Install and configure Timescale: Install the TimescaleDB extension in your PostgreSQL database. You can follow the installation guide provided by TimescaleDB based on your PostgreSQL version or simply sign up for a 30-day free trial—no credit card required.

2. Install and configure pgvector: Download and install the pgvector extension for PostgreSQL. You can find the installation instructions and source code in the official pgvector repository. Enable the pgvector extension in your database using the CREATE EXTENSION command.

3. Create a Timescale hypertable with a vector column: Create a regular table in your database that will serve as the basis for the hypertable. Convert the table into a hypertable using the CREATE_HYPERTABLE function provided by TimescaleDB. Specify the time column and other relevant options. Add a column of type vector to the hypertable to store the vector data. You can use the ALTER TABLE command to add the column.

CREATE TABLE sensor_data (
  time TIMESTAMPTZ NOT NULL,
  data VECTOR
);
SELECT create_hypertable('sensor_data', 'time');
ALTER TABLE sensor_data ADD COLUMN data VECTOR;
Copy

Step 4: 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. Depending on your specific use case, you may need to pre-process or transform the raw data into vector representations.

INSERT INTO sensor_data (time, data)
VALUES ('2023-06-01 00:00:00', '1,2,3'),
       ('2023-06-02 00:00:00', '4,5,6'),
       ('2023-06-03 00:00:00', '7,8,9');
Copy

Step 5: Querying and analyzing vector data: Use SQL queries 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.

Examples of queries you can perform include:

• Similarity search: Find vectors similar to a given query vector using the @@ operator and similarity functions provided by pgvector.

• Aggregation and grouping: Use TimescaleDB's time-series aggregation functions to aggregate vector data over time intervals or other dimensions.

• Filtering and selection: Use regular SQL filters and conditions to select specific vector data based on certain criteria.

-- Similarity search
SELECT *
FROM sensor_data
WHERE data @@ '1,2,3'::vector;

-- Aggregation and grouping
SELECT time_bucket('1 day', time) AS day, avg(data) AS avg_data
FROM sensor_data
GROUP BY day
ORDER BY day;

-- Filtering and selection
SELECT *
FROM sensor_data
WHERE time >= '2023-06-02 00:00:00' AND time < '2023-06-03 00:00:00';Copy

Step 6: Optimization and performance: Depending on the scale and complexity of your data, consider optimizing the performance of your queries by creating indexes on the vector column using the CREATE INDEX command with the pgvector operator class. Tune the configuration parameters of TimescaleDB and PostgreSQL based on your workload and resource requirements to achieve optimal performance.

CREATE INDEX data_vector_idx ON sensor_data USING pgvector(data);
Copy

Next Steps

Want to learn more about pgvector and how to use it? Read the following resources:

• PostgreSQL as a Vector Database: Create, Store, and Query OpenAI Embeddings With pgvector

• How to Build LLM Applications With pgvector Vector Store in LangChain

• Nearest Neighbor Indexes: What Are ivfflat Indexes in pgvector and How Do They Work