pgvector for Absolute Beginners: Build Your First AI Search from Zero

pgvector for Beginners: Build Your First AI Search with PostgreSQL (No Experience Needed)

Never used PostgreSQL or Docker before? Learn pgvector from absolute zero with step-by-step instructions, screenshots, and zero jargon. Build your first AI search in just 15 minutes.

Table of Contents

1. What You’ll Build Today

2. What Even Is pgvector? 

3. What You Need Before Starting

4. Installing Docker 

5. Running PostgreSQL with pgvector 

6. Connecting to Your Database (The GUI Way)

7. Creating Your First Table (Like Excel, But Better)

8. Adding Your First Document (Without 384 Numbers!)

9. Searching By Meaning (The Magic)

10. What You Just Built

11. What’s Next

12. FAQs for Real Beginners

1. What You’ll Build Today ?

You’re going to build a document search engine that finds documents by meaning, not just keywords.

The Problem: You have 100 documents. You search “How do I restart my laptop?” The document says “Power cycle the device.” Normal search fails because the words don’t match. Vector search finds it because the meaning matches.

What you’ll build in 15 minutes:

• A database with pgvector

• A table to store documents

• Documents with their “meaning” as numbers

• A search that works like magic

No prior experience needed. I’ll hold your hand through EVERY step.

2. What Even Is pgvector? 

pgvector is a tool that lets PostgreSQL (a database) understand the meaning of text, not just the exact words.

Analogy: Imagine you’re in a library. You tell the librarian “I need books about leadership.” The librarian brings you books about managing teams, even if the word “leadership” never appears. That’s vector search. pgvector gives PostgreSQL this superpower.

3. What You Need Before Starting :

You need a computer (Windows, Mac, or Linux). That’s it.

You don’t need:

• PostgreSQL installed

• Docker knowledge

• Python knowledge (yet)

• Any experience with databases

I’ll walk you through EVERYTHING.

4. Installing Docker :

What Is Docker?

Think of Docker like a magic box. You put a command in, it creates a complete, ready-to-use computer inside your computer. No installation mess. No “it works on my machine” problems.

For Windows Users

1. Go to docker.com

2. Click “Download for Windows”

3. Run the installer (just click “Next” a bunch of times)

4. Restart your computer when it asks

5. Open “Docker Desktop” from your Start menu

6. Wait for the whale icon to say “Running” (it takes a minute)

For Mac Users

1. Go to docker.com

2. Click “Download for Mac”

3. Open the downloaded file and drag Docker to your Applications folder

4. Open Docker from Applications

5. Wait for it to say “Running”

For Linux Users

Open your terminal and run:

sudo apt update
sudo apt install docker.io docker-compose -y
sudo systemctl start docker
sudo systemctl enable docker

Check If Docker Works

Open a terminal (Command Prompt on Windows, Terminal on Mac/Linux) and type:

docker --version

You should see something like:

Docker version 24.0.7

If you see this, you’re ready.

5. Running PostgreSQL with pgvector :

Search for Docker Desktop and open it. Wait a few moments. Look at the bottom left corner of the Docker Desktop window—wait until the icon turns green and says “Engine running”.

Now the magic. Copy this EXACT command and paste it in your terminal (for Windows users):

docker run -d --name pgvector-demo -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=password -e POSTGRES_DB=vectordb -p 5432:5432 pgvector/pgvector:pg16

What This Command Does ?

Check If It Worked :

docker ps

You should see:

CONTAINER ID   IMAGE                    STATUS
abc123...      pgvector/pgvector:pg16   Up 2 minutes

Congratulations! You have a PostgreSQL database with pgvector running. You didn’t install anything. You didn’t configure anything. It just works.

6. Connecting to Your Database (The GUI Way) 

We need a way to talk to the database. The terminal is scary. Let’s use a visual tool.

Install DBeaver (it’s Free)

1. Go to dbeaver.io

2. Download the Community Edition for your OS

3. Install it (just click “Next” a bunch of times)

4. Open DBeaver

Connect to Your Database

1. In DBeaver, click the New Database Connection icon (a plug with a plus sign)

2. Select PostgreSQL from the list

3. Fill in:

   • Host: localhost

   • Port: 5432

   • Database: vectordb

   • Username: postgres

   • Password: password

4. Click Test Connection

5. You should see “Connected!”

6. Click Finish

What You See Now

You should see:

vectordb
  ├── Schemas
  │   └── public
  │       ├── Tables
  │       ├── Views
  │       └── ...

This is your database. Empty right now. Let’s fill it.

7. Creating Your First Table (Like Excel, But Better) 

What Is a Table?

Think of a table like an Excel spreadsheet. It has columns (headers) and rows (data).

Open the SQL Editor

1. Right-click on vectordb in DBeaver

2. Select SQL Editor → New SQL Script

3. A blank window opens. This is where you type commands.

Enable pgvector :

Type this in the SQL Editor:

CREATE EXTENSION IF NOT EXISTS vector;

Click the Execute button (the play button ▶️).

You should see:

CREATE EXTENSION

This enables pgvector in your database.

Create Your First Table

Now type this:

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    content TEXT,
    embedding vector(3)
);

Execute it. You should see:

CREATE TABLE

What This Does ?

Why vector(3)? It’s just for learning. Real embeddings have hundreds of numbers. But 3 is easier to understand.

See Your Table :

In DBeaver, right-click on Tables and click Refresh. You’ll see documents.

8. Adding Your First Document (Without 384 Numbers!) 

The Problem With Big Vectors

Real embeddings have 384 or 1536 numbers. Nobody wants to type that. So let’s use small, made-up numbers for learning.

Insert 3 Documents

Type this in the SQL Editor:

INSERT INTO documents (content, embedding) VALUES
('PostgreSQL is a powerful database system.', '[1.0, 0.0, 0.0]'),
('Vector search finds meaning, not just words.', '[0.0, 1.0, 0.0]'),
('Docker makes it easy to run databases.', '[0.0, 0.0, 1.0]');

Execute it. You should see:

INSERT 0 3

What Just Happened?

Each document got a vector of 3 numbers:

• Document 1: [1.0, 0.0, 0.0] → “database” concept

• Document 2: [0.0, 1.0, 0.0] → “search” concept

• Document 3: [0.0, 0.0, 1.0] → “Docker” concept

The key idea: Similar concepts have similar numbers. “PostgreSQL” and “database” are similar. “Search” and “find” are similar.

See Your Data :

SELECT * FROM documents;

You’ll see:

 id |                     content                      |  embedding
----+--------------------------------------------------+-------------
  1 | PostgreSQL is a powerful database system.        | [1,0,0]
  2 | Vector search finds meaning, not just words.    | [0,1,0]
  3 | Docker makes it easy to run databases.          | [0,0,1]

9. Searching By Meaning (The Magic) 

Now the fun part. Let’s search for documents by meaning.

The Query

SELECT 
    content,
    1 - (embedding <=> '[0.9, 0.1, 0.0]') AS similarity
FROM documents
ORDER BY embedding <=> '[0.9, 0.1, 0.0]'
LIMIT 1;

What Does <-> Mean?

<=> is the “cosine distance” operator. It measures how different two vectors are:

• 0 = identical

• 2 = completely opposite

1 - distance gives you similarity:

• 1 = identical

• 0 = completely different

The Result

You’ll see:

                     content                      | similarity
--------------------------------------------------+------------
 PostgreSQL is a powerful database system.        | 0.9

Why? Because [0.9, 0.1, 0.0] is closest to [1.0, 0.0, 0.0] (the database document).

Try a Different Query

SELECT 
    content,
    1 - (embedding <=> '[0.1, 0.9, 0.0]') AS similarity
FROM documents
ORDER BY embedding <=> '[0.1, 0.9, 0.0]'
LIMIT 1;

Result:

Vector search finds meaning, not just words.    | 0.9

The magic: You searched for “meaning” and found the document about “search.” The words don’t match, but the meaning does.

10. What You Just Built :

You built a working AI search engine.

You didn’t install PostgreSQL. You didn’t configure anything. You didn’t write hundreds of lines of code.

11. What’s Next ?

Real-World Next Steps

1. Use real embeddings (384 or 1536 numbers) using OpenAI’s API

2. Add an index to make searches fast (HNSW)

3. Add metadata filters (search only documents by a specific user)

4. Build an AI agent that uses this search as a tool

The 12 Things You’ll Actually Use

Once you understand the basics, here’s what matters:

1. Dimension must match your embedding model (OpenAI 1536, etc.)

2. HNSW index for fast searches (CREATE INDEX … USING hnsw)

3. halfvec to save memory (half the size)

4. Pre-filter vs post-filter for metadata (WHERE clauses)

5. Hybrid search (keywords + vectors combined)

6. SQLRecordManager to prevent duplicate data

7. maintenance_work_mem for index building

8. pg_vectorscale for even faster search

9. Partitioning for millions of vectors

10. Reindex during low-traffic windows

11. Monitor memory usage (HNSW is RAM-hungry)

12. Backup strategy (pg_dump handles vectors)

12. FAQs for Real Beginners :

What is Docker and why do I need it?

Docker creates a mini-computer inside your computer with everything already installed. You don’t need to install PostgreSQL, configure it, or worry about versions. It just works.

What is PostgreSQL?

PostgreSQL is a database. Think of it like a giant Excel spreadsheet that millions of people can use at the same time.

What is a vector?

A vector is a list of numbers that represents the “meaning” of text. Similar texts have similar numbers.

Why vector(3) instead of vector(384)?

For learning. 3 numbers are easy to understand. Real embeddings have hundreds of numbers.

Do I need to remember these commands?

No. Copy-paste is fine. You’ll remember the important ones with practice.

Where do vectors come from?

AI models (like OpenAI’s text-embedding-3-small) turn text into vectors. You don’t create them manually.

Can I use this in a real project?

Yes. Replace the fake vectors with real embeddings from an AI model.

The Bottom Line

You just built an AI search engine in 15 minutes. From absolute zero.

• You learned what pgvector does (finds meaning, not words)

• You set up PostgreSQL with pgvector (without installing anything)

• You created a table and added documents

• You searched by meaning and it worked

The one-line takeaway: pgvector turns your PostgreSQL database into an AI search engine that finds documents by meaning, not just keywords.