Hanzo Database - PostgreSQL, Redis & Vector Storage

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

-- Create embeddings table
CREATE TABLE embeddings (
    id SERIAL PRIMARY KEY,
    content TEXT NOT NULL,
    embedding vector(1536),       -- OpenAI ada-002 dimensions
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- IVFFlat index (fast approximate search)
CREATE INDEX ON embeddings
    USING ivfflat (embedding vector_cosine_ops)
    WITH (lists = 100);

-- HNSW index (better recall, more memory)
CREATE INDEX ON embeddings
    USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

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

-- L2 distance
SELECT content, embedding <-> $1::vector AS distance
FROM embeddings
ORDER BY embedding <-> $1::vector
LIMIT 5;

-- Inner product
SELECT content, embedding <#> $1::vector AS score
FROM embeddings
ORDER BY embedding <#> $1::vector
LIMIT 5;

-- With metadata filter
SELECT content, 1 - (embedding <=> $1::vector) AS similarity
FROM embeddings
WHERE metadata->>'source' = 'docs'
ORDER BY embedding <=> $1::vector
LIMIT 5;

import psycopg2
from pgvector.psycopg2 import register_vector
import numpy as np

conn = psycopg2.connect(os.environ["DATABASE_URL"])
register_vector(conn)
cur = conn.cursor()

# Insert embedding
embedding = np.random.rand(1536).astype(np.float32)  # Your actual embedding
cur.execute(
    "INSERT INTO embeddings (content, embedding, metadata) VALUES (%s, %s, %s)",
    ("Hello world", embedding, '{"source": "docs"}')
)

# Similarity search
query_vec = np.random.rand(1536).astype(np.float32)  # Your query embedding
cur.execute(
    "SELECT content, 1 - (embedding <=> %s) AS similarity "
    "FROM embeddings ORDER BY embedding <=> %s LIMIT 5",
    (query_vec, query_vec)
)
for row in cur.fetchall():
    print(f"{row[0]}: {row[1]:.3f}")

conn.commit()

import (
    "github.com/jackc/pgx/v5"
    "github.com/pgvector/pgvector-go"
)

conn, _ := pgx.Connect(ctx, os.Getenv("DATABASE_URL"))

// Insert
embedding := pgvector.NewVector(floats)
conn.Exec(ctx,
    "INSERT INTO embeddings (content, embedding) VALUES ($1, $2)",
    "Hello world", embedding,
)

// Search
rows, _ := conn.Query(ctx,
    "SELECT content, 1 - (embedding <=> $1) AS similarity "+
    "FROM embeddings ORDER BY embedding <=> $1 LIMIT 5",
    pgvector.NewVector(queryVec),
)

# Connection
REDIS_URL=redis://redis.hanzo.svc:6379

# From K8s pod
redis-cli -h redis.hanzo.svc

import redis

r = redis.from_url(os.environ["REDIS_URL"])

# Cache
r.setex("key", 3600, "value")  # 1 hour TTL
value = r.get("key")

# Session
r.hset(f"session:{session_id}", mapping={"user_id": "123", "role": "admin"})

# Rate limiting
key = f"ratelimit:{user_id}:{minute}"
count = r.incr(key)
r.expire(key, 60)
if count > 100:
    raise RateLimitExceeded()

# Job queue (BullMQ pattern)
r.xadd("jobs:inference", {"model": "zen-70b", "prompt": "Hello"})

services:
  postgres:
    image: postgres:16
    ports:
      - "5432:5432"
    environment:
      POSTGRES_DB: hanzo
      POSTGRES_USER: hanzo
      POSTGRES_PASSWORD: "${DB_PASSWORD}"
    volumes:
      - postgres_data:/var/lib/postgresql/data
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql

  redis:
    image: redis:7-alpine
    ports:
      - "6379:6379"
    volumes:
      - redis_data:/data

  mongodb:
    image: mongo:7
    ports:
      - "27017:27017"
    environment:
      MONGO_INITDB_ROOT_USERNAME: hanzo
      MONGO_INITDB_ROOT_PASSWORD: "${MONGO_PASSWORD}"
    volumes:
      - mongo_data:/data/db

  minio:
    image: minio/minio
    ports:
      - "9000:9000"
      - "9001:9001"
    environment:
      MINIO_ROOT_USER: hanzo
      MINIO_ROOT_PASSWORD: "${MINIO_PASSWORD}"
    command: server /data --console-address ":9001"
    volumes:
      - minio_data:/data

volumes:
  postgres_data:
  redis_data:
  mongo_data:
  minio_data:

# Port conflicts
lsof -i :5432

# Full reset (destructive — loses all data)
docker compose down -v && docker system prune -a

# Connect to prod DB (from K8s pod)
kubectl exec -it postgres-0 -n hanzo -- psql -U hanzo