How to Automatically Create & Update Embeddings in PostgreSQL—With One SQL Query
Creating vector embeddings is straightforward. Updating and keeping them in sync for production usage is anything but simple.
Any engineer who’s taken an AI system from MVP to production-ready can recount challenges like handling stale embeddings, synchronizing embeddings with new or changed source data, and building increasingly complex glue services to manage it all across multiple databases. All this complexity makes it unnecessarily hard for teams to integrate AI capabilities like search, recommendation systems, RAG (retrieval-augmented generation), and agentic behavior into their applications.
Pgai Vectorizer provides a simpler way forward. It makes it possible to automate embedding creation, keep them up-to-date as the source data changes, and manage it all within PostgreSQL without the complexity of building custom data workflows and processing systems. Pgai Vectorizer enables you to set up and manage vector embeddings using a single SQL command, just like creating a database index.
If you already use extensions like pgvector and pgvectorscale to utilize PostgreSQL as your vector database, pgai Vectorizer helps make PostgreSQL even more powerful for AI application development, allowing your teams to focus on building innovative applications faster rather than managing infrastructure.
We’ve already made the case for managing everything—from vectors to relational data—inside PostgreSQL with just SQL. In this blog post, we’ll demonstrate how pgai Vectorizer simplifies embedding creation within PostgreSQL and streamlines the process for building RAG systems using a product catalog dataset and OpenAI’s text-embedding-3-small
embedding model and gpt-4o mini
as our large language model.
One SQL Command to Run Them All: Pgai Vectorizer
Pgai Vectorizer is a powerful developer tool that automates the creation and management of vector embeddings with PostgreSQL. It abstracts embeddings into a declarative feature, allowing developers to manage embeddings with just a single SQL command.
Code comparison
With pgai Vectorizer, you only need to configure the vectorizer once and let it handle the rest automatically. This “set it and forget it” approach eliminates the need for ongoing management, streamlining your workflow.
Generating embeddings
Without pgai Vectorizer, you would need to manually call the embedding function for each entry and handle chunking and formatting independently. That requires a lot of surrounding infrastructure, such as a queuing system to handle the embedding endpoint being down, monitoring, alerting, proper retry handling, etc.—adding significant time and complexity.
On the other hand, pgai Vectorizer allows you to generate embeddings for the entire dataset with just one SQL command. You don’t need specialized tools: PostgreSQL is now a platform that helps you with your AI development workflow.
Updating embeddings
Without pgai Vectorizer, you would have to build a custom workflow involving functions, background workers, and state management to handle new or modified documents. With pgai Vectorizer, no additional code is needed. Just insert/delete/change data into the source table, and the vectorizer will automatically update the embeddings in the background.
Using pgai Vectorizer not only saves you development time but also simplifies your system architecture. All you need is to configure the vectorizer and an API key, reducing the complexity of managing multiple systems. With fewer components to monitor, you can focus on building the best AI application possible.
Key features of pgai Vectorizer
- Effortless configuration of embedding workflows with a simple, declarative command: with pgai Vectorizer, setting up the workflow for generating and updating embeddings is as easy as creating an index.
A single SQL command lets you define:
- Embedding model and dimensions: pgai Vectorizer is integrated with multiple AI providers, such as OpenAI (more will be added soon).
- Chunking and formatting: determine how your data is split into chunks and easily format them with more information for richer embeddings.
- Indexing options: select the best indexing for the separate embeddings table, which pgai Vectorizer creates to store the embeddings in order to optimize your similarity searches and simplify your queries.
- Automated embedding creation: after being configured, the vectorizer automatically manages the embedding generation for existing and new data.
- Automated embedding synchronization: pgai Vectorizer seamlessly updates embeddings when sourcing data changes (inserts, updates, and deletes) and ensures that data and embeddings remain in sync.
- Scalable performance: this vectorizer is designed to handle large volumes of data powered by batch processing and concurrent execution.
- Seamless integration: pgai Vectorizer is compatible with AI-related PostgreSQL extensions such as pgvector, pgvectorscale, and pgai, enabling developers to build AI-powered applications entirely within PostgreSQL.
Pgai Vectorizer encourages developers to focus on building their next innovative product without needing to maintain infrastructure. It also accelerates AI development and reduces the complexity of working with vector data, making PostgreSQL an even more attractive platform for developers building AI applications.
This new PostgreSQL tool is available on Timescale Cloud or as a self-hosted option, offering flexibility for various deployment scenarios.
Configuring and Using Pgai Vectorizer on Timescale Cloud
In this section, we build a simple RAG system to illustrate how pgai Vectorizer works. We use the cloud version, which is accessible via Timescale Cloud. (If you want to try it, you can create a free account and get a 30-day free trial!) You can find the code in this GitHub repository or this Google Colab notebook.
Prerequisites
- OpenAI API key: If you don’t have an OpenAI developer account, sign up for one and configure an API key here.
Database connection string: After creating your Timescale account, define a database service by clicking on New service, choosing AI and Vector, and configuring your database specifications. For this specific dataset, I suggest 0-100k vectors or 0.5 CPU/2 Gib memory for the compute size. Make sure to save the connection string provided once the creation process is complete.
As a best practice, create a .env
file to store both the OpenAI API key and the database connection string in your Python environment.
Now, let’s install the necessary Python libraries: the PostgreSQL adapter for Python, psycopg2
, the pandas library for data loading and preparation, and python-dotenv
for reading the value stored in your environment.
Data processing and environment variables loading
We use a product catalog dataset containing 500 entries and split it into two subsets:
subset_1
: inserted before the pgai Vectorizer is created.subset_2
: to demonstrate how the vectorizer updates embeddings after a data change.
Database connection and source table creation
After defining and processing variables in your .env
file, connect to your database service using our Python adapter, psycopg2
. Ensure that pgai (0.4.0) is installed before creating the source table.
Insert subset_1
in the source table
We do multiple inserts with our subsets and define insert_subset_data
, a helper function, for inserting data into the source table.
Then, we insert the data from subset_1
with the following call:
Creating the vectorizer
First, set up your OpenAI API key from the database service. Go to the Project settings and input your key as follows:
Then, navigate to the AI tab of your service overview and create a vectorizer by following the checklist for customizing the SQL command template provided.
Vectorizers are very customizable, and we encourage you to play around with various options. Here is the SQL command we used to create a vectorizer:
Let’s break down this SQL command and understand its parameters:
- Define the table (
products
) whose data you want to create embeddings for. - Then, configure the vectorizer using the following options:
- Embedding:
ai.embedding_openai
. This function accesses the embedding model by providing the model name (text-embedding-3-small
), the vector dimensions (768), and the API key name. - Chunking:
ai.chunking_recursive_character_text_splitter
. This function prepares the data by splitting it into smaller chunks to meet input size limits for the embedding model and improve model processing time. You can fine-tune the parameters for more control over the chunking formation. I use the default values here:
- Embedding:
- Formatting:
ai.formatting_python_template
. It allows you to define a pattern for your chunks to add context from other columns before sending them to the embedding model. For instance, in this case, we prepend the product name to each chunk of the description.
There are more advanced options for configuring indexing and other parameters; we use the defaults in this example. Check out the pgai Vectorizer API reference for more information.
View of source data and embeddings
Pgai Vectorizer also creates a view incorporating source data and the corresponding embedding information. The default naming for this view is <source_table>_embedding
. This view contains all the columns from the source table (in this case, products
) as well as the following embedding-related columns:
embedding_uuid
: a unique identifier for the embeddingchunk_seq
: the sequence number of the chunk within the source text (e.g., 1 indicates the first chunk)chunk
: the actual text chunk sent to the embedding modelembedding
: the vector representation generated from the chunk
Retrieving context: Querying embeddings
A RAG application relies on vector embeddings to find information relevant to the generative model query from a knowledge base. This supplemental information is used as context and appended to the query to prevent hallucination.
Using the embeddings generated by pgai Vectorizer, we retrieve the top three chunks relevant to the query provided. Since we named all chunks with the product name, we decided to query the embeddings table (instead of the view) and only extract the chunk from the table.
Here are the chunks retrieved:
Generating the model response
Let’s generate a response using the retrieved context alongside the model query. The retrieval and generation workflow is straightforward thanks to pgai
, which integrates seamlessly with AI model providers such as OpenAI and Anthropic. pgai
provides embedding functions (used in the previous sections of this article) and chat generation functions, which also simplify the development of AI applications within PostgreSQL.
We use one of the latest generative models, gpt-4o mini
, to generate the response.
Updating embeddings
Besides creating embeddings, pgai Vectorizer also manages embeddings whenever the source data changes. In this section, we’ll walk you through an embedding management use case by inserting new data in the source table.
We only use half of the dataset to create the vectorizer. Let’s use the other half, subset_2
, to showcase how pgai Vectorizer handles new data.
After inserting the second half of the dataset into the source table, products
, using the helper method, we can go to the AI
tab in the service console and observe that the vectorizer has already scheduled the processing of 250 items (the new entries).
Since the Timescale job scheduler runs every five minutes, we can expect the vectorizer to start processing the new items in the next five minutes or less. Less than a minute after inserting the new items into the source table, the vectorizer runs and generates 604 embeddings from the new data! This is achieved without any other code! Just insert new data, and the vectorizer handles the rest.
This example illustrates not only how the embedding management workflow is simplified by pgai Vectorizer but also how quickly the extension processes data changes. We can observe the newly generated embeddings in the following query and its results below:
Conclusion
In this blog post, we walked you through using pgai Vectorizer. This open-source tool streamlines the process of managing embeddings in PostgreSQL by automating embedding creation and updates directly within PostgreSQL—with a single SQL command.
Forget external pipelines, complex workflows, and custom infrastructure. With this tool and your regular SQL, you can significantly reduce operational overhead while ensuring data and embeddings stay aligned as your dataset evolves.
Features like flexible chunking, data synchronization, and easy model experimentation allow pgai Vectorizer to replace custom solutions and infrastructure without losing the ability to customize your embeddings the way you want. This empowers developers to focus on building AI solutions rather than maintaining infrastructure.
Its seamless integration with the PostgreSQL ecosystem, especially with extensions like pgvector
and pgvectorscale
, makes AI application development more efficient—you don’t have to leave your PostgreSQL database. Better yet, you don’t have to be an AI expert. Pgai Vectorizer ensures scalable and reliable embedding management, making PostgreSQL an ideal platform for AI innovation.
Pgai Vectorizer is available for you to get started with today in Early Access. Pick your favorite deployment option: fully managed on Timescale Cloud or self-hosted in the PostgreSQL environment of your choice (and leave us a GitHub ⭐ along the way.)
Questions or feedback? Join our Discord community, where our team is ready to help you make the most of your embedding models.
Learn more
Learn more about pgai Vectorizer and how it can help you build better AI systems with PostgreSQL in these companion blogs and videos:
- Vector Databases Are the Wrong Abstraction. Here’s a Better Way.
- Which OpenAI Embedding Model Is Best for Your RAG App With Pgvector?
- Which RAG Chunking and Formatting Strategy Is Best for Your AI App With Pgvector?
- Watch 🎥: Auto Create and Sync Vector Embeddings in 1 Line of SQL
- Watch 🎥: What If Vector Embeddings Were Database Indexes?