TimescaleDB

Time-series database features used in Tracera

TimescaleDB

Tracera uses TimescaleDB — a PostgreSQL extension purpose-built for time-series data. This gives us full SQL compatibility with automatic time-based partitioning.

Why TimescaleDB?

Feature	Benefit for Tracera
Hypertables	Automatic partitioning of price history by time
Continuous Aggregates	Pre-computed 1h, 24h, 7d windows without manual cron jobs
Retention Policies	Automated cleanup of old raw data
Full PostgreSQL	SQL joins, indexes, constraints — no new query language

Hypertables

The price_history table is converted to a hypertable:

SELECT create_hypertable('price_history', 'time', if_not_exists => TRUE);

This transparently partitions data into time-based chunks. Queries with time-range filters are automatically optimized to scan only relevant chunks.

Continuous Aggregates

Pre-computed materialized views that refresh automatically:

CREATE MATERIALIZED VIEW price_1h
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 hour', time) AS bucket,
    item_id,
    AVG(price)::BIGINT          AS avg_price,
    MIN(price)                  AS low,
    MAX(price)                  AS high,
    STDDEV(price)               AS stddev,
    SUM(volume)::BIGINT         AS total_volume
FROM price_history
GROUP BY bucket, item_id
WITH NO DATA;

Three windows are maintained:

View	Bucket Size	Use Case
price_1h	1 hour	Short-term volatility, intraday analysis
price_24h	24 hours	Daily trends, day-over-day comparison
price_7d	7 days	Weekly trends, longer-term analysis

Refresh Policies

Continuous aggregates are configured with automated refresh policies so they stay up-to-date without manual intervention.

Retention Policies

Raw price data is retained for a configurable period. Continuous aggregates are retained indefinitely, preserving historical analysis capability while keeping database size manageable.

Raw data:   Retained for N days (configurable)
1h aggs:    Retained indefinitely
24h aggs:   Retained indefinitely
7d aggs:    Retained indefinitely

Connection Configuration

DB_HOST=localhost      # TimescaleDB host
DB_PORT=5432           # PostgreSQL port
DB_USER=skinvestment   # Database user
DB_PASSWORD=skinvestment # Database password
DB_NAME=skinvestment   # Database name
DB_SSLMODE=disable     # SSL mode (use 'require' in production)

The backend uses pgxpool for connection pooling, avoiding connection-per-request overhead.