Use Open-Source LLMs in PostgreSQL With Ollama and Pgai

Open-source large language models (LLMs) are a top choice for developers building AI applications like retrieval-augmented generation (RAG), search, and AI agents. In contrast to proprietary models, open-source models are private, free to use (hardware costs aside), can run locally, and can be easily customized for your use case. But getting started with open-source LLMs can be tricky.

That’s where Ollama comes in. Ollama is the easiest and most popular way to get up and running with open-source language models. Think of Ollama as “Docker for LLMs,” enabling easy access and usage of a variety of open-source models like Llama 3, Mistral, Phi 3, Gemma, and more. Plus, Ollama enables local deployment of open-source LLMs on your existing machines, making it easy to get started and build fully-fledged, local-first AI applications.

Today, we’re excited to announce that PostgreSQL developers can now access Ollama models right inside the database with pgai. Pgai is an open-source PostgreSQL extension that brings AI models closer to your data.

What’s in it for PostgreSQL developers working with open-source large language models?

Here’s why Ollama in pgai is a gamechanger for PostgreSQL developers:

Embedding creation: PostgreSQL developers can now create embeddings on data in PostgreSQL tables using popular open-source embedding models like BERT, Meta’s Llama3, Nomic Embed, and mxbai (MixedBread)—all with a simple SQL query. Pgai stores embeddings in the pgvector data type, making it easy to perform search and RAG with pgvector and pgvectorscale after embeddings are created.

RAG and LLM reasoning: Developers can now perform RAG and LLM reasoning tasks on data in PostgreSQL tables, leveraging state-of-the-art open-source models like Meta’s Llama 3, Mistral, Gemma, Qwen, Phi, and more. This unlocks common reasoning tasks like summarization, categorization, and data enrichment, all with a SQL query rather than an entire data pipeline.

Pgai uses Python and PL/Python to interact with Ollama model APIs within your PostgreSQL database.

Start building more private AI applications with open-source models using pgai and Ollama today.

Pgai is open source under the PostgreSQL License and is available for you to use in your AI projects today. You can find installation instructions on the pgai GitHub repository. You can also access pgai on any database service on Timescale’s cloud PostgreSQL platform.

Got questions about using Ollama in pgai? Join the Postgres for AI Discord. Share what you’re working on, and help or get helped by a community of peers.

Let’s now discuss the benefits of using open-source large language models via Ollama for RAG and search applications. We also showcase examples (with code!) of using Ollama via pgai to create embeddings and perform LLM reasoning.

Why Use Open-Source LLMs via Ollama for RAG and Search Applications?

Greater data privacy: By running LLMs directly on your machines using Ollama, you avoid sending your data to third parties, maintaining greater control over where your data is sent compared to cloud-based solutions. This control is essential as many LLM applications run over sensitive business data, which companies want to avoid getting leaked or used to train new models.
Low cost (free): Hardware costs aside, you do not have to pay per token to embed data or generate outputs from models run using Ollama. This makes it a great choice for AI applications where cost is a significant concern or with limited budgets.
Local deployment: Since models run locally on the host machine, Ollama enables working with LLMs even in environments with limited or no Internet connectivity.
Faster processing speed: For local applications, accessing the models on the same machine as your application means avoiding waiting for network calls, resulting in smoother end-user experiences.
Customization and fine-tuning: You can customize models, like modifying the system prompt and fine-tuning models to suit your applications’ needs better. You can also load your own custom models via Ollama for use in your applications.

How to Build Private, Low-Cost RAG Applications

To illustrate what’s possible with Ollama on pgai, let’s take a look at two common tasks developers building RAG applications face:

Creating embeddings for data
LLM reasoning over data, like summarization, classification, or data enrichment
Implementing text-to-SQL

Example 1: Embedding creation using open-source large language models with Ollama and pgai

Creating embeddings for data already in PostgreSQL is a common task when building RAG or search applications. The following example creates a table of quotes about programming. We then use the pgai extension to pass the quotes to an Ollama instance to be embedded by the Llama3 model. We use a simple UPDATE statement with the ollama_embed function in the pgai extension. We store the embeddings in a column named “embedding” of type vector(4096). The vector type comes from the pgvector extension. We can use the vector_dims function from pgvector to count the number of dimensions in each of the embeddings we generated.

-- install the pgai extension
create extension if not exists ai cascade;

-- a table of programming quotes
create table quotes
( id int not null primary key generated by default as identity
, quote text
, person text
, embedding vector(4096) -- the vector data type is from the pgvector extension
);

insert into quotes (quote, person) values
  ('What one programmer can do in one month, two programmers can do in two months.', 'Frederick P. Brooks')
, ('The only way to learn a new programming language is by writing programs in it.', 'Dennis Ritchie')
, ('Talk is cheap. Show me the code.', 'Linus Torvalds')
;

-- use ollama with the llama3 model to embed the content
update quotes set embedding = ollama_embed('llama3', format('%s - %s', person, quote));

-- show the number of vectors in each embedding
select quote, person, vector_dims(embedding)
from quotes
;

Example 2: LLM reasoning using open-source models with Ollama and pgai

Next, let’s look at an example of using Ollama to reason over data stored in a PostgreSQL table. For this example, we will create a table of movies and ask an LLM to summarize the career of Robert Downey Jr. based on the information contained in the table.

Install some necessary Python packages with the following command:

pip install pandas pyarrow fsspec huggingface_hub

Run the following Python script to download the parquet file containing the movies and convert it to a CSV file.

import pandas as pd

df = pd.read_parquet("hf://datasets/Cohere/movies/movies.parquet")
df.to_csv('movies.csv')

Next, we create a table in PostgreSQL and load it with the movies from the CSV file. Finally, select all the movies starring Robert Downey Jr. and ask Llama 3 to summarize his career using the ollama_generate function in the pgai extension:

create extension if not exists ai cascade;

create table movie
( id int not null primary key
, title text not null
, overview text
, genres text
, producer text
, "cast" text
);

\copy movie from 'movies.csv' with (format csv, header on)

with career as
(
    -- construct a big multi-line string containing a list of movies
    select string_agg(format(E'title: %s\noverview: %s\ngenres: %s\n', title, overview, genres), E'\n') as movies
    from movie
    where "cast" like '%Robert Downey Jr.%'
)
select ollama_generate
( 'llama3'
, concat(E'Robert Downey Jr. was an actor in the movies listed below. Please summarize his career giving an overview of the types of movies he has starred in.\n', career.movies)
)->>'response'
from career
;

Here’s the LLM response:

Robert Downey Jr. is an American actor known for his versatility in playing a wide range of characters across various genres. His filmography showcases his ability to adapt to different roles and projects.
Downey Jr.'s most notable work has been in the Marvel Cinematic Universe (MCU), where he played Tony Stark/Iron Man in multiple films, including:
* Iron Man (2008)
* The Avengers (2012)
* Iron Man 3 (2013)
* The Avengers: Age of Ultron (2015)
* Captain America: Civil War (2016)
* Spider-Man: Homecoming (2017)
* Avengers: Infinity War (2018)
* Avengers: Endgame (2019)
He has also appeared in other notable films, such as:
* Sherlock Holmes (2009) and its sequel Sherlock Holmes: A Game of Shadows (2011), where he played the titular character
* Tropic Thunder (2008), a comedy film that parodies Hollywood culture
* The Judge (2014), a drama film where he plays a lawyer who returns to his hometown for his mother's funeral
* The Shaggy Dog (2006), a family-friendly comedy film based on the classic Disney franchise
In addition to these notable roles, Downey Jr. has also appeared in a wide range of smaller films and independent projects, showcasing his ability to take on diverse characters and genres.
Throughout his career, Robert Downey Jr. has demonstrated his versatility as an actor, taking on complex characters, and exploring various genres from drama and comedy to action and science fiction.

Example 3: Text-to-SQL using Ollama and pgai

Now that we’ve seen embedding creation and summarization, let’s see how to use pgai and Ollama to perform text-to-SQL.

In the following example, we use the ollama_generate function to send a prompt to Llama3 and get a response. We provide a multi-line prompt using a dollar-quoted string literal. In the prompt, we describe a PostgreSQL table and a function from the pgai extension. We then ask Llama3 to write a SQL UPDATE statement.

select ollama_generate
( 'llama3'
, $$
In PostgreSQL version 16, I have a table defined like this:

create table quotes
( id int not null primary key generated by default as identity
, quote text
, person text
, embedding vector(4096) -- the vector data type is from the pgvector extension
);

There is a function called "ollama_embed" with a signature like this:

ollama_embed(_model text, _prompt text) returns vector

I want to embed the contents of the quote column in the quotes table using the ollama_embed function and the "llama3" model.

Write an UPDATE statement to set the embedded column of the quotes table to the vector returned from the ollama_embed function.

Your response should ONLY include the SQL statement with NO commentary. Make sure the output is valid SQL for PostgreSQL version 16.
$$
, _system=>'You are an expert PostgreSQL developer who assists in writing SQL queries.'
)->>'response'
;

Running this multiple times will likely produce different SQL statements with varying correctness and quality. However, it did produce the correct answer for us:

UPDATE quotes 
SET embedding = ollama_embed('llama3', quote)

Get Started With Ollama and Pgai Today

Pgai is an effort to enrich the PostgreSQL ecosystem for AI. If you’d like to help, here’s how you can get involved:

Got questions about using Ollama in pgai? Join the Postgres for AI Discord, a community of developers building AI applications with PostgreSQL. Share what you’re working on, and help or get helped by a community of peers.
Share the news with your friends and colleagues: Share our posts announcing pgai on X/Twitter, LinkedIn, and Threads. We promise to RT back.
Submit issues and feature requests: We encourage you to submit issues and feature requests for functionality you’d like to see, bugs you find, and suggestions you think would improve pgai. Head over to the pgai GitHub repo to share your ideas.
Make a contribution: We welcome community contributions for pgai. Pgai is written in Python and PL/Python. Let us know which models you want to see supported, particularly for open-source embedding and generation models. See the pgai GitHub for instructions to contribute.
Offer the pgai extension on your PostgreSQL cloud: Pgai is an open-source project under the PostgreSQL License. We encourage you to offer pgai on your managed PostgreSQL database-as-a-service platform and can even help you spread the word. Get in touch via our Contact Us form and mention pgai to discuss further.

Let's make Postgres a better database for AI, together!