Optimizing Time-Series Data Storage for Backtesting

1. The Core Problem: Efficiently Querying Time-Series Data

Before choosing a tool, we must define the problem. A backtesting engine primarily performs read-heavy, analytical queries on time-series data. Common query patterns include:

• Single-instrument time slice: "Fetch all daily OHLCV data for BTC-USDT from 2018 to the present."
• Multi-instrument time slice: "Fetch 1-hour OHLCV data for BTC-USDT, ETH-USDT, and SOL-USDT for the month of May 2023."
• Cross-sectional snapshot: "Fetch the closing prices for all assets in our universe on January 1st, 2022." (Less common for pure time-series momentum, but important for other strategies).

The central debate for storing this kind of data often boils down to using a traditional database versus a structured system of flat files. This is a classic data engineering dilemma. Let's explore the arguments from practitioners who have faced this exact problem.

This discussion highlights that for a single researcher focused on backtesting, a well-designed file-based system can be extremely performant. However, as complexity grows (e.g., multiple users, real-time updates), the features of a database system become more compelling. Let's explore both paths.

2. Option 1: A File-Based Lakehouse with Apache Parquet

If we choose a file-based approach, the format is critical. Plain text formats like CSV are a poor choice for performance-sensitive applications. A binary, columnar format is far superior.

The video makes it clear that Parquet is a strong candidate. But why is it so effective for our backtesting use case? The answer lies in three key optimizations.

Designing a Parquet-Based Schema

The key to an efficient Parquet-based store is partitioning. By structuring our data in a directory tree based on symbol and time, we allow query engines to perform "partition pruning"—skipping enormous amounts of data by only reading the relevant folders.

A robust schema for our OHLCV data would be a Hive-style partitioned layout:

base_path/{timeframe}/{symbol}/year={YYYY}/month={MM}/data.snappy.parquet

For example:

• data/1h/BTC-USDT/year=2023/month=01/data.snappy.parquet
• data/1h/BTC-USDT/year=2023/month=02/data.snappy.parquet
• data/1h/ETH-USDT/year=2023/month=01/data.snappy.parquet

Why this structure?

• timeframe: Separates different candle granularities (e.g., 1h, 1d).
• symbol: The primary filter for most queries.
• year/month: Allows for efficient time-based filtering. A query for "BTC-USDT in Q1 2023" would only need to touch three folders. The choice of partitioning by month vs. day is a trade-off between the number of files and the granularity of pruning. For LFT data, monthly is often a good balance.

Implementation with Python

The pyarrow library, which integrates with pandas, makes it straightforward to implement this.

Here is a conceptual example of how you would write your fetched data:

import pandas as pd

# Assume 'df' is a DataFrame from the previous lesson's downloader
# It contains OHLCV data for multiple symbols and has a datetime index

# Add year and month columns for partitioning
df['year'] = df.index.year
df['month'] = df.index.month

# Write to a partitioned Parquet dataset
df.to_parquet(
    path='data/1h/',
    engine='pyarrow',
    compression='snappy',
    partition_cols=['symbol', 'year', 'month']
)

Reading the data for a specific symbol and time range becomes highly efficient:

# Read data for a single symbol for two months of 2023
df_filtered = pd.read_parquet(
    path='data/1h/',
    engine='pyarrow',
    filters=[
        ('symbol', '=', 'BTC-USDT'),
        ('year', '=', 2023),
        ('month', 'in', [1, 2])
    ]
)

This approach is simple, requires no extra services, and is extremely fast for a single-user backtesting workflow.

3. Option 2: A Time-Series Database with TimescaleDB

The file-based approach is powerful, but a database offers benefits like a rich query language (SQL), easier data management, and built-in support for concurrency. For time-series data, a standard PostgreSQL or MySQL database can become slow as tables grow. This is where specialized time-series databases shine.

TimescaleDB is a PostgreSQL extension that adds powerful time-series capabilities to a standard, robust SQL database.

Designing a TimescaleDB Schema

The schema design in TimescaleDB is deceptively simple. You start with a standard SQL table and then transform it into a hypertable.

1. Create the Table:
   A single table can hold all our OHLCV data.

   CREATE TABLE ohlcv (
       time TIMESTAMPTZ NOT NULL,
       symbol TEXT NOT NULL,
       open DOUBLE PRECISION NOT NULL,
       high DOUBLE PRECISION NOT NULL,
       low DOUBLE PRECISION NOT NULL,
       close DOUBLE PRECISION NOT NULL,
       volume DOUBLE PRECISION NOT NULL
   );
   

2. Create the Hypertable:
   This command tells TimescaleDB to automatically partition the ohlcv table by the time column.

   SELECT create_hypertable('ohlcv', 'time');
   

3. Add an Index:
   For efficient queries, a unique index on symbol and time is essential. This allows the database to quickly find the starting point for a query for a specific asset.

   CREATE UNIQUE INDEX idx_symbol_time ON ohlcv (symbol, time DESC);
   

   (The DESC on time is a common optimization for time-series, as queries often ask for the most recent data first).

This setup is demonstrated clearly in the video.

The power of this approach is that you now have the full analytical capability of SQL at your disposal, supercharged for time-series data. A query like "find all days where the daily range (high - low) for BTC-USDT was greater than 5% of the opening price" is much more expressive in SQL than with a file-based API.

4. Comparison and Conclusion

Both Parquet and TimescaleDB are excellent, production-grade solutions. The choice depends on the specific constraints and future goals of your project.

Recommendation:

• For getting started quickly with a focus purely on a single-user backtesting engine, partitioned Parquet files are arguably the simplest and most efficient path. The infrastructure is minimal, and it integrates seamlessly with the tools you'll use for analysis.
• If you envision a system that might later incorporate live data streams, serve a dashboard, or be accessed by multiple processes, TimescaleDB is a more robust and scalable foundation. The slight increase in setup complexity pays dividends in flexibility and advanced features.

Key Takeaways:

• For efficient backtesting, you must move beyond simple formats like CSV.
• Apache Parquet offers superior performance through columnar storage, compression, and especially partitioning, which is the key to designing an efficient file-based schema.
• TimescaleDB enhances PostgreSQL with time-series superpowers like hypertables (automatic time-based partitioning) and continuous aggregates, providing a full SQL interface with excellent performance.
• The choice between them is a classic data engineering trade-off between the simplicity of a file-based system and the feature-richness of a specialized database.

In our previous lesson, you learned to fetch the data. Now, you know how to store it. The next step is to ensure the data is statistically sound for our chosen strategy.

In the next lesson, we will tackle the learning outcome: "Analyze stationarity of crypto asset returns using statistical tests (e.g., ADF, KPSS) and interpret the results for strategy selection." This will be our first deep dive into the statistical properties that underpin momentum and mean-reversion strategies.