Generate Embeddings in SQL with Aurora and Bedrock

Most embedding pipelines on AWS have the same shape: a job reads rows out of the database, calls Amazon Bedrock, and writes the vectors back. That is a second service to deploy, monitor, and pay for, and it exists only to move text a few hundred milliseconds away and bring numbers back. Aurora PostgreSQL can do the whole thing in one statement. The Aurora machine learning extension calls Bedrock from inside SQL, and pgvector stores and searches the result in the same table.

1. Wire the cluster to Bedrock

Aurora ML needs an IAM role carrying bedrock:InvokeModel, attached to the cluster under the Bedrock feature in the Connectivity and security tab. Model access has to be requested in Bedrock separately. After that, two extensions in the target database. The Aurora machine learning documentation walks through the console steps.

-- aws_ml pulls in aws_commons and creates the aws_bedrock schema
CREATE EXTENSION IF NOT EXISTS aws_ml CASCADE;
CREATE EXTENSION IF NOT EXISTS vector;

SELECT extname, extversion FROM pg_extension
WHERE extname IN ('aws_ml', 'vector');
  

You want aws_ml at 2.0, which is the version that adds the Bedrock functions, and vector at 0.8.0. Version 2.0 of the extension ships with Aurora PostgreSQL 16.1, 15.5, and 14.10 and higher. pgvector 0.8.0 needs 17.4, 16.8, 15.12, 14.17, or 13.20 and higher.

2. Call Bedrock from a SELECT

The function aws_bedrock.invoke_model_get_embeddings takes the model payload as JSON and pulls the vector out by key. Titan Text Embeddings V2 returns 1,024 dimensions by default and puts them under embedding. Aurora hands the result back as float8[], so cast it.

SELECT aws_bedrock.invoke_model_get_embeddings(
    model_id     := 'amazon.titan-embed-text-v2:0',
    content_type := 'application/json',
    json_key     := 'embedding',
    model_input  := '{"inputText": "tempdb contention on a heap"}'
)::vector AS embedding;
  

3. Materialize the vector, do not compute it at query time

Every invocation is billed, so the embedding belongs in a stored column that you fill once. Backfill in bounded batches rather than one enormous UPDATE, because each row is a separate synchronous call and a single failure rolls the whole statement back.

CREATE TABLE documents (
    id        bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    tenant_id text NOT NULL,
    content   text NOT NULL,
    embedding vector(1024)
);

-- backfill 500 rows at a time
UPDATE documents d
SET embedding = aws_bedrock.invoke_model_get_embeddings(
        'amazon.titan-embed-text-v2:0',
        'application/json',
        'embedding',
        json_build_object('inputText', d.content)::text
    )::vector
WHERE d.id IN (
    SELECT id FROM documents
    WHERE embedding IS NULL ORDER BY id LIMIT 500
);
  

4. Index it, then filter without losing rows

Titan V2 normalizes its output by default, which means inner product ranks identically to cosine while skipping the norm division on every comparison. Pair the <#> operator with vector_ip_ops. The m and ef_construction values below are the AWS starting points from the pgvector production guidance.

CREATE INDEX documents_embedding_ip_idx
ON documents USING hnsw (embedding vector_ip_ops)
WITH (m = 16, ef_construction = 128);

BEGIN;
SET LOCAL hnsw.iterative_scan = relaxed_order;
SET LOCAL hnsw.ef_search = 100;

SELECT id, content
FROM documents
WHERE tenant_id = 'acme'
ORDER BY embedding <#> '<query embedding>'
LIMIT 10;
COMMIT;
  

Those two settings are the reason to be on pgvector 0.8.0. Before it, a WHERE clause combined with a vector search regularly returned fewer rows than the LIMIT asked for, because the filter ran after the index had already handed back its top candidates. Iterative scans keep pulling from the index until the query is satisfied, and relaxed_order is the mode to reach for in production. The ef_search default of 40 is usually too low once real traffic arrives. Worth confirming with EXPLAIN that the planner actually picks the HNSW index here and has not quietly fallen back to a sequential scan, which is the kind of thing PlanTrace makes obvious at a glance.

Before you point this at a large table

The appeal here is the missing infrastructure. No Lambda, no queue, no separate deployment, and the vector never leaves the transaction that produced it. The limit is that Bedrock calls through Aurora ML are one row at a time: the UDFs support neither batching nor parallel execution, unlike the Comprehend and SageMaker functions in the same extension. That makes this a good fit for scheduled backfills through pg_cron and for low-volume writes, and a poor fit for an INSERT trigger on a high-throughput table. Size the instance so the HNSW graph stays in memory, watch BufferCacheHitRatio, and treat a drop there as the first sign the index has outgrown the box.