migrating market data to timescaledb - 10x query speedup

been storing market data in regular postgres.

works but slow for time-series queries.

migrated to timescaledb this week.

10x speedup on historical queries.

got the idea from NexusFi algo infrastructure discussions about optimizing market data storage. someone mentioned timescaledb and i researched it.

the problem

#

typical query: get all 1-min bars for SPY last 30 days

postgres: 8-12 seconds

timescaledb: 0.8 seconds

difference: timescaledb optimized for time-series data.

what is timescaledb

#

postgres extension for time-series data.

automatically partitions data by time (hypertables).

compresses old data.

way faster for time-range queries.

migration process

#

1. installed timescaledb

apt-get install timescaledb-postgresql-14

2. created hypertable

CREATE TABLE market_data (
    time BIGINT NOT NULL,
    symbol TEXT NOT NULL,
    open NUMERIC,
    high NUMERIC,
    low NUMERIC,
    close NUMERIC,
    volume BIGINT
);

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

3. migrated existing data

8GB of historical bars (6 months, 10 symbols, 1-min resolution).

took 45 minutes to migrate.

INSERT INTO market_data
SELECT * FROM market_data_old
ORDER BY time;

4. created indexes

CREATE INDEX idx_symbol_time ON market_data (symbol, time DESC);

5. enabled compression

ALTER TABLE market_data
SET (timescaledb.compress,
     timescaledb.compress_segmentby = 'symbol');

SELECT add_compression_policy('market_data', INTERVAL '7 days');

compresses data older than 7 days.

reduces storage by 70%.

performance comparison

#

query: last 30 days of SPY 1-min bars

postgres:

EXPLAIN ANALYZE
SELECT * FROM market_data_old
WHERE symbol = 'SPY'
  AND time >= extract(epoch from now() - interval '30 days')
ORDER BY time;

-- Planning time: 2.4ms
-- Execution time: 11,842ms

timescaledb:

EXPLAIN ANALYZE
SELECT * FROM market_data
WHERE symbol = 'SPY'
  AND time >= extract(epoch from now() - interval '30 days')
ORDER BY time;

-- Planning time: 0.8ms
-- Execution time: 823ms

14x faster.

storage savings

#

before (postgres):

• 8GB data
• no compression
• indexes: 2GB
• total: 10GB

after (timescaledb):

• 8GB data (uncompressed recent)
• 1.8GB compressed (older than 7 days)
• indexes: 1.2GB
• total: 4.2GB

58% storage reduction.

backtesting impact

#

before: 6-hour backtest (2 years data, 3 strategies)

after: 45-minute backtest (same data)

8x faster backtesting.

can iterate way faster now.

continuous aggregates

#

also set up continuous aggregates for common queries.

CREATE MATERIALIZED VIEW daily_ohlcv
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 day', time) AS day,
    symbol,
    first(open, time) AS open,
    max(high) AS high,
    min(low) AS low,
    last(close, time) AS close,
    sum(volume) AS volume
FROM market_data
GROUP BY day, symbol;

daily bars pre-calculated.

queries instant (50ms vs 2000ms).

retention policy

#

keeping detailed data for limited time.

SELECT add_retention_policy('market_data', INTERVAL '1 year');

automatically drops data older than 1 year.

keeps database size manageable.

real trading impact

#

strategy research:

before: run backtest overnight, see results morning.

after: run backtest in 1 hour, iterate same day.

live trading:

historical data queries for indicators way faster.

reduced latency from 200ms → 20ms on indicator calculations.

lessons learned

#

1. right tool for right job

postgres great for general data.

timescaledb better for time-series.

2. compression matters

70% storage reduction from compression.

older data rarely accessed, compress it.

3. indexes on time + symbol

critical for query performance.

infrastructure details

#

running on:

• same dell server
• postgres 14 + timescaledb extension
• 32GB RAM allocated to postgres
• NVMe storage for database

monitoring:

• grafana tracks query performance
• alerts if p95 query time > 1 second
• dashboard shows compression ratio

next optimizations

#

already planning:

1. add more continuous aggregates (weekly, monthly bars)
2. partition by symbol for even faster queries
3. replicate to backup server for redundancy

trading update

#

tuesday 8/8: +$340 (TLT call spread) wednesday 8/9: +$405 (SPX put spread)

august total: +$3,195

goal: +$4,000. need: +$805 in 2.5 weeks.

2:25pm wednesday. migrated to timescaledb. 10x query speedup. 8x faster backtesting. can iterate way faster now.

-AK