Time-series data with TimescaleDB: compress, roll up, and when never to delete

Any system that receives data every few seconds, such as IoT sensors or the balance of a trading account, hits the same wall. Data grows without stopping, hundreds of thousands of rows a day. A few months later the table is slow to search, the disk fills up, and a six-month chart takes ages. This article shows how the trading journal we run uses TimescaleDB to handle it, with the real SQL it runs.

Tested with TimescaleDB on PostgreSQL 16 (the timescale/timescaledb image).

How time-series data differs

Time-series data has predictable habits:

• It is appended; old rows are almost never changed
• It is queried by time range, such as “this account’s balance over the last 30 days”
• New data is used often, old data rarely, and very old data is only wanted as an overview, not every point

An ordinary database does not know these habits, so it stores every row the same way forever. TimescaleDB puts them to work.

Principle: data of different ages has different jobs. Let the system handle it by age automatically, but “handle” does not have to mean “delete”.

Hypertables: a table that splits itself by time

Create an ordinary table, then turn it into a hypertable:

CREATE TABLE equity_ticks (
    account_id  BIGINT      NOT NULL,
    ts          TIMESTAMPTZ NOT NULL,
    balance     NUMERIC(20,5) NOT NULL,
    equity      NUMERIC(20,5) NOT NULL,
    PRIMARY KEY (account_id, ts)
);

SELECT create_hypertable('equity_ticks', 'ts', if_not_exists => TRUE);

From outside it is still one table, with the same SELECT and INSERT. Inside, it is split into pieces by time range, called chunks, 7 days each by default. Searching “the last 30 days” opens only the chunks involved instead of scanning everything, and deleting old data means dropping whole chunks, far faster than deleting rows one by one.

Policies that run by data age

1. Compress data older than 7 days

ALTER TABLE equity_ticks SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'account_id'
);
SELECT add_compression_policy('equity_ticks', INTERVAL '7 days', if_not_exists => TRUE);

compress_segmentby = 'account_id' keeps each account’s data together when compressing, because we always query one account at a time. Choose this column to match how you really query. Time-series data usually compresses very well, because neighbouring values are similar.

2. An hourly rollup that updates itself (a continuous aggregate)

CREATE MATERIALIZED VIEW equity_1h WITH (timescaledb.continuous) AS
SELECT account_id,
       time_bucket('1 hour', ts) AS bucket,
       first(balance, ts) AS balance_open,
       last(balance, ts)  AS balance_close,
       last(equity, ts)   AS equity_close,
       min(equity)        AS equity_low,
       max(equity)        AS equity_high
FROM equity_ticks
GROUP BY account_id, bucket
WITH NO DATA;

SELECT add_continuous_aggregate_policy('equity_1h',
    start_offset      => INTERVAL '3 hours',
    end_offset        => INTERVAL '1 hour',
    schedule_interval => INTERVAL '1 hour');

Every hour the system rolls up the window from 3 hours ago to 1 hour ago into one row per account per hour, keeping open, close, low and high, enough to draw long charts and find long-term drawdown. The latest hour is left alone (end_offset) because its data may still be arriving.

3. Dropping old data (available, but we chose not to)

SELECT add_retention_policy('equity_ticks', INTERVAL '180 days');

This drops whole chunks older than 180 days. It suits data that loses its value with age, such as logs or server-room temperatures. Our system, though, is a trading verification system: every point of history is evidence, so we decided to delete nothing and rely on compression and the rollup for space. The full story is in the next section.

A true story: the drop policy that almost ran

When the system started, we set raw data to be dropped at 180 days, assuming charts would read the rollup by then. While writing this article we went through the code again and found:

• The balance chart and the statistics still read raw data only; nothing read the rollup
• The starting balance for percentage gain came from the very first raw row
• The oldest data in the database was already 183 days old

Soon the chart would have started vanishing from the left and the percentage gain would have changed on its own, with no error anywhere. The owner decided on the spot: never delete. We removed the policy with one command:

SELECT remove_retention_policy('equity_ticks');

Then we confirmed no real data had been lost (real accounts started recording four months ago, under 180 days) and took the command out of the database setup script so a fresh install cannot bring it back.

A drop policy is code that runs quietly in the future. You set it today; it acts when nobody remembers setting it.

How much it saves

Say one account sends its balance every 5 seconds, around the clock:

	Rows
Raw, per day	86,400 ÷ 5 = 17,280
Raw, 180 days	17,280 × 180 = 3,110,400
Rollup, 180 days	24 × 180 = 4,320

A six-month chart read from the rollup reads 4,320 rows instead of three million, 720 times fewer.

Do not write what did not change

Compression helps, but not writing at all is better. An account with no open trades does not move all night; writing every 5 seconds produces thousands of identical rows.

So our system keeps a “signature” of the latest values in Redis (balance, equity, margin and position count joined as text). If the new values match, the write is skipped, though a row is still written at least every 5 minutes as a sign of life, and the last-seen time is updated every time. Quiet periods almost vanish from storage while the chart looks exactly the same.

What to watch

• Ask first whether this data may really be deleted. Logs, yes. Financial evidence or data customers rely on should not be deleted automatically
• If you do drop data, move the reads too. Every query that looks far back must read the rollup before the policy starts dropping
• Keep lifetime values separately. A starting balance, for example: if it comes from the first raw row, it changes once that row is dropped
• Compress only what no longer changes. Editing compressed data is harder. Set the compression delay past the window where data may be resent or corrected
• Do not put permanent records in a hypertable with a drop policy. Our trade history is an ordinary table, because it must be kept forever and exactly

Common mistakes

• Using TIMESTAMP instead of TIMESTAMPTZ, then fighting time zones when data comes from several countries
• Leaving the time column out of the primary key; a hypertable requires every unique index to include it
• Creating a continuous aggregate without a policy, so it never refreshes
• Adding a drop policy without telling the team, then being asked where last year’s data went