Skip to main content

Database Setup

PostgreSQL configuration for production

Requirements​

  • PostgreSQL: 16+ (LTS recommended)
  • TimescaleDB: 2.x (for time-series events)
  • pgvector: Latest (for semantic search)

Installation​

  • Supabase: Managed PostgreSQL with extensions
  • Railway: Easy deployment with PostgreSQL
  • AWS RDS: Enterprise-grade managed PostgreSQL
  • Any PostgreSQL 14+: Cloud, managed, or self-hosted

Self-Hosted​

# Ubuntu/Debian
sudo apt install postgresql-16 postgresql-contrib-16
sudo apt install timescaledb-2-postgresql-16
sudo apt install postgresql-16-pgvector

# Configure
sudo timescaledb-tune
sudo systemctl restart postgresql

Current Architecture​

Synap uses a pure PostgreSQL 14+ approach with two complementary database clients:

Database Clients​

postgres.js​

  • Purpose: Connection management and raw SQL queries
  • Use For: Complex queries, PostgreSQL-specific features, performance-critical operations

Drizzle ORM​

  • Purpose: Type-safe query building and schema management
  • Use For: CRUD operations, schema definitions, type-safe queries

Why Both?

  • Flexibility: Raw SQL for complex queries, ORM for simple operations
  • Performance: postgres.js is one of the fastest PostgreSQL clients
  • Type Safety: Drizzle provides full TypeScript support
  • Cost Control: Works with any PostgreSQL 14+ provider

Compatible With:

  • Supabase
  • Railway
  • AWS RDS
  • Google Cloud SQL
  • Self-hosted PostgreSQL
  • Any PostgreSQL 14+ provider

Configuration​

Enable Extensions​

-- Connect to database
\c synap

-- Enable TimescaleDB
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- Enable pgvector
CREATE EXTENSION IF NOT EXISTS vector;

-- Verify
\dx

Create Hypertable​

-- Events table should be a hypertable
SELECT create_hypertable('events_v2', 'created_at');

Performance Tuning​

Connection Pooling​

# Use connection pooler (PgBouncer)
DATABASE_URL=postgresql://user:pass@host:5432/synap?pgbouncer=true

Query Optimization​

-- Set work_mem for heavy queries
SET work_mem = '256MB';

-- Analyze tables regularly
ANALYZE events_v2;
ANALYZE entities;

Indexes​

-- Ensure indexes exist
CREATE INDEX IF NOT EXISTS idx_events_user_id ON events_v2(user_id);
CREATE INDEX IF NOT EXISTS idx_events_type ON events_v2(type);
CREATE INDEX IF NOT EXISTS idx_entities_user_id ON entities(user_id);

Backup Strategy​

Automated Backups​

# Daily backup script
#!/bin/bash
pg_dump -U synap_user synap | gzip > backup_$(date +%Y%m%d).sql.gz

# Keep last 30 days
find . -name "backup_*.sql.gz" -mtime +30 -delete

Point-in-Time Recovery​

Configure WAL archiving for point-in-time recovery:

# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'cp %p /backup/wal/%f'

Monitoring​

Key Metrics​

  • Connection pool usage
  • Query performance
  • Table sizes
  • Index usage
  • Replication lag (if applicable)

Next: See Storage Setup for file storage configuration.