Tag: pgvector

Integrate AI with Rails: Day 10 – RAG with PostgreSQL + pgvector – part 1

We’ll move quickly, but this time keep each milestone runnable. Since you already have PostgreSQL and a working Rails 8.1 app, pgvector is a natural fit: it stores vectors alongside normal PostgreSQL data and supports cosine similarity plus exact and approximate nearest-neighbor search. (GitHub)

Step 13A – Install and enable pgvector

1. Check your PostgreSQL version

Run:

psql --version

Then check whether the extension is already installed:

bin/rails dbconsole

Inside PostgreSQL:

SELECT extname, extversion
FROM pg_extension
WHERE extname = 'vector';

If you get a row

For example:

 vector | 0.8.6

you’re ready.

If you get no rows

You need to install the extension on your PostgreSQL installation.

Since you’re on macOS, if PostgreSQL was installed via Homebrew:

brew install pgvector

The pgvector project currently documents Homebrew installation for PostgreSQL 17/18 formulas. (GitHub)

Then restart PostgreSQL if required by your installation:

brew services restart postgresql@14

Use your actual PostgreSQL version if different.

Step 13B – Enable pgvector in Rails

Once PostgreSQL has the extension available, exit psql:

\q

Generate the migration:

bin/rails generate migration EnablePgvector

Open the migration and use:

class EnablePgvector < ActiveRecord::Migration[8.1]
  def change
    enable_extension "vector"
  end
end

Then:

bin/rails db:migrate

Error: PG::UndefinedFile: ERROR: could not open extension control file "/opt/homebrew/share/postgresql@14/extension/vector.control": No such file or director

This error occurs because the pgvector extension is not installed or cannot be found in the directory of your specific Homebrew-managed PostgreSQL 14 installation.

Do:

# 1. Clone the pgvector repository
cd /tmp
git clone --branch v0.8.6 https://github.com/pgvector/pgvector.git
cd pgvector

# 2. Explicitly point to your PostgreSQL 14 pg_config binary
export PG_CONFIG=/opt/homebrew/opt/postgresql@14/bin/pg_config

# 3. Build and install the extension
make
make install # may need sudo

# Verify the Installation: after the installation completes successfully, check if the vector.control file is present in the target directory
ls /opt/homebrew/share/postgresql@14/extension/vector.control

Verify:

➜  ai_assistant git:(main) rails dbconsole
psql (14.17 (Homebrew))
Type "help" for help.

ai_assistant_development=# SELECT extname, extversion
FROM pg_extension
WHERE extname = 'vector';
 extname | extversion
---------+------------
(0 rows)

ai_assistant_development=#
\q
➜  ai_assistant git:(main) ✗ brew services restart postgresql@14
Stopping `postgresql@14`... (might take a while)
==> Successfully stopped `postgresql@14` (label: sh.brew.postgresql@14)
==> Successfully started `postgresql@14` (label: sh.brew.postgresql@14)
➜  ai_assistant git:(main) ✗ rails dbconsole
psql (14.17 (Homebrew))
Type "help" for help.

ai_assistant_development=# SELECT extname, extversion
FROM pg_extension
WHERE extname = 'vector';
 extname | extversion
---------+------------
 vector  | 0.8.6
(1 row)

You should now see vector.

Step 13C – Understand our RAG data model

We’re going to introduce two models:

Document
   │
   └── has_many :document_chunks

A document could be:

Ruby Guide

and chunks might be:

Chunk 1 → Ruby blocks
Chunk 2 → Classes
Chunk 3 → Modules
Chunk 4 → Metaprogramming

Each chunk gets its own embedding:

Chunk text
   ↓
Embedding API
   ↓
[0.021, -0.318, ...]
   ↓
PostgreSQL vector column

We’ll use 1536 dimensions initially, because we’ll use an embedding model that produces 1536-dimensional vectors. The actual dimension must match the embedding model you choose; pgvector requires the declared vector dimension to match stored vectors.

Step 13D – Create Document

Run:

bin/rails g model Document title:string source:string

Then:

bin/rails db:migrate

Open:

app/models/document.rb

Change it to:

class Document < ApplicationRecord
  has_many :document_chunks, dependent: :destroy

  validates :title, presence: true
end

Step 13E – Create DocumentChunk

Generate it:

bin/rails g model DocumentChunk \
  document:references \
  content:text \
  chunk_index:integer

Then don’t migrate yet.

We need to add the vector column manually because Rails’ generator doesn’t know which embedding dimension we want.

Open the generated migration and make it:

class CreateDocumentChunks < ActiveRecord::Migration[8.1]
  def change
    create_table :document_chunks do |t|
      t.references :document, null: false, foreign_key: true
      t.text :content, null: false
      t.integer :chunk_index, null: false
      t.vector :embedding, limit: 1536

      t.timestamps
    end

    add_index(
      :document_chunks,
      [:document_id, :chunk_index],
      unique: true
    )
  end
end

Depending on the pgvector Rails integration available in your environment, t.vector may not be recognized. If that happens, we’ll use:

add_column :document_chunks, :embedding, :vector, limit: 1536

instead.

The underlying PostgreSQL representation is:

embedding vector(1536)

which is the pgvector-native type.

Then:

bin/rails db:migrate

As expected gets the error:

-- create_table(:document_chunks)
bin/rails aborted!
StandardError: An error has occurred, this and all later migrations canceled: (StandardError)

undefined method 'vector' for an instance of ActiveRecord::ConnectionAdapters::PostgreSQL::TableDefinition

Do:

rails g migration addEmbeddingToDocumentChunks

# add
add_column :document_chunks, :embedding, :vector, limit: 1536

# do
rails db:migrate -t

Step 13F – Model association

Open:

app/models/document_chunk.rb

Use:

class DocumentChunk < ApplicationRecord
  belongs_to :document

  validates :content, presence: true
  validates :chunk_index, presence: true
end

Step 13G – Verify the database

Run:

bin/rails dbconsole

Then:

\d document_chunks

You should have:

ai_assistant_development=# \d document_chunks
                                          Table "public.document_chunks"
   Column    |              Type              | Collation | Nullable |                   Default
-------------+--------------------------------+-----------+----------+---------------------------------------------
 id          | bigint                         |           | not null | nextval('document_chunks_id_seq'::regclass)
 document_id | bigint                         |           | not null |
 content     | text                           |           | not null |
 chunk_index | integer                        |           | not null |
 created_at  | timestamp(6) without time zone |           | not null |
 updated_at  | timestamp(6) without time zone |           | not null |
 embedding   | vector                         |           |          |
Indexes:
    "document_chunks_pkey" PRIMARY KEY, btree (id)
    "index_document_chunks_on_document_id" btree (document_id)
    "index_document_chunks_on_document_id_and_chunk_index" UNIQUE, btree (document_id, chunk_index)
Foreign-key constraints:
    "fk_rails_99b41ada32" FOREIGN KEY (document_id) REFERENCES documents(id)

And:

SELECT vector_dims(
  '[1,2,3]'::vector
);

should return:

3

That proves the extension itself is working.

Exit:

\q

Step 13H – Create your first document manually

Before worrying about PDFs, parsers, Sidekiq, etc., let’s prove the RAG data model.

Run:

bin/rails c

Then:

document = Document.create!(
  title: "Ruby Guide",
  source: "manual"
)

Create chunks:

document.document_chunks.create!(
  content: "Ruby blocks are chunks of code passed to methods.",
  chunk_index: 0
)

document.document_chunks.create!(
  content: "Ruby modules allow code to be organized and reused.",
  chunk_index: 1
)

document.document_chunks.create!(
  content: "Ruby classes define objects and their behavior.",
  chunk_index: 2
)

Check:

document.document_chunks.count

Expected:

3

Step 13I – What we’ve built

Our database is now:

documents
----------------
id
title
source

        │
        │ 1 → many
        ▼

document_chunks
----------------
id
document_id
content
chunk_index
embedding

The crucial field is:

embedding

which will eventually contain:

[0.012, -0.883, 0.217, ...]

Int. Checkpoint

You should now be able to explain:

Why don’t we put the embedding on documents?

Because a document is usually too large to embed as one semantic unit.

We split it into chunks and embed each chunk independently:

Document
  ↓
Chunks
  ↓
Embeddings

That lets retrieval find the relevant section instead of returning the entire document.

One important design choice

We’re not adding an HNSW index yet.

An HNSW (Hierarchical Navigable Small World) index is a high-speed graph-based algorithm used to find similar items in large collections of high-dimensional data. It is widely used in vector databases for AI tasks like semantic search and recommendation systems.

pgvector supports exact nearest-neighbor search by default, and approximate indexes such as HNSW and IVFFlat become useful as the dataset grows. HNSW generally offers a strong speed/recall tradeoff but costs more memory and has a slower build.

IVFFlat (Inverted File with Flat compression) is a type of database index used to speed up similarity searches for high-dimensional vectors

For our small learning dataset:

exact search first

Once we have real embeddings and enough data:

HNSW index

We’ll deliberately compare both, which makes a good senior-level discussion.

We’ll create an Ai::EmbeddingService, generate a real embedding through our current provider setup, store it in PostgreSQL, and then perform our first semantic similarity search. That will be the point where we can honestly say we’ve built RAG mechanics rather than just knowing the definition.

to be continued ..