Using AI directly from your database - with PostgreSQL and pgai

Andreas Nigg
Andreas Nigg · Co-Founder, Pondhouse Data
Published Updated 10 min read
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:

  1. Continuously fetches data from your database
  2. Sends these data to an LLM
  3. Receives the labels from the AI model
  4. 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:

  1. Using the pre-built TimescaleDB Docker image.

  2. 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:

  1. Clone the pgai github repository
  2. Run make install

Using the pre-built TimescaleDB Docker image

Run:

1docker run -d --name timescaledb -p 5432:5432 -e POSTGRES_PASSWORD=password timescale/timescaledb-ha:pg16

Once pgai is installed with either option, create the extension on your server:

  1. Connect to your server

    1psql -d "postgres://<username>:<password>@<host>:<port>/<database-name>"
  2. Create the extension:

    1CREATE EXTENSION IF NOT EXISTS ai CASCADE;

    Note: The CASCADE keyword also installs the required dependencies plpython3u and pgvector

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.

  1. Export your API key to your environment:

    1export OPENAI_API_KEY="this-is-my-super-secret-api-key-dont-tell"
  2. When connecting to your database, use a session parameter as follows:

    1PGOPTIONS="-c ai.openai_api_key=$OPENAI_API_KEY" psql -d "postgres://<username>:<password>@<host>:<port>/<database-name>"

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:

1CREATE TABLE user_content (
2 updated_at TIMESTAMP NOT NULL,
3 user_id INT NOT NULL,
4 content TEXT NOT NULL,
5 embedding VECTOR(1536),
6 summary TEXT,
7 blocked BOOLEAN DEFAULT FALSE
8);
1INSERT INTO user_content (updated_at, user_id, content)
2VALUES
3 ('2024-06-14 10:00:00', 1, 'The French Revolution, which began in 1789 and lasted until 1799, was a period of profound political and social upheaval in France that fundamentally transformed the nation and had far-reaching impacts across the globe'),
4 ('2024-06-14 11:00:00', 2, 'Triggered by widespread discontent with the monarchy of Louis XVI, economic hardship, and the influence of Enlightenment ideas, the revolution sought to dismantle the old feudal structures and create a society based on principles of liberty, equality, and fraternity'),
5 ('2024-06-14 12:00:00', 3, 'Key events included the storming of the Bastille, the Reign of Terror led by figures such as Robespierre, and the eventual rise of Napoleon Bonaparte');

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.

1UPDATE user_content
2SET embedding = openai_embed('text-embedding-ada-002', content);

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:

1SELECT * FROM user_content;

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:

1response = client.chat.completions.create(
2 model="gpt-3.5-turbo",
3 messages=[
4 {"role": "system", "content": "You are a helpful assistant."},
5 {"role": "user", "content": "Who won the world series in 2020?"},
6 {"role": "assistant", "content": "The Los Angeles Dodgers won the World Series in 2020."},
7 {"role": "user", "content": "Where was it played?"}
8 ]
9)

We provide a list of messages, each with a role and the message content.

The result object looks like this:

1{
2 "choices": [
3 {
4 "finish_reason": "stop",
5 "index": 0,
6 "message": {
7 "content": "The 2020 World Series was played in Texas at Globe Life Field in Arlington.",
8 "role": "assistant"
9 },
10 "logprobs": null
11 }
12 ],
13 "created": 1677664795,
14 "id": "chatcmpl-7QyqpwdfhqwajicIEznoc6Q47XAyW",
15 "model": "gpt-3.5-turbo-0613",
16 "object": "chat.completion",
17 "usage": {
18 "completion_tokens": 17,
19 "prompt_tokens": 57,
20 "total_tokens": 74
21 }
22}

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:

1select openai_chat_complete
2( 'gpt-4o'
3, jsonb_build_array
4 ( jsonb_build_object('role', 'system', 'content', 'You are tasked to summarize texts. Tone should be educational and neutral.')
5 , jsonb_build_object('role', 'user', 'content', 'Text to summarize: ')
6 )
7)->'choices'->0->'message'->>'content'
8;
  1. We call the openai_chat_complete function to get chat completion results
  2. The first parameter is the OpenAI model to use
  3. The second parameter is a JSON array (jsonb_build_array) containing the messages we send to the LLM. Typically you want a system message that sets the main context for the model and a user message with the actual question or text.
  4. The OpenAI API returns JSON, but we only need the content field, 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:

1UPDATE user_content
2SET summary = openai_chat_complete
3( 'gpt-4o'
4, jsonb_build_array
5 ( jsonb_build_object('role', 'system', 'content', 'You are tasked to summarize texts. Tone should be educational and neutral.')
6 , jsonb_build_object('role', 'user', 'content', 'Text to summarize: ' || content)
7 )
8)->'choices'->0->'message'->>'content';

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:

1{
2 "id": "modr-9RsN6qZWoZYm1AK4mtrKuEjfOcMWp",
3 "model": "text-moderation-007",
4 "results": [
5 {
6 "flagged": true,
7 "categories": {
8 "hate": false,
9 "sexual": false,
10 "violence": true,
11 "self-harm": false,
12 "self_harm": false,
13 "harassment": true,
14 "sexual/minors": false,
15 "sexual_minors": false,
16 "hate/threatening": false,
17 "hate_threatening": false,
18 "self-harm/intent": false,
19 "self_harm_intent": false,
20 "violence/graphic": false,
21 "violence_graphic": false,
22 "harassment/threatening": true,
23 "harassment_threatening": true,
24 "self-harm/instructions": false,
25 "self_harm_instructions": false
26 },
27 "category_scores": {
28 "hate": 0.2324090600013733,
29 "sexual": 0.00001205232911161147,
30 "violence": 0.997192919254303,
31 "self-harm": 0.0000023696395601291442,
32 "self_harm": 0.0000023696395601291442,
33 "harassment": 0.5278584957122803,
34 "sexual/minors": 0.00000007506431387582779,
35 "sexual_minors": 0.00000007506431387582779,
36 "hate/threatening": 0.024183575063943863,
37 "hate_threatening": 0.024183575063943863,
38 "self-harm/intent": 0.0000017161115692942985,
39 "self_harm_intent": 0.0000017161115692942985,
40 "violence/graphic": 0.00003399916022317484,
41 "violence_graphic": 0.00003399916022317484,
42 "harassment/threatening": 0.5712487697601318,
43 "harassment_threatening": 0.5712487697601318,
44 "self-harm/instructions": 0.000000001132860139030356,
45 "self_harm_instructions": 0.000000001132860139030356
46 }
47 }
48 ]
49}

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:

1UPDATE user_content
2SET blocked = (openai_moderate
3 ( 'text-moderation-stable'
4 , content
5 ) ->'results'->0->>'flagged')::BOOLEAN;

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).

  1. Create the function which is called by the trigger:

    1CREATE OR REPLACE FUNCTION create_embedding()
    2RETURNS TRIGGER AS $$
    3BEGIN
    4 NEW.embedding = openai_embed('text-embedding-ada-002', NEW.content);
    5 RETURN NEW;
    6END;
    7$$ LANGUAGE plpgsql;
  2. Create the trigger:

    1CREATE TRIGGER create_embedding_trigger
    2BEFORE INSERT OR UPDATE ON user_content
    3FOR EACH ROW
    4EXECUTE FUNCTION create_embedding();

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:

1INSERT INTO user_content (updated_at, user_id, content)
2VALUES
3 ('2024-06-14 13:00:00', 4, 'The quick brown fox jumps over the lazy dog'),
4 ('2024-06-14 14:00:00', 5, 'The five boxing wizards jump quickly');

And check the result:

1select content, embedding from user_content where user_id in (4, 5);

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.

Further reading

Andreas Nigg

About the author

Andreas Nigg

Co-Founder, Pondhouse Data

Andreas has spent more than eight years building data applications. At Pondhouse Data he designs and ships AI systems and integrates them into complex, real-world environments.