Developers need flexible, efficient item searches by description. Using a Wikipedia movie dataset, I started with full-text search, then created embeddings for descriptions, and performed semantic searches. Queries combined text or similarity searches with filters. Common questions arose: How to update embeddings as data changes? How to avoid reprocessing all data? How to make AI calls more efficient? I prefer to keep business logic in the app and data manipulation in SQL, but triggers and stored procedures complicate this.
This post explores keeping retrieval workflows inside the database, using data, indexes, filters, and rules already in place. Instead of external workers, I want SQL abstractions to handle operations like generating and maintaining embeddings as part of the data model. I begin with relational data, using BM25 text search, generating embeddings with AI Model Management (AIMM) and AI Functions, storing them with pgvector, indexing them with DiskANN, and integrating AI into PostgreSQL. Then I define an AI pipeline to maintain consistency without extra procedural code.
The goal is to keep AI processing close to data with declarative SQL. I used a Kaggle movies dataset with 30,000 entries over 11 years, importing a subset for simplicity. Each file has title, description, director, country, year, and ID.
HorizonDB and AI extensions
I've run this on HorizonDB preview, where I enabled pg_textsearch, pg_vector, DiskANN, and azure_ai extensions:
postgres=> select name, current_setting(name)
from pg_settings
where name in ('server_version', 'azure.extensions')
;
name | current_setting
------------------+-----------------------------------------------
azure.extensions | pg_diskann,vector,pg_textsearch,azure_ai
server_version | 17.9 (Azure HorizonDB (81895d42565)(release))
(2 rows)
I downloaded and unzipped the dataset files in the current directory:
\! curl -L -o movies-dataset-2016-2026.zip https://www.kaggle.com/api/v1/datasets/download/lakshyaupadhyaya/wikipedia-movies-dataset-2016-2026
\! unzip -o movies-dataset-2016-2026.zip
\! rm movies-dataset-2016-2026.zip
I created a table where I can load this data and add embeddings later:
postgres=> drop table if exists wikipedia_movies
;
DROP TABLE
postgres=> create table wikipedia_movies (
year int,
id bigint,
title text,
description text,
directed_by text,
written_by text,
produced_by text,
starring text,
cinematography text,
edited_by text,
release_date text,
country text,
language text,
primary key (year, id)
);
CREATE TABLE
postgres=> create index on wikipedia_movies (country)
;
CREATE INDEX
I loaded data from 2026 using a one-liner that prepends the year from the filename.
postgres=> \copy wikipedia_movies from program 'awk ''FNR>1{print substr(FILENAME,1,4)","$0}'' {2026..2026}.csv' with (format csv)
COPY 852
Some rows have a NULL description. I updated them with the title so every row has something meaningful to embed or search, and make sure the description cannot be null:
postgres=> update wikipedia_movies
set description = format('Title: %s', title)
where description is null
;
UPDATE 22
postgres=> alter table wikipedia_movies
alter column description set not null
;
ALTER TABLE
So far, there's nothing AI-specific here. It's simply PostgreSQL functioning as it always does: storing structured data, enforcing keys, and allowing me to load, clean, and query using SQL.
Try full-text search first
Before generating embeddings for similarity search, I started with lexical search. Azure Database for PostgreSQL supports pg_textsearch, which provides BM25-based full-text search and scoring.
postgres=> create extension if not exists pg_textsearch;
CREATE EXTENSION
postgres=> create index wikipedia_movies_description_idx
on wikipedia_movies
using bm25 (description)
with (text_config = 'english')
;
NOTICE: BM25 index build started for relation wikipedia_movies_description_idx
NOTICE: Using text search configuration: english
NOTICE: Using index options: k1=1.20, b=0.75
NOTICE: BM25 index build completed: 852 documents, avg_length=35.95
CREATE INDEX
Then I can search the descriptions:
postgres=> select
description <@> 'Spanish tragicomedy autofiction from 2026' as score,
title,
description
from wikipedia_movies
where country = 'Spain' and year = '2026'
order by score limit 2
;
score | title | description
--------------------+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-17.06686845421791 | Bitter Christmas | Bitter Christmas (Spanish: Amarga Navidad) is a 2026 Spanish tragicomedy film written and directed by Pedro Almodóvar. It stars Bárbara Lennie and Leonardo Sbaraglia alongside Aitana Sánchez-Gijón, Victoria Luengo, Patrick Criado, Milena Smit, and Quim Gutiérrez. It incorporates elements of autofiction.
-5.273793399333954 | Aida, the Movie | Aida, the Movie (Spanish: Aída y vuelta) is a 2026 Spanish comedy film directed by Paco León serving as a meta-sequel to the sitcom Aída.
(2 rows)
Time: 31.024 ms
This is the appropriate initial step. When the query includes terms found in the text, BM25 provides search and scoring capabilities. It is transparent, efficient, and easy to combine with relational filters. Nevertheless, lexical search has its limitations. It works best when the query vocabulary matches the data. But what if the user searches by concept rather than exact words? Or if the query is in French while the descriptions are in English? In such situations, embeddings help capture the semantic meaning of the description rather than its words. They do not replace BM25 but serve as an additional signal for retrieval.
AI Model Management
AI Model Management (AIMM) includes pre-provisioned models, so manual registration is not required, and I've just enabled the managed model:
Once successfully enabled, I can see three registered models, which are used by default by the AI functions:
postgres=> select alias, model_name, status from model_registry.model_list_all();
alias | model_name | status
-------------------+-------------------------+------------
default-chat | gpt-5.4 | registered
default-embedding | text-embedding-3-small | registered
default-reranker | Cohere-rerank-v4.0-fast | registered
(3 rows)
postgres=>
Without AIMM, you would need to bring your own model, or deploy an OpenAI model in Azure AI Foundry, and register it:
postgres=> create extension if not exists azure_ai
;
CREATE EXTENSION
postgres=> select model_registry.model_add('default-embedding',...)
;
model_add
------------------------------------------------------------------------
Model 'default-embedding' (text-embedding-3-small) added successfully.
(1 row)
Time: 40.762 ms
Generate embeddings manually
I created a vector column and generated embeddings directly from the movie descriptions.
postgres=> create extension if not exists azure_ai
;
CREATE EXTENSION
postgres=> create extension if not exists vector;
CREATE EXTENSION
postgres=> alter table wikipedia_movies add column embedding vector(1536),
alter column embedding set storage external -- (it's the default)
;
ALTER TABLE
postgres=> update wikipedia_movies
set embedding = azure_openai.create_embeddings(
input => description,
dimensions => 1536
)::vector(1536)
;
INFO: Using user-assigned managed identity authentication method.
UPDATE 852
Time: 200905.958 ms (03:20.906)
This approach invokes the embedding service separately for each row. These calls are visible if you set client_min_messages to 'log'. Making external service calls asynchronously for each row is not optimal. Performance could be improved by batching the requests into a more complex query:
postgres=> with numbered as (
select year, id, description,
(row_number() over() - 1) / 100 as batch_num
from wikipedia_movies
),
batched AS (
select array_agg(year) as years,
array_agg(id) as ids,
array_agg(description) as texts,
batch_num
from numbered
group by batch_num
),
embedded AS (
select unnest(years) as year,
unnest(ids) as id,
azure_openai.create_embeddings(
input => texts,
dimensions => 1536
) as emb
from batched
)
update wikipedia_movies t
set embedding = e.emb::vector(1536) from embedded e
where t.year = e.year and t.id = e.id
;
UPDATE 852
Time: 10892.871 ms (00:10.893)
This is precisely where an abstraction is needed to simplify and optimize embedding generation. I will add that later.
After setting the vectors in the table, I can proceed to add a DiskANN index.
postgres=> CREATE EXTENSION IF NOT EXISTS pg_diskann
;
CREATE EXTENSION
postgres=> CREATE INDEX wikipedia_movies_embedding_idx
ON wikipedia_movies
USING diskann (embedding vector_cosine_ops)
;
CREATE INDEX
Time: 5369.375 ms (00:05.369)
This allows me to perform a semantic query in French on English descriptions while continuing to filter using standard SQL:
postgres=> select azure_openai.create_embeddings(
input => 'une comédie amère sur le thème de l''auto-fiction'
)::vector(1536) <=> embedding as score,
title, description
from wikipedia_movies
where country = 'Spain' and year = '2026'
order by score limit 2
;
score | title | description
-------------------+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0.692949073279717 | Bitter Christmas | Bitter Christmas (Spanish: Amarga Navidad) is a 2026 Spanish tragicomedy film written and directed by Pedro Almodóvar. It stars Bárbara Lennie and Leonardo Sbaraglia alongside Aitana Sánchez-Gijón, Victoria Luengo, Patrick Criado, Milena Smit, and Quim Gutiérrez. It incorporates elements of autofiction.
0.757078099513101 | Cool Books | Cool Books (Spanish: Casi todo bien)[1] is a 2026 Spanish comedy-drama film directed by Andrés Salmoyraghi and Rafael López Saubidet and written by López Saubidet and Ricardo Uhagón Vivas. It stars Marcel Borràs and Silma López alongside Lorenzo Ferro, Julián Villagrán, Secun de la Rosa, and Adelfa Calvo.
(2 rows)
Time: 269.598 ms
The query goes beyond simple token matching: it searches based on the description's meaning across different languages, while PostgreSQL continues to enforce relational filters on the year and country.
The execution plan shows a filtered scan through the DiskANN index:
QUERY PLAN
-----------------------------------------------------------------------------------
Limit (actual time=1.622..1.642 rows=2 loops=1)
-> Custom Scan (DiskANNFilteredScan) (actual time=1.620..1.639 rows=2 loops=1)
Strategy: Filter(BitmapHeapScan) -> Vector
Rows Retrieved: 2 count
TIDs Collected: 31 count
Planning Time: 250.003 ms
Execution Time: 1.789 ms
(7 rows)
Time: 283.231 ms
This approach is efficient: the "country" predicate leverages the b-tree index for pre-filtering, narrowing the search to 31 rows identified by their TID—tuple identifiers stored in the b-tree leaf nodes. The Approximate Nearest Neighbors search then considers only this pre-filtered list when navigating the vector index, finding the best candidates, and subsequently retrieving the top two. Although I could have used pgvector's HNSW index instead of DiskANN, it lacks pre-filtering capabilities.
I now face a maintenance issue: the embeddings remain correct only until the next insert or update of the description. In SQL, I expect indexes to be maintained synchronously by the database rather than by the application, but embedding generation is a step in between.
Embedding becomes a pipeline
For a one-time load, manually running the UPDATE is sufficient. However, for regular applications, this approach isn't practical. I don't want to remember to run an embedding update whenever new movies arrive, nor do I want an extra worker polling the table, additional queues, retry loops, or extra status tables that can cause data to get out of sync. Instead, I prefer the embedding workflow to be directly linked to the data, with AI pipelines in the AI Functions. A pipeline consists of a source, steps, a trigger, and a sink. In this case, the source is the movie table, the step is embedding generation, the trigger is an on_change event, and the sink is the same table with the embeddings stored as a column within each row’s description.
postgres=> SELECT ai.create_pipeline(
name => 'movie_embeddings',
source => ai.table_source('wikipedia_movies'),
steps => ARRAY[
ai.embed(
model => 'default-embedding',
input => 'description',
dimensions => 1536
)
],
trigger => 'on_change',
sink => ai.table_sink(
'wikipedia_movies',
on_conflict => ARRAY['year', 'id'],
on_conflict_action => 'DO UPDATE SET embedding = EXCLUDED.embedding'
)
);
NOTICE: trigger "_ai_pipeline_movie_embeddings_trigger" for relation "public.wikipedia_movies" does not exist, skipping
create_pipeline
--------------------------------------------------
Pipeline 'movie_embeddings' created successfully
(1 row)
The AI pipeline is configured to insert embeddings into a sink table, with an on-conflict clause that updates the existing row rather than inserts a new one when the key already exists. When the sink table is the source table, this always leads to an insert conflict because the row already exists when the pipeline runs, making it an update. The pipeline creates an internal batch from the source, generates embeddings, and writes them back with ON CONFLICT DO UPDATE. The trigger verifies whether the pipeline is already running in the same transaction to prevent an infinite recursion when it writes back to the same table.
I can validate my pipeline by updating all embeddings for each description using the AI model and requesting a backfill:
postgres=> select ai.backfill('movie_embeddings')
;
NOTICE: table "_ai_batch_d03946bdb634_chunks" does not exist, skipping
backfill
-----------------------------------------------------------
Pipeline 'movie_embeddings' completed: 852 rows processed
(1 row)
Time: 10653.393 ms (00:10.65)
This was as quick as my batching query because the default batch size is 100.
Under the hood: triggers and batches
I like to understand what is executed behind the abstraction. The call to ai.create_pipeline() has created an internal trigger on my table:
postgres=> \d wikipedia_movies
Table "public.wikipedia_movies"
Column | Type | Collation | Nullable | Default
------------------+--------------+-----------+----------+---------
year | integer | | not null |
id | bigint | | not null |
title | text | | |
description | text | | |
directed_by | text | | |
written_by | text | | |
produced_by | text | | |
starring | text | | |
cinematography | text | | |
edited_by | text | | |
release_date | text | | |
country | text | | |
language | text | | |
embedding | vector(1536) | | |
Indexes:
"wikipedia_movies_pkey" PRIMARY KEY, btree (year, id)
"wikipedia_movies_country_idx" btree (country)
"wikipedia_movies_description_idx" bm25 (description) WITH (text_config=english)
"wikipedia_movies_embedding_idx" diskann (embedding vector_cosine_ops)
Triggers:
_ai_pipeline_movie_embeddings_trigger AFTER INSERT OR UPDATE ON wikipedia_movies FOR EACH STATEMENT EXECUTE FUNCTION ai._pipeline_trigger_movie_embeddings()
postgres=> \sf ai._pipeline_trigger_movie_embeddings
CREATE OR REPLACE FUNCTION ai._pipeline_trigger_movie_embeddings()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
DECLARE running BOOLEAN;
BEGIN
SELECT EXISTS(SELECT 1 FROM ai.pipeline_runs WHERE pipeline_name = 'movie_embeddings' AND status = 'running') INTO running;
IF NOT running THEN PERFORM ai.run('movie_embeddings'); END IF;
RETURN NULL;
END;
$function$
This trigger function is called after each INSERT or UPDATE statement, not after individual rows, and runs the pipeline if it's not already active. The FOR EACH STATEMENT option ensures it triggers once per DML statement rather than per row, enabling batching. By default, it behaves like my previous ai.backfill(), updating all embeddings regardless of description changes. Interestingly, it calls ai.run() instead of ai.backfill(), which can operate incrementally if a tracking column exists in the source table.
Only process what changed: incremental pipelines
Without an incremental_column specified in the pipeline, it replicates the source table as its batch table and re-embeds all data each time. Generating embeddings again for the unchanged description is inefficient. To prevent this, I should add a timestamp column to indicate each row's last modification time and configure the pipeline to use it as a watermark:
postgres=> -- add the tracking column
alter table wikipedia_movies
add column updated_at timestamptz not null default clock_timestamp()
;
postgres=> -- function to set the update_at to now()
create or replace function update_timestamp()
returns trigger as $$
begin new.updated_at = clock_timestamp(); return new; end;
$$ language plpgsql
;
postgres=> -- trigger to call the function on insert or update
create trigger wikipedia_movies_updated
before insert or update on wikipedia_movies
for each row execute function update_timestamp()
;
Lots of PostgreSQL examples use now() to set the update time, but now() returns the time when the transaction began, not the time when the update occurred, so using it for an incremental approach could miss the changes if a concurrent session ran the pipeline between those two in Read Committed isolation. I use clock_timestamp() which gets the time after the verification that no concurrent pipeline is running.
Now I can re-create the pipeline with the incremental column defined:
postgres=> select ai.drop_pipeline( 'movie_embeddings' );
drop_pipeline
-------------------------------------
Pipeline 'movie_embeddings' dropped
(1 row)
postgres=> select ai.create_pipeline(
name => 'movie_embeddings',
source => ai.table_source(
'wikipedia_movies',
incremental_column => 'updated_at'
),
steps => ARRAY[
ai.embed(
model => 'default-embedding',
input => 'description',
dimensions => 1536
)
],
trigger => 'on_change',
sink => ai.table_sink(
'wikipedia_movies',
on_conflict => ARRAY['year', 'id'],
on_conflict_action => 'DO UPDATE SET embedding = EXCLUDED.embedding'
)
);
NOTICE: trigger "_ai_pipeline_movie_embeddings_trigger" for relation "public.wikipedia_movies" does not exist, skipping
create_pipeline
--------------------------------------------------
Pipeline 'movie_embeddings' created successfully
(1 row)
When using incremental_column => 'updated_at', the batch records the last execution time as a checkpoint, so the next run only processes rows with an updated_at value higher than that checkpoint.
The next run will then process all rows again:
postgres=> select ai.run('movie_embeddings');
NOTICE: table "_ai_batch_ef762cbb918f_chunks" does not exist, skipping
run
-----------------------------------------------------------
Pipeline 'movie_embeddings' completed: 852 rows processed
(1 row)
Time: 12231.656 ms (00:12.232)
However, a new execution will process only incrementally:
postgres=> select ai.run('movie_embeddings');
run
-----------------------------------------------------
Pipeline 'movie_embeddings': no new rows to process
(1 row)
postgres=> update wikipedia_movies
set description = description || ' (film Français)'
where country = 'France'
;
NOTICE: table "_ai_batch_9769bddf6224_chunks" does not exist, skipping
UPDATE 7
Time: 477.686 ms
postgres=> select ai.run('movie_embeddings');
run
-----------------------------------------------------
Pipeline 'movie_embeddings': no new rows to process
(1 row)
Time: 33.692 ms
The 7 updated rows have been handled by the trigger, which set the checkpoint timestamp, ensuring that the next run has no further processing to perform.
I loaded only 2026 movies to save AI model tokens. I insert more movies from previous years and specific countries:
\copy wikipedia_movies from program 'awk ''FNR>1{print substr(FILENAME,1,4)","$0",,"}'' {2016..2025}.csv' with (format csv) where description is not null and country like '%France%'
COPY 1730
Time: 24935.910 ms (00:24.936)
The insert has automatically generated 1730 embeddings from the model. The new rows are immediately accessible and can be retrieved using a new query.
postgres=> select azure_openai.create_embeddings(
input => 'a movie based on a novel from A. Camus'
)::vector(1536) <=> embedding as score,
year, title, country
from wikipedia_movies
order by score limit 2
;
score | year | title | country
--------------------+------+------------------+-----------------
0.4765376989279472 | 2025 | The Stranger | France, Belgium
0.5485678715584099 | 2024 | An Ordinary Case | France
(2 rows)
Time: 448.648 ms
The result includes the newly inserted movies from previous years. The index remains strongly consistent when the “embedding” column is modified, and the AI pipeline ensures this consistency also applies to changes in the “description” column. It behaves exactly like indexes in SQL: transparent to the application, consistent in the database.
More AI functions
Generating the embeddings is not the only feature of AI functions. Previously I've updated the description for the movies that didn't have one, simply putting the title in it. However, I can use generate() to get a real description:
postgres=> \x
Expanded display is on.
postgres=> select azure_ai.generate(format(
'Write a short description for the movie %s, directed by %s',
title, directed_by
)), title
from wikipedia_movies
where description like 'Title: %'
limit 3
;
-[ RECORD 1 ]---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
generate | Billie Eilish – Hit Me Hard and Soft: The Tour (Live in 3D) is a concert film directed by James Cameron and Billie Eilish, capturing the energy and emotion of Eilish’s live tour in immersive 3D. Blending striking visuals with powerful performances, the film brings audiences into the heart of the show and offers a vivid celebration of her music and stage presence.
title | Billie Eilish – Hit Me Hard and Soft: The Tour (Live in 3D)
-[ RECORD 2 ]---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
generate | Border 2 is an upcoming Indian war drama directed by Anurag Singh. Serving as a sequel to the iconic film Border, it is expected to bring a powerful story of patriotism, courage, and sacrifice, set against the backdrop of military conflict.
title | Border 2
-[ RECORD 3 ]---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
generate | *February* (2022) is a drama film directed by Kamen Kalev. The story follows a quiet man through different stages of his life, using minimal dialogue and striking imagery to reflect on solitude, routine, and the passage of time.
title | (February 2022)
The generate() function doesn't simply repeat the title. Using the title and director as context, it generates a richer description that can be stored in the table, indexed with BM25, embedded for semantic search.
Instead of generating text from structured column, with the generate() function, I can do the opposite: create new columns from the description with the extract() function:
postgres=> select azure_ai.extract(
description,
array['genre']
), title
from wikipedia_movies limit 3
;
-[ RECORD 1 ]--------------------------------------
extract | {"genre": "coming-of-age romantic drama"}
title | 18th Rose
-[ RECORD 2 ]--------------------------------------
extract | {"genre": "drama"}
title | Animol
-[ RECORD 3 ]--------------------------------------
extract | {"genre": "documentary film"}
title | The Best Summer
Like embeddings, extracted attributes or additional columns could be maintained automatically through an AI Pipeline when descriptions change.
Another function, azure_ai.rank(), can use the re-ranker model on the set of candidates (AIMM installed the Cohere Rerank model).
Hybrid search in one query
Once the table has both a BM25 index and embeddings, there is no need to choose between lexical and semantic retrieval. For many real applications, the best answer is to get candidates from both approaches and combine them to retrieve the best overall score.
A classic way to combine them is Reciprocal Rank Fusion:
postgres=> with semantic as (
select year, id,
rank() over (
order by embedding <=>
azure_openai.create_embeddings(
input => 'space opera produced or directed by luc besson',
dimensions => 1536
)::vector(1536)
) as r
from wikipedia_movies
where year between 2016 and 2026
),
lexical as (
select year, id,
rank() over (
order by description <@>
'space opera produced or directed by luc besson'
) as r
from wikipedia_movies
where year between 2016 and 2026
)
select m.year, m.title
from wikipedia_movies m
join semantic s using (year, id)
-- Join both result sets (full join to keep all)
full join lexical l using (year, id)
-- Reciprocal Rank Fusion (RRF):
order by coalesce(1.0 / (60 + s.r), 0)
+ coalesce(1.0 / (60 + l.r), 0) desc
limit 5
;
NOTICE: pg_diskann: Filter selectivity too high (1.0000), skipping filtered vector scan
year | title
------+---------------------------------------------
2017 | Valerian and the City of a Thousand Planets
2025 | Dracula
2016 | Ballerina
2024 | Meanwhile on Earth
2016 | The Warriors Gate
(5 rows)
Time: 410.727 ms
The data and filters remain relational, with both BM25 and vector search options available. Ranking is handled through SQL, embeddings are synchronized through a pipeline, and reranking can be delegated to an AI model when additional precision is required. The notable aspect isn't just PostgreSQL's ability to invoke AI models, but that the entire retrieval workflow, from indexing and filtering to ranking and reranking, remains close to the data and can be expressed declaratively in SQL.
Conclusion
This example started with a simple retrieval problem: searching movie descriptions, combining lexical and semantic relevance, and keeping embeddings synchronized as data changes. Along the way, it used AI Functions, BM25 full-text search, vector embeddings, DiskANN indexes, and AI Pipelines, all from within PostgreSQL.
Those capabilities are part of a broader set of multi-model features in HorizonDB, including built-in AI models and functions, graph queries with Apache AGE, and durable workflow execution. What interested me here was not any individual feature, but how they fit together around the data. Search, embeddings, indexing, and synchronization can be expressed in SQL and managed alongside the application data they depend on.
AI applications still need models, prompts, and business logic in the application layer. But retrieval remains a critical foundation. By combining relational data, keyword search, vector search, and automated embedding pipelines within a single PostgreSQL database, HorizonDB reduces the infrastructure required to keep that foundation accurate, consistent, and up to date.
If you'd like to try these capabilities yourself, visit the PostgreSQL Hub for sample applications, learning paths, and solution accelerators. The PostgreSQL Developer Forum is also the best place to share feedback, ask questions, and participate in the community.