Best Database for Tick Data Backtesting: Parquet vs Servers

What access pattern does tick data backtesting have?

Start from how the data is read, not from which product is popular. A backtest reads large, time-ordered ranges for one symbol or one underlying, usually a handful of columns, and almost never fetches a single row by key. Writes are append-only: today's data arrives, yesterday's never changes.

That profile favours columnar storage sorted by time. A columnar file lets the reader skip columns it does not need, and a time-sorted layout lets it skip whole blocks using min/max statistics. Row-oriented OLTP engines are built for the opposite: random lookups, updates and transactions.

Three access patterns decide the engine, and they often mix. Match yours before choosing.

Why is partitioned Parquet the default for 0.5 to 1TB?

At this size the data fits on one NVMe drive or one cheap bucket, and a single machine can scan it quickly. Running a database server adds operations work (upgrades, backups, memory tuning) that Parquet does not need. Files are also trivially shareable: copy a folder, or point a reader at a bucket.

Raw CSV tick data typically shrinks 5 to 10x in Parquet with zstd, so 1TB of CSV often becomes roughly 100 to 200GB. Gains come from column-wise compression, dictionary encoding of repeated symbols, and delta encoding of near-monotonic timestamps. Actual ratios depend on how noisy your prices and sizes are, so measure on one day before planning capacity.

Use a Hive-style layout so engines can prune whole directories from the path alone:

ticks/
  trade_date=2024-03-15/
    symbol_bucket=07/part-0000.parquet
    symbol_bucket=12/part-0000.parquet
  trade_date=2024-03-18/
    ...

# Each file is sorted by (symbol, ts_event_ns).
# Partition by date, plus a symbol hash bucket (or underlying for options)
# so no file is tiny and no directory holds thousands of files.

How do I query Parquet tick data with DuckDB and Polars?

DuckDB reads Parquet in place, pushes filters down to row-group statistics, and only decodes the columns you select. Because files are sorted by (symbol, timestamp), the min/max statistics let it skip most row groups for a single-symbol query.

Polars lazy scans do the same from Python and hand you columnar data without a pandas copy. The second example streams a date range as Arrow batches, which suits an event-driven backtest that cannot hold everything in memory.

import duckdb, polars as pl, pyarrow.dataset as ds

# 1) Ad-hoc: 1-minute bars for one symbol, only 4 columns read
con = duckdb.connect()
bars = con.sql("""
    SELECT time_bucket(INTERVAL 1 MINUTE, to_timestamp(ts_event_ns / 1e9)) AS minute,
           first(price ORDER BY ts_event_ns) AS open,
           max(price) AS high, min(price) AS low,
           last(price ORDER BY ts_event_ns) AS close,
           sum(size) AS volume
    FROM read_parquet('ticks/trade_date=*/**/*.parquet', hive_partitioning=true)
    WHERE trade_date BETWEEN '2024-03-01' AND '2024-03-29'
      AND symbol = 'AAPL'
    GROUP BY 1 ORDER BY 1
""").pl()

# 2) Polars lazy scan: filters and projection pushed into the scan
lf = (pl.scan_parquet('ticks/**/*.parquet', hive_partitioning=True)
        .filter((pl.col('symbol') == 'AAPL') &
                (pl.col('trade_date') >= pl.date(2024, 3, 1)))
        .select(['ts_event_ns', 'price', 'size']))
df = lf.collect()

# 3) Streaming for event-driven replay: bounded memory
dataset = ds.dataset('ticks', format='parquet', partitioning='hive')
scanner = dataset.scanner(columns=['ts_event_ns', 'price', 'size'],
                          filter=(ds.field('symbol') == 'AAPL'),
                          batch_size=500_000)
for batch in scanner.to_batches():
    ts = batch.column('ts_event_ns').to_numpy(zero_copy_only=False)
    px = batch.column('price').to_numpy(zero_copy_only=False)
    # feed arrays to the engine; never iterate rows in Python

Which is the best database for tick data backtesting if I need a server?

Choose a server when you need concurrent users, continuous ingestion, or low-latency queries over recent data. Row stores such as SQLite, MySQL and plain Postgres struggle here: B-tree indexes on billions of rows bloat storage, row storage reads every column, and index maintenance slows bulk inserts. Indexing everything in Postgres is a classic trap at this scale.

TimescaleDB softens this with hypertables and columnar compression, but very large scans remain slower than ClickHouse or Parquet engines.

How should I store options chain historical data?

Options data is large mostly because of quotes: a single underlying can have thousands of live contracts, each updating constantly, and full OPRA feeds are enormous. If you do not need tick quotes, store 1-minute NBBO snapshots per contract instead, which cuts volume by orders of magnitude.

Use one row per contract per timestamp. Normalise contract identity into a dimension table rather than repeating the OCC symbol string on every row, and join through an integer ID. Partition by trade date and underlying, sort by (contract_id, ts_event_ns), and dictionary-encode low-cardinality columns.

import pyarrow as pa

# Dimension: one row per contract (small, rarely changes)
contracts = pa.schema([
    ('contract_id', pa.int32()),
    ('underlying', pa.dictionary(pa.int16(), pa.string())),
    ('expiry', pa.date32()),
    ('strike_x10000', pa.int32()),     # strike * 10000, scaled integer
    ('right', pa.dictionary(pa.int8(), pa.string())),  # C or P
    ('occ_symbol', pa.string()),       # stored once here
])

# Fact: one row per contract per timestamp
quotes = pa.schema([
    ('ts_event_ns', pa.int64()),       # exchange time, UTC ns
    ('ts_recv_ns', pa.int64()),        # receive time, UTC ns
    ('contract_id', pa.int32()),
    ('bid_x10000', pa.int64()), ('ask_x10000', pa.int64()),
    ('last_x10000', pa.int64()),
    ('bid_size', pa.int32()), ('ask_size', pa.int32()),
    ('volume', pa.int32()), ('open_interest', pa.int32()),
    ('iv', pa.float64()), ('delta', pa.float64()),
    ('gamma', pa.float64()), ('theta', pa.float64()), ('vega', pa.float64()),
])

What schema, file size and compression settings matter?

Store timestamps as int64 nanoseconds in UTC, and keep both exchange time and receive time. Mixing timezones is a leading source of silent look-ahead errors, so convert to local time only at presentation. Use sizes as int32.

For prices, pick one convention and keep it everywhere: scaled integers (price times 10,000) avoid rounding drift and compress well, while float64 is acceptable and simpler. Avoid float32 for prices because it cannot represent many tick values exactly.

Sort by (symbol, timestamp) inside each file so row-group min/max statistics work. Too many tiny files hurts S3 and metadata performance, while one giant file defeats parallel reads and makes appends painful.

1. Land each day's vendor files unchanged in an immutable raw/ folder.
2. Convert to Parquet sorted by (symbol, ts_event_ns) with zstd.
3. Write into trade_date= partitions, targeting 128MB to 1GB per file.
4. Run a compaction job to merge any small files.
5. Record a dataset version (date, row counts, checksums) for reproducibility.

Where should I host it, and what are the cost traps?

Keep the working copy on local NVMe, where scan speed is highest, and use object storage for sharing and backup. S3, Cloudflare R2 (no egress fees) and Backblaze B2 all work with DuckDB httpfs or Athena. Managed options include ClickHouse Cloud and MotherDuck, which trade control for less operations work.

Compare options with a simple model: monthly cost equals storage per TB-month plus egress per GB times data read, plus compute. Repeated full scans over a remote bucket can cost more than the storage itself, since every backtest rerun re-reads the data. Filter with partitions and columns, and cache hot slices on NVMe.

If several people share the bucket, an open table format such as Iceberg or Delta Lake gives versioning, schema evolution and safe appends. Plain Parquet folders are enough when one writer appends by date and readers tolerate a daily refresh.

Keep adjusted and unadjusted prices in separate datasets, and store corporate actions and symbol changes as their own tables so results are reproducible.