Module 15 — The Extensions Ecosystem: pg_cron, TimescaleDB, Citus, and pgvector

Data Retention

-- Automatically drop chunks older than 90 days
SELECT add_retention_policy('metrics', INTERVAL '90 days');

TimescaleDB vs Plain Postgres Partitioning

TimescaleDB is the right choice when: you have high-volume time-series data (metrics, events, IoT) that needs compression and fast aggregate queries, and you want to stay on Postgres rather than migrating to InfluxDB or ClickHouse.

---

Citus: Horizontal Sharding for Postgres

Citus distributes a Postgres database across multiple nodes — sharding both data and query execution. It is built by Microsoft and available as an open-source extension or managed service (Azure Cosmos DB for PostgreSQL).

The Citus Architecture

Coordinator node:
  - Accepts all SQL queries
  - Parses and plans queries
  - Routes data operations to the correct worker(s)
  - Aggregates results from workers
 
Worker nodes (N nodes):
  - Store shards of distributed tables
  - Execute fragments of queries pushed down from coordinator
  - Each shard is a regular Postgres table on the worker

Distribution Key Selection

-- On the coordinator: distribute transactions by sender address
SELECT create_distributed_table('transactions', 'sender');
 
-- Now:
-- Rows with the same sender are colocated on the same shard
-- Queries filtering by sender go to exactly one worker
-- Aggregations by sender are pushed down to workers, results aggregated on coordinator

The distribution key is the most critical decision. Choose it so:

1. Frequently joined tables are colocated — if transactions and blocks are joined frequently, distribute both by a compatible key
2. Hot queries filter on it — WHERE sender = '0xabc...' goes to one shard, not all shards
3. Data is evenly distributed — high-cardinality columns like UUIDs or blockchain addresses

Reference Tables

Some tables are small and frequently joined — distribute them to all workers:

-- Reference table: replicated to every worker node
SELECT create_reference_table('status_codes');
-- Every worker has a full copy — joins with reference tables are local

When Citus Is the Right Answer

Citus is appropriate when:

• Write throughput exceeds what a single Postgres instance handles (~50,000+ TPS)
• Data volume exceeds what a single server stores efficiently (10+ TB)
• Most queries filter on the distribution key — if queries frequently scan all shards, sharding adds overhead without benefit

For most applications below these thresholds, a single Postgres primary with read replicas is simpler and sufficient.

---

pgvector: Vector Embeddings for AI Workloads

pgvector adds a vector data type and vector similarity search indexes to Postgres. It is the extension that makes Postgres competitive with dedicated vector databases like Pinecone, Weaviate, and Chroma for AI/RAG (Retrieval-Augmented Generation) workloads.

What Vector Search Is

An embedding is a fixed-size array of floating-point numbers that represents the semantic meaning of text, an image, or any other content. Embeddings with similar meaning have small vector distances. Searching for the most similar embeddings is the core operation in:

• RAG (Retrieval-Augmented Generation): find documents semantically similar to a query, feed them to an LLM as context
• Semantic search: return results by meaning, not keyword matching
• Recommendation systems: find items similar to what a user engaged with
• Duplicate detection: find near-duplicate content by vector proximity

Installation and Schema

CREATE EXTENSION vector;
 
-- Store 1536-dimensional embeddings (OpenAI ada-002 output size)
CREATE TABLE documents (
  id          BIGSERIAL PRIMARY KEY,
  content     TEXT NOT NULL,
  embedding   vector(1536),
  metadata    JSONB,
  created_at  TIMESTAMPTZ DEFAULT now()
);

Inserting Embeddings

import openai
import psycopg2
 
# Generate embedding from OpenAI
response = openai.embeddings.create(
    input="PostgreSQL internals for production engineers",
    model="text-embedding-ada-002"
)
embedding = response.data[0].embedding  # list of 1536 floats
 
# Insert into Postgres
cur.execute(
    "INSERT INTO documents (content, embedding) VALUES (%s, %s)",
    ("PostgreSQL internals...", embedding)
)

Vector Similarity Search: Exact vs Approximate

Exact search (no index — scans all vectors):

-- Find the 5 most similar documents to a query embedding
SELECT id, content,
  embedding <-> '[0.1, 0.2, ...]'::vector AS distance
FROM documents
ORDER BY embedding <-> '[0.1, 0.2, ...]'::vector
LIMIT 5;

Operators:

• <-> — L2 (Euclidean) distance
• <#> — negative inner product (for dot product similarity)
• <=> — cosine distance (most common for text embeddings)

Exact search is O(N) — every vector is compared. For up to ~100K vectors, this is fast enough. Above that, you need an approximate nearest-neighbor (ANN) index.

IVFFlat Index

IVFFlat (Inverted File Flat) divides vectors into lists clusters. A query searches only the nearest N clusters instead of all vectors.

-- Build IVFFlat index: 100 lists for ~1M vectors
-- pgvector's documented rule of thumb: for row counts UNDER 1M, lists ≈ rows/1000;
-- for row counts OVER 1M, lists ≈ sqrt(row_count). Below 1M rows, sqrt()
-- under-shoots the list count pgvector actually recommends.
CREATE INDEX ON documents USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);
 
-- Query with probe count: search nearest probes clusters
-- Higher probes = better recall, slower query
SET ivfflat.probes = 10;  -- search 10 of 100 clusters
 
SELECT id, content, embedding <=> $1 AS distance
FROM documents
ORDER BY embedding <=> $1
LIMIT 5;

Recall vs speed trade-off:

• probes = 1: fastest, lowest recall (~70% of exact results)
• probes = 10: balanced (~90% recall)
• probes = lists: exact search (same as no index)

Build IVFFlat only after the table has data — the index quality depends on the data distribution at build time. Rebuild if data distribution changes significantly.

HNSW Index (Recommended)

HNSW (Hierarchical Navigable Small World) builds a graph-based index with better recall/speed trade-offs than IVFFlat for most workloads.

-- Build HNSW index
-- m: number of connections per node (higher = better recall, larger index)
-- ef_construction: search width during build (higher = better index, slower build)
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
 
-- Query: ef_search controls recall at query time (higher = better recall, slower)
SET hnsw.ef_search = 100;
 
SELECT id, content, embedding <=> $1 AS distance
FROM documents
ORDER BY embedding <=> $1
LIMIT 5;

HNSW vs IVFFlat:

Hybrid Search: Vector + SQL Filters

The real power of pgvector in Postgres: combine semantic vector search with SQL predicates in one query:

-- Find the 5 most similar documents to a query embedding
-- but only from the 'technical' category published in 2026
SELECT id, content, embedding <=> $1 AS distance
FROM documents
WHERE category = 'technical'
  AND created_at >= '2026-01-01'
ORDER BY embedding <=> $1
LIMIT 5;

A dedicated vector database requires an additional filter step after the ANN search — often requiring you to fetch more candidates than needed and filter in the application. Postgres does this in a single query with the planner's cost model deciding the optimal execution order.

pgvector vs Dedicated Vector Databases

For most RAG and semantic search applications below 100M vectors: pgvector in Postgres eliminates the need for a dedicated vector database entirely. Your embeddings, metadata, and business data live in one system with consistent transactions.

---

Summary

The principle: before adding a new database to your infrastructure, check if a Postgres extension solves the problem. The operational cost of one well-understood Postgres cluster is almost always lower than operating two specialized systems.

---