OrochiDB Documentation

-- Configure tiered storage policy
SELECT set_tiered_storage_policy(
  'events',
  hot_to_warm_after => INTERVAL '7 days',
  warm_to_cold_after => INTERVAL '30 days',
  cold_to_frozen_after => INTERVAL '90 days'
);

-- Hot tier: NVMe SSD (fastest, most expensive)
-- Warm tier: SSD (balanced performance/cost)
-- Cold tier: HDD (cost-effective storage)
-- Frozen tier: S3/Object storage (lowest cost)

-- View tier distribution
SELECT * FROM orochi_tier_stats('events');

-- Query across all tiers transparently
SELECT * FROM events
WHERE created_at > NOW() - INTERVAL '1 year';

-- Create table with vector column
CREATE TABLE documents (
  id SERIAL PRIMARY KEY,
  title TEXT,
  content TEXT,
  embedding vector(1536)  -- OpenAI ada-002 dimensions
);

-- Create HNSW index for fast similarity search
CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- Insert embeddings from your AI model
INSERT INTO documents (title, content, embedding)
VALUES ('My Document', 'Content here...', '[0.1, 0.2, ...]');

-- Find similar documents
SELECT title, content,
       1 - (embedding <=> query_embedding) AS similarity
FROM documents
ORDER BY embedding <=> query_embedding
LIMIT 10;

-- Hybrid search with filters
SELECT * FROM documents
WHERE category = 'tech'
ORDER BY embedding <=> query_embedding
LIMIT 10;

-- Create CDC subscription
SELECT create_cdc_subscription(
  'orders_to_kafka',
  source_tables => ARRAY['orders', 'order_items'],
  destination => 'kafka://broker:9092/orders-topic',
  format => 'avro'
);

-- Configure CDC options
ALTER SUBSCRIPTION orders_to_kafka SET (
  include_transaction_ids => true,
  include_timestamps => true,
  batch_size => 1000
);

-- Monitor CDC lag
SELECT * FROM cdc_subscription_stats('orders_to_kafka');

-- Pause/resume subscription
SELECT pause_cdc_subscription('orders_to_kafka');
SELECT resume_cdc_subscription('orders_to_kafka');

-- Create Kafka ingestion pipeline
SELECT create_pipeline(
  'kafka_events',
  source => 'kafka://broker:9092/events',
  target_table => 'events',
  format => 'json',
  transform => $$
    SELECT
      data->>'id' AS id,
      data->>'type' AS event_type,
      (data->>'timestamp')::timestamptz AS created_at
    FROM source_data
  $$
);

-- Create S3 ingestion pipeline
SELECT create_pipeline(
  's3_logs',
  source => 's3://my-bucket/logs/*.parquet',
  target_table => 'access_logs',
  format => 'parquet',
  schedule => 'every 1 hour'
);

-- Monitor pipeline status
SELECT * FROM pipeline_stats();