Database Configuration

Pulsimo uses PostgreSQL 17 as its primary database. This guide covers configuration, migrations, performance optimization, and backup strategies.

Database Schema

Core tables in the Pulsimo database:

organizations

Top-level tenant isolation

users

User accounts and authentication

projects

Logical grouping of endpoints

endpoints

Services being monitored

health_checks

Check results and response times

incidents

Service outages and failures

Database Migrations

Pulsimo uses numbered SQL migration files for schema changes:

backend/migrations/
├── 001_initial_schema.sql
├── 002_add_organizations.sql
├── 003_add_health_checks.sql
├── ...
└── 029_latest_feature.sql

Docker Initialization

First Start: All migrations execute automatically in order when database is created.

Subsequent Starts: Migrations are skipped if database exists. Apply new migrations manually.

Manual Migration

# Apply new migration
docker exec -i monitoring-postgres psql \
  -U monitoring -d monitoring_system \
  < backend/migrations/029_new_feature.sql

Performance Optimization

Indexing Strategy

Table	Index	Purpose
endpoints	project_id	Fast project filtering
health_checks	endpoint_id, checked_at	Time-series queries
incidents	status, created_at	Active incident lookup

Connection Pooling

# Recommended pool settings
DATABASE_POOL_SIZE=20
DATABASE_MAX_OVERFLOW=10
DATABASE_POOL_TIMEOUT=30

Backup & Recovery

Automated Backup

# Daily backup script
docker exec monitoring-postgres pg_dump \
  -U monitoring monitoring_system \
  | gzip > backup_${DATE}.sql.gz

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

Restore from Backup

# Stop services
docker compose down

# Restore database
gunzip -c backup_2025-11-09.sql.gz | \
  docker exec -i monitoring-postgres psql \
  -U monitoring -d monitoring_system

# Restart services
docker compose up -d

Best Practices

Regular Backups: Automate daily backups and test restoration quarterly to ensure recoverability.

Monitor Performance: Track query times, connection pool usage, and slow queries. Add indexes for frequently queried columns.

Secure Access: Use strong passwords, limit network access, and enable SSL/TLS for production databases.