---
title: "Storing OpenAI embeddings in Postgres with pgvector"
description: "Post describes how embeddings represent semantic relatedness as vectors and how OpenAI’s text-embedding-ada-002 model produces 1536-dimensional embeddings for text. It addresses storing and searching large or dynamically changing collections that may not fit comfortably in memory, using PostgreSQL and the pgvector extension instead of a small CSV-and-NumPy workflow. The implementation enables the vector extension, creates a documents table with a vector(1536) column, and defines match_documents() with cosine distance, thresholds, counts, and ordering. It also shows JavaScript code for generating embeddings, a search flow that retrieves matching documents for prompt context, and token limiting before a completion request, while noting that documents can be chunked. For larger tables, the post recommends an ivfflat index with vector_cosine_ops and gives 4 * sqrt(table_rows) as a starting point for lists."
---

# Storing OpenAI embeddings in Postgres with pgvector

[Supabase](https://yomu.fyi/company/supabase) · gregnr · Feb 6, 2023

**Type:** Tutorial

## Summary

Post describes how embeddings represent semantic relatedness as vectors and how OpenAI’s text-embedding-ada-002 model produces 1536-dimensional embeddings for text. It addresses storing and searching large or dynamically changing collections that may not fit comfortably in memory, using PostgreSQL and the pgvector extension instead of a small CSV-and-NumPy workflow. The implementation enables the vector extension, creates a documents table with a vector(1536) column, and defines match\_documents() with cosine distance, thresholds, counts, and ordering. It also shows JavaScript code for generating embeddings, a search flow that retrieves matching documents for prompt context, and token limiting before a completion request, while noting that documents can be chunked. For larger tables, the post recommends an ivfflat index with vector\_cosine\_ops and gives 4 \* sqrt(table\_rows) as a starting point for lists.

## Context

Small-scale embedding storage and similarity calculations can use CSV files, Python, and NumPy, but the post identifies challenges when collections exceed available memory or embeddings need to be created, updated, and deleted dynamically. It presents PostgreSQL as a way to manage larger embedding collections and support applications that do not use Python.

## Approach / What changed

Enable pgvector in PostgreSQL, store document text or references alongside vector(1536) embeddings, and define a match\_documents() SQL function using cosine distance, a similarity threshold, and a match count. Generate embeddings with OpenAI’s text-embedding-ada-002 model, query the function for relevant documents, limit injected context by tokens, and add an ivfflat vector\_cosine\_ops index as the table grows.

## Takeaways

- pgvector adds a vector data type and three distance operators: Euclidean distance, negative inner product, and cosine distance. The example uses cosine distance because OpenAI recommends cosine similarity for its embeddings.
- The search flow generates one embedding for the user’s query, calls match\_documents() with a threshold of 0.78 and a maximum of 10 matches, then limits combined context to 1500 tokens before sending it to a completion model.
- For cosine-distance ordering, the example uses an ivfflat index with vector\_cosine\_ops; 4 \* sqrt(table\_rows) is given as a starting value for the number of lists.

**Tags:** [Machine Learning](https://yomu.fyi/topic/machine-learning), [Performance](https://yomu.fyi/topic/performance), [Postgres](https://yomu.fyi/topic/postgres), [Search](https://yomu.fyi/topic/search), [Serverless](https://yomu.fyi/topic/serverless)

- Source: [Supabase](https://supabase.com/blog/openai-embeddings-postgres-vector)
- Source URL: https://supabase.com/blog/openai-embeddings-postgres-vector
- Ingested by Yomu: 2026-08-29T01:16:14.934Z

[Read original post](https://supabase.com/blog/openai-embeddings-postgres-vector)