Time Series Databases in Finance: When Relational Is Not Enough

The Problem With Relational Databases and Market Data

Relational databases are excellent general-purpose tools. But throw a few billion timestamped price ticks at PostgreSQL and you will start to feel the pain. Not because Postgres is bad — it is genuinely excellent — but because the access patterns for time series data are fundamentally different from transactional data.

With market data, you almost always query by time range: "give me AAPL prices between 9:30 and 10:00 on January 15th." You rarely update old data. You insert in chronological order. And the volumes can be staggering — a single liquid equity can generate thousands of ticks per second during market hours.

Time series databases (TSDBs) are engineered specifically for these patterns.

---

What Makes a TSDB Different

Time-Based Partitioning

Data is automatically chunked by time period. Query a one-hour window and the database only touches the relevant chunk, ignoring terabytes of irrelevant historical data. In a relational database, you would need to set up partitioning manually, and it is rarely as well optimised for time-range queries.

Columnar Compression

Time series data is highly compressible. Adjacent timestamps differ by small increments. Prices change by small amounts tick to tick. TSDBs exploit this with specialised compression algorithms that can achieve 10-20x compression ratios compared to row-based relational storage.

Built-In Downsampling

Converting tick data to 1-minute, 5-minute, or hourly candles is a core operation in finance. TSDBs have native functions for this, rather than requiring complex SQL aggregations or application-level processing.

Retention Policies

Automatically age out old data. Keep raw ticks for 30 days, 1-minute aggregates for a year, daily aggregates forever. TSDBs handle this automatically.

---

The Major Players

If you are already invested in the PostgreSQL ecosystem, TimescaleDB is the path of least resistance — you keep standard SQL, your existing tooling, and your team's knowledge, while gaining time series optimisations.

For greenfield projects where performance is critical, QuestDB is worth evaluating. It was designed from the ground up for financial data and benchmarks extremely well for both ingestion and query speed.

---

A Practical Example

Here is what working with TimescaleDB looks like. Notice it is just SQL with some extra functions:

-- Create a hypertable (time-partitioned table)
CREATE TABLE ticks (
    time TIMESTAMPTZ NOT NULL,
    symbol TEXT NOT NULL,
    price DOUBLE PRECISION,
    volume INTEGER
);

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

-- Add a composite index for symbol + time queries
CREATE INDEX idx_ticks_symbol_time ON ticks (symbol, time DESC);

-- Query: OHLCV candles from raw ticks
SELECT
    time_bucket('1 minute', time) AS candle_time,
    symbol,
    first(price, time) AS open,
    max(price) AS high,
    min(price) AS low,
    last(price, time) AS close,
    sum(volume) AS volume
FROM ticks
WHERE symbol = 'AAPL'
  AND time >= NOW() - INTERVAL '1 day'
GROUP BY candle_time, symbol
ORDER BY candle_time;

The time_bucket, first, and last functions are TSDB-specific and make time series aggregations trivial. Compare this to the raw SQL gymnastics you would need in a standard relational database.

Continuous Aggregates

Pre-compute common aggregations that update automatically:

-- Materialised view that automatically stays up to date
CREATE MATERIALIZED VIEW ohlcv_1min
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 minute', time) AS bucket,
    symbol,
    first(price, time) AS open,
    max(price) AS high,
    min(price) AS low,
    last(price, time) AS close,
    sum(volume) AS volume
FROM ticks
GROUP BY bucket, symbol;

---

When You Actually Need One

Not every project needs a TSDB. If you are working with daily data for a few hundred instruments, a well-designed relational database handles that perfectly well. TSDBs earn their keep when:

• You are storing tick-level or sub-second data
• Ingestion rates exceed thousands of rows per second
• You need fast time-range queries over billions of rows
• Downsampling and time-based aggregations are core workflows
• Storage costs matter (compression helps significantly)

For connecting your TSDB to the rest of your infrastructure, understanding API design and data pipeline architecture becomes important — the database is just one component of a larger system. And in trading infrastructure, where latency matters, the choice of database can directly impact how fast your systems react to market events.