How to Use pgvector on AWS RDS for PostgreSQL

pgvector turns Postgres into a vector store: a vector column type, distance operators and approximate nearest neighbor indexes. On AWS, the natural home for it is Amazon RDS for PostgreSQL, which ships the extension on current engine versions. Your embeddings sit next to the rows they describe, and backups, failover and IAM work the way they already do for the rest of your data.

This guide sets up pgvector on RDS for a semantic search API in TypeScript: the migration, the index, the query, and the RDS settings that matter once the table grows. If you are still deciding between pgvector and a dedicated vector database, start with pgvector vs Pinecone.

Quick start with Encore

Encore is infrastructure from code for TypeScript and Go, with a platform that provisions and deploys into your own AWS or GCP account. You declare the database in code, and Encore runs it locally with pgvector included and provisions it as RDS in your own AWS account when you deploy. The first migration enables the extension and creates the table:

-- search/migrations/1_create_documents.up.sql
CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE documents (
    id BIGSERIAL PRIMARY KEY,
    content TEXT NOT NULL,
    embedding vector(1536) NOT NULL
);

-- HNSW index for cosine distance, the metric OpenAI embeddings are built for.
CREATE INDEX documents_embedding_idx ON documents
    USING hnsw (embedding vector_cosine_ops);

The service declares the database, stores the OpenAI key as a secret and exposes two endpoints, one to add a document and one to search:

// search/search.ts
import { api } from "encore.dev/api";
import { secret } from "encore.dev/config";
import { SQLDatabase } from "encore.dev/storage/sqldb";

// RDS for PostgreSQL on AWS, a local Postgres with pgvector under encore run.
const db = new SQLDatabase("search", { migrations: "./migrations" });

const openAIKey = secret("OpenAIKey");

async function embed(text: string): Promise<number[]> {
  const res = await fetch("https://api.openai.com/v1/embeddings", {
    method: "POST",
    headers: {
      Authorization: `Bearer ${openAIKey()}`,
      "Content-Type": "application/json",
    },
    body: JSON.stringify({ model: "text-embedding-3-small", input: text }),
  });
  if (!res.ok) throw new Error(`embedding failed: ${res.status}`);
  const body = (await res.json()) as { data: { embedding: number[] }[] };
  return body.data[0].embedding;
}

// pgvector accepts the text form "[0.1,0.2,...]" and casts it to vector.
const toVector = (v: number[]) => `[${v.join(",")}]`;

export const add = api(
  { expose: true, method: "POST", path: "/documents" },
  async ({ content }: { content: string }): Promise<{ id: number }> => {
    const embedding = await embed(content);
    const row = await db.queryRow<{ id: number }>`
      INSERT INTO documents (content, embedding)
      VALUES (${content}, ${toVector(embedding)}::vector)
      RETURNING id`;
    return { id: row!.id };
  },
);

interface Match {
  id: number;
  content: string;
  distance: number;
}

export const search = api(
  { expose: true, method: "GET", path: "/search" },
  async ({ q, limit }: { q: string; limit?: number }): Promise<{ matches: Match[] }> => {
    const embedding = toVector(await embed(q));
    const matches: Match[] = [];
    // <=> is cosine distance, so ORDER BY uses the HNSW index.
    for await (const row of db.query<Match>`
      SELECT id, content, embedding <=> ${embedding}::vector AS distance
      FROM documents
      ORDER BY embedding <=> ${embedding}::vector
      LIMIT ${limit ?? 5}`) {
      matches.push(row);
    }
    return { matches };
  },
);

encore run starts a local Postgres from Encore's image, which includes pgvector among its extensions, and applies the migration. Set the key with encore secret set --type local OpenAIKey and the endpoints work against real embeddings. On deploy, Encore provisions RDS in your AWS account, runs the same migration before the new code goes live, and wires the connection into the service, so there is no connection string to manage. The AWS infrastructure docs describe the network and IAM setup it creates.

The same declaration is what makes this workable for coding agents: an agent can add a vector column in a migration, run it locally and open a pull request. Cloud access sits with Encore's IAM role, not the agent.

The TypeScript database docs cover migrations and queries in more depth.

Manual setup on RDS

If you manage RDS yourself, the extension is the easy part. Create an RDS for PostgreSQL instance on a current engine version, connect as the master user (which has the rds_superuser role needed to create extensions) and run the same migration SQL. Then confirm the version, since features depend on it:

SELECT extversion FROM pg_extension WHERE extname = 'vector';

After an RDS minor version upgrade, a newer pgvector may be available but not active in existing databases. ALTER EXTENSION vector UPDATE; moves the database to it. Put that in a migration too, so every environment ends up on the same version.

The application side is the same as above with a connection pool such as pg, the connection string in Secrets Manager, and a security group that only lets the service reach the instance.

Choosing an index

pgvector supports two approximate indexes. HNSW, used above, gives a better trade-off between speed and recall and can be created on an empty table, which suits a table that fills up over time. IVFFlat builds faster and uses less memory, but it should be created after the table has data, because it clusters the vectors it sees at build time.

Pick the operator class that matches your distance metric and use the same operator in queries, or the index will not be used:

OpenAI embeddings are normalized, so cosine distance and inner product rank results the same way, and cosine is the common default.

RDS settings that matter

Memory. HNSW search is fast when the index fits in memory. A vector(1536) value takes about 6 KB, so a million documents is roughly 6 GB of vectors before the index itself. Size the instance class so the working set fits in RAM, and watch the FreeableMemory and ReadIOPS metrics in CloudWatch as the table grows.

Index builds. Building HNSW on a large existing table is much faster when maintenance_work_mem is large enough to hold the graph. You can raise it for the session that builds the index, or in the DB parameter group for the instance. Create the index after a bulk load rather than before it.

Recall. hnsw.ef_search controls how many candidates a query looks at, 40 by default. Raising it per session, for example SET hnsw.ef_search = 100, improves recall at the cost of latency. Queries that combine vector search with a WHERE filter can return fewer rows than the LIMIT; pgvector 0.8.0 and later add iterative index scans to address that.

Dimensions. Smaller embeddings are cheaper to store and faster to search. text-embedding-3-small produces 1536 dimensions and can be shortened through the API's dimensions parameter if recall stays acceptable for your data.

How to choose

Use Encore when you want pgvector on RDS in your own AWS account with the migration, the database and the API in one codebase, and a local Postgres with pgvector for development. The pgvector quick start and the RAG pipeline guide build on the same setup.

Set up RDS by hand when your team already manages its databases with Terraform and wants this one alongside them. In both cases the SQL is the same, so moving between them later is a matter of where the database is declared.

Related guides

• pgvector vs Pinecone
• pgvector vs Qdrant
• Best vector databases
• How to build a RAG pipeline with TypeScript and PostgreSQL