Rails pgvector: Semantic Search and RAG with PostgreSQL for LLM Applications

A founder called me in June with a familiar problem: their support team was drowning. They had six years of resolved Zendesk tickets, a Notion knowledge base, and product docs in three different formats, and their new hires spent their first two weeks just learning where things lived. “Can we point an LLM at all of it?” they asked. I opened their Rails console, checked their Postgres version — 16.2, already on RDS with pgvector available — and told them we would ship a working answer bot in three weeks. We shipped it in four. The retrieval layer was Rails pgvector, built directly on the Postgres they already ran.

That project is the reason Rails pgvector is the retrieval stack I recommend by default in 2026. After nineteen years of Rails I have watched a lot of “add a specialized database” pitches age poorly, and the pattern I keep seeing in production RAG systems is that a well-tuned Postgres beats a separate Pinecone or Weaviate cluster for the vast majority of teams. This post is the exact Rails pgvector setup I ship: schema, indexing strategy, embedding pipeline, and the hybrid retrieval that makes results actually useful.

Why Rails pgvector Beats a Dedicated Vector Database

Rails pgvector is the pgvector PostgreSQL extension exposed to Rails through the neighbor gem (or plain ActiveRecord if you like typing). It gives you a vector column type, cosine/L2/inner-product distance operators, and HNSW / IVFFlat approximate nearest neighbor indexes — all inside the same Postgres that already stores your users, tenants, and access controls.

The math on switching to a separate vector database looks bad on almost every axis I have measured:

• You already ran Postgres. Adding pgvector is a CREATE EXTENSION on managed services (RDS, Aurora, Supabase, Neon, GCP Cloud SQL, DigitalOcean Managed Postgres all support it). Adding Pinecone is a new vendor contract, a new SDK, a new outage domain, and a new billing line.
• Joins are free. Your embeddings live next to the row they describe, so a search that filters “documents this user can see” is a single query with a WHERE tenant_id = ? and an ORDER BY the vector distance. In a separate vector DB you fetch top-K, then round-trip to Postgres to filter, then re-rank whatever survived. That extra hop is where recall goes to die.
• Transactions are consistent. When you update a document, you can update the embedding in the same transaction. Systems that push embeddings to a separate store asynchronously ship stale results on every write.
• Backups, replicas, and observability are the ones you already have. Your Postgres backup covers your vectors. Your read replica serves vector queries. Your Datadog Postgres dashboard shows the slow ones. No new runbook.

The failure mode where dedicated vector databases genuinely win is when you cross ~50M vectors with high write throughput and need horizontally sharded ANN — think product-scale semantic search on Etsy or Airbnb. Below that threshold, Rails pgvector is faster to ship, cheaper to run, and easier to reason about. My biggest production Rails pgvector deployment is at 8M vectors across 2,400 tenants and handles p95 search latency of 40 ms on a single db.r6g.2xlarge.

Installing pgvector and the neighbor Gem

The extension needs to exist on your Postgres server. On RDS it is a checkbox; on self-hosted it is an apt install postgresql-16-pgvector or the official install docs. Once the binary is present, enable it in Rails through a migration:

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

On the Rails side I use the neighbor gem, which teaches ActiveRecord about the vector type and gives you nice scopes:

# Gemfile
gem "neighbor", "~> 0.5"
gem "ruby-openai", "~> 7.0"  # or anthropic, or bedrock — whichever embedding model you use

Neighbor handles the awkward parts: serializing Ruby arrays into pgvector’s [0.1,0.2,...] string format, generating the right SQL for cosine vs L2 vs inner-product distance, and building HNSW / IVFFlat indexes with the right operator class. Without it you end up hand-rolling Arel.sql for every query, which is exactly the kind of code future-you will hate.

Schema Design for Rails pgvector

The schema that has held up across every Rails pgvector project I have shipped puts the embedding on a separate table, not on the source row. It sounds like premature normalization; it is not. The reason is that embedding models change — you will re-embed your corpus at least once — and you want the freedom to add a content_embeddings_v2 table without touching your source rows or your application code.

class CreateContentEmbeddings < ActiveRecord::Migration[8.0]
  def change
    create_table :content_embeddings do |t|
      t.references :tenant, null: false, foreign_key: true, index: true
      t.references :embeddable, polymorphic: true, null: false, index: true
      t.string :model, null: false                    # "text-embedding-3-small"
      t.integer :chunk_index, null: false, default: 0
      t.text :chunk_text, null: false
      t.vector :embedding, limit: 1536, null: false
      t.jsonb :metadata, null: false, default: {}
      t.timestamps
    end

    add_index :content_embeddings,
      [:embeddable_type, :embeddable_id, :chunk_index],
      unique: true,
      name: "idx_embeddings_source_chunk"
  end
end

A few things worth calling out:

• limit: 1536 matches OpenAI’s text-embedding-3-small. If you use text-embedding-3-large change it to 3072, or if you use Voyage’s voyage-3 change it to 1024. Getting this wrong throws a PG::DataException on insert with a bewildering error, and I have watched more than one team spend an afternoon on it.
• chunk_index lets a single source row (a support ticket, a wiki page) produce multiple embeddings — one per chunk of ~500 tokens. Long documents need chunking; there is no way around this because context windows and semantic coherence both cap out.
• tenant_id on every row is the single most important field for a multi-tenant SaaS. It goes in every query, and it should be the leading column of your HNSW index — or you should use partial indexes per tenant, if your tenant count is small enough. I wrote about the tenant isolation model I use as the base layer under this table.

Building the HNSW Index

Rails pgvector supports two index types: HNSW (hierarchical navigable small world) and IVFFlat. In 2026 I recommend HNSW for almost every workload — it has better recall at the same speed, builds faster than it used to, and does not require a training set. Create it with:

class AddHnswIndexToContentEmbeddings < ActiveRecord::Migration[8.0]
  disable_ddl_transaction!

  def change
    add_index :content_embeddings, :embedding,
      using: :hnsw,
      opclass: :vector_cosine_ops,
      with: { m: 16, ef_construction: 64 },
      algorithm: :concurrently,
      name: "idx_content_embeddings_hnsw_cosine"
  end
end

The two parameters that matter:

• m: 16 is the number of connections per graph node. Higher recall, higher memory. 16 is the pgvector default and the right starting point.
• ef_construction: 64 controls index build quality. Higher is slower to build but gives better recall. Bump to 128 if your dataset is small enough that build time is not a concern.

At query time, ef_search controls the speed/recall trade-off. Set it per-connection right before you run the search:

ActiveRecord::Base.connection.execute("SET LOCAL hnsw.ef_search = 100")

Higher ef_search gives more accurate results but slower queries. 40 is the default; 100 is where I usually sit for user-facing search. Above 200 you are paying latency for negligible recall gains.

One trap: HNSW indexes are large in memory and slow to build on a hot production table. Do the initial build in a maintenance window, or on a replica that you then promote. Building an HNSW index concurrently on an 8M-row table took me 47 minutes on the client project above; the same build on a db.r6g.4xlarge was 22 minutes. Budget for it.

The Embedding Pipeline

Embeddings need to be generated somewhere. I put this in a background job triggered by an ActiveRecord callback, with idempotency and rate-limiting built in:

class EmbedContentJob < ApplicationJob
  queue_as :embeddings

  def perform(embeddable_gid, model: "text-embedding-3-small")
    embeddable = GlobalID::Locator.locate(embeddable_gid)
    return unless embeddable

    text = embeddable.embeddable_text
    chunks = TextChunker.new(text, max_tokens: 500, overlap: 50).call

    ContentEmbedding.transaction do
      embeddable.content_embeddings.where(model: model).delete_all

      chunks.each_with_index do |chunk, index|
        embedding = OpenAI::Client.new.embeddings(
          parameters: { model: model, input: chunk }
        ).dig("data", 0, "embedding")

        embeddable.content_embeddings.create!(
          tenant_id: embeddable.tenant_id,
          model: model,
          chunk_index: index,
          chunk_text: chunk,
          embedding: embedding,
          metadata: { char_count: chunk.length }
        )
      end
    end
  end
end

class SupportTicket < ApplicationRecord
  has_many :content_embeddings, as: :embeddable, dependent: :destroy

  after_commit :enqueue_embedding_job, on: [:create, :update], if: :saved_change_to_body?

  def embeddable_text
    "Subject: #{subject}\n\n#{body}"
  end

  private

  def enqueue_embedding_job
    EmbedContentJob.perform_later(to_gid.to_s)
  end
end

Three subtleties that took me too long to figure out:

• Delete existing embeddings inside the transaction. If a document is updated, its old chunks must go before the new ones arrive, or you accumulate stale results. Doing it outside the transaction opens a window where the document has zero embeddings and searches silently miss it.
• Batch your embedding API calls when you can. OpenAI’s embedding endpoint accepts up to 2,048 inputs per request. If you are backfilling 100k documents, a naive one-at-a-time loop takes a day; batches of 100 finish in an hour. The LLM cost-tracking discipline I use here applies to embeddings too — they are cheap per token but easy to run away with at scale.
• Guard your background queue. Embedding jobs are cheap CPU but hit an external API with rate limits. Give them their own queue, cap concurrency, and let the rest of your background work stay unaffected when OpenAI hiccups.

Querying with Rails pgvector

The search itself is where most tutorials stop and where production Rails pgvector systems just get started. The naive query looks like this:

class ContentEmbedding < ApplicationRecord
  belongs_to :tenant
  belongs_to :embeddable, polymorphic: true
  has_neighbors :embedding, dimensions: 1536, normalize: true
end

query_embedding = OpenAI::Client.new.embeddings(
  parameters: { model: "text-embedding-3-small", input: user_question }
).dig("data", 0, "embedding")

results = ContentEmbedding
  .where(tenant_id: Current.tenant.id)
  .nearest_neighbors(:embedding, query_embedding, distance: "cosine")
  .limit(10)

That works. What that does not do is handle the two failure modes of pure vector search: it misses documents that use different vocabulary from the question (“password reset” vs “cannot log in”), and it happily returns semantically-close but factually-wrong chunks. The fix is hybrid search — combine vector similarity with Postgres full-text search and re-rank.

Here is the pattern I ship:

class HybridSearch
  def initialize(tenant:, query:, limit: 10)
    @tenant = tenant
    @query = query
    @limit = limit
  end

  def call
    vector_results = vector_search(k: 40)
    text_results = text_search(k: 40)
    reciprocal_rank_fusion(vector_results, text_results).first(@limit)
  end

  private

  def vector_search(k:)
    embedding = embed(@query)
    ContentEmbedding
      .where(tenant_id: @tenant.id)
      .nearest_neighbors(:embedding, embedding, distance: "cosine")
      .limit(k)
      .pluck(:id)
  end

  def text_search(k:)
    ContentEmbedding
      .where(tenant_id: @tenant.id)
      .where("chunk_text_tsv @@ websearch_to_tsquery('english', ?)", @query)
      .order(Arel.sql("ts_rank_cd(chunk_text_tsv, websearch_to_tsquery('english', #{ActiveRecord::Base.connection.quote(@query)})) DESC"))
      .limit(k)
      .pluck(:id)
  end

  def reciprocal_rank_fusion(*rankings, k: 60)
    scores = Hash.new(0.0)
    rankings.each do |ranking|
      ranking.each_with_index do |id, index|
        scores[id] += 1.0 / (k + index + 1)
      end
    end
    scores.sort_by { |_, score| -score }.map(&:first)
  end

  def embed(text)
    OpenAI::Client.new.embeddings(
      parameters: { model: "text-embedding-3-small", input: text }
    ).dig("data", 0, "embedding")
  end
end

Reciprocal Rank Fusion (RRF) is the fusion algorithm that just works — no tuning parameters that matter, no calibration required. It beats every “weighted combination of scores” approach I have benchmarked, because raw vector distances and text-search scores are on completely different scales. RRF only cares about rank position, which is scale-free.

The chunk_text_tsv is a generated tsvector column indexed with GIN. Add it in a migration:

class AddTsvectorToContentEmbeddings < ActiveRecord::Migration[8.0]
  def up
    execute <<~SQL
      ALTER TABLE content_embeddings
      ADD COLUMN chunk_text_tsv tsvector
      GENERATED ALWAYS AS (to_tsvector('english', chunk_text)) STORED;
    SQL
    add_index :content_embeddings, :chunk_text_tsv, using: :gin
  end
end

Feeding Results to an LLM: The RAG Loop

The retrieval half of a RAG system feeds a generation call. Here is the shape I ship — token budget aware, tenant scoped, and instrumented:

class AnswerQuestion
  MAX_CONTEXT_TOKENS = 6000

  def initialize(tenant:, question:)
    @tenant = tenant
    @question = question
  end

  def call
    embedding_ids = HybridSearch.new(tenant: @tenant, query: @question, limit: 20).call
    chunks = ContentEmbedding.where(id: embedding_ids).index_by(&:id)
    ranked = embedding_ids.map { |id| chunks[id] }.compact

    context, used_ids = pack_context(ranked)

    response = OpenAI::Client.new.chat(
      parameters: {
        model: "gpt-4o-mini",
        messages: [
          { role: "system", content: SYSTEM_PROMPT },
          { role: "user", content: "Context:\n#{context}\n\nQuestion: #{@question}" }
        ],
        temperature: 0.2
      }
    )

    { answer: response.dig("choices", 0, "message", "content"), sources: used_ids }
  end

  private

  def pack_context(chunks)
    budget = MAX_CONTEXT_TOKENS
    used = []
    parts = []

    chunks.each do |chunk|
      tokens = TokenCounter.count(chunk.chunk_text)
      break if tokens > budget
      parts << "[#{chunk.embeddable_type}##{chunk.embeddable_id}]\n#{chunk.chunk_text}"
      used << chunk.id
      budget -= tokens
    end

    [parts.join("\n\n---\n\n"), used]
  end
end

Two production disciplines from that code:

• Return source IDs to the caller. Every answer your app renders should show the user which documents it cited. This is what makes an LLM answer trustworthy — and it is what lets your support team spot when the retrieval is wrong.
• Pack the context greedily inside a token budget. Sending the LLM everything you retrieved wastes money and adds latency. Sending it truncated garbage produces hallucinations. Greedy pack-until-full is boring, cheap, and correct.

What to Watch in Production

Ship Rails pgvector with instrumentation on day one, because retrieval quality regresses silently. The signals I put on every dashboard:

• p50 / p95 vector query latency. A spike usually means someone added a WHERE filter that made the HNSW index unusable, or shared_buffers is under-sized for the index. Aim for p95 under 50 ms.
• Recall against a labelled gold set. Pick 50 real questions with known-correct answer chunks, and re-run them on every embedding-model change. This is the only way to catch a model swap that quietly makes results worse.
• Cache hit rate on the embedding API. Repeat queries hit the same cache line; if hit rate drops, someone is calling embed() in a hot path.
• Sources-clicked rate. If users never click through to the cited sources, either the answer is fully self-contained (good) or the sources are irrelevant (bad). Segment and investigate.

I pipe all four into the LLM observability stack I standardized on this year, which correlates retrieval quality with downstream answer quality automatically.

The Payoff

Four weeks after that first founder call, the support answer bot was live in the client’s Zendesk sidebar. Six weeks after that, they measured a 43% deflection rate on tier-1 tickets and a 60% reduction in new-hire ramp time. The whole retrieval layer runs on the Postgres they were already paying for. No new vendor. No new outage domain. That is the return Rails pgvector delivers when you take it seriously as a production system rather than a proof of concept.

FAQ

Should I use pgvector or a dedicated vector database like Pinecone or Weaviate?

Use Rails pgvector unless you are past ~50M vectors and need horizontally sharded ANN with sub-10ms latency at scale. Below that threshold pgvector is faster to ship, cheaper to run, and gives you free joins with your tenant and access-control data. The teams I have seen regret picking Pinecone almost universally cite the round-trip cost of “fetch top-K from Pinecone, then filter in Postgres” as the reason.

What embedding model should I use with Rails pgvector?

For English text, text-embedding-3-small from OpenAI is the default I recommend — 1536 dimensions, cheap, good quality, and well-understood. For multilingual or specialized domains, evaluate Voyage’s voyage-3 and Cohere’s embed-multilingual-v3. Whichever you pick, treat the choice as reversible: put the model name on the row, keep the source text so you can re-embed, and be ready to run a bake-off against a gold set before switching.

How do I re-embed a corpus without downtime?

Add a second content_embeddings_v2 table with the new model and dimensions, backfill it in the background while the app keeps serving from content_embeddings, and cut over reads with a feature flag once the backfill is complete and quality metrics look good. Delete the old table only after you are sure. This is exactly the same shape as the zero-downtime migrations pattern applied to embeddings.

Does Rails pgvector work on RDS, Aurora, Supabase, and Cloud SQL?

Yes. RDS PostgreSQL 15+ supports pgvector as a managed extension (enable in the parameter group and run CREATE EXTENSION vector). Aurora PostgreSQL 15.5+ supports it. Supabase ships with pgvector enabled by default. GCP Cloud SQL supports it on PostgreSQL 15+. DigitalOcean Managed Postgres supports it. The only place I have hit friction is very old Heroku Postgres, where the extension is not available on Standard-tier plans — Standard-2 and up are fine.

Building semantic search or a RAG system on Rails and want to make sure the retrieval, the pipeline, and the observability all hold up in production? TTB Software helps teams ship LLM-powered features on top of Postgres they already run. Nineteen years of Rails, three years of production LLM applications, and pgvector is the retrieval stack I trust.