TimescaleDB

GPS location data is stored in a dedicated TimescaleDB instance, optimized for high-volume time-series ingestion and range queries.

Migration file: crates/apollon-db/tsdb_migrations/20260609000000_gps_hypertable.up.sql

Hypertable Schema

CREATE TABLE gps_locations (
    id          UUID NOT NULL DEFAULT gen_random_uuid(),
    timestamp   TIMESTAMPTZ NOT NULL,
    latitude    DOUBLE PRECISION NOT NULL,
    longitude   DOUBLE PRECISION NOT NULL,
    altitude    DOUBLE PRECISION,
    speed_kmh   DOUBLE PRECISION NOT NULL DEFAULT 0.0,
    speed_mps   DOUBLE PRECISION NOT NULL DEFAULT 0.0,
    speed_mph   DOUBLE PRECISION NOT NULL DEFAULT 0.0,
    speed_knots DOUBLE PRECISION NOT NULL DEFAULT 0.0,
    trip_id     UUID,
    PRIMARY KEY (id, timestamp)
);

Column	Type	Constraints	Description
id	UUID	NOT NULL, part of composite PK	Unique identifier
timestamp	TIMESTAMPTZ	NOT NULL, part of composite PK	GPS fix time
latitude	DOUBLE PRECISION	NOT NULL	Latitude in decimal degrees
longitude	DOUBLE PRECISION	NOT NULL	Longitude in decimal degrees
altitude	DOUBLE PRECISION	nullable	Altitude in meters
speed_kmh	DOUBLE PRECISION	NOT NULL, default 0.0	Speed in km/h
speed_mps	DOUBLE PRECISION	NOT NULL, default 0.0	Speed in meters per second
speed_mph	DOUBLE PRECISION	NOT NULL, default 0.0	Speed in miles per hour
speed_knots	DOUBLE PRECISION	NOT NULL, default 0.0	Speed in nautical knots
trip_id	UUID	nullable	Associated trip (references trips.id in PostgreSQL)

Composite Primary Key

The primary key is (id, timestamp) rather than just id. This is required by TimescaleDB because the partitioning column (timestamp) must be part of any unique constraint or primary key.

Speed Formats

Four speed units are stored simultaneously to avoid repeated conversions at query time:

Column	Unit	Use Case
speed_kmh	km/h	European road transport
speed_mps	m/s	Scientific / SI standard
speed_mph	mph	US/UK road transport
speed_knots	knots	Maritime / aviation

Hypertable Configuration

SELECT create_hypertable(
    'gps_locations',
    by_range('timestamp', INTERVAL '1 day')
);

• Chunk interval: 1 day
• Partitioning column: timestamp
• Each chunk holds one day of GPS data, enabling efficient time-range queries and chunk-level operations (drop, compress, move)

Indexes

CREATE INDEX idx_gps_locations_trip_id ON gps_locations (trip_id, timestamp DESC);

This composite index supports two common query patterns:

1. Get all GPS points for a trip, ordered by time (most recent first)
2. Find the latest GPS point for a trip (LIMIT 1 with descending timestamp)

Compression

ALTER TABLE gps_locations SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'trip_id',
    timescaledb.compress_orderby = 'timestamp DESC, id'
);

SELECT add_compression_policy('gps_locations', INTERVAL '7 days');

Compression Settings

Setting	Value	Description
compress_segmentby	trip_id	Each compressed segment groups data by trip
compress_orderby	timestamp DESC, id	Within each segment, data is sorted by time descending
Compression policy	7 days	Chunks older than 7 days are automatically compressed

Compression typically achieves 90%+ storage reduction for GPS time-series data. Compressed chunks remain fully queryable.

Connection Pool Settings

The TimescaleDB pool is configured separately from the main PostgreSQL pool:

[timescale]
url = "postgresql://apollon:apollon@localhost:5433/apollon_tsdb"
max_connections = 5
min_connections = 1
connect_timeout = 30       # seconds
idle_timeout = 600         # seconds
max_lifetime = 1800        # seconds

Setting	Default	Description
max_connections	5	Maximum pool size
min_connections	1	Minimum idle connections
connect_timeout	30s	Connection acquisition timeout
idle_timeout	600s (10 min)	Close idle connections after this duration
max_lifetime	1800s (30 min)	Maximum connection age before recycling

All settings can be overridden via environment variables with the APOLLON__TIMESCALE__ prefix.

Initialization

At startup, the API runs:

1. Create pool with the configured connection parameters
2. Enable extension: CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE
3. Run migrations from crates/apollon-db/tsdb_migrations/

pub async fn init_tsdb_pool_and_migrate(
    config: &TimescaleConfig,
) -> Result<PgPool, Box<dyn std::error::Error>> {
    let pool = init_tsdb_pool(config).await?;
    enable_tsdb_extensions(&pool).await?;
    run_tsdb_migrations(&pool).await?;
    Ok(pool)
}

Dev Stack

The development Docker Compose stack runs TimescaleDB on port 5433:

timescaledb:
  image: timescale/timescaledb-ha:pg18
  environment:
    - POSTGRES_USER=apollon
    - POSTGRES_PASSWORD=apollon
    - POSTGRES_DB=apollon_tsdb
    - TS_TUNE_MEMORY=2GB
    - TS_TUNE_NUM_CPUS=2
  ports:
    - "5433:5432"

The TS_TUNE_* settings auto-configure TimescaleDB memory and parallelism for the dev environment.