Automatic embeddings architecture overview
Automated embedding generation uses pgvector for storing and querying vector embeddings, pgmq for queuing embedding generation requests, pg_net for asynchronous HTTP requests from Postgres to Edge Functions, pg_cron for automatically processing and retrying embedding generations, database triggers to detect content changes and enqueue requests, and Supabase Edge Functions to generate embeddings via external APIs like OpenAI. The system is designed to be generic for use with any table and content, and handles failures gracefully through retries with detailed job status information.
Extensions required for automatic embeddings
Enable the following extensions: vector (for vector operations), pgmq (for queueing and processing jobs, creates its own schema), pg_net (for async HTTP requests, schema: extensions), pg_cron (for scheduled processing and retries, creates its own schema), and hstore (for clearing embeddings during updates, schema: extensions). These can be enabled via SQL with 'create extension if not exists' commands or through the Dashboard Extensions page.
Utility function: project_url() for Edge Functions
Create a utility function that retrieves the Supabase project URL from Vault, required to invoke Edge Functions. The function queries vault.decrypted_secrets table where name = 'project_url'. For local Supabase stack, add to supabase/seed.sql: select vault.create_secret('http://api.supabase.internal:8000', 'project_url'). For cloud deployment, run in SQL editor: select vault.create_secret('<project-url>', 'project_url') where <project-url> is from dashboard project settings API page.
Utility function: invoke_edge_function() for generic invocation
Create a generic function invoke_edge_function(name text, body jsonb, timeout_milliseconds int = 5*60*1000) that invokes any Edge Function. It retrieves authorization headers from the current PostgREST session via current_setting('request.headers', true), then performs a net.http_post request to util.project_url() || '/functions/v1/' || name with Content-Type and Authorization headers. Default timeout is 5 minutes.
Utility function: clear_column() trigger for updates
Create a generic trigger function clear_column() that accepts the column name as TG_ARGV[0] and sets it to NULL in the NEW record using hstore operations: NEW := NEW #= hstore(clear_column, NULL). Use with a before trigger and 'for each row' clause to clear columns on update before writing to disk, avoiding extra update statements.
Queue creation for embedding jobs
Create a pgmq queue for embedding jobs with: select pgmq.create('embedding_jobs'). This queue is used to queue and track embedding generation requests for asynchronous processing.
Trigger function: queue_embeddings() for generic job queueing
Create a generic trigger function queue_embeddings() that accepts two arguments: content_function (name of a function that returns text content to be embedded, accepts a single row as input) and embedding_column (destination column name for the embedding). It queues a job via pgmq.send() with message containing: id (from NEW.id), schema (TG_TABLE_SCHEMA), table (TG_TABLE_NAME), contentFunction, and embeddingColumn. Requires 'for each row' clause in trigger definition.
Function: process_embeddings() for batch job processing
Create function process_embeddings(batch_size int = 10, max_requests int = 10, timeout_milliseconds int = 5*60*1000) that: reads jobs from embedding_jobs queue, assigns batch numbers with (row_number()-1)/batch_size, groups jobs into batches using jsonb_agg, and invokes the 'embed' Edge Function for each batch with timeout. Use pg_cron to schedule it: select cron.schedule('process-embeddings', '10 seconds', 'select util.process_embeddings();'). Visibility timeouts ensure failed requests are retried.
Edge Function implementation for embeddings
The embed Edge Function must: (1) accept POST requests with JSON body containing array of job objects validated against schema with jobId, id, schema, table, contentFunction, embeddingColumn fields, (2) fetch content via SQL calling the contentFunction on the row, (3) call generateEmbedding() to create vector via OpenAI API (or custom service), (4) update the database row's embedding column, (5) delete the job from pgmq queue, (6) return 200 OK with JSON containing completedJobs and failedJobs arrays, (7) set response headers 'x-completed-jobs' and 'x-failed-jobs' with counts, (8) handle worker termination gracefully by catching beforeunload event.
Edge Function setup with Supabase CLI
Create new Edge Function with: supabase functions new embed. This creates supabase/functions/embed/index.ts. Set OPENAI_API_KEY environment variable locally in .env file. Deploy with: supabase secrets set --env-file .env or supabase secrets set OPENAI_API_KEY=<your-api-key>. The function uses imports: import postgres from 'https://deno.land/x/postgresjs@v3.4.5/mod.js', import OpenAI from 'jsr:@openai/openai', import { z } from 'npm:zod'. Access SUPABASE_DB_URL (built-in) and OPENAI_API_KEY (manual) environment variables.
TypeScript Edge Function structure for embeddings
The embed function uses Zod schemas: jobSchema with fields jobId (number), id (number), schema (string), table (string), contentFunction (string), embeddingColumn (string); failedJobSchema extends jobSchema with error (string) field. Type Row has: id (string), content (unknown). Process pendingJobs array, track completedJobs and failedJobs separately. Use Promise.race([processJobs(), catchUnload()]) to handle worker termination. Return response with status 200, content-type application/json, body { completedJobs, failedJobs }.
OpenAI embedding generation in Edge Function
Use OpenAI client initialized with Deno.env.get('OPENAI_API_KEY'). Call openai.embeddings.create({ model: 'text-embedding-3-small', input: text }) to generate embeddings. The model generates 1536-dimensional embeddings. Check that response.data[0] exists before using. Store embedding as JSON.stringify(embedding) in database. Can be replaced with custom embedding logic.
Documents table for embeddings example
Create table: id (integer primary key generated always as identity), title (text not null), content (text not null), embedding (halfvec(1536)), created_at (timestamp with time zone default now()). Create HNSW index: create index on documents using hnsw (embedding halfvec_cosine_ops). Use halfvec for 16-bit precision storage. OpenAI text-embedding-3-small generates 1536 dimensions. HNSW indexes support max 4000 dimensions for halfvec. Table must have primary key column named 'id' for triggers and Edge Function to work correctly.
Creating triggers for automatic embedding on insert and update
Create a content function (e.g., embedding_input) that takes a row and returns text for embedding. Create trigger for inserts: 'create trigger embed_documents_on_insert after insert on documents for each row execute function util.queue_embeddings('embedding_input', 'embedding')'. Create trigger for updates: 'create trigger embed_documents_on_update after update of title, content on documents for each row execute function util.queue_embeddings('embedding_input', 'embedding')'. Update trigger must specify columns (of title, content) that, when changed, should trigger re-embedding. Columns must match those used in the content function.
Optional: clear embeddings on update for accuracy
To ensure embeddings are always in sync with content, add a before trigger to clear the embedding column when content changes: 'create trigger clear_document_embedding_on_update before update of title, content on documents for each row execute function util.clear_column('embedding')'. This requires the hstore extension and nullable embedding column. The before trigger avoids an extra update statement. Use only if accurate embeddings are more important than always having some embedding. After content updates, embedding will be null until regenerated (up to 10 seconds).
Embedding generation processing behavior
After inserting a document, the embedding column is initially null because embedding generation is asynchronous. The cron job processes every 10 seconds. Wait up to 10 seconds for the next scheduled task to run and embedding to be generated. When content is updated and the clear trigger is active, the embedding resets to null. A new embedding will be generated in the next scheduled task run (up to 10 seconds). When title or content changes, the embedding_input function is called to regenerate the input text for embedding.
Edge Function response format and troubleshooting
The embed function returns 200 OK with JSON body containing completedJobs and failedJobs arrays. Response headers include 'x-completed-jobs' and 'x-failed-jobs' with integer counts. Each completed job includes jobId, id, schema, table, contentFunction, embeddingColumn. Each failed job includes those fields plus an error field describing the failure. Use 'x-deno-execution-id' header to trace execution in dashboard logs. Query net._http_response table to diagnose issues: select * from net._http_response where (headers->>'x-failed-jobs')::int > 0.
Common failure reasons in embedding generation
Jobs fail when: (1) error generating embedding via external API (e.g., OpenAI connection issues, rate limits), (2) error connecting to the database, (3) Edge Function terminated due to wall clock limit or other termination, (4) row not found in the specified schema/table/id combination, (5) content from contentFunction is not a string when string is expected, (6) any other error thrown during processJob() execution. Failed jobs remain in queue with visibility timeout and are retried in next scheduled task.
Visibility timeouts and retry mechanism
When pgmq.read() reads a message from the queue, it sets a visibility timeout specified by the vt parameter (in seconds). This hides the message from other readers for that duration. If the Edge Function fails to process a message within this timeout, the message becomes visible again and is available for retry by the next scheduled task. The process_embeddings function sets visibility timeout by dividing timeout_milliseconds by 1000. Failed jobs remain queued and will be retried automatically.
Batching strategy for embedding requests
Jobs are batched to avoid long processing times, timeouts, and to handle failures effectively. Batching balances efficiency and reliability better than one request per row (which causes rate limiting) or all embeddings in one request (which causes timeouts). The process_embeddings function uses batch_size (default 10) to group jobs: batch_num = (row_number()-1)/batch_size. It can handle up to max_requests*batch_size jobs per execution. For example, with defaults: max 100 jobs are read and split into up to 10 batches of 10 jobs each, invoked as separate Edge Function calls.
Embedding vectors in semantic search
Semantic search uses an intermediate representation called an embedding vector to link database records with search queries. A vector is a list of numerical values representing various features of text. Embeddings are plotted on a graph where similar concepts are positioned close together and dissimilar concepts are far apart, allowing for semantic comparison between different pieces of text.
Embedding model consistency requirement
When using embedding models with semantic search, you must use the same model for all embedding comparisons. Comparing embeddings created by different models will yield meaningless results.
Enable pgvector extension in Postgres
To enable the pgvector extension in Postgres, run: create extension vector with schema extensions;
Create table with vector column for embeddings
To create a table storing embeddings, use: create table documents (id bigint primary key generated always as identity, content text, embedding extensions.vector(512));. The vector size in parentheses represents the number of dimensions in the embedding and should match the dimensions produced by your embedding model.
Add vector column to existing table
To add a vector column to an existing table, use: alter table documents add column embedding extensions.vector(512);. The vector size should match the number of dimensions produced by your embedding model.
Semantic search function using cosine distance
Example function for semantic search using cosine distance: create or replace function match_documents (query_embedding extensions.vector(512), match_threshold float, match_count int) returns setof documents language sql as $$ select * from documents where documents.embedding <=> query_embedding < 1 - match_threshold order by documents.embedding <=> query_embedding asc limit least(match_count, 200); $$;. Parameters: query_embedding (one-time embedding for search query), match_threshold (minimum similarity between -1 and 1, where 1 is most similar), match_count (maximum results, limited to 200).
Semantic search function using negative inner product
Example function for semantic search using negative inner product (<#>): create or replace function match_documents (query_embedding extensions.vector(512), match_threshold float, match_count int) returns setof documents language sql as $$ select * from documents where documents.embedding <#> query_embedding < -match_threshold order by documents.embedding <#> query_embedding asc limit least(match_count, 200); $$;. Use this operator when embeddings are confirmed to be normalized (e.g., from OpenAI) for better performance than cosine distance.
Call match_documents function from SQL
To call the match_documents function directly from SQL: select * from match_documents('[...]'::extensions.vector(512), 0.78, 10);. When executing from an application, use a Postgres client library to establish a direct connection and parameterize arguments before executing the query.
Filtering vector search by metadata pitfall
Chaining .eq() after rpc() is applied by PostgREST as an outer filter on the function result after the function has already executed its similarity ranking and limit. This prevents the vector planner from using the filter, and selective filters can leave you with fewer than match_count rows. Push filters into the SQL function instead so the planner can combine it with the vector predicate.
Filter vector search by category column
To extend match_documents with a typed filter parameter for category: create or replace function match_documents (query_embedding extensions.vector(512), match_threshold float, match_count int, filter_category text) returns setof documents language sql as $$ select * from documents where documents.category = filter_category and documents.embedding <=> query_embedding < 1 - match_threshold order by documents.embedding <=> query_embedding asc limit least(match_count, 200); $$;
Filter vector search by jsonb metadata
To filter vector search using a jsonb metadata column: where documents.metadata @> filter_metadata and documents.embedding <=> query_embedding < 1 - match_threshold. Use the @> containment operator to filter by JSON data while pushing the filter into the SQL function.
IVFFlat index best practices
IVFFlat indexes are best for large datasets (100k-10M rows), fast approximate search, and lower memory usage. Create with: create index on documents using ivfflat (embedding vector_cosine_ops) with (lists = 100);
HNSW index best practices
HNSW indexes are best for high accuracy requirements, read-heavy workloads, low-latency semantic search, and scenarios where recall is more important than memory usage. Create with: create index on documents using hnsw (embedding vector_cosine_ops);
Cosine distance as safe default similarity metric
Cosine distance (<=> operator) is a safe default when you don't know whether embeddings are normalized. If embeddings are confirmed to be normalized (for example, from OpenAI), use negative inner product (<#>) for better performance.
HNSW filtering with pgvector 0.8.0 and later
From pgvector 0.8.0 onwards, the planner supports iterative index scans that re-enter the HNSW index to gather more candidates when filtering results. This improves handling of selective filters that would otherwise return fewer rows than expected.
Amazon Titan multimodal embedding model ID
The Amazon Titan multimodal model for image embeddings is accessed via the model ID 'amazon.titan-embed-image-v1' when invoking the Bedrock API.
Titan multimodal embedding output dimensions
Amazon Titan multimodal embeddings produce 1024-dimensional vectors. The embedding configuration parameter 'outputEmbeddingLength' can be set to 1024.
Bedrock API invoke_model parameters for Titan image embeddings
The boto3 bedrock_client.invoke_model() call requires: body (JSON string with 'inputImage' as base64-encoded image and 'embeddingConfig' with 'outputEmbeddingLength'), modelId ('amazon.titan-embed-image-v1'), accept ('application/json'), and contentType ('application/json'). The response body contains an 'embedding' field with the vector.
Generate text embeddings with Titan for semantic search
To generate embeddings from text queries with Amazon Titan, invoke the bedrock_client with a body JSON containing 'inputText' (the query string) and 'embeddingConfig' with 'outputEmbeddingLength' set to 1024, using modelId 'amazon.titan-embed-image-v1'. The response contains the embedding vector.
Complete example of semantic image search with Titan and Supabase Vector
Full Python example showing: (1) Base64 encoding images, (2) Constructing Bedrock request bodies for Titan multimodal embeddings with outputEmbeddingLength 1024, (3) Creating/retrieving a vecs collection with dimension 1024, (4) Upserting image embeddings with metadata, (5) Creating an index, (6) Querying with text embeddings and metadata filters, (7) Displaying results with matplotlib. Uses boto3 for Bedrock, vecs client for vector operations, and accepts command-line search terms via sys.argv[1].
Amazon Titan Embeddings model ID
The Amazon Titan Embeddings G1 – Text v1.2 model is identified by the modelId 'amazon.titan-embed-text-v1' when invoking the bedrock-runtime client.
Create embeddings with Amazon Bedrock Python example
This example shows how to create embeddings using the Amazon Titan Embeddings G1 – Text v1.2 model (amazon.titan-embed-text-v1) with boto3:
```python
import boto3
import vecs
import json
client = boto3.client(
'bedrock-runtime',
region_name='us-east-1',
aws_access_key_id='<replace_your_own_credentials>',
aws_secret_access_key='<replace_your_own_credentials>',
aws_session_token='<replace_your_own_credentials>',
)
dataset = [
"The cat sat on the mat.",
"The quick brown fox jumps over the lazy dog.",
"Friends, Romans, countrymen, lend me your ears",
"To be or not to be, that is the question.",
]
embeddings = []
for sentence in dataset:
response = client.invoke_model(
body= json.dumps({"inputText": sentence}),
modelId= "amazon.titan-embed-text-v1",
accept = "application/json",
contentType = "application/json"
)
response_body = json.loads(response["body"].read())
embeddings.append((sentence, response_body.get("embedding"), {}))
```
Store Amazon Bedrock embeddings with vecs
This example shows how to store embeddings in Postgres using vecs:
```python
import vecs
DB_CONNECTION = "postgresql://<user>:<pa••••••d>@<host>:<port>/<db_name>"
vx = vecs.Client(DB_CONNECTION)
sentences = vx.get_or_create_collection(name="sentences", dimension=1536)
sentences.upsert(records=embeddings)
sentences.create_index()
```
The dimension is set to 1536 to match the default dimension of the Amazon Titan Embeddings G1 - Text model.
Query embeddings with vecs to find similar sentences
This example shows how to query a vecs collection to find the most similar sentences to a query:
```python
query_sentence = "A quick animal jumps over a lazy one."
vx = vecs.Client(DB_CONNECTION)
response = client.invoke_model(
body= json.dumps({"inputText": query_sentence}),
modelId= "amazon.titan-embed-text-v1",
accept = "application/json",
contentType = "application/json"
)
response_body = json.loads(response["body"].read())
query_embedding = response_body.get("embedding")
results = sentences.query(
data=query_embedding,
limit=3,
include_value = True
)
for result in results:
print(result)
```
This returns the most similar 3 records and their distance to the query vector.
Amazon Titan embedding dimension
The Amazon Titan Embeddings G1 - Text model produces embeddings with a default dimension of 1536.
gte-small is the only supported embedding model
Currently, only the gte-small text embedding model (from https://huggingface.co/Supabase/gte-small) is supported in Supabase's Edge Runtime.
Initialize inference session for embeddings
Create a new inference session using: const session = new Supabase.ai.Session('gte-small'). Multiple requests can use the same inference session.
Generate embedding with session.run()
Generate an embedding by calling session.run(input) with the input string. The example code shows: const embedding = await session.run(input, { mean_pool: true, normalize: true, }).
session.run() options for embeddings
The session.run() method accepts options: mean_pool (boolean) sets pooling to mean, which compresses token-level embedding representations into a single sentence embedding. normalize (boolean) normalizes the embedding vector to length 1 (unit vector), enabling use with distance measures like dot product.
Complete Edge Function example for embeddings
Deno.serve(async (req) => {
const { input } = await req.json();
const embedding = await session.run(input, {
mean_pool: true,
normalize: true,
});
return new Response(
JSON.stringify({ embedding }),
{ headers: { 'Content-Type': 'application/json' } }
);
});
Call match_documents function from supabase-js
To call the match_documents function from a JavaScript application using supabase-js:
const { data: documents } = await supabase.rpc('match_documents', {
query_embedding: embedding,
match_threshold: 0.78,
match_count: 10,
filter_category: 'blog', // optional filter parameter
});
The function accepts the following parameters:
- query_embedding: the embedding vector to match against
- match_threshold: similarity threshold for filtering results (default: 0.78)
- match_count: maximum number of documents to return (default: 10)
- filter_category: optional category filter to narrow results (e.g., 'blog')
pgvector distance operators
pgvector supports 3 operators for computing distance between embeddings and retrieving similar records in SQL queries:
- `<->` computes Euclidean distance
- `<#>` computes negative inner product
- `<=>` computes cosine distance
Inner product (dot product) tends to be fastest if vectors are normalized. Choose the operator based on your similarity search needs.