Database Overview

Apollon uses a dual-database architecture with two separate PostgreSQL instances, each optimized for its workload.

Architecture

┌──────────────────┐     ┌──────────────────────┐
│   PostgreSQL 18  │     │  TimescaleDB (pg18)  │
│   Port 5432      │     │  Port 5433           │
│                  │     │                      │
│  clients         │     │  gps_locations       │
│  contact_persons │     │  (hypertable)        │
│  cargo_categories│     │                      │
│  trips           │     │                      │
└──────────────────┘     └──────────────────────┘
        │                          │
        └──────────┬───────────────┘
                   │
            ┌──────┴──────┐
            │  Rust API   │
            │  (2 pools)  │
            └─────────────┘

Why Two Databases?

Concern	PostgreSQL	TimescaleDB
Data type	Relational (clients, trips, categories)	Time-series (GPS locations)
Query pattern	CRUD with joins and foreign keys	Time-range scans, ordered by timestamp
Storage	Standard B-tree indexes	Hypertable with 1-day chunks
Compression	None needed	Automatic after 7 days
Scaling	Vertical (standard PostgreSQL tuning)	Chunk-based (drop old chunks, compression)
Volume	Low (thousands of records)	High (millions of GPS points)

GPS locations are stored in TimescaleDB because:

• Chunk partitioning automatically splits data into manageable 1-day intervals
• Compression reduces storage by 90%+ for older GPS data
• Time-range queries are orders of magnitude faster on hypertables than standard B-tree indexes
• Retention policies can drop old chunks without expensive DELETE operations

Relational data stays in standard PostgreSQL because:

• Foreign key constraints between clients, trips, and categories are enforced at the database level
• CRUD operations benefit from standard PostgreSQL query planning
• No time-series-specific features are needed

Connection Pools

The API maintains two separate sqlx::PgPool instances:

// Main PostgreSQL pool (relational data)
let db_pool = apollon_db::init_pool(&settings.database.url).await?;

// TimescaleDB pool (GPS time-series data)
let tsdb_pool = apollon_db::init_tsdb_pool(&settings.timescale).await?;

The TimescaleDB pool is wrapped in a TsdbPool newtype to prevent accidental pool confusion at compile time:

pub struct TsdbPool(pub PgPool);

Connection Configuration

PostgreSQL (relational)

Setting	Default	Env Override
URL	postgresql://apollon:apollon@localhost:5432/apollon	APOLLON__DATABASE__URL
Max connections	10	(hardcoded)

TimescaleDB (GPS)

Setting	Default	Env Override
URL	postgresql://apollon:apollon@localhost:5433/apollon_tsdb	APOLLON__TIMESCALE__URL
Max connections	5	APOLLON__TIMESCALE__MAX_CONNECTIONS
Min connections	1	APOLLON__TIMESCALE__MIN_CONNECTIONS
Connect timeout	30s	APOLLON__TIMESCALE__CONNECT_TIMEOUT
Idle timeout	600s	APOLLON__TIMESCALE__IDLE_TIMEOUT
Max lifetime	1800s	APOLLON__TIMESCALE__MAX_LIFETIME

Auto-Migration

Both databases run migrations automatically at startup:

// PostgreSQL migrations
apollon_db::run_migrations(&db_pool).await?;

// TimescaleDB: enable extension + run migrations
apollon_db::init_tsdb_pool_and_migrate(&settings.timescale).await?;

The TimescaleDB initialization also runs CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE before applying migrations.

Health Checks

The API exposes a /health endpoint that checks both database connections:

pub async fn tsdb_health_check(pool: &PgPool) -> bool {
    sqlx::query("SELECT 1").execute(pool).await.is_ok()
}

If either pool is unreachable, the health endpoint returns HTTP 503.

Dev Stack

See Docker Deployment for the Docker Compose configuration that runs both databases locally.