Skip to main content

97. Component-Based Indicator Storage

Status: Accepted Date: 2025-07-06

Context​

We need to store the historical values of technical indicators in our PostgreSQL database. A single indicator, like MACD, is often composed of multiple values (e.g., the MACD line, the signal line, and the histogram). A naive approach would be to store the entire indicator result as a single JSON blob in the database. However, this makes it very difficult to query for specific parts of an indicator or to analyze the relationships between components across different indicators.

Decision​

We will adopt a Component-Based Storage model for technical indicators. Each individual value within an indicator will be stored as a separate row in an IndicatorComponent table.

The schema will look something like this:

  • IndicatorReading (Table)

    • id (PK)
    • symbol, timeframe, timestamp
    • Links to multiple IndicatorComponent rows

  • IndicatorComponent (Table)

    • indicator_reading_id (FK)
    • name (e.g., "rsi", "macd_line", "bollinger_upper_band")
    • value (the numeric value of that component)

This "tall" table format stores each piece of an indicator as a distinct, queryable entity, linked to a single time-slice reading.

Consequences​

Positive:

  • Extreme Query Flexibility: This schema makes it very easy to write flexible queries. We can ask for "the RSI and the MACD signal line for BTC/USDT on the 1-hour timeframe" with a simple SQL query, without having to parse any JSON.
  • Efficient Storage: Avoids storing redundant information. The symbol, timeframe, and timestamp are stored once per reading, not repeated for every component.
  • Easy to Add New Indicators: Adding a new indicator is as simple as defining new component names. No database schema migrations are needed to add new types of indicators.

Negative:

  • More Complex Queries for Full Indicators: Reconstructing a single, complete indicator for a specific timestamp requires querying the IndicatorComponent table for all its components and then "pivoting" the data in the application code. This is more complex than just selecting a single row and parsing a JSON blob.
  • Larger Table Size: This design leads to a larger number of rows in the IndicatorComponent table compared to a "wide" table with one column per component.

Mitigation:

  • ORM and Repository Pattern: The complexity of reconstructing indicators from their components will be hidden behind a repository or service layer. The application code will call a simple method like indicatorRepository.findRsi('BTC/USDT', '1h') and the repository will be responsible for executing the more complex query and assembling the data.
  • Database Indexing: The IndicatorComponent table will be heavily indexed on indicator_reading_id, name, and value. This will ensure that queries, even on a very large table, remain highly performant.
  • Read-Optimized Views: For analysis, we can create materialized views in PostgreSQL that pre-pivot the data into a "wider" format, providing the best of both worlds: a flexible, normalized write schema and a denormalized, performant read view.