Using AI directly from your database - with PostgreSQL and pgai

On this page
pgai is a PostgreSQL extension from Timescale
that lets you call OpenAI models directly from SQL. With
pgai you can create embeddings,
summarize text and moderate content with a single SQL query. There is no
separate service that moves data between your database and the AI API, and no
extra AI infrastructure to run.
That matters most for developers who spend their days in the database. Adding AI to an existing (or new) application usually means writing a Python service, finding a place to run it and giving it secure database access. pgai lets you skip all of that.
This guide covers what pgai is, how to install it, how to provide an OpenAI API key and how to use it for embeddings, summaries and content moderation. At the end we build an insert trigger that creates embeddings automatically whenever a row is inserted or updated.
Update (November 2026): The pgai repository
says the project is no longer maintained or supported as of February 2026, and
it was archived in May 2026. Later extension versions moved the functions into
the ai schema (ai.openai_embed, ai.openai_chat_complete,
ai.openai_moderate, see the OpenAI function docs),
and Timescale now operates as Tiger Data.
OpenAI shut down text-moderation-stable in October 2025 (replacement:
omni-moderation) and gpt-3.5-turbo in October 2026, and the GPT-4o
snapshots are scheduled to shut down on January 14, 2027 (see the
deprecations page).
What is pgai?
pgai is an extension for PostgreSQL, developed by the folks at Timescale, that brings AI capabilities into the database. Its goal is to simplify AI workflows and make PostgreSQL a better fit for AI-native applications.
pgai ships helper functions that run and manage AI workflows inside PostgreSQL, so you don't have to switch between tools or platforms. Because the AI calls live in the database, analysis and processing tasks (anything from predictive analytics to complex data transformations) run within the PostgreSQL setup you already know.
The extension needs two other components: vector data types and the PL/Python
procedural language (plpython3u), which lets PostgreSQL execute Python
functions.
Use cases for AI in PostgreSQL with pgai
Why integrate AI capabilities directly into your PostgreSQL database? Because many everyday AI tasks are operations on rows that already live there. Here are five of them.
Automatically tagging and categorizing data
Say you have a dataset of user-generated content and want to tag and categorize each entry based on what it says. LLMs like GPT-4 are very good at exactly that: labelling data based on its content.
Traditionally, you would build a small, separate service which:
- Continuously fetches data from your database
- Sends these data to an LLM
- Receives the labels from the AI model
- Updates the corresponding rows in the database
On top of writing that service, you have to provide a runtime environment, create secure access to the database, and set up monitoring and logging.
With pgai, the same job is a single SQL query. Even better, you can create
an insert trigger that tags and categorizes new data as it is inserted. No
batch processing, no job scheduling, no separate runtime environment.
Moderating user content
Content moderation works the same way. Picture an application with a comment
section: reviewing every comment by hand doesn't scale, and a dedicated
moderation service is one more thing to maintain. With pgai it is again a
single SQL query. pgai implements the openai_moderate function for this,
which calls the OpenAI content moderation
API.
Automatically summarizing text
Summarizing text is another common task that suits LLMs well. pgai's
openai_chat_complete function exposes the OpenAI chat completion API, so a
summary is one SQL query away.
Creating embeddings for text
Besides chat completion and moderation, pgai (at the time of writing) also
wraps the OpenAI embeddings API. The function creates vector embeddings for
any text, whether you pass it in directly or read it from a table column.
Stored in a pgvector column, they are ready for semantic search and RAG.
We used the same setup to chat with Confluence pages stored in
PGVector.
Bulk-updating embeddings, tags or categories
In my view, the best part of pgai is that its AI function calls fit the set-based nature of relational SQL databases.
What do I mean by that? Assume you have thousands of texts and need a summary for each of them, because you didn't create summaries when the data was inserted. Normally that is an annoying job: write a Python script, load the data, batch it, write the results back.
With pgai, a single statement can update every row of a table. Summaries for thousands of texts, from one SQL query.
Note: These operations take time, so make sure your statement_timeout
is set accordingly.
Bulk-creating embeddings is another good example. For our product Pondhouse
AI, we recently switched to a more capable
embedding model and had to re-index millions of rows of text by hand. With
pgai this would have been a single UPDATE statement.
(By the way, read more on how we use Matryoshka Embeddings and Adaptive Retrieval to get both high retrieval accuracy and low query latency. If your data sits in a data warehouse instead, we cover the same idea in RAG, embeddings and vector search with Google BigQuery.)
How to install pgai?
There are currently two supported ways of installing pgai:
-
Using the pre-built TimescaleDB Docker image.
-
Installing the extension from source.
Do you need to run TimescaleDB to use pgai? No. The pre-built image is the easier route, but you can install the extension on any vanilla Postgres server.
Installing from source
To install pgai from source:
- Clone the pgai github repository
- Run
make install
Using the pre-built TimescaleDB Docker image
Run:
Once pgai is installed with either option, create the extension on your server:
-
Connect to your server
-
Create the extension:
Note: The
CASCADEkeyword also installs the required dependenciesplpython3uandpgvector
Providing an OpenAI API key to pgai
Most pgai functions need an OpenAI API key to call any of the OpenAI models.
There are several options for handling API keys with pgai. We found a session-level parameter the most straightforward: you set the API key once when the session is created and never think about it again.
-
Export your API key to your environment:
-
When connecting to your database, use a session parameter as follows:
From now on, all pgai functions use this API key to authenticate against the OpenAI API.
One word of caution: Bulk updates are powerful, but each row is potentially one OpenAI API call. Updating a large table can create a significant bill, so be careful.
Hands-on: example queries with pgai
Enough talking, let's look at some examples.
One remark before we start: every use case below takes a single SQL query. Some of those queries look a little complex, though, because our AI requests have to be mapped to a JSON API, which takes some data wrangling. We'll explain each step.
First, let's create a demo table with some dummy data:
Admittedly, this is lazy demo data, but it does the trick. The queries create
a table user_content for user-generated entries. It has columns for the
updated_at timestamp, the user_id of the author, the content of the
entry, an embedding column for the vector embeddings of the text, a
summary column and a blocked column that marks whether content
moderation blocked the entry.
Creating embeddings with pgai's openai_embed function
With the demo table in place, let's create embeddings for each of the texts.
You might think we forgot something, but we didn't. This is all it takes to create embeddings for every row in the table.
Let's check the results:
All the rows now have embeddings.
It's plain old SQL, so you can add WHERE clauses, ON CONFLICT statements and everything else PostgreSQL offers.
Creating summaries with pgai's openai_chat_complete function
Next, let's call the chat completion API to generate summaries for our texts.
The process is similar to creating embeddings, but we need some JSON manipulation to get the data into the right format.
The OpenAI Chat Completions API expects a JSON object containing the messages to send to the model, and it returns its results as a JSON object too.
To see what we need to pass to the function, let's first look at the same call in Python, which most of us know well:
We provide a list of messages, each with a role and the message content.
The result object looks like this:
The part we care about is the content field of the message object.
Knowing how to call the API and what comes back, we can write the SQL equivalent:
- We call the
openai_chat_completefunction to get chat completion results - The first parameter is the OpenAI model to use
- The second parameter is a JSON array (
jsonb_build_array) containing the messages we send to the LLM. Typically you want asystemmessage that sets the main context for the model and ausermessage with the actual question or text. - The OpenAI API returns
JSON, but we only need thecontentfield, so we use Postgres' JSON field select operator (->) to pick that property.
In short, the query calls the API and returns the message content of the LLM answer as text.
Creating summaries for all our texts is now straightforward:
Note the content column in the second message object. We send the same
system prompt as before, but append the content of each row of our
user_content table to the user message.
Once you're used to the JSON syntax, it doesn't get much simpler. We just created a text summary for every row in the table with one SQL query.
Moderating user-generated content with pgai's openai_moderate function
Finally, the moderation API. The function is called openai_moderate, and it
returns the following JSON object:
It contains the following information:
flagged: A summary flag that is true if the text violates any of the moderation categories.categories: Detailed flags for each individual moderation category.category_scores: A numeric score for each category. This is handy if you don't want to rely on the automatic flags and want more control over how seriously each category is treated.
Again, the Postgres JSON operators get us the field we need. If we simply use
the flagged field to decide whether content should be blocked, the UPDATE
statement that moderates all our texts at once looks like this:
Interested in building high-quality AI agent systems?
We prepared a comprehensive guide based on cutting-edge research for how to build robust, reliable AI agent systems that actually work in production. This guide covers:
- Understanding the 14 systematic failure modes in multi-agent systems
- Evidence-based best practices for agent design
- Structured communication protocols and verification mechanisms
Showcase: creating embeddings automatically with a Postgres insert trigger
With the basics covered, here is a more useful example. Instead of running these statements by hand, we can create insert triggers that create embeddings (or summaries, or moderation results, or all three) as soon as data is inserted.
Postgres triggers come in a few flavors. They can run before or after an INSERT, UPDATE, DELETE or TRUNCATE statement, and they can run once per row or once per statement.
Our use case: create embeddings for every new text inserted into the
user_content table. A BEFORE INSERT trigger does that, and a BEFORE UPDATE trigger can refresh the embeddings when a text changes. Since this is
a row-wise operation, we want a row trigger (FOR EACH ROW).
-
Create the function which is called by the trigger:
-
Create the trigger:
The trigger runs every time a row is inserted or updated and calls a function that creates text embeddings for the new or changed text.
Let's test it by inserting two new rows:
And check the result:
Both new rows have embeddings stored in the embedding column.
Note: We find this a compelling use case, but consider the performance
cost. The trigger calls the embedding API on every insert, so insert latency
goes up.
Evaluate your use case carefully and switch to batch processing if triggers
are not an option.
TimescaleDB provides an automation framework called
jobs
that can schedule pgai calls to generate your AI outputs. If you don't run
TimescaleDB, consider pg_cron.
That said, many use cases don't need sub-second insert latency and benefit
from this simple, maintainable solution.
Conclusion: what pgai is good for
Timescale's pgai extension integrates cleanly with Postgres and makes a
number of AI tasks automatable from SQL. In this post we used it to:
- Moderate user-generated content automatically
- Create vector embeddings for our texts
- Create summaries and labels
The most convincing part of the extension is that it hides the complexity of
the OpenAI API behind simple PostgreSQL functions. It also fits the SQL
syntax of Postgres well: updates, inserts and triggers all work with pgai
the way you'd expect.
That could let database professionals use AI productively for the first time.
Many of them didn't want to fight with Python, separate runtimes and builds
"just" for AI, and rightly so. With pgai, those obstacles are gone. A good
first step is a single UPDATE with openai_embed on a small table, before
you wire up triggers.



