Live market ticks into Tiger Cloud, then candles in Grafana

Live prices are easy to print and awkward to keep. A websocket hands you one tick at a time. A candlestick chart wants open, high, low, and close for a whole bucket. If you insert each tick in its own transaction, the database spends more time committing than storing. If you only keep a daily rollup, a chart you open during the same session has a single bar, or none at all.

I followed the Tiger Data tutorial for ingesting real-time financial data and wired Twelve Data into a Tiger Cloud hypertable, then drew the candles in Grafana. The code is at github.com/tharun634/tiger-realtime-finance.

What landed in the database

crypto_ticks is a hypertable partitioned on time and segmented by symbol. Recent ticks stay in the row store so inserts stay cheap. Older chunks can move to the column store. A normal table, crypto_assets, holds the symbol and the display name.

CREATE TABLE crypto_ticks (
    "time" TIMESTAMPTZ NOT NULL,
    symbol TEXT NOT NULL,
    price DOUBLE PRECISION,
    day_volume NUMERIC
) WITH (
    tsdb.hypertable,
    tsdb.partition_column = 'time',
    tsdb.segmentby = 'symbol',
    tsdb.orderby = 'time DESC'
);

Timestamps go in as timezone-aware UTC. datetime.utcfromtimestamp is naive, and a timestamptz column will interpret a naive value in the session time zone.

Batch the ticks

The tutorial flushes every 100 price events. That is the right shape, and it is also how people ingest websocket feeds into TimescaleDB in general: hold a short list in memory, then one INSERT for the batch. Predzone's odds pipeline measured about a 60% latency drop from micro-batches versus row-by-row writes.

A quiet stretch never reaches 100 rows, so the chart sits empty. The heartbeat loop already wakes every 10 seconds, so the same loop flushes whatever is in the list:

def _on_event(self, event):
    if event.get("event") != "price":
        print(event)
        return
    timestamp = datetime.fromtimestamp(event["timestamp"], tz=timezone.utc)
    self.current_batch.append(
        (timestamp, event["symbol"], event["price"], event.get("day_volume"))
    )
    if len(self.current_batch) >= self.MAX_BATCH_SIZE:
        self._flush()

execute_values sends the batch as one statement. Non-price events, including subscribe status and heartbeats, are printed and skipped.

A heartbeat only proves the socket answered. Carl Bouvet's write-up of a silent market feed is the failure mode to watch: the connection stays open, pings succeed, and the trade history grows a hole. Proof of life here is a price event, not the heartbeat.

Two candle sizes, not one

The tutorial builds one_day_candle with time_bucket('1 day', time), FIRST and LAST for open and close, and a refresh policy whose end_offset is one day. That offset is intentional. The newest bucket is still filling, so the policy leaves it alone. Philip McClarence's note on real-time versus materialized-only aggregates is the explanation I wish I had read first: the gap is the design, and real-time mode is what fills it by unioning raw ticks at query time.

ALTER MATERIALIZED VIEW one_day_candle
    SET (timescaledb.materialized_only = false);

A daily bar is still one point after twenty minutes of ingest. Pavel's post on continuous aggregates makes the matching point: a view only answers at the bucket you defined. I added one_minute_candle with the same OHLCV shape, a one-minute refresh, and real-time aggregation on. Grafana's minute panel then has a candle per minute. The daily panel is there for the tutorial query, with its own time range so midnight's bucket is not clipped out of "last 30 minutes."

CREATE MATERIALIZED VIEW ... WITH DATA cannot run inside a transaction. Applying the whole schema file in one implicit transaction fails on that statement. The apply script runs each statement with autocommit.

The free websocket plan is picky

The script subscribes to BTC/USD, ETH/USD, MSFT, and AAPL, which is the tutorial list. The first connection came back subscribe-status: warning. BTC/USD and AAPL succeeded. ETH/USD and MSFT were in fails, with no error string. Crypto ticks all day. AAPL only moves while the US cash session is open. A higher Twelve Data plan is what adds the rejected symbols. The script still requests all four, so a later plan change does not require a code change.

Grafana

Grafana OSS reads the service over Postgres with sslmode=require and the TimescaleDB option enabled. The candlestick panel wants a time column plus open, high, low, and close. On a six-hour range, one-minute candles collapse into a smear at the right edge. The dashboard default is the last 30 minutes, and the daily panel overrides that to two days.

Credentials stay in an untracked .env. The repo has .env.example only.

What I would change

The 10-second flush is a demo choice. A busier feed wants a shorter timer and a cap so a burst cannot grow the list without bound. I would also alert when the newest price timestamp for a symbol stops advancing, instead of trusting the heartbeat. And I would keep the raw ticks longer than I need the minute aggregate, then let the daily view serve the history.

This article was written by an agent from the build session and published without a human edit pass. The SQL and the Python above match the repo.