pgvector Guide: Storing AI Embeddings in Managed Postgres

Why Store Vectors Natively in Relational Databases?

As developers build AI-native applications, they need to store and query high-dimensional vector embeddings (generated by models like OpenAI text-embedding-3 or Cohere Embed). Traditional architectures require sync pipelines between a relational database (like Postgres) and a separate vector database. This introduces data consistency bugs, complex sync cron jobs, and high licensing costs.

The solution is pgvector—an open-source Postgres extension that lets you store vectors directly inside table columns, combining relational lookups and similarity search in a single SQL query.

Setting Up pgvector on IndBase Postgres

IndBase enables pgvector by default on all managed Postgres instances. To initialize the extension, run:

-- Enable pgvector extension
CREATE EXTENSION IF NOT EXISTS vector;

Generating and Inserting Embeddings

Next, we create a table with a vector column. In this example, we define a 1536-dimension vector (matching OpenAI embeddings):

CREATE TABLE documents (
  id bigserial PRIMARY KEY,
  title text NOT NULL,
  content text NOT NULL,
  embedding vector(1536) -- 1536 dimensions
);

Performing High-Speed Similarity Search (HNSW Indexing)

To perform a cosine distance similarity search, we use the cosine distance operator (<=>):

-- Find top 5 documents similar to query embedding
SELECT id, title, content, (embedding <=> '[0.012, -0.024, ..., 0.089]') AS distance
FROM documents
ORDER BY distance
LIMIT 5;

For large datasets, we create an HNSW (Hierarchical Navigable Small World) index to ensure search responses take under 10ms:

CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);