been putting this off for months.
timescaledb getting slow. finally fixed it.
the problem #
my options flow data pipeline ingests about 3 million rows per day.
quotes, trades, greeks, IV surface points.
writes were fine. reads were getting brutal.
simple query for daily aggregates taking 8+ seconds.
unacceptable when algos need sub-second decisions.
the diagnosis #
EXPLAIN ANALYZE
SELECT date_trunc('minute', timestamp) as minute,
symbol,
AVG(iv) as avg_iv,
SUM(volume) as total_vol
FROM options_flow
WHERE timestamp > NOW() - INTERVAL '24 hours'
AND symbol = 'SPX'
GROUP BY 1, 2
ORDER BY 1;
execution time: 8.4 seconds.
wtf.
looked at the query plan. full table scan on 890 million rows.
root cause #
three issues:
- hypertable chunks too large (default 7 days = 21M rows per chunk)
- no compression on old data
- missing index on symbol + timestamp combo
the fix #
step 1: chunk interval
-- shrink chunks to 1 day for recent data
SELECT set_chunk_time_interval('options_flow', INTERVAL '1 day');
-- recompress existing data into smaller chunks
CALL reorder_chunk('options_flow', 'options_flow_timestamp_idx');
step 2: compression policy
-- compress anything older than 3 days
ALTER TABLE options_flow SET (
timescaledb.compress,
timescaledb.compress_segmentby = 'symbol',
timescaledb.compress_orderby = 'timestamp DESC'
);
SELECT add_compression_policy('options_flow', INTERVAL '3 days');
-- manually compress existing old data
SELECT compress_chunk(i) FROM show_chunks('options_flow',
older_than => INTERVAL '3 days') i;
step 3: proper indexing
-- compound index for symbol + time queries
CREATE INDEX CONCURRENTLY idx_options_flow_symbol_time
ON options_flow (symbol, timestamp DESC);
-- partial index for recent hot data
CREATE INDEX CONCURRENTLY idx_options_flow_recent
ON options_flow (timestamp DESC, symbol)
WHERE timestamp > NOW() - INTERVAL '7 days';
the results #
same query:
before: 8.4 seconds
after: 0.12 seconds
70x improvement.
compression ratio on old data: 12:1
disk usage dropped from 340GB to 89GB.
python integration update #
updated my ingestion pipeline to batch writes properly:
import asyncio
from asyncpg import Pool
from typing import List, Dict, Any
class OptionsFlowIngester:
def __init__(self, pool: Pool, batch_size: int = 5000):
self.pool = pool
self.batch_size = batch_size
self.buffer: List[Dict[str, Any]] = []
async def ingest(self, record: Dict[str, Any]) -> None:
self.buffer.append(record)
if len(self.buffer) >= self.batch_size:
await self._flush()
async def _flush(self) -> None:
if not self.buffer:
return
async with self.pool.acquire() as conn:
await conn.executemany(
"""
INSERT INTO options_flow
(timestamp, symbol, strike, expiry, call_put,
bid, ask, iv, delta, gamma, theta, vega, volume)
VALUES
($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)
""",
[(r['ts'], r['sym'], r['strike'], r['exp'], r['cp'],
r['bid'], r['ask'], r['iv'], r['d'], r['g'], r['t'], r['v'],
r['vol']) for r in self.buffer]
)
self.buffer.clear()
async def close(self) -> None:
await self._flush()
batching at 5000 rows instead of row-by-row.
ingestion throughput: 180k rows/second (was 12k).
monitoring #
added grafana dashboard for chunk sizes and compression stats.
set alerts for:
- chunk size > 25M rows
- compression ratio < 8:1
- query latency p99 > 1 second
lessons learned #
- timescaledb defaults are for general use cases, not high-volume trading data
- compress aggressively - old tick data rarely accessed at full resolution
- partial indexes on hot data are magic
- batch writes always, never row-by-row
should’ve done this 6 months ago. been running suboptimal this whole time.
2:14am friday. finally optimized timescaledb. 3M rows/day ingestion was crushing read performance. fixed with 1-day chunks, aggressive compression (12:1 ratio), and proper compound indexes. query time: 8.4s → 0.12s (70x faster). disk: 340GB → 89GB. batched writes now 180k rows/sec.
-AK