Enabling pgvector and Defining Vector Columns in Supabase

To store and query vector embeddings inside your Supabase project, you must enable the pgvector database extension and create tables with vector-typed columns.

1. Enabling the pgvector Extension

By default, the pgvector extension is installed on Supabase Postgres instances, but it is not enabled on your database schema.

To enable it, navigate to the Supabase Dashboard -> SQL Editor, and run this command:

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

You can confirm the installation by running this query:

-- Check active database extensions
SELECT * FROM pg_extension WHERE extname = 'vector';

2. Defining Vector Columns in Database Tables

Once enabled, you can define columns using the new vector data type. When defining a vector column, you must specify its dimensions (the number of dimensions must match the output size of your embedding model).

Common embedding models and their dimensions:

• OpenAI text-embedding-3-small: 1536 dimensions.
• OpenAI text-embedding-3-large: 3072 dimensions.
• Hugging Face all-MiniLM-L6-v2: 384 dimensions.

Let us create a table to store document fragments and their vector embeddings:

CREATE TABLE document_sections (
  id BIGSERIAL PRIMARY KEY,
  content TEXT NOT NULL, // The actual text fragment
  metadata JSONB, // Document metadata
  embedding VECTOR(1536) // Column to store 1536-dimension OpenAI embeddings
);

3. Inserting Vector Data

Vector data is inserted as an array of decimal numbers formatted as a string list:

INSERT INTO document_sections (content, embedding)
VALUES (
  'Zustand is a state management library for React.',
  '[0.0023, -0.0142, 0.0891, ..., 0.0125]' // String representation of the array
);