Build an AI FAQ Assistant with PostgreSQL, pgvector and Gemini

Ordinary keyword search needs matching words, and people rarely use the same words as the person who wrote the answer. Look at these two sentences:

A person sees straight away that they mean almost the same thing. A computer comparing letters sees only one shared word: “I”. Semantic search compares meaning instead of words, and this guide shows you how to build it inside PostgreSQL.

Try it: keyword search on five documents

Below are the five documents used in the workshop's semantic-search lab (Chapter 8). The box runs the same kind of keyword match as SQL's ILIKE '%word%'. Search for system, which is the word a user asking “Which system can manage structured information?” might type.

Keyword search returns 0 rows for “system”. In the workshop, semantic search ranked “PostgreSQL can store and manage structured information.” first for the same question, because it matched on meaning.

The finished system

By the end you will have an AI FAQ Assistant. A user types a question, the system finds the most relevant FAQs by meaning in PostgreSQL, and Gemini writes a short answer using only those FAQs. This pattern is called RAG: Retrieval-Augmented Generation. In one line: find the right information first, then let AI write the answer from it.

The tools: four pieces, one database

One database stores both the normal data and the vectors, so you don't need a separate vector database.

Each step builds on the one before it. Colours: store data · connect code · understand meaning · answer questions.

You need four things: PostgreSQL (the database server), pgAdmin 4 or DBeaver (a SQL editor), Python with Jupyter Notebook (to run code cell by cell), and the pgvector extension. The detailed steps below are for Windows, which is what I used. Mac and Linux users can follow the official links in section 2.3.

2.1 Install PostgreSQL 18 on Windows

Before you begin: a 64-bit Windows PC (the EDB download page lists only Windows x86-64 as supported), an administrator account (the installer sets PostgreSQL up as a Windows service), an internet connection, and a password for the database superuser postgres. Write the password down, because you need it every time you connect. The install takes about 10 minutes, most of it spent waiting for files to unpack.

SELECT version();

"C:\Program Files\PostgreSQL\18\bin\psql.exe" -U postgres -h localhost -p 5432
postgres=# SELECT version();
postgres=# \q

2.2 Install pgvector on Windows (build from source)

pgvector is a separate add-on. If CREATE EXTENSION vector; fails with a message that the extension is not available, pgvector isn't installed yet. On Windows you compile it with Visual Studio's C++ tools. This guide uses pgvector 0.8.7 (released 1 October 2026) with PostgreSQL 18.

1. 1
   Download Visual Studio

   Open the official Microsoft Visual Studio website and download the installer.

2. 2
   Run the Visual Studio Installer

   If User Account Control appears, confirm the publisher is Microsoft Corporation and continue.

3. 3
   Install the C++ workload

   Select Desktop development with C++, click Install, and wait for it to finish. This provides the Microsoft C++ compiler and build tools.

4. 4
   Open the right command prompt

   Search for x64 Native Tools Command Prompt for VS, right-click it and choose Run as administrator. A normal Command Prompt will not work.

5. 5
   Run the six commands below one at a time

   Check that each one completes before running the next.

set "PGROOT=C:\Program Files\PostgreSQL\18"
cd %TEMP%
git clone --branch v0.8.7 https://github.com/pgvector/pgvector.git
cd pgvector
nmake /F Makefile.win
nmake /F Makefile.win install

Enable and verify. Installing makes the extension available. You must still enable it once in each database where you want to use it. In pgAdmin or psql, connected to that database, run:

CREATE EXTENSION vector;

SELECT extname, extversion
FROM pg_extension
WHERE extname = 'vector';   -- a fresh install reports 0.8.7

Quick test. Create a tiny table with 3-number vectors and run a nearest-neighbour query (<-> is Euclidean/L2 distance):

CREATE TABLE items (
    id BIGSERIAL PRIMARY KEY,
    embedding vector(3)
);

INSERT INTO items (embedding) VALUES ('[1,2,3]'), ('[4,5,6]');

SELECT * FROM items;

SELECT * FROM items
ORDER BY embedding <-> '[3,1,2]'
LIMIT 5;

If these run, pgvector is installed and working. Build errors are covered in Chapter 11.

2.3 macOS and Linux

My workshop used Windows, so I haven't tested other systems. Please follow the official instructions:

• PostgreSQL: postgresql.org/download. Choose macOS or your Linux distribution.
• pgvector: the Installation section of the pgvector README covers Linux, Mac and other options.

Everything from CREATE EXTENSION vector; onwards is the same on every operating system.

2.4 Python, Jupyter and your Gemini API key

1. 1
   Install Python 3 and Jupyter Notebook

   The workshop ran every step in a Jupyter notebook, cell by cell.

2. 2
   Create a Gemini API key

   Create a free key in Google AI Studio.

3. 3
   Save the key in a file, not in your notebook

   Create a file named api_key.txt next to your notebook and paste only the key into it. Never share a notebook that contains your real key.

4. 4
   Install the libraries

   Run this in a notebook cell. The ! runs it as a shell command, and -q hides the long install log.

!pip install -q -U google-genai "psycopg[binary]" psycopg2-binary pandas

Close an app and open it tomorrow, and your data is still there. A college app keeps names, departments and marks; a shopping app keeps products and orders; a chat app keeps users and messages. The system that keeps this information safe, organised and searchable is a database.

Why not a text file? Four lines are easy, but forty thousand are not. Finding every CSE student quickly, correcting one age without breaking other lines, removing one record safely, or letting two people edit at once are all hard with a plain file. A database gives you ready-made tools for all of them.

PostgreSQL is a free, open-source relational database management system (RDBMS) that stores structured information in tables. Others include MySQL, Oracle, SQL Server and SQLite. With the pgvector extension, PostgreSQL can also store AI embeddings. One PostgreSQL server holds many databases, a database holds many tables, and a table holds rows and columns.

Anatomy of a table

Lab: create your workshop database

In pgAdmin or DBeaver, check the server is reachable, then create a database:

SELECT version();
CREATE DATABASE ai_workshop;

Create the first table

CREATE TABLE developers (
    id         SERIAL PRIMARY KEY,
    name       VARCHAR(100),
    department VARCHAR(50),
    age        INTEGER
);

Insert and view data

INSERT INTO developers (name, department, age)
VALUES ('Ravi',  'CSE', 21),
       ('Priya', 'ECE', 22),
       ('Arun',  'CSE', 20),
       ('Meena', 'IT',  21);

SELECT * FROM developers;   -- * means "all columns"

Text values go in single quotes; numbers don't. You never supply id because SERIAL fills it.

Filter with WHERE, sort with ORDER BY

SELECT * FROM developers WHERE department = 'CSE';   -- Ravi and Arun

SELECT * FROM developers ORDER BY age DESC;  -- oldest first
SELECT * FROM developers ORDER BY age ASC;   -- youngest first (ASC is the default)

Update and delete

UPDATE developers SET age = 22 WHERE id = 1;  -- Ravi's age
DELETE FROM developers WHERE id = 4;           -- remove Meena
SELECT * FROM developers;                      -- check

That is all the SQL this project needs: CREATE TABLE, INSERT, SELECT, WHERE, ORDER BY, UPDATE, DELETE.

Real applications don't type SQL into pgAdmin; their code sends it automatically. Python speaks Python and PostgreSQL speaks SQL, so a driver library such as psycopg acts as the phone line between them. It carries SQL from your code to the server and brings the rows back. Later, Python will fetch embeddings from Gemini and store them through this same line.

Install, import and connect (psycopg 3)

import psycopg

conn = psycopg.connect(
    host="localhost",
    dbname="ai_workshop",
    user="postgres",
    password="YOUR_POSTGRES_PASSWORD"
)
print("Connected!")

If Connected! prints, Python can reach the database.

Read data using Python

with conn.cursor() as cur:
    cur.execute("SELECT * FROM developers")
    rows = cur.fetchall()

for row in rows:
    print(row)

(1, 'Ravi', 'CSE', 22)
(2, 'Priya', 'ECE', 22)
(3, 'Arun', 'CSE', 20)

The cursor sends SQL and receives results. fetchall() returns a list in which each row is a Python tuple. with … as cur closes the cursor automatically. This Python → SQL → PostgreSQL → rows-back round trip is the heart of the AI project.

Insert data using Python

with conn.cursor() as cur:
    cur.execute(
        """
        INSERT INTO developers (name, department, age)
        VALUES (%s, %s, %s)
        """,
        ("Kiran", "CSE", 21)
    )
conn.commit()
print("Data inserted!")

An API (Application Programming Interface) is a way for one software application to communicate with another. Analogy: you (Python) never enter the kitchen (the Gemini model, which runs on Google's servers). You give your order to the waiter (the API), who returns with the dish (the response).

Connect to Gemini

This reads the key from api_key.txt (created in Chapter 2). .strip() removes stray spaces or newlines. The client is your connection to Gemini, and every Gemini call goes through it.

from google import genai

api_key = open("api_key.txt").read().strip()
client = genai.Client(api_key=api_key)
print("Gemini client created successfully")

Choose your models and test

The project uses two Gemini models with different jobs. Keep the names in variables so you only change them in one place.

TEXT_MODEL = "gemini-3.5-flash-lite"       # writes answers (text generation)
EMBEDDING_MODEL = "gemini-embedding-001"   # converts text into vectors

response = client.models.generate_content(
    model=TEXT_MODEL,
    contents="Explain postgresql in one simple sentence."
)
print(response.text)

PostgreSQL is a powerful, open-source relational database management system known for its reliability, advanced features, and ability to handle complex data at scale.

If a one-sentence answer prints, three things work: your key, the SDK and your internet connection.

Computers compare numbers, not meanings. Which two of these are most similar?

Obviously A and B, but a computer can't “see” that from letters. Goal: give every sentence a list of numbers so that similar meanings get similar numbers. Once meaning becomes numbers, comparing meaning becomes arithmetic.

What is a vector?

A vector is simply a list of numbers. The number of values is its number of dimensions.

Map analogy: a place needs 2 numbers, and nearby places have nearby numbers. An embedding is a “meaning address” with 768 numbers instead of 2.

How does a model compute an embedding? (simplified)

Real models (transformers) adjust each token using its neighbours, so “bank” of a river differs from a “bank” account. In the toy model below we simply average the token vectors and scale the result to length 1.

Step 1: give each word its numbers (the lookup table)

Step 2: combine the words by averaging

Sentence A before normalising = [0.4, 0.3, 0.0]: mostly animal, some feeling, no money.

Step 3: normalise to length 1

We care about the direction of the arrow, not its length. A long sentence can produce bigger numbers than a short one even when they mean the same thing, and normalising removes that effect.

length = √(0.4² + 0.3² + 0.0²) = √(0.16 + 0.09 + 0) = √0.25 = 0.5

animal  = 0.4 ÷ 0.5 = 0.8
feeling = 0.3 ÷ 0.5 = 0.6
finance = 0.0 ÷ 0.5 = 0.0

Embedding of "I love dogs" = [0.8, 0.6, 0.0]
Check: 0.8² + 0.6² + 0.0² = 0.64 + 0.36 = 1.00  ->  √1.00 = 1  ✓

The same three steps give B “I like puppies” = [0.6, 0.8, 0.0] (like = [0.1, 0.9, 0.0], puppies = [0.7, 0.2, 0.0]) and C “The stock market fell today” = [0.0, 0.28, 0.96]. C has 5 words, so you divide by 5 (The = [0, 0, 0], stock = [0, 0, 0.9], market = [0, 0, 1.0], fell = [0, 0.6, 0.3], today = [0, 0.1, 0.2]).

Try it yourself: toy embedding calculator

Type sentences using the toy words and watch every step: lookup, average, length, normalise. Chapter 7 explains the similarity numbers it also shows.

Where do the real numbers come from?

1. Start random. Before training, “dog” and “puppy” are no more alike than “dog” and “stock”.
2. Practise fill-in-the-blank, millions of times: “I took my ___ for a walk in the park.” A wrong guess nudges the numbers a little. Words that fit the same blanks get nudged the same way, so their lists slowly become similar.
3. Pull together, push apart (contrastive learning): “How do I reset my password?” and “I forgot my password, what should I do?” are pulled closer, while an unrelated sentence is pushed away. As a result, similar meanings get nearby vectors.

Google hasn't published every detail of how Gemini's embedding model was trained; this is the standard approach for models of this kind.

Why can't we name the 768 positions? One position may respond a little to several ideas at once, and one idea is spread across many positions. Meaning lives in directions. A famous result from an older model (word2vec) is king − man + woman ≈ queen, which works with no column called “gender”.

Real embeddings with gemini-embedding-001

This is the embedding function from the workshop slides. It asks for 768 numbers per text.

from google.genai import types

EMBEDDING_MODEL = "gemini-embedding-001"

def create_embedding(text):
    result = client.models.embed_content(
        model=EMBEDDING_MODEL,
        contents=text,
        config=types.EmbedContentConfig(output_dimensionality=768)
    )
    return result.embeddings[0].values

embed_content can embed many texts, so it returns a list; [0] takes the first. .values is a plain Python list of 768 decimal numbers.

embedding = create_embedding("PostgreSQL is a database.")
print(len(embedding))    # 768
print(embedding[:10])    # first 10 numbers

sentences = ["I love dogs.", "I like puppies.", "The stock market fell today."]
embeddings = [create_embedding(t) for t in sentences]
print(len(embeddings))       # 3
print(len(embeddings[0]))    # 768

Your actual numbers will differ from anything shown here. What matters is the length: 768.

Similar meanings point in similar directions. A small angle between two arrows means the sentences mean nearly the same thing (A and B); a large angle means they are about different things (A and C). You measure the angle with cosine.

Step 1: the dot product (multiply matching positions, then add)

A = [0.8, 0.6, 0.0]      B = [0.6, 0.8, 0.0]
A · B = 0.8×0.6 + 0.6×0.8 + 0.0×0.0 = 0.48 + 0.48 + 0.00 = 0.96

Step 2: the length (magnitude) of each vector

|A| = √(0.64 + 0.36 + 0) = 1.0 and |B| = 1.0. Because we normalised, every length is 1.

Step 3: cosine similarity

Step 4: cosine distance (what pgvector uses)

In PostgreSQL, embedding <=> query returns this value: 0 = same meaning, 1 = unrelated, 2 = opposite. Smaller means closer, so you ORDER BY it smallest first.

The maths agrees with human judgement. Sorting by distance is exactly what semantic search does. You can check these three rows with the calculator in Chapter 6.

Other distance operators in pgvector

For length-1 vectors all three give the same ranking. Cosine is the usual choice for text.

pgvector adds a VECTOR type next to TEXT, INTEGER, DATE and BOOLEAN, so the text and its meaning live in the same row.

Illustrative values. Your real numbers will differ.

Enable pgvector and create the documents table

Run this in pgAdmin, connected to ai_workshop:

CREATE EXTENSION IF NOT EXISTS vector;
SELECT extname FROM pg_extension;   -- 'vector' should be listed

CREATE TABLE documents (
    id        SERIAL PRIMARY KEY,
    content   TEXT,
    embedding VECTOR(768)
);

Store five documents

These five were chosen so the results are easy to judge: two about PostgreSQL, one about another database, one about Python and one about machine learning.

documents = [
    "PostgreSQL is an open-source relational database.",
    "PostgreSQL can store and manage structured information.",
    "MongoDB is a document-oriented database.",
    "Python is a popular programming language used in AI.",
    "Machine learning allows computers to learn patterns from data.",
]

with conn.cursor() as cur:
    cur.execute("TRUNCATE TABLE documents RESTART IDENTITY")
conn.commit()

for text in documents:
    embedding = create_embedding(text)
    with conn.cursor() as cur:
        cur.execute(
            "INSERT INTO documents (content, embedding) VALUES (%s, %s)",
            (text, embedding))
    conn.commit()

with conn.cursor() as cur:
    cur.execute("SELECT COUNT(*) FROM documents")
    print(cur.fetchone()[0])      # 5

TRUNCATE … RESTART IDENTITY empties the table and restarts ids at 1, so nothing is duplicated. Count shows 6 or 10? A cell ran twice, so re-run the TRUNCATE and insert again. When inserting into a VECTOR column, the Python list is converted to a vector automatically. Check with SELECT id, content FROM documents; skip the 768 numbers because they are hard to read.

Semantic search in SQL: it is just ORDER BY

SELECT content,
       embedding <=> %s::vector AS distance
FROM documents
ORDER BY embedding <=> %s::vector
LIMIT 5;

Read it aloud: for every document, compute its distance to the question, sort closest first, and return 5. The question vector appears twice: once to show the distance and once to sort by it.

A reusable search function

def semantic_search(query, top_k=3):
    query_embedding = create_embedding(query)
    with conn.cursor() as cur:
        cur.execute("""
            SELECT content, embedding <=> %s::vector AS distance
            FROM documents
            ORDER BY embedding <=> %s::vector
            LIMIT %s""",
            (query_embedding, query_embedding, top_k))
        return cur.fetchall()

for content, distance in semantic_search(
        "Which system can manage structured information?"):
    print(round(distance, 3), content)

Expected: the two PostgreSQL documents appear at the top. This function becomes the retrieval part of RAG.

Predict first, then run

for q in ["Which database can store information?",
          "Which programming language is popular in AI?",
          "How can computers learn from data?"]:
    print("QUESTION:", q)
    for content, distance in semantic_search(q):
        print(f"  {distance:.3f}  {content}")

What about a million documents?

This lab compares the question against every row (exact search). That always finds the true closest match, but it slows down as rows grow into millions. A vector index, like the index at the back of a book, jumps to nearby vectors fast; results are approximate but very good. pgvector offers HNSW, and vector_cosine_ops matches the <=> operator:

CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops);

You don't need it for small tables like these; just know it exists.

Semantic search returns documents, but a person still has to read them and write the answer. Can AI do that step? First, ask Gemini on its own: “What is the last date to pay the exam fee at our college?”

Gemini: “The last date to pay the exam fee is 15 March.” ✗ sounds right, but invented

Each side has half of the answer. Gemini writes clear, natural answers but doesn't know our information. Our database knows our information but can't write answers. RAG joins the two: the database finds, Gemini writes.

Analogy: closed-book vs open-book exam

Another way to see it: the librarian (pgvector) doesn't write anything; it quickly fetches the pages that match your question by meaning. The writer (Gemini) doesn't search anything; it writes a clear answer using only the pages given. The writer is only as good as the pages the librarian brings.

Follow one question through the system

A student types “Till when can I pay my exam fees?”. The FAQ texts and distances below are illustrative examples from the workshop.

Setup (done once): 50 college FAQs → create_embedding() → stored in the documents table with pgvector.

1. 1
   Embed the question

   The same create_embedding() used for the FAQs turns it into 768 numbers, so the question and the FAQs live in the same “meaning space”. You must use the same embedding model for both.

2. 2
   Search: find the right “pages”

   semantic_search(question, top_k=3) returns: “The exam fee must be paid before 10 October through the student portal.” (0.12), “A late fee of ₹500 applies for payments between 11 and 15 October.” (0.19), and “Hall tickets are issued only after fee payment.” (0.31). The question said till when … fees and the FAQ says must be paid before … fee: different words, same meaning.

3. 3
   Build the prompt (augment)

   Add the found FAQs to the prompt. You aren't changing Gemini at all, only giving it a better prompt.

4. 4
   Generate

   Gemini writes: “You can pay the exam fee until 10 October through the student portal. Between 11 and 15 October you can still pay, but a late fee of ₹500 applies.” It combined FAQ 1 and FAQ 2 into one natural answer.

Answer the question using ONLY the information below.
If the answer is not there, say "I don't know".

Information:
1. The exam fee must be paid before 10 October through the student portal.
2. A late fee of ₹500 applies for payments between 11 and 15 October.
3. Hall tickets are issued only after fee payment.

Question: Till when can I pay my exam fees?

Chunk = a small piece of a long document, so each embedding captures one focused idea. Each FAQ here is already small, so one FAQ = one chunk.

Gemini alone vs Gemini with RAG

Updating the assistant needs no retraining

If the exam date changes, retraining an AI model would need huge data, powerful GPUs, days or weeks, and high cost. With RAG you update one row in the table, and its embedding, in seconds:

new_text = "The exam fee must be paid before 12 October."
cur.execute("UPDATE documents SET content = %s, embedding = %s::vector WHERE id = 1",
            (new_text, create_embedding(new_text)))
conn.commit()

Think first: five questions

Answer in your head, then click to reveal.

This is the main project, exactly as I ran it in a Jupyter notebook. It uses psycopg2 and connects to the default postgres database. The outputs shown are the real ones from my run. Yours may differ slightly, especially the AI-written answers.

Part 1: set up PostgreSQL and load the FAQs

!pip install -q google-genai psycopg2-binary pandas

import pandas as pd
import psycopg2
from google import genai

api_key = open("api_key.txt").read().strip()
client = genai.Client(api_key=api_key)
print("gemini client created successfully")

TEXT_MODEL = "gemini-3.5-flash-lite"
EMBEDDING_MODEL = "gemini-embedding-001"

DB_HOST = "localhost"
DB_PORT = 5432
DB_NAME = "postgres"
DB_USER = "postgres"
DB_PASSWORD = "YOUR_POSTGRES_PASSWORD"   # better: read it from an environment variable

conn = psycopg2.connect(host=DB_HOST, port=DB_PORT, database=DB_NAME,
                        user=DB_USER, password=DB_PASSWORD)
cur = conn.cursor()
print("connected to postgresql successfully!")

Analogy: conn is the phone call to the database, and cur is you speaking and listening during the call.

cur.execute("CREATE EXTENSION IF NOT EXISTS vector;")
conn.commit()
print("pgvector enabled")

cur.execute("""
CREATE TABLE IF NOT EXISTS faqs (
    id         SERIAL PRIMARY KEY,   -- 1, 2, 3 ... assigned automatically
    question   TEXT NOT NULL,
    answer     TEXT NOT NULL,
    embedding  VECTOR(768)           -- exactly 768 numbers
);""")
conn.commit()
print("FAQ table created!")

Text and vectors live side by side in the same row. The embedding column starts empty (NULL) and gets filled in Part 2. IF NOT EXISTS means running the cell twice does no harm.

The knowledge base: 50 FAQs. These are the only facts the assistant will answer from. The topics match the workshop: databases & SQL (12), Python & programming (7), APIs & API keys (2), AI/ML/NLP (5), embeddings & vectors (4), vector databases & pgvector (3), semantic search (4), RAG (4), prompts/LLMs/Gemini (3), Docker & cloud (3) and data in AI apps (3).

faq_data = [
    {
        "question": "What is PostgreSQL?",
        "answer": "PostgreSQL is a free and open-source relational database system used to store, organize, and manage data."
    },
    {
        "question": "What is SQL?",
        "answer": "SQL stands for Structured Query Language. It is used to work with data stored in databases."
    },
    {
        "question": "What is a database?",
        "answer": "A database is an organized collection of information that can be stored, searched, and managed by a computer system."
    },
    {
        "question": "What is a table in a database?",
        "answer": "A table is a structure inside a database that stores information using rows and columns."
    },
    {
        "question": "What is a row?",
        "answer": "A row represents one complete record in a database table."
    },
    {
        "question": "What is a column?",
        "answer": "A column represents one type or category of information stored in a database table."
    },
    {
        "question": "What is a primary key?",
        "answer": "A primary key is a column that uniquely identifies each row in a database table."
    },
    {
        "question": "What is an index?",
        "answer": "An index is a database structure that helps the database find information more efficiently."
    },
    {
        "question": "What is SELECT in SQL?",
        "answer": "SELECT is an SQL command used to read or retrieve data from a database."
    },
    {
        "question": "What is INSERT in SQL?",
        "answer": "INSERT is an SQL command used to add new records to a database table."
    },
    {
        "question": "What is UPDATE in SQL?",
        "answer": "UPDATE is an SQL command used to change existing data in a database table."
    },
    {
        "question": "What is DELETE in SQL?",
        "answer": "DELETE is an SQL command used to remove records from a database table."
    },
    {
        "question": "What is Python?",
        "answer": "Python is a popular programming language known for its simple syntax and use in software development, data science, and AI."
    },
    {
        "question": "What is a variable in Python?",
        "answer": "A variable is a name used to store a value in a Python program."
    },
    {
        "question": "What is a Python function?",
        "answer": "A function is a reusable block of Python code that performs a particular task."
    },
    {
        "question": "What is a Python list?",
        "answer": "A Python list is a collection that can store multiple values in a single variable."
    },
    {
        "question": "What is a Python dictionary?",
        "answer": "A Python dictionary stores information as key-value pairs."
    },
    {
        "question": "What is programming?",
        "answer": "Programming is the process of writing instructions that tell a computer what to do."
    },
    {
        "question": "What is a programming language?",
        "answer": "A programming language is a language used by programmers to write instructions that computers can execute."
    },
    {
        "question": "What is an API?",
        "answer": "An API is a way for one software application to communicate with another software application."
    },
    {
        "question": "What is an API key?",
        "answer": "An API key is a piece of information used by an application to identify or authenticate access to an API."
    },
    {
        "question": "What is Artificial Intelligence?",
        "answer": "Artificial Intelligence, or AI, is technology that allows computers to perform tasks that normally require human-like intelligence."
    },
    {
        "question": "What is Machine Learning?",
        "answer": "Machine Learning is a part of AI where computers learn patterns from data and use those patterns to make predictions or decisions."
    },
    {
        "question": "What is an AI model?",
        "answer": "An AI model is a computer system that has learned patterns from data and can perform tasks such as generating text or making predictions."
    },
    {
        "question": "What is Natural Language Processing?",
        "answer": "Natural Language Processing, or NLP, is a field of AI that helps computers work with human language."
    },
    {
        "question": "What is text generation?",
        "answer": "Text generation is the process of using an AI model to create human-readable text from an input."
    },
    {
        "question": "What is an embedding?",
        "answer": "An embedding is a list of numbers that represents information such as text in a form that an AI system can process."
    },
    {
        "question": "What is a vector?",
        "answer": "A vector is a list of numbers. In AI applications, vectors can be used to represent information such as the meaning of text."
    },
    {
        "question": "Why do we use embeddings?",
        "answer": "Embeddings allow computers to represent the meaning of information as numbers so that similar information can be compared."
    },
    {
        "question": "How does text become an embedding?",
        "answer": "An embedding model takes text as input and converts it into a list of numbers that represents information about the text."
    },
    {
        "question": "What is a vector database?",
        "answer": "A vector database is a database system designed to store and search vector representations of information."
    },
    {
        "question": "What is pgvector?",
        "answer": "pgvector is a PostgreSQL extension that allows PostgreSQL to store and search vector data."
    },
    {
        "question": "Why use pgvector with PostgreSQL?",
        "answer": "pgvector allows PostgreSQL to store normal data together with vector data and perform similarity searches."
    },
    {
        "question": "What is semantic search?",
        "answer": "Semantic search finds information based on meaning instead of only looking for exact matching words."
    },
    {
        "question": "How is semantic search different from keyword search?",
        "answer": "Keyword search mainly looks for matching words, while semantic search uses embeddings to compare the meaning of information."
    },
    {
        "question": "What is similarity search?",
        "answer": "Similarity search finds information whose vector representation is similar to the vector representation of the user's question."
    },
    {
        "question": "Why are vectors useful for search?",
        "answer": "Vectors allow information to be represented as numbers so that a system can compare the similarity between different pieces of information."
    },
    {
        "question": "What is RAG?",
        "answer": "RAG stands for Retrieval-Augmented Generation. It retrieves relevant information and gives that information to an AI model to generate an answer."
    },
    {
        "question": "How does RAG work?",
        "answer": "RAG first searches a knowledge source for relevant information and then gives the retrieved information to an AI model before generating the final answer."
    },
    {
        "question": "Why do we use RAG?",
        "answer": "RAG allows an AI application to use information retrieved from a knowledge source when generating an answer."
    },
    {
        "question": "What is a RAG application?",
        "answer": "A RAG application is an AI application that retrieves relevant information from a knowledge source and uses that information to generate an answer."
    },
    {
        "question": "What is a prompt?",
        "answer": "A prompt is an instruction or question given to an AI model."
    },
    {
        "question": "What is a language model?",
        "answer": "A language model is an AI model that can process and generate human language."
    },
    {
        "question": "What is Gemini?",
        "answer": "Gemini is a family of AI models from Google that can be used for tasks such as understanding and generating content."
    },
    {
        "question": "What is Docker?",
        "answer": "Docker is a technology used to package applications and their dependencies into containers."
    },
    {
        "question": "What is a Docker container?",
        "answer": "A Docker container is an isolated environment that contains an application and the components it needs to run."
    },
    {
        "question": "What is cloud computing?",
        "answer": "Cloud computing means using computing resources such as servers and storage over the internet."
    },
    {
        "question": "What is data?",
        "answer": "Data is information that can be stored, processed, and analyzed by a computer."
    },
    {
        "question": "Why do AI applications need databases?",
        "answer": "AI applications can use databases to store information, retrieve useful data, and provide information to AI models."
    },
    {
        "question": "How can PostgreSQL be used in an AI application?",
        "answer": "PostgreSQL can store application data and, with pgvector, can also store and search vector embeddings for AI applications."
    }
]

print("number of faqs:", len(faq_data))     # 50
df = pd.DataFrame(faq_data)
df                                          # preview as a table

for item in faq_data:
    cur.execute("INSERT INTO faqs (question, answer) VALUES (%s, %s)",
                (item["question"], item["answer"]))
conn.commit()

cur.execute("SELECT COUNT(*) FROM faqs;")
print("Number of faqs in postgresql:", cur.fetchone()[0])

number of faqs: 50
Number of faqs in postgresql: 50

Part 2: turn every FAQ into a vector

An embedding is meaning written as numbers. Every text, short or long, becomes exactly 768 numbers. Texts with similar meaning get vectors that point the same way. No single number means anything alone; the pattern as a whole encodes the meaning.

def create_embedding(text):
    result = client.models.embed_content(
        model=EMBEDDING_MODEL,
        contents=text,
        config={"output_dimensionality": 768}   # must match VECTOR(768)
    )
    return result.embeddings[0].values

embedding = create_embedding(" ")
print("Number of values:", len(embedding))
print("First 10 values:", embedding[:10])

Number of values: 768
First 10 values: [-0.0053882604, 0.0076578804, 0.011579545, -0.0575647, 0.029505298, 0.011536011, -0.0020401385, 0.003505577, -0.007628863, 0.0032300877]

Embed question + answer together, so the vector carries the full meaning of the FAQ. That way a user's question can match ideas that appear only in the answer.

def faq_text(item):
    return f"""
    question:
    {item["question"]}
    Answer:
    {item["answer"]}
    """

for i, item in enumerate(faq_data):
    print(f"Processing FAQ {i+1} of {len(faq_data)}")
    text = faq_text(item)
    embedding = create_embedding(text)
    faq_id = i + 1
    embedding_string = "[" + ",".join(str(x) for x in embedding) + "]"
    cur.execute("UPDATE faqs SET embedding = %s WHERE id = %s",
                (embedding_string, faq_id))
    conn.commit()
print("All faqs embedding stored successfully")

Each Python list is converted to the text format pgvector understands ("[0.012,-0.013,…]"), then saved with UPDATE … WHERE id. faq_id = i + 1 works because the table was filled once, in the same order. This makes one API call per FAQ, so it takes a little while.

cur.execute("SELECT COUNT(*) FROM faqs WHERE embedding IS NOT NULL;")
print("FAQs with embeddings:", cur.fetchone()[0])

FAQs with embeddings: 50

Part 3: search by meaning with pgvector

Semantic search is one SQL query: embed the question with the same model as the FAQs, ORDER BY embedding <=> question_vector (nearest meaning first), and keep the best top_k. %s::vector converts the text "[...]" into a pgvector value.

def search_faq(question, top_k=3):
    query_embedding = create_embedding(question)
    embedding_string = "[" + ",".join(str(x) for x in query_embedding) + "]"
    cur.execute("""
        SELECT question, answer,
               embedding <=> %s::vector AS distance
        FROM faqs
        WHERE embedding IS NOT NULL
        ORDER BY embedding <=> %s::vector
        LIMIT %s;""",
        (embedding_string, embedding_string, top_k))
    return cur.fetchall()

results = search_faq("What is a database?", top_k=3)
for result in results:
    print("Question:", result[0])
    print("Answer:", result[1])
    print("Distance:", result[2])
    print("=" * 70)

Explore my real search results

Click each question to see the top 3 FAQs pgvector returned in my run.

What these show: exact questions match closely (0.234). Questions that share no words with an FAQ still find the right ones (0.297–0.326). A typo still lands on related FAQs, though less sharply (0.42+). The off-topic question's best match was 0.525, clearly worse than every relevant match. Distance is a useful signal for “nothing relevant found”; choose a cutoff by testing your own data.

Part 4: add Gemini (Retrieval-Augmented Generation)

Gemini never searches the database itself. We give it the facts, and we can always show which FAQs it used.

def get_context(question):
    results = search_faq(question, top_k=3)
    context = ""
    for result in results:
        context += f"""
        Question:{result[0]}
        Answer: {result[1]}"""
    return context

print(get_context(" What is pgvector"))

Question:What is pgvector?
Answer: pgvector is a PostgreSQL extension that allows PostgreSQL to store and search vector data.
Question:Why use pgvector with PostgreSQL?
Answer: pgvector allows PostgreSQL to store normal data together with vector data and perform similarity searches.
Question:How can PostgreSQL be used in an AI application?
Answer: PostgreSQL can store application data and, with pgvector, can also store and search vector embeddings for AI applications.

def generate_answer(question, context):
    prompt = f"""
    You are a helpful beginner-friendly AI assistant.
    Use the information below to answer the user question.
    Retrieved information:
    {context}
    User question:
    {question}

    Instructions:
    1. Answer in simple English
    2. Use the retrieved information
    3. Do not invent information
    4. Keep the answer suitable for a beginner
    5. Give a clear and short explanation
    """
    response = client.models.generate_content(model=TEXT_MODEL, contents=prompt)
    return response.text

def ask_ai(question):
    context = get_context(question)              # step 1: retrieval
    answer = generate_answer(question, context)  # step 2: generation
    return answer

Instruction 3, “Do not invent information”, is the guardrail that keeps Gemini inside the retrieved context. The other instructions control tone and length.

Real answers from my run

None of the last two questions match an FAQ word for word, and the last one even has a typo. Semantic search still found the right facts.

See every RAG step (great for demos)

def explain_rag_process(question):
    print("STEP 1 — USER QUESTION:", question)
    query_embedding = create_embedding(question)
    print("STEP 2 — EMBEDDING CREATED. Number of values:", len(query_embedding))
    print("STEP 3 — SEARCH POSTGRESQL")
    for i, result in enumerate(search_faq(question, top_k=3)):
        print(f"RESULT {i+1}:", result[0], "| distance:", result[2])
    context = get_context(question)
    print("STEP 4 — RAG CONTEXT:", context)
    print("STEP 5 — SEND CONTEXT TO GEMINI")
    answer = generate_answer(question, context)
    print("STEP 6 — FINAL AI ANSWER:", answer)

explain_rag_process("How can computers understand the meaning of text?")

Chat with your assistant

while True:
    question = input("\nAsk a question: ")
    if question.lower() == "exit":
        print("Goodbye!")
        break
    print("\nAI:")
    print(ask_ai(question))

conn.close()   # when you are finished

Try: What is pgvector? · How can I search by meaning? · Why do AI applications need databases? · and an off-topic question: the retrieved FAQs won't help, so does Gemini admit it, or drift?

Taking it beyond 50 FAQs

Installing PostgreSQL on Windows

Building pgvector on Windows

Database and Python

AI, pgvector and search

Quick quiz

Hands-on checklist

• Database ai_workshop created
• developers table: INSERT, SELECT, UPDATE, DELETE
• Python connected to PostgreSQL (SELECT and INSERT)
• Gemini API connected and text generation tested
• gemini-embedding-001 returns 768-number vectors
• pgvector enabled, VECTOR(768) column, embeddings stored
• Semantic search: query embedded, sorted by distance, relevant results
• AI FAQ Assistant answers questions with RAG

What each technology does

RAG in one line: find the right information first, then let AI write the answer from it.

Official references

• pgvector on GitHub: installation, operators and indexes
• pgvector 0.8.7 on PGXN
• PostgreSQL downloads and PostgreSQL 18 platform notes
• Google AI Studio: create your Gemini API key