Semantic Search With Ollama and PostgreSQL in 10 Minutes

Posted by

Team TigerData

Ollama is an open-source tool that has quickly become a favorite among AI developers. It allows you to run and manage open-source and open-weight large language models (LLMs) on any server. Once installed, you can easily run popular models or fine-tuned versions using simple commands like llama pull and llama run. This makes Ollama a powerful tool for building sovereign AI applications, enabling you to deploy models locally or on GPU-powered cloud servers.

Semantic search aims to improve search accuracy by grasping the searcher's intent and the contextual meaning of terms. Combining Ollama with tools like PostgreSQL, pgvector, and pgai allows you to create fully private AI solutions that leverage semantic search for applications such as retrieval-augmented generation (RAG), AI agents, AI assistants, or recommendation systems.

In this guide, you'll learn how to build a semantic search system using Ollama, PostgreSQL, pgvector, and pgai—all in under 10 minutes. Pgvector is a robust extension that brings vector handling capabilities to PostgreSQL, while the pgai extension adds advanced features essential for developing AI applications.

Understanding the Architecture for Semantic Search

Semantic search goes beyond traditional keyword-based matching by capturing the meaning and context of your query. Instead of relying solely on exact word matches, it uses vector embeddings to represent the semantic content of text (or any unstructured data). This approach enables your system to retrieve contextually relevant results, even when the exact keywords aren't present.

For example, if you search for "best places to eat," a semantic search system might retrieve documents about "top restaurants nearby" or "highly-rated dining experiences," effectively capturing your intent rather than the exact phrasing.

In practice, semantic search relies on embedding models to generate embeddings. Many top-performing embedding models and LLMs are open-source, and if you want to use them in your application, Ollama offers a simple tool.

In addition to the embedding model, you'll need a solution to store and query vector embeddings. As we've discussed previously, a specialized vector database isn't necessary—you can leverage PostgreSQL with pgvector, pgai, and pgai Vectorizer to achieve efficient vector handling capabilities at any scale.

Here’s a quick overview of each component in the architecture:

Ollama: An open-source tool that enables you to run and manage LLMs and vision language models (VLMs) locally on your computer or a cloud server, providing enhanced privacy and control over data.

pgvector: A PostgreSQL extension that adds support for storing, indexing, and querying high-dimensional vector embeddings.

pgai: A PostgreSQL extension that simplifies data storage and retrieval for RAG and other AI applications by automating the creation and management of embeddings, facilitating semantic search, and enabling the execution of LLM functions directly within SQL queries.

pgai Vectorizer: A feature of the pgai extension that automates the creation and synchronization of vector embeddings directly within the database without the need for external tools.

Prerequisites

Before you get started, ensure that you have a working installation of PostgreSQL with the pgvector, pgai, and vectorizer extensions. You’ll also need to run the vectorizer workers, which automates the embedding creation process. To help you set up quickly, we’ve provided an example docker-compose file to get you started.

Download the Docker Compose file

curl -O https://raw.githubusercontent.com/timescale/pgai/main/examples/docker_compose_pgai_ollama/docker-compose.yml

Start the Docker Compose file

docker compose up -d

That should set up a PostgreSQL database with pgai, pgvector, and pgvectorscale extensions. It will also set up Ollama, which you can now use to deploy LLMs and embedding models.

Download embedding model

We will use the nomic-embed-text model for embedding generation. Download it using the following command:

docker compose exec ollama ollama pull nomic-embed-text

Set up PopSQL

We will use PopSQL as our SQL client. Head over to PopSQL, sign up, and download the desktop client. Once installed, click on Manage Connections and create a new PostgreSQL connection. Select the connection type as ‘Direct’ to connect to your local PostgreSQL installation.

Enable PostgreSQL extensions

We now need to enable the pgai and pgvector extensions. You can do so with the following SQL commands:

CREATE EXTENSION IF NOT EXISTS vector CASCADE; 
CREATE EXTENSION IF NOT EXISTS ai CASCADE;

Your setup is ready. Let’s proceed!

Steps to Build Semantic Search with Ollama

The steps to implement semantic search within PostgreSQL are relatively straightforward. First, we’ll load a dataset from Hugging Face and use pgai’s vectorizer function to automatically generate embeddings from the data using Ollama and the nomic-embed-text embedding model. Then, we’ll perform semantic search by converting the user query into vector embeddings with the ai.ollama_embed function. These embeddings will be used to calculate similarity metrics and fetch relevant data.

Let’s start by creating a table and loading the dataset.

Create table

We’ll create a simple movies table to store selected columns from the dataset. Here’s how:

CREATE TABLE IF NOT EXISTS movies (
   id SERIAL PRIMARY KEY,
   title TEXT NOT NULL,
   overview TEXT,
   genres TEXT,
   producer TEXT,
   "cast" TEXT
);

Load dataset

This guide will use the Cohere/movies dataset from Hugging Face. Pgai provides a convenient utility, ai.load_dataset, which allows you to download any dataset from Hugging Face and populate a table. Let’s use this utility to download the Cohere/movies dataset.

SELECT ai.load_dataset(
   name => 'Cohere/movies',
   table_name => 'movies',
   if_table_exists => 'append',
   field_types => '{"title": "TEXT", "overview": "TEXT", "genres": "TEXT", "producer": "TEXT", "cast": "TEXT"}'::jsonb
);

This will load the data into the movies table.

Create vectorizer

Next, you can automate the embedding creation using the vectorizer tool provided by pgai. The vectorizer eliminates the need for any external tools to create embeddings. You can set it up as follows:

SELECT ai.create_vectorizer(
   'public.movies'::regclass,
   destination => 'movies_embeddings',
   embedding => ai.embedding_ollama('nomic-embed-text', 768),
   chunking => ai.chunking_recursive_character_text_splitter('overview')
);

We used the ai.embedding_ollama function along with the nomic-embed-text embedding model. The generated embeddings are stored in the movie_embeddings table. Additionally, the vectorizer tool automatically creates a convenient view that joins the movies table with its embeddings.

Now, we’re ready to perform semantic search on this data.

Semantic search

To perform semantic search, we’ll use the ai.ollama_embed function with the same embedding model (nomic-embed-text) to generate query embeddings. These query embeddings will then be used to perform a similarity search on the movie_embeddings_vectorized view, which was automatically created by the vectorizer. Here’s how:

WITH query_embedding AS (
    -- Generate the query embedding
    SELECT ai.ollama_embed('nomic-embed-text', 'Epic science fiction movies', host => 'http://ollama:11434') AS embedding
)
SELECT
    m.title,
    m.overview,
    m.genres,
    m.producer,
    m.cast,
    t.embedding <=> (SELECT embedding FROM query_embedding) AS distance
FROM movies_embeddings t
LEFT JOIN movies m ON t.id = m.id
ORDER BY distance
LIMIT 5;

In the semantic search query above, we used the Cosine distance algorithm (<=>) to calculate similarity. You can also use L2 distance or inner product, depending on the nature of your data. Here’s how they are represented:

<-> : L2 (Euclidean) distance
<#> : Inner product
<=> : Cosine distance

Results

The above query will give you the following results when you export in JSON format using PopSQL:

[
  {
    "title": "Epic",
    "overview": "A teenager finds herself transported to a deep forest setting where a battle between the forces of good and the forces of evil is taking place. She bands together with a rag-tag group characters in order to save their world -- and ours.",
    "genres": "Animation, Adventure, Family, Fantasy",
    "producer": "Blue Sky Studios, Twentieth Century Fox Animation",
    "cast": "Josh Hutcherson as Nod (voice), Amanda Seyfried as Mary Katherine (M.K.) (voice), Colin Farrell as Ronin (voice), Jason Sudeikis as Bomba (voice), Aziz Ansari as Mub (voice), Beyoncé Knowles as Queen Tara (voice), Judah Friedlander as Taxi Driver (voice), Steven Tyler as Nim Galuu (voice), Pitbull as Bufo (voice), Blake Anderson as Dagda (voice), Christoph Waltz as Mandrake (voice), John DiMaggio as Pinecone Jinn (voice), Allison Bills as Dandelion Jinn (voice), Todd Cummings as Fruit Fly (Old) (voice), Troy Evans as Thistle Jinn (voice), Emma Kenney as Marigold Girl (voice), Jason Harris as (voice), Jim Conroy as Race Announcer / Additional Voices (voice), Jason Fricchione as Bufo's Goon (voice), Helen Hong as Thistle Lady (voice), Kelly Keaton as Berry Lady (voice), Kyle Kinane as Biker Dude (voice), Anthony Lumia as Fruit Fly (Young) (voice), Malikha Mallette as Jinn Mom (voice), Rosa Salazar as Roller Derby Girl (voice)",
    "distance": 0.5153232235101693
  },
  {
    "title": "Epic Movie",
    "overview": "When Edward, Peter, Lucy and Susan each follow their own path, they end up finding themselves at Willy's Chocolate factory. Walking through a wardrobe, they discover the world of Gnarnia, which is ruled by the White Bitch. Meeting up with characters such as Harry Potter and Captain Jack Swallows, the newly reunited family must team up with Aslo, a wise-but-horny lion to stop the white bitch's army",
    "genres": "Action, Adventure, Comedy",
    "producer": "Twentieth Century Fox Film Corporation, Regency Enterprises, New Regency Pictures, Paul Schiff Productions",
    "cast": "Kal Penn as Edward, Adam Campbell as Peter, Jennifer Coolidge as White Bitch, Jayma Mays as Lucy, Faune A. Chambers as Susan, Crispin Glover as Willy, Tony Cox as Bink, Héctor Jiménez as Mr. Tumnus, Darrell Hammond as Captain Jack Swallows, Carmen Electra as Mystique, Fred Willard as Aslo, David Carradine as Museum Curator, Kevin McDonald as Harry Potter, George Alvarez as Ron, Crista Flanagan as Hermoine, Jill Latiano as Singing Pirate Girl, Irina Voronina as Jogging Girl, Britten Kelley as Cribs Faun",
    "distance": 0.5596413576816696
  },
  {
    "title": "Superhero Movie",
    "overview": "The team behind Scary Movie takes on the comic book genre in this tale of Rick Riker, a nerdy teen imbued with superpowers by a radioactive dragonfly. And because every hero needs a nemesis, enter Lou Landers, aka the villainously goofy Hourglass.",
    "genres": "Action, Comedy, Science Fiction",
    "producer": "Dimension Films",
    "cast": "Drake Bell as Dragonfly, Sara Paxton as Jill Johnson, Leslie Nielsen as Uncle Albert Adams, Christopher McDonald as Lou Landers, Jeffrey Tambor as Dr. Whitby, Pamela Anderson as Invisible Girl, Kevin Hart as Trey, Marion Ross as Aunt Lucille, Ryan Hansen as Lance Landers, Keith David as The Chief of Police, Robert Joy as Dr. Hawking, Robert Hays as Blaine Riker, Nicole Sullivan as Julia Riker, Sam Cohen as Young Rick, Tracy Morgan as Professor Xavier, Brent Spiner as Dr. Strom",
    "distance": 0.5697590708732605
  },
  {
    "title": "Titan A.E.",
    "overview": "A young man finds out that he holds the key to restoring hope and ensuring survival for the human race, while an alien species called the Dredge are bent on mankind's destruction.",
    "genres": "Animation, Action, Science Fiction, Family, Adventure",
    "producer": "Twentieth Century Fox Film Corporation, David Kirschner Productions, Fox Animation Studios",
    "cast": "Matt Damon as Cale Tucker, Bill Pullman as Captain Korso, Drew Barrymore as Akima, John Leguizamo as Gune, Nathan Lane as Preed, Janeane Garofalo as Stith, Ron Perlman as Prof. Sam Tucker, Alex D. Linz as Young Cale",
    "distance": 0.5731358528137207
  },
  {
    "title": "The Book of Eli",
    "overview": "A post-apocalyptic tale, in which a lone man fights his way across America in order to protect a sacred book that holds the secrets to saving humankind.",
    "genres": "Action, Thriller, Science Fiction",
    "producer": "Alcon Entertainment, Silver Pictures",
    "cast": "Denzel Washington as Eli, Gary Oldman as Carnegie, Mila Kunis as Solara, Ray Stevenson as Redridge, Jennifer Beals as Claudia, Tom Waits as Engineer, Michael Gambon as George, Frances de la Tour as Martha, Lateef Crowder as Highjacker #3, Chris Browning as Highjacker Leader, Joe Pingue as Hoyt, Evan Jones as Martz, Lora Martinez-Cunningham as Young Woman Highjacker, Malcolm McDowell as Lombardi, Mike McCarty as Sniper, Richard Cetrone as Hijacker, Don Thai Theerathada as Hijacker, Thom Williams as Hijacker, Scott Wilder as Middle-Aged Man, Heidi Pascoe as Middle-Aged Woman, Jennifer Caputo as Biker, Eddie Perez as Biker, Spencer Sano as Biker / Town Thug, Mike Gunther as Sniper, John Koyama as Sniper, Scott Michael Morgan as Construction Thug Leader, Sala Baker as Construction Thug, Arron Shiver as Bartender, Justin Tade as Town Doctor, Mike Seal as Door Guard, Edward A. Duran as Town Thug, David Wald as Town Thug, Jermaine Washington as Town Thug, Clay Donahue Fontenot as Convoy Thug, Al Goto as Convoy Thug, Brad Martin as Convoy Thug, Tim Rigby as Convoy Thug, Luis Bordonada as Carnegie Gunman #1, Angelique Midthunder as Gatling Gun Gunner, Todd Schneider as Caddy Driver, Darrin Prescott as Suburban Driver, Laurence Chavez as Ice Cream Truck Driver, Brian Neal Lucero as Alcatraz Guard, David Midthunder as Alcatraz Soldier, Karin Silvestri as Biker",
    "distance": 0.5773254679039392
  }
]

That’s it! Pgai and pgai Vectorizer enabled us to create semantic search directly within PostgreSQL using models deployed via Ollama. There was no need to rely on any external libraries or tools.

Next Steps

In this guide, we showed you how to set up a powerful semantic search system directly within PostgreSQL, leveraging open-weight models using Ollama. Ollama makes it easy to deploy popular LLMs, while pgai and vectorizer simplify the AI workflow and your interaction with AI models. With this architecture, you can build a wide range of AI-powered applications that use semantic search, such as RAG systems, recommendation engines, or anomaly detection systems.

Want to start building? Sign up for Timescale Cloud (you can try it for free for 30 days), or learn more about pgai, pgai Vectorizer, or Ollama, and start creating semantic search applications today.