提升HDF5文件读取速度咨询——基于期权链金融数据存储场景

Great question—handling large time-series option chain data with HDF5 and Pandas is super common in quant finance, and there are several concrete tweaks you can make to slash read times. Let’s break this down based on your existing hierarchical structure (trading day → ticker → calls/puts):

First, ditch the default fixed storage mode and use table mode instead. fixed is fast for writes but locks you into full-table reads; table lets you query subsets of data and leverage indexing, which is critical for option chains where you rarely need every strike/expiration at once.

When writing your DataFrames, use these parameters:

with pd.HDFStore('option_chain.h5', 'a') as store:
    store.put(
        f'/trade_date/{ticker}/calls',
        calls_df,
        format='table',
        complib='blosc:lz4',
        complevel=5,
        chunksize=1000  # Adjust based on average rows per ticker/call/put
    )

• blosc:lz4 is a sweet spot: it’s fast to compress/decompress (way faster than gzip) and reduces file size without killing read performance.
• chunksize ensures data is split into manageable blocks—pick a size that matches how you typically slice the data (e.g., if you often read 500 rows per query, set chunksize to 500-1000).

When storing calls/puts, explicitly mark columns you frequently filter on (like strike, expiration_date, implied_volatility) as data_columns. This tells HDF5 to build indexes for these columns, so you can query subsets directly without loading the entire DataFrame.

Example write with data columns:

store.put(
    f'/trade_date/{ticker}/puts',
    puts_df,
    format='table',
    complib='blosc:lz4',
    complevel=5,
    data_columns=['strike', 'expiration_date', 'implied_volatility']
)

Then when reading, use select() instead of get() to filter on indexed columns:

with pd.HDFStore('option_chain.h5', 'r') as store:
    # Only load puts with strike > 200 and expiration in 30 days
    filtered_puts = store.select(
        '/20240520/MSFT/puts',
        where='strike > 200 and expiration_date >= "2024-06-20"'
    )

• Keep the HDFStore open during batch reads: Don’t open/close the file for every single ticker or date query. Use a context manager to hold the connection open while you run multiple reads—this cuts down on filesystem handshake overhead.
• Use memory mapping for large files: For your 9GB (and growing) file, enable memory mapping when opening the store:

  with pd.HDFStore('option_chain.h5', 'r', mmap_mode='r') as store:
      # Reads will use mapped memory, faster for repeated access
  

  This maps the file to your system’s memory, so repeated reads of the same blocks don’t hit the disk every time.

Your current structure (/trade_date/ticker/calls) is perfect if you mostly query all options for a single day. But if you often pull data for a single ticker across multiple days, consider flipping the hierarchy to /ticker/trade_date/calls. This way, all data for a ticker is stored in contiguous blocks on disk, which speeds up cross-date reads for that ticker.

No need to rebuild everything overnight—test both structures with your most common query patterns to see which performs better.

If you frequently run the same queries (e.g., daily at-the-money options for your 300 tickers), cache the results to avoid re-reading and filtering the HDF5 file every time. You can use joblib or even a simple in-memory cache:

from joblib import Memory

# Cache results to a local directory
memory = Memory(cachedir='./option_cache', verbose=0)

@memory.cache
def get_atm_calls(trade_date, ticker):
    with pd.HDFStore('option_chain.h5', 'r') as store:
        df = store.select(f'/{trade_date}/{ticker}/calls')
        # Calculate ATM logic here
        return df[abs(df['strike'] - df['underlying_price']) < 1]

This is especially useful if you’re running backtests or dashboards that repeat the same queries.

Your database will grow to ~37GB per year (250 days * 150MB/day). Once it hits 20-30GB, splitting the file by quarter or month (e.g., option_chain_2024Q2.h5) will help keep read times fast—smaller files mean less disk seeking and faster indexing. You can build a simple wrapper function to load the correct file based on the date you’re querying.

内容的提问来源于stack exchange，提问作者AleVis