Skip to main content

​
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?

FeatureBenefit for Tracera
HypertablesAutomatic partitioning of price history by time
Continuous AggregatesPre-computed 1h, 24h, 7d windows without manual cron jobs
Retention PoliciesAutomated cleanup of old raw data
Full PostgreSQLSQL 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:
ViewBucket SizeUse Case
price_1h1 hourShort-term volatility, intraday analysis
price_24h24 hoursDaily trends, day-over-day comparison
price_7d7 daysWeekly 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.