What You Can Build

Why This Matters

Keep Everything Together

aside positive The AI landscape moves fast, but your database is the stable foundation. Building on Postgres means you can swap embedding models, try new retrieval strategies, or add graph traversal — all without re-architecting your infrastructure.

Storing and Querying Vectors

Enable pgvector

CREATE EXTENSION vector;

Create a Table with Embeddings

CREATE TABLE documents (
  id         bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  title      text NOT NULL,
  content    text NOT NULL,
  metadata   jsonb DEFAULT '{}',
  embedding  vector(1536)
);

Distance Operators

-- Cosine distance (most common for normalized embeddings)
SELECT title, 1 - (embedding <=> $1::vector) AS similarity
FROM documents
ORDER BY embedding <=> $1::vector
LIMIT 5;

-- L2 (Euclidean) distance
SELECT title, embedding <-> $1::vector AS distance
FROM documents
ORDER BY embedding <-> $1::vector
LIMIT 5;

-- Inner product (use with non-normalized embeddings)
SELECT title, (embedding <#> $1::vector) * -1 AS score
FROM documents
ORDER BY embedding <#> $1::vector
LIMIT 5;

Indexing for Performance

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

-- Tune query-time recall vs. speed
SET hnsw.ef_search = 100;

-- Build after loading data (needs representative sample)
CREATE INDEX ON documents
  USING ivfflat (embedding vector_cosine_ops)
  WITH (lists = 100);

-- Tune: higher probes = better recall, more latency
SET ivfflat.probes = 10;

Half-Precision and Binary Vectors

-- Half-precision vectors use ~50% less memory
CREATE TABLE documents_half (
  id        bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  content   text NOT NULL,
  embedding halfvec(1536)
);

CREATE INDEX ON documents_half
  USING hnsw (embedding halfvec_cosine_ops);

aside positive Use halfvec when you need to scale to millions of vectors. The recall loss is minimal for most applications, and you cut index memory in half.

Semantic Search

Why Hybrid Search?

Setting Up Hybrid Search

CREATE TABLE articles (
  id         bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  title      text NOT NULL,
  body       text NOT NULL,
  -- Full-text search column (auto-updated)
  tsv        tsvector GENERATED ALWAYS AS (
               to_tsvector('english', title || ' ' || body)
             ) STORED,
  -- Vector embedding for semantic similarity
  embedding  vector(1536)
);

-- Index both search methods
CREATE INDEX ON articles USING gin (tsv);
CREATE INDEX ON articles USING hnsw (embedding vector_cosine_ops);

Hybrid Search Query

WITH keyword_results AS (
  SELECT id, ts_rank_cd(tsv, query) AS rank
  FROM articles, plainto_tsquery('english', $1) query
  WHERE tsv @@ query
  ORDER BY rank DESC
  LIMIT 20
),
vector_results AS (
  SELECT id, 1 - (embedding <=> $2::vector) AS rank
  FROM articles
  ORDER BY embedding <=> $2::vector
  LIMIT 20
),
combined AS (
  SELECT
    COALESCE(k.id, v.id) AS id,
    -- RRF formula: sum of 1/(k + rank_position) across both lists
    COALESCE(1.0 / (60 + ROW_NUMBER() OVER (ORDER BY k.rank DESC NULLS LAST)), 0) +
    COALESCE(1.0 / (60 + ROW_NUMBER() OVER (ORDER BY v.rank DESC NULLS LAST)), 0) AS rrf_score
  FROM keyword_results k
  FULL OUTER JOIN vector_results v ON k.id = v.id
)
SELECT a.id, a.title, a.body, c.rrf_score
FROM combined c
JOIN articles a ON a.id = c.id
ORDER BY c.rrf_score DESC
LIMIT 10;

aside negative The 60 constant in RRF is standard (from the original paper). It prevents high-ranked items in one list from dominating. You can tune it, but 60 works well as a default.

Simpler Hybrid Search with Weighted Scoring

SELECT id, title, body,
  (0.7 * (1 - (embedding <=> $2::vector))) +
  (0.3 * ts_rank_cd(tsv, plainto_tsquery('english', $1))) AS score
FROM articles
WHERE tsv @@ plainto_tsquery('english', $1)
   OR embedding <=> $2::vector < 0.5
ORDER BY score DESC
LIMIT 10;

RAG (Retrieval-Augmented Generation)

The RAG Pattern

Schema for RAG

-- Source documents
CREATE TABLE documents (
  id           bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  title        text NOT NULL,
  source_url   text,
  content      text NOT NULL,
  metadata     jsonb DEFAULT '{}',
  created_at   timestamptz DEFAULT now()
);

-- Chunked and embedded content
CREATE TABLE chunks (
  id           bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  document_id  bigint REFERENCES documents(id) ON DELETE CASCADE,
  chunk_index  int NOT NULL,
  content      text NOT NULL,
  token_count  int,
  embedding    vector(1536),
  UNIQUE (document_id, chunk_index)
);

-- Index for fast retrieval
CREATE INDEX ON chunks USING hnsw (embedding vector_cosine_ops);
CREATE INDEX ON chunks (document_id);

Retrieval with Metadata Filtering

-- Find relevant chunks, filtered by source and recency
SELECT c.content, c.chunk_index,
       d.title, d.source_url,
       1 - (c.embedding <=> $1::vector) AS similarity
FROM chunks c
JOIN documents d ON d.id = c.document_id
WHERE d.metadata->>'category' = 'engineering'
  AND d.created_at > now() - interval '90 days'
ORDER BY c.embedding <=> $1::vector
LIMIT 5;

Chunking Strategies

-- Example: inserting chunks with overlap tracking
INSERT INTO chunks (document_id, chunk_index, content, token_count, embedding)
VALUES
  ($1, 0, $2, $3, $4),  -- tokens 0-500
  ($1, 1, $5, $6, $7),  -- tokens 450-950 (50-token overlap)
  ($1, 2, $8, $9, $10); -- tokens 900-1400

aside negative Chunk size matters. Too large and the embedding loses specificity; too small and you lose context. Start with 500–1000 tokens with 10–20% overlap and iterate based on retrieval quality.

Context Window Assembly

-- Get chunks with surrounding context (previous and next chunks)
SELECT c.content,
       lag(c.content) OVER (PARTITION BY c.document_id ORDER BY c.chunk_index) AS prev_chunk,
       lead(c.content) OVER (PARTITION BY c.document_id ORDER BY c.chunk_index) AS next_chunk
FROM chunks c
WHERE c.id IN (SELECT id FROM retrieved_chunk_ids)
ORDER BY c.embedding <=> $1::vector;

GraphRAG

When to Use GraphRAG

Setting Up a Knowledge Graph with Apache AGE

-- Enable the AGE extension
CREATE EXTENSION age;
LOAD 'age';
SET search_path = ag_catalog, "$user", public;

-- Create a knowledge graph
SELECT create_graph('knowledge_base');

Building the Graph

-- Add entities as graph nodes
SELECT * FROM cypher('knowledge_base', $$
  CREATE (:Document {
    id: 1,
    title: 'Postgres Performance Guide',
    category: 'engineering'
  })
$$) AS (v agtype);

SELECT * FROM cypher('knowledge_base', $$
  CREATE (:Concept {
    name: 'Index Tuning',
    description: 'Optimizing database indexes for query performance'
  })
$$) AS (v agtype);

-- Create relationships
SELECT * FROM cypher('knowledge_base', $$
  MATCH (d:Document {id: 1}), (c:Concept {name: 'Index Tuning'})
  CREATE (d)-[:COVERS {section: 'chapter_3', depth: 'detailed'}]->(c)
$$) AS (e agtype);

GraphRAG Retrieval Pattern

-- Step 1: Find relevant chunks via vector search
WITH seed_chunks AS (
  SELECT c.id, c.document_id, c.content,
         1 - (c.embedding <=> $1::vector) AS similarity
  FROM chunks c
  ORDER BY c.embedding <=> $1::vector
  LIMIT 5
),
-- Step 2: Find related entities via graph traversal
related_entities AS (
  SELECT entity_id, relationship, depth
  FROM cypher('knowledge_base', $$
    MATCH (d:Document {id: $doc_id})-[r*1..2]-(related)
    RETURN id(related) AS entity_id, type(r) AS relationship, length(r) AS depth
  $$) AS (entity_id agtype, relationship agtype, depth agtype)
  WHERE doc_id IN (SELECT document_id FROM seed_chunks)
),
-- Step 3: Get chunks from related documents
graph_chunks AS (
  SELECT c.content, c.embedding, 'graph' AS source
  FROM chunks c
  JOIN documents d ON d.id = c.document_id
  WHERE d.id IN (SELECT entity_id::int FROM related_entities)
  ORDER BY c.embedding <=> $1::vector
  LIMIT 5
)
-- Combine vector-similar and graph-connected chunks
SELECT content, similarity, source FROM (
  SELECT content, similarity, 'vector' AS source FROM seed_chunks
  UNION ALL
  SELECT content, 1 - (embedding <=> $1::vector), source FROM graph_chunks
) combined
ORDER BY similarity DESC
LIMIT 10;

Entity Extraction and Graph Maintenance

-- Table to track extracted entities
CREATE TABLE entities (
  id            bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  document_id   bigint REFERENCES documents(id) ON DELETE CASCADE,
  entity_name   text NOT NULL,
  entity_type   text NOT NULL,  -- 'person', 'concept', 'tool', etc.
  properties    jsonb DEFAULT '{}'
);

CREATE TABLE entity_relationships (
  id            bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  source_id     bigint REFERENCES entities(id) ON DELETE CASCADE,
  target_id     bigint REFERENCES entities(id) ON DELETE CASCADE,
  relationship  text NOT NULL,  -- 'mentions', 'depends_on', 'authored_by'
  properties    jsonb DEFAULT '{}'
);

-- Indexes for fast graph traversal
CREATE INDEX ON entity_relationships (source_id);
CREATE INDEX ON entity_relationships (target_id);
CREATE INDEX ON entities (entity_type, entity_name);

GraphRAG with Common Table Expression (CTE)

-- Traverse relationships up to 3 hops from seed entities
WITH RECURSIVE graph_walk AS (
  -- Start from entities mentioned in relevant chunks
  SELECT e.id, e.entity_name, e.entity_type, 0 AS depth
  FROM entities e
  JOIN chunks c ON c.document_id = e.document_id
  WHERE c.id IN (SELECT id FROM seed_chunk_ids)

  UNION ALL

  -- Walk relationships
  SELECT e2.id, e2.entity_name, e2.entity_type, gw.depth + 1
  FROM graph_walk gw
  JOIN entity_relationships er ON er.source_id = gw.id
  JOIN entities e2 ON e2.id = er.target_id
  WHERE gw.depth < 3
)
SELECT DISTINCT entity_name, entity_type, min(depth) AS min_depth
FROM graph_walk
GROUP BY entity_name, entity_type
ORDER BY min_depth, entity_name;

aside positive You don't need a dedicated graph database for GraphRAG. Postgres recursive CTEs handle most knowledge graph traversal patterns. Apache AGE adds openCypher syntax for complex graph queries, but the relational approach works for simpler graphs.

Snowflake Cortex Integration

Generating Embeddings with Cortex

-- Generate an embedding with AI_EMBED (recommended)
SELECT AI_EMBED('snowflake-arctic-embed-l-v2.0', 'Your text here');

-- Specify output dimensions (model-dependent)
SELECT AI_EMBED('snowflake-arctic-embed-l-v2.0', 'Your text here');

Batch Embedding Generation

-- Embed all rows in a content table
CREATE OR REPLACE TABLE my_db.my_schema.embedded_chunks AS
SELECT
  id,
  document_id,
  chunk_index,
  content,
  AI_EMBED('snowflake-arctic-embed-l-v2.0', content) AS embedding
FROM my_db.my_schema.chunks;

Scripting Between Snowflake Postgres and Snowflake

-- In Snowflake: create a storage integration pointing to your Postgres instance
CREATE STORAGE INTEGRATION my_pg_stage_integration
  TYPE = POSTGRES_INTERNAL_STORAGE
  POSTGRES_INSTANCE = 'my_postgres_instance';

-- Create a stage for file exchange
CREATE STAGE my_pg_stage
  RELATIVE_URL = '/embeddings'
  STORAGE_INTEGRATION = my_pg_stage_integration;

-- In Postgres: export content that needs embeddings
COPY (
  SELECT id, content
  FROM documents
  WHERE embedding IS NULL
)
TO '@STAGE/embeddings/to_embed.parquet'
WITH (FORMAT 'parquet');

-- In Snowflake: read the exported file and generate embeddings
CREATE OR REPLACE TABLE embedded_results AS
SELECT
  $1:id::int AS id,
  AI_EMBED('snowflake-arctic-embed-l-v2.0', $1:content::text) AS embedding
FROM @my_pg_stage/to_embed.parquet;

-- Write results back to the stage
COPY INTO @my_pg_stage/embeddings_done.parquet
FROM embedded_results
FILE_FORMAT = (TYPE = PARQUET);

-- In Postgres: load the generated embeddings
CREATE TEMP TABLE embedding_import (
  id       int,
  embedding vector(1024)
);

COPY embedding_import
FROM '@STAGE/embeddings/embeddings_done.parquet'
WITH (FORMAT 'parquet');

-- Update the source table
UPDATE documents d
SET embedding = e.embedding
FROM embedding_import e
WHERE d.id = e.id;

aside positive With pg_lake stages, you can script the entire embedding pipeline without any external infrastructure. Postgres exports text, Snowflake generates embeddings with Cortex, and the vectors flow right back — no S3 buckets, no custom ETL code, no third-party orchestrators.

Syncing Embeddings with Shared Iceberg

-- In Postgres: create an Iceberg table with your content
CREATE TABLE documents_iceberg (
  id      int,
  content text
) USING iceberg;

-- In Snowflake: read the table, generate embeddings, and write back via stage
CREATE OR REPLACE ICEBERG TABLE my_iceberg_docs
  CATALOG = 'my_postgres_catalog'
  CATALOG_TABLE_NAME = 'documents_iceberg';

-- Generate embeddings from the synced data
SELECT id, AI_EMBED('snowflake-arctic-embed-l-v2.0', content) AS embedding
FROM my_iceberg_docs;

Embedding Queries at Runtime

# Python example using Snowflake connector + psycopg
import snowflake.connector
import psycopg

# Generate query embedding via Cortex
sf_conn = snowflake.connector.connect(...)
cursor = sf_conn.cursor()
cursor.execute("""
    SELECT AI_EMBED('snowflake-arctic-embed-l-v2.0', %s)::ARRAY
""", (user_question,))
query_embedding = cursor.fetchone()[0]

# Search Postgres with the embedding
pg_conn = psycopg.connect(...)
results = pg_conn.execute("""
    SELECT content, 1 - (embedding <=> %s::vector) AS similarity
    FROM chunks
    ORDER BY embedding <=> %s::vector
    LIMIT 5
""", (query_embedding, query_embedding)).fetchall()

Cortex AI Functions

Cortex Search

Integration with Postgres

aside positive Using Snowflake Cortex for embeddings means you don't need to provision GPU infrastructure, manage model versions, or handle scaling. Generate embeddings at Snowflake scale, then serve them from Postgres at application speed.

Conclusion

Related Resources

---

---

---

---