PostgreSQL + TimescaleDB (financial time series)

Continuous aggregates

• Use continuous aggregates to maintain lower-frequency candles (e.g. 1h from 1m) without ad hoc batch jobs on raw tables.
• Refresh policies must align with latency vs load; document materialization lag for strategies that read aggregates.

Relational vs series

• Use SQLAlchemy or Tortoise ORM (async) for normal relational data: users, accounts, strategy settings, API keys metadata (store secrets via vault/KMS references, not plaintext in hot tables).
• Keep time-series facts in hypertables; reference them with stable ids (symbol_id, instrument_id) rather than repeating long symbols in every hypertable row when normalized design helps indexing size.

Ingestion performance

• Bulk paths: prefer COPY for initial loads and large backfills; for online upserts use INSERT ... ON CONFLICT (or Timescale-compatible upsert patterns) keyed on natural uniqueness (e.g. (symbol_id, bucket_start) for candles).
• Batch writes in transactions sized to balance WAL, lock time, and replay safety.
• Always pre-create or ensure constraints supporting idempotent ingestion.

Retention and deletion

• Define retention policies on hypertables (and optionally on continuous aggregate storage) to drop data that strategies no longer need—tier by timeframe (raw 1m shorter than 1d aggregates, etc.).
• For removing large time ranges, use Timescale drop_chunks (or equivalent APIs), not row-by-row or broad DELETE that bloats tables and vacuum pressure.

Indexing

• Default pattern: composite index on (symbol, timestamp) (or (symbol_id, time)), aligned with common filters (WHERE symbol = ? AND ts BETWEEN ...).
• Add covering indexes only when EXPLAIN shows sequential scans or sort-heavy plans on hot paths.

Anti-patterns

• DELETE-ing huge historical ranges on hypertables instead of drop_chunks / retention tooling.
• Heavy feature engineering inside SQL when the same pipeline is clearer and faster vectorized in Pandas/NumPy after a tight SQL fetch—use the DB for filtering, aggregation windows, and storage economics, not a second indicator engine.
• Storing secrets in logs or unencrypted strategy rows; duplicating business logic in SQL that belongs in domain/.

Quick checklist

• Hypertable chosen for tick/OHLCV; chunk policy justified
• Continuous aggregates for rollups; refresh lag documented
• Bulk: COPY / batched INSERT ... ON CONFLICT
• Retention policies + chunk drops for pruning
• Indexes on (symbol[, id], time) matching queries
• ORM for relational metadata; ports/adapters keep SQL out of domain