Documentation

"color:#64748b"># Docker (recommended)
docker run -d --name synergydb \
  -p 5432:5432 \
  -p 3306:3306 \
  -p 27017:27017 \
  -p 6379:6379 \
  -p 7687:7687 \
  -p 8080:8080 \
  -p 9090:9090 \
  synergytechnologies/synergydb:latest

docker run -d --name synergydb \
  -v /etc/synergydb/config.yaml:/etc/synergydb/config.yaml \
  -p 5432:5432 \
  -p 3306:3306 \
  -p 27017:27017 \
  -p 6379:6379 \
  -p 7687:7687 \
  -p 8080:8080 \
  -p 9090:9090 \
  synergytechnologies/synergydb:latest

"color:#64748b"># Binary
curl -fsSL https:"color:#64748b">//get.synergydb.io | sh
synergydb start --config /etc/synergydb/config.yaml

"color:#64748b"># macOS
brew install synergytechnologies/tap/synergydb

"color:#64748b"># Debian / Ubuntu
curl -fsSL https:"color:#64748b">//repo.synergydb.io/gpg | sudo gpg --dearmor -o /usr/share/keyrings/synergydb.gpg
echo "deb [signed-by=/usr/share/keyrings/synergydb.gpg] https:">//repo.synergydb.io/apt stable main" \
  | sudo tee /etc/apt/sources.list.d/synergydb.list
sudo apt update && sudo apt install synergydb

"color:#64748b"># RHEL / Fedora
sudo dnf install -y https:"color:#64748b">//repo.synergydb.io/rpm/synergydb-latest-1.x86_64.rpm

"color:#64748b"># SynergyDB Configuration
"color:#64748b"># /etc/synergydb/config.yaml

cluster:
  node_id: node-1
  advertise_addr: 10.0.1.10:9400
  peers:
    - 10.0.1.11:9400
    - 10.0.1.12:9400

"color:#64748b"># Protocol listeners
protocols:
  postgresql:
    enabled: true
    host: 0.0.0.0
    port: 5432
    max_connections: 500
    ssl: true
    ssl_cert: /etc/synergydb/certs/server.crt
    ssl_key: /etc/synergydb/certs/server.key
  mysql:
    enabled: true
    host: 0.0.0.0
    port: 3306
    max_connections: 500
    ssl: true
  mongodb:
    enabled: true
    host: 0.0.0.0
    port: 27017
    max_connections: 1000
    ssl: true
  redis:
    enabled: true
    host: 0.0.0.0
    port: 6379
    max_connections: 10000
    requirepass: ''
  bolt:
    enabled: true
    host: 0.0.0.0
    port: 7687
    max_connections: 500
  http:
    enabled: true
    host: 0.0.0.0
    port: 8080
    max_connections: 1000
  grpc:
    enabled: true
    host: 0.0.0.0
    port: 9090
    max_connections: 1000

"color:#64748b"># Vector index
vector:
  default_index: hnsw
  hot_threshold_secs: 3600
  warm_threshold_secs: 604800

"color:#64748b"># Storage engine
storage:
  engine: synergy  "color:#64748b"># synergy | rocksdb | hybrid
  data_dir: /var/lib/synergydb/data
  wal_dir: /var/lib/synergydb/wal
  max_wal_size: 4GB
  compaction_style: leveled
  bloom_filter_bits: 10
  block_cache_size: 8GB
  write_buffer_size: 256MB
  max_write_buffer_number: 4

"color:#64748b"># Replication
replication:
  mode: synchronous  "color:#64748b"># synchronous | asynchronous | quorum
  quorum_size: 2
  wal_shipping: true
  snapshot_interval: 6h
  max_lag_bytes: 67108864  "color:#64748b"># 64MB

"color:#64748b"># Security
security:
  authentication: scram-sha-256  "color:#64748b"># scram-sha-256 | ldap | oidc
  encryption_at_rest: true
  encryption_algorithm: AES-256-GCM
  master_key_provider: vault  "color:#64748b"># vault | aws-kms | gcp-kms | local
  vault_addr: https:"color:#64748b">//vault.internal:8200
  vault_path: secret/synergydb/master-key
  audit_log:
    enabled: true
    path: /var/log/synergydb/audit.log
    format: json
    include_queries: true
  tls:
    min_version: '1.3'
    cipher_suites:
      - TLS_AES_256_GCM_SHA384
      - TLS_CHACHA20_POLY1305_SHA256

"color:#64748b"># Observability
telemetry:
  metrics:
    enabled: true
    endpoint: /metrics
    port: 9090
  tracing:
    enabled: true
    exporter: otlp
    endpoint: http:"color:#64748b">//otel-collector:4317
  logging:
    level: info  "color:#64748b"># debug | info | warn | error
    format: json
    output: /var/log/synergydb/synergydb.log

psql -h localhost -p 5432 -U admin -d synergydb

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO users (name, email) VALUES
  ('Alice Chen', 'alice@example.com'),
  ('Bob Martinez', 'bob@example.com'),
  ('Carol Johnson', 'carol@example.com');

SELECT * FROM users WHERE name LIKE 'A%';

mysql -h 127.0.0.1 -P 3306 -u admin -p synergydb

-- Query the same table created via PostgreSQL
SELECT id, name, email FROM users ORDER BY created_at;

-- MySQL-specific syntax works natively
INSERT INTO users (name, email) VALUES ('Dave Kim', 'dave@example.com');

SELECT COUNT(*) AS total_users FROM users;

mongosh "mongodb:">//admin:password@localhost:27017/synergydb"

"color:#64748b">// The "users" table is accessible as a collection
db.users.find({ name: /^A/ })

"color:#64748b">// Insert a document -- it appears as a row in SQL protocols
db.users.insertOne({
  name: "Eve Park",
  email: "eve@example.com",
  created_at: new Date()
})

"color:#64748b">// Aggregation pipeline
db.users.aggregate([
  { $group: { _id: null, count: { $sum: 1 } } }
])

redis-cli -h localhost -p 6379

"color:#64748b"># Access user records as Redis hashes
HGETALL users:1

"color:#64748b"># Set a cache value
SET session:abc123 '{"user_id":1,"role":"admin"}' EX 3600

"color:#64748b"># Query keys by pattern
KEYS users:*

"color:#64748b"># Use Redis data structures alongside SQL tables
LPUSH notifications '{"type":"welcome","user_id":5}'
LRANGE notifications 0 -1

postgresql:"color:#64748b">//user:password@host:5432/database?sslmode=require

"color:#64748b"># With all parameters
postgresql:"color:#64748b">//admin:s3cureP4ss@synergydb.example.com:5432/myapp?sslmode=verify-full&sslrootcert=/path/to/ca.crt&connect_timeout=10&application_name=myservice

"color:#64748b">// Prisma schema for SynergyDB (PostgreSQL protocol)
datasource db {
  provider = "postgresql"
  url      = "postgresql:">//admin:password@localhost:5432/myapp"
}

generator client {
  provider = "prisma-client-js"
}

"color:#64748b">// Models work exactly as with native PostgreSQL
"color:#64748b">// SynergyDB handles schema introspection and migrations

mysql:"color:#64748b">//user:password@host:3306/database

"color:#64748b"># DSN format (Go, PHP)
user:password@tcp(host:3306)/database?tls=true&parseTime=true&multiStatements=true

"color:#64748b"># JDBC
jdbc:mysql:"color:#64748b">//host:3306/database?useSSL=true&serverTimezone=UTC

mongodb:"color:#64748b">//user:password@host:27017/database

"color:#64748b"># Replica set (SynergyDB cluster)
mongodb:"color:#64748b">//user:password@node1:27017,node2:27017,node3:27017/database?replicaSet=synergy-rs&authSource=admin

"color:#64748b"># With TLS
mongodb:"color:#64748b">//user:password@host:27017/database?tls=true&tlsCAFile=/path/to/ca.pem

"color:#64748b">// Node.js MongoDB driver -- connects to SynergyDB without changes
const { MongoClient } = require('mongodb')

const client = new MongoClient('mongodb:"color:#64748b">//admin:password@localhost:27017')
await client.connect()

const db = client.db('myapp')
const users = db.collection('users')

"color:#64748b">// Aggregation pipeline with $lookup (joins across collections)
const results = await users.aggregate([
  { $match: { status: 'active' } },
  { $lookup: {
      from: 'orders',
      localField: '_id',
      foreignField: 'user_id',
      as: 'orders'
  }},
  { $addFields: { order_count: { $size: '$orders' } } },
  { $sort: { order_count: -1 } },
  { $limit: 10 }
]).toArray()

"color:#64748b">// Change streams for real-time updates
const changeStream = users.watch()
changeStream.on('change', (change) => {
  console.log('Change detected:', change.operationType)
})

redis:"color:#64748b">//user:password@host:6379/0

"color:#64748b"># With TLS
rediss:"color:#64748b">//user:password@host:6379/0

"color:#64748b"># Cluster mode (SynergyDB automatically handles slot distribution)
redis:"color:#64748b">//host:6379?cluster=true

bolt:"color:#64748b">//user:password@host:7687

"color:#64748b"># With TLS
bolt+s:"color:#64748b">//user:password@host:7687

"color:#64748b"># Neo4j URI scheme (also supported)
neo4j:"color:#64748b">//user:password@host:7687

"color:#64748b">// Find direct connections
SELECT * FROM GRAPH_MATCH(
    (person:User {name: 'Alice'})-[:FOLLOWS]->(friend:User)
);

"color:#64748b">// Friends of friends (2 hops)
SELECT * FROM GRAPH_MATCH(
    (person:User {name: 'Alice'})-[:FOLLOWS*2]->(fof:User)
);

"color:#64748b">// Variable-length paths (1 to 5 hops)
SELECT * FROM GRAPH_MATCH(
    (a:User)-[:FOLLOWS*1..5]->(b:User)
)
WHERE a.id = 1;

"color:#64748b">// Shortest path
SELECT * FROM SHORTEST_PATH(
    (start:User {id: 1}),
    (end:User {id: 100}),
    [:FOLLOWS]
);

"color:#64748b"># Python with neo4j driver
from neo4j import GraphDatabase

driver = GraphDatabase.driver("bolt:">//localhost:7687", auth=("admin", "password"))

with driver.session() as session:
    result = session.run("""
        MATCH (u:User {name: $name})-[:FOLLOWS]->(friend)
        RETURN friend.name AS name, friend.email AS email
    """, name="Alice")

    for record in result:
        print(f"{record['name']}: {record['email']}")

http:"color:#64748b">//localhost:8080/api/v1

curl -X POST http:"color:#64748b">//localhost:8080/api/v1/query \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer $API_KEY" \
  -d '{
    "query": "SELECT id, name FROM users WHERE created_at > $1",
    "params": ["2024-01-01"],
    "language": "sql"
  }'

"color:#64748b"># Python gRPC client example
import grpc
from synergydb.v1 import synergydb_pb2, synergydb_pb2_grpc

channel = grpc.insecure_channel('localhost:9090')
stub = synergydb_pb2_grpc.SynergyDBStub(channel)

"color:#64748b"># Execute a SQL query
response = stub.Query(synergydb_pb2.QueryRequest(
    query="SELECT * FROM users LIMIT 10",
    language="sql"
))

"color:#64748b"># Vector similarity search
results = stub.VectorSearch(synergydb_pb2.VectorSearchRequest(
    table="documents",
    column="embedding",
    vector=[0.1, 0.2, 0.3],
    top_k=10,
    metric="cosine"
))

-- Table with vector column for embeddings
CREATE TABLE documents (
    id INTEGER PRIMARY KEY,
    title TEXT,
    content TEXT,
    embedding VECTOR(1536)  -- OpenAI embedding dimension
);

-- Create HNSW index with custom parameters
CREATE INDEX idx_docs_embedding ON documents
    USING HNSW (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 200);

-- Parameters:
-- m: Number of connections per layer (default: 16)
-- ef_construction: Search width during construction (default: 200)
-- Higher values = better recall, slower insert

-- L2 distance search using <-> operator
SELECT id, title, embedding <-> '[0.1, 0.2, ...]' AS distance
FROM documents
ORDER BY embedding <-> '[0.1, 0.2, ...]'
LIMIT 10;

-- Cosine similarity search
SELECT id, title
FROM documents
ORDER BY COSINE_DISTANCE(embedding, '[0.1, 0.2, ...]')
LIMIT 10;

-- Hybrid search (vector + SQL filters)
SELECT id, title
FROM documents
WHERE category = 'tech'
  AND created_at > '2024-01-01'
ORDER BY embedding <-> $1
LIMIT 10;

-- Vector with metadata filtering
SELECT id, title, embedding <-> $1 AS score
FROM documents
WHERE tags @> ARRAY['ai', 'ml']
ORDER BY score
LIMIT 10;

-- Find direct connections
SELECT * FROM GRAPH_MATCH(
    (person:User {name: 'Alice'})-[:FOLLOWS]->(friend:User)
);

-- Friends of friends
SELECT * FROM GRAPH_MATCH(
    (person:User {name: 'Alice'})-[:FOLLOWS*2]->(fof:User)
);

-- Variable-length paths (1 to 5 hops)
SELECT * FROM GRAPH_MATCH(
    (a:User)-[:FOLLOWS*1..5]->(b:User)
)
WHERE a.id = 1;

-- Shortest path between two nodes
SELECT * FROM SHORTEST_PATH(
    (start:User {id: 1}),
    (end:User {id: 100}),
    [:FOLLOWS]
);

-- Create a graph index for relationship traversal
CREATE INDEX idx_follows ON follows
    USING GRAPH (from_user, to_user);

-- The graph index enables efficient BFS/DFS traversal
-- and shortest path computation across millions of edges

-- Create a full-text search index
CREATE INDEX idx_docs_content ON documents
    USING FULLTEXT (content);

-- Basic full-text search
SELECT id, title, content
FROM documents
WHERE MATCH(content, 'machine learning');

-- Full-text with BM25 ranking
SELECT id, title, SCORE() as relevance
FROM documents
WHERE MATCH(content, 'neural networks')
ORDER BY relevance DESC
LIMIT 10;

-- Phrase search
SELECT * FROM documents
WHERE MATCH(content, '"deep learning"');

-- Fuzzy search (allow 2 character edits)
SELECT * FROM documents
WHERE MATCH(content, 'neurl~2');

-- Boolean operators
SELECT * FROM documents
WHERE MATCH(content, 'machine AND learning NOT python');

-- Cross-model query: combine graph traversal with vector similarity
SELECT
    friend.name,
    doc.title,
    COSINE_DISTANCE(doc.embedding, $query_vector) AS similarity
FROM GRAPH_MATCH(
    (me:User {id: $user_id})-[:FOLLOWS]->(friend:User)
)
JOIN documents doc ON doc.author_id = friend.id
WHERE MATCH(doc.content, 'machine learning')
ORDER BY similarity ASC
LIMIT 10;

-- Query that spans relational + document + vector
SELECT
    u.name,
    u.metadata->>'department' AS dept,
    d.title,
    d.embedding <-> $query AS score
FROM users u
JOIN documents d ON d.author_id = u.id
WHERE u.metadata @> '{"role": "researcher"}'
ORDER BY score
LIMIT 5;

"color:#64748b"># Set your API key for the embedding provider
export OPENAI_API_KEY="sk-..."

"color:#64748b"># Start SynergyDB -- it will automatically use OpenAI for embeddings
synergydb start --config /etc/synergydb/config.yaml

curl -X POST http:"color:#64748b">//localhost:8080/v1/embeddings \
  -H "Content-Type: application/json" \
  -d '{
    "input": "Your text here",
    "model": "text-embedding-3-small"
  }'

from langchain_openai import OpenAIEmbeddings
from langchain_community.vectorstores import PGVector

"color:#64748b"># Use SynergyDB as the embedding provider
embeddings = OpenAIEmbeddings(
    model="text-embedding-3-small",
    openai_api_base="http:">//localhost:8080/v1",
    openai_api_key="unused"  "color:#64748b"># Not validated by SynergyDB
)

"color:#64748b"># Use SynergyDB as the vector store (PostgreSQL protocol)
vectorstore = PGVector(
    connection_string="postgresql:">//admin:password@localhost:5432/myapp",
    embedding_function=embeddings,
    collection_name="documents"
)

"color:#64748b"># Similarity search
results = vectorstore.similarity_search("machine learning papers", k=5)

from llama_index.core import VectorStoreIndex, SimpleDirectoryReader
from llama_index.vector_stores.postgres import PGVectorStore

"color:#64748b"># Connect to SynergyDB via PostgreSQL protocol
vector_store = PGVectorStore.from_params(
    host="localhost",
    port="5432",
    user="admin",
    password="password",
    database="myapp",
    table_name="documents",
    embed_dim=1536
)

"color:#64748b"># Load and index documents
documents = SimpleDirectoryReader("./data").load_data()
index = VectorStoreIndex.from_documents(
    documents, vector_store=vector_store
)

"color:#64748b"># Query
query_engine = index.as_query_engine()
response = query_engine.query("What are the key findings?")

migration:
  source:
    type: postgresql
    host: source-pg.example.com
    port: 5432
    database: production
    user: replication_user
    password: {PGPASSWORD}
    ssl_mode: require
  target:
    database: production
  options:
    include_schemas:
      - public
      - app
    exclude_tables:
      - pg_stat_*
      - _prisma_migrations
    parallel_workers: 4
    batch_size: 10000

"color:#64748b"># Start the migration (initial snapshot + continuous replication)
synergydb migrate start --config migration-pg.yaml

"color:#64748b"># Monitor progress
synergydb migrate status --migration-id pg-20260210-001

"color:#64748b"># Run row-count and checksum validation across all tables
synergydb migrate validate --migration-id pg-20260210-001

"color:#64748b"># Sample output:
"color:#64748b"># Table              Source Rows  Target Rows  Checksum Match
"color:#64748b"># public.users       1,284,531    1,284,531    OK
"color:#64748b"># public.orders      8,412,003    8,412,003    OK
"color:#64748b"># public.products    42,891       42,891       OK
"color:#64748b"># app.sessions       312,445      312,445      OK
"color:#64748b"># Validation PASSED: 4/4 tables consistent

"color:#64748b"># Point read traffic to SynergyDB first (canary)
"color:#64748b"># Update your load balancer or DNS to route 10% of reads

"color:#64748b"># Once validated, route all reads
"color:#64748b"># Finally, redirect writes and stop replication
synergydb migrate cutover --migration-id pg-20260210-001

"color:#64748b"># Finalize the migration (cleans up replication slot on source)
synergydb migrate finalize --migration-id pg-20260210-001

"color:#64748b"># Verify no remaining lag
synergydb migrate status --migration-id pg-20260210-001
"color:#64748b"># Status: COMPLETED

migration:
  source:
    type: mysql
    host: source-mysql.example.com
    port: 3306
    database: production
    user: replication_user
    password: {MYSQL_PASSWORD}
    ssl_mode: required
  target:
    database: production
  options:
    include_databases:
      - production
    exclude_tables:
      - _migrations
      - sessions
    parallel_workers: 4
    batch_size: 10000
    charset: utf8mb4

synergydb migrate start --config migration-mysql.yaml

"color:#64748b"># The engine reads the binlog from the current GTID position
synergydb migrate status --migration-id mysql-20260210-001

synergydb migrate validate --migration-id mysql-20260210-001

"color:#64748b"># Validates row counts, checksums, and schema compatibility
"color:#64748b"># Reports any data type mapping differences

synergydb migrate cutover --migration-id mysql-20260210-001

synergydb migrate finalize --migration-id mysql-20260210-001

migration:
  source:
    type: mongodb
    uri: mongodb:"color:#64748b">//repl_user:password@mongo1:27017,mongo2:27017,mongo3:27017/?replicaSet=rs0&authSource=admin
  target:
    database: production
  options:
    include_databases:
      - production
      - analytics
    exclude_collections:
      - system.profile
      - tmp_*
    parallel_workers: 8
    batch_size: 5000
    preserve_ids: true  "color:#64748b"># Keep original _id values

synergydb migrate start --config migration-mongo.yaml

"color:#64748b"># Uses change streams for continuous replication
synergydb migrate status --migration-id mongo-20260210-001

synergydb migrate validate --migration-id mongo-20260210-001

"color:#64748b"># Validates document counts, samples documents for field-level comparison,
"color:#64748b"># and verifies all indexes have been recreated

synergydb migrate cutover --migration-id mongo-20260210-001

synergydb migrate finalize --migration-id mongo-20260210-001

"color:#64748b"># Closes the change stream cursor and marks migration complete

migration:
  source:
    type: sqlite
    path: /path/to/database.sqlite
  target:
    database: production
  options:
    include_tables:
      - users
      - orders
      - products
    parallel_workers: 4
    batch_size: 10000

"color:#64748b"># Import all tables from SQLite
synergydb migrate start --config migration-sqlite.yaml

"color:#64748b"># Monitor progress
synergydb migrate status --migration-id sqlite-20260210-001

synergydb migrate validate --migration-id sqlite-20260210-001

"color:#64748b"># Validates row counts and schema mapping
"color:#64748b"># SQLite types are mapped to SynergyDB types automatically:
"color:#64748b"># INTEGER -> INTEGER, TEXT -> TEXT, REAL -> FLOAT, BLOB -> BYTES

security:
  encryption_at_rest: true
  encryption_algorithm: AES-256-GCM
  master_key_provider: vault
  vault_addr: https:"color:#64748b">//vault.internal:8200
  vault_path: secret/synergydb/master-key
  key_rotation_interval: 90d  "color:#64748b"># Rotate DEK every 90 days

security:
  tls:
    min_version: '1.3'
    cipher_suites:
      - TLS_AES_256_GCM_SHA384
      - TLS_CHACHA20_POLY1305_SHA256
    cert_file: /etc/synergydb/certs/server.crt
    key_file: /etc/synergydb/certs/server.key
    ca_file: /etc/synergydb/certs/ca.crt
    client_auth: require  "color:#64748b"># mutual TLS

-- Enable row-level security on the orders table
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Users can only see their own orders
CREATE POLICY user_isolation ON orders
  USING (user_id = current_user_id());

-- Admins can see all orders
CREATE POLICY admin_access ON orders
  TO admin_role
  USING (true);

-- The policy applies to all protocols:
-- PostgreSQL: SELECT * FROM orders (filtered by policy)
-- MySQL: SELECT * FROM orders (filtered by policy)
-- MongoDB: db.orders.find() (filtered by policy)
-- Redis: HGETALL orders:* (filtered by policy)

{
  "timestamp": "2026-02-10T14:32:18.445Z",
  "event_type": "query",
  "user": "app_service",
  "source_ip": "10.0.2.15",
  "protocol": "postgresql",
  "database": "production",
  "query": "SELECT * FROM users WHERE id = $1",
  "params": ["42"],
  "rows_affected": 1,
  "duration_ms": 0.82,
  "status": "success"
}

-- Create roles
CREATE ROLE readonly;
CREATE ROLE readwrite;
CREATE ROLE admin_role;

-- Grant permissions
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO readwrite;
GRANT ALL PRIVILEGES ON DATABASE production TO admin_role;

-- Assign users to roles
GRANT readonly TO analyst_user;
GRANT readwrite TO app_service;
GRANT admin_role TO dba_user;

-- Column-level security
GRANT SELECT (id, name, email) ON users TO readonly;
-- readonly cannot access: users.ssn, users.salary

"color:#64748b"># For a 64 GB server
storage:
  block_cache_size: 40GB            "color:#64748b"># 62.5% of RAM -- primary read cache
  write_buffer_size: 512MB          "color:#64748b"># Size of each memtable
  max_write_buffer_number: 6        "color:#64748b"># Up to 3 GB total write buffers
  max_open_files: 10000             "color:#64748b"># OS file descriptor limit

"color:#64748b"># Per-protocol connection memory
protocols:
  postgresql:
    work_mem: 64MB                  "color:#64748b"># Memory for sorts and hash joins per query
    maintenance_work_mem: 512MB     "color:#64748b"># Memory for VACUUM, CREATE INDEX
  mysql:
    sort_buffer_size: 64MB
    join_buffer_size: 64MB
  mongodb:
    wired_tiger_cache_size: 8GB     "color:#64748b"># Additional cache for document workloads
  redis:
    maxmemory: 4GB                  "color:#64748b"># Limit for Redis key-value data
    maxmemory_policy: allkeys-lru   "color:#64748b"># Eviction policy when limit is reached

protocols:
  postgresql:
    pooling:
      enabled: true
      mode: transaction          "color:#64748b"># transaction | session
      pool_size: 100             "color:#64748b"># Backend connections to storage engine
      max_client_connections: 5000  "color:#64748b"># Client-facing connection limit
      idle_timeout: 300s         "color:#64748b"># Close idle backend connections after 5 min
      connection_lifetime: 3600s "color:#64748b"># Recycle backend connections after 1 hour
  mysql:
    pooling:
      enabled: true
      mode: transaction
      pool_size: 100
      max_client_connections: 5000
      idle_timeout: 300s

-- View the index advisor recommendations
SELECT * FROM synergydb_index_advisor();

-- Sample output:
-- recommendation | table     | columns         | estimated_speedup
-- CREATE INDEX   | orders    | (user_id, date) | 47x
-- CREATE INDEX   | products  | (category_id)   | 12x
-- DROP INDEX     | users     | idx_users_tmp   | saves 2.1 GB

-- Create a concurrent index (no table lock)
CREATE INDEX CONCURRENTLY idx_orders_user_date
  ON orders (user_id, created_at DESC);

-- Partial index for hot data
CREATE INDEX idx_orders_active
  ON orders (status, priority)
  WHERE status IN ('pending', 'processing');

-- Covering index to avoid table lookups
CREATE INDEX idx_users_email_name
  ON users (email) INCLUDE (name, created_at);

-- Monitor index usage
SELECT index_name, idx_scan, idx_tup_read, idx_tup_fetch, size_bytes
FROM synergydb_index_stats
ORDER BY idx_scan DESC;

storage:
  query_cache:
    enabled: true
    max_size: 2GB                   "color:#64748b"># Maximum memory for cached results
    max_entry_size: 16MB            "color:#64748b"># Skip caching results larger than this
    ttl: 300s                       "color:#64748b"># Default TTL for cache entries
    invalidation: immediate         "color:#64748b"># immediate | lazy
    exclude_patterns:
      - "SELECT.*NOW()"            "color:#64748b"># Never cache time-dependent queries
      - "SELECT.*RANDOM()"        "color:#64748b"># Never cache non-deterministic queries

    "color:#64748b"># Per-protocol cache hints
    postgresql:
      enable_prepared_cache: true   "color:#64748b"># Cache prepared statement plans
      plan_cache_size: 500          "color:#64748b"># Max cached plans per connection
    redis:
      passthrough: true             "color:#64748b"># Redis GET/SET bypass the query cache
                                     "color:#64748b"># (Redis protocol has its own semantics)

-- Force cache bypass for a specific query
SELECT /*+ NO_CACHE */ * FROM orders WHERE status = 'pending';

-- Set a custom TTL for a specific query
SELECT /*+ CACHE_TTL(60) */ count(*) FROM users;

-- View cache statistics
SELECT hit_count, miss_count, eviction_count,
       hit_count::float / (hit_count + miss_count) AS hit_ratio,
       memory_used_bytes, entry_count
FROM synergydb_query_cache_stats;