--- title: " Tick Data and OHLC Candlesticks in PostgreSQL" description: "Store tick data in Postgres with hypertables, build OHLC candlesticks with time_bucket and continuous aggregates, and compress high-frequency data." section: "Postgres for real-time analytics" published: 2026-11-02T12:24:36.348Z updated: 2026-10-23T00:00:00.000Z --- *Updated at Oct 23, 2026* > **TimescaleDB is now Tiger Data.** ## What is tick data? Tick data is the record of every individual market event for an instrument, typically each trade (price, size, timestamp) and each quote change (bid and ask), stored at the finest granularity an exchange or feed publishes. Everything coarser, from one-minute bars to daily closes, is derived from it. Trade ticks record executions; quote ticks record changes to the bid and ask. Level 1 data carries the best bid, best ask and last trade, while Level 2 adds depth across multiple price levels. For developers, tick data is the raw material for every bar, indicator and backtest, and it arrives as an append-heavy, time-ordered stream. ## What are OHLC and OHLCV? OHLC stands for open, high, low, close: the first, highest, lowest and last price in a fixed time interval. OHLCV adds the volume traded in that interval. A candlestick is the usual chart rendering of one OHLC row. Most "OHLC data" you download or query is a time-bucketed aggregate of tick data. To read a candle, the body spans open to close, the wicks reach to the low and high, and the color or fill shows whether price rose or fell. The mapping from ticks to a bar is mechanical: | **Bar field** | **Computed from the ticks in the interval** | | --- | --- | | Open | First price | | High | Maximum price | | Low | Minimum price | | Close | Last price | | Volume | Sum of trade size | Time bars group ticks by clock interval, such as one minute. Tick bars group by a fixed number of trades, such as every 500. This guide builds time bars; tick-count bars need a window function to number trades per symbol first, which [`time_bucket`](https://www.tigerdata.com/docs/build/continuous-aggregates/create-a-continuous-aggregate) does not do. ## Why tick data is hard on a database A tick data database has to absorb a constant write stream and still answer time-range queries quickly. Plain PostgreSQL handles modest tick volumes well. The trouble starts as tables grow, indexes bloat, and aggregation queries scan more data than they need. The constraints: - Sustained write rates during market hours, with bursts at the open and on news - Row counts that grow into the billions - Time-ordered queries, almost always filtered by symbol and time range - Late and out-of-order arrivals from feeds and reconnects - High symbol cardinality - Different retention needs for raw ticks and derived bars This guide uses three tools for these tradeoffs: hypertables for time-based partitioning, continuous aggregates for pre-aggregation, and the columnstore for compression. Tiger Data builds this tooling, so the examples use it; the final section covers when plain PostgreSQL or another system fits better. ## How to store tick data in PostgreSQL Create a [Tiger Cloud service](https://www.tigerdata.com/docs/get-started/quickstart/create-service) and define a hypertable with `tsdb.hypertable` in `CREATE TABLE ... WITH`, declaring columnstore settings up front. `CREATE TABLE crypto_ticks ( "time" TIMESTAMPTZ NOT NULL, symbol TEXT NOT NULL, price DOUBLE PRECISION, size DOUBLE PRECISION ) WITH ( tsdb.hypertable, tsdb.segmentby = 'symbol', tsdb.orderby = 'time DESC' ); CREATE INDEX ON crypto_ticks (symbol, "time" DESC);` The `time` column is a `TIMESTAMPTZ` and becomes the partition column automatically, since it is the first timestamp column. `size` is the quantity traded in a single trade. The Tiger Data tutorial dataset carries a cumulative `day_volume` column instead, but a running total is not what you want to sum into candle volume, so this guide uses per-trade size. `tsdb.segmentby = 'symbol'` keeps each instrument's rows together when data is compressed, which helps symbol-filtered `scans. tsdb.orderby = 'time DESC'` matches the dominant query shape, newest first. Choose data types deliberately. `DOUBLE PRECISION` is fast and compact but inexact. `NUMERIC` is exact but slower and larger. Many teams store integer ticks or scaled integer prices when they need exchange-grade accuracy. A hypertable gets a default descending index on time. The extra `(symbol, "time" DESC)` index speeds per-instrument lookups on recent, uncompressed chunks. Chunks default to a 7-day interval; the [hypertable CREATE TABLE reference](https://www.tigerdata.com/docs/reference/timescaledb/hypertables/create_table) covers tuning. Reference data stays in an ordinary relational table. Define a small `crypto_assets` lookup of display names to join against later: `CREATE TABLE crypto_assets ( symbol TEXT PRIMARY KEY, name TEXT NOT NULL );` For ingestion, batch inserts or `COPY` give the best throughput, and the [asyncpg guide](https://www.tigerdata.com/blog/how-to-build-applications-with-asyncpg-and-postgresql) shows the Python pattern for tick data. For a full dataset walkthrough, see the tutorial on [ingesting real-time financial data](https://www.tigerdata.com/docs/learn/tutorials/ingest-real-time-financial-data). ## How to turn ticks into OHLC candlesticks in SQL ### Core TimescaleDB: time_bucket with first() and last() For candlestick chart SQL without the Toolkit extension, this is the baseline: `SELECT time_bucket('1 hour', "time") AS bucket, symbol, first(price, "time") AS open, max(price) AS high, min(price) AS low, last(price, "time") AS close, sum(size) AS volume FROM crypto_ticks WHERE "time" > now() - INTERVAL '1 day' GROUP BY bucket, symbol ORDER BY bucket, symbol;` `first()` and `last()` take a second argument because they order by the column you pass, not by physical row order. Rows can arrive out of order, so "the first row seen" is not "the earliest trade," and naive approaches get it wrong. By comparison, `date_trunc` handles fixed calendar units only. PostgreSQL 14 and later adds date_bin for fixed widths such as 5 or 15 minutes, while time_bucket also handles months and time zones. Window-function candle logic works but is more verbose and harder to optimize. For volume, sum per-trade size as above. A cumulative figure like day_volume needs different handling: `last(day_volume, "time") - first(day_volume, "time")` misses the first trade in the bucket and breaks when the counter resets at the session boundary. If your feed only provides cumulative volume, difference consecutive rows first, then sum. Intervals with no trades produce no row, which leaves holes in a chart. Use `time_bucket_gapfill` with `locf` to carry the previous close forward: `SELECT time_bucket_gapfill('5 minutes', "time") AS bucket, locf(last(price, "time")) AS close, COALESCE(sum(size), 0) AS volume FROM crypto_ticks WHERE symbol = 'BTC/USD' AND "time" >= now() - INTERVAL '1 hour' AND "time" < now() GROUP BY bucket ORDER BY bucket;` Gapfill needs a bounded time range in the `WHERE` clause. The [advanced analytic queries docs](https://www.tigerdata.com/docs/build/data-management/query-data/advanced-analytic-queries) cover gapfill and interpolation in more depth. ### The candlestick_agg() hyperfunction For any candlestick PostgreSQL workload, [`candlestick_agg()`](https://www.tigerdata.com/docs/reference/toolkit/candlestick_agg/candlestick_agg)` `is the cleaner path. One aggregate returns the open, high, low, close and volume together, and accessors pull out each piece: `SELECT time_bucket('1 hour', "time") AS bucket, symbol, open(candlestick_agg("time", price, size)) AS open, high(candlestick_agg("time", price, size)) AS high, low(candlestick_agg("time", price, size)) AS low, close(candlestick_agg("time", price, size)) AS close, volume(candlestick_agg("time", price, size)) AS volume, open_time(candlestick_agg("time", price, size)) AS open_time, high_time(candlestick_agg("time", price, size)) AS high_time FROM crypto_ticks WHERE "time" > now() - INTERVAL '1 day' GROUP BY bucket, symbol ORDER BY bucket, symbol;` The arguments are the timestamp, the price and the per-trade volume, and the function stores the `(timestamp, value)` pair behind each price. That is why it can report when the high and low occurred (`high_time`, `low_time`), compute a volume-weighted average price with `vwap()`, and, most importantly, be rolled up. The [candlestick_agg reference](https://www.tigerdata.com/docs/reference/toolkit/candlestick_agg) lists every accessor. These functions are part of the Toolkit extension. Tiger Cloud includes all hyperfunctions by default, per the docs, so no install step is needed; on self-hosted databases, install the TimescaleDB Toolkit extension first. `candlestick_agg()`, `vwap()` and `candlestick()` have been available since Toolkit 1.14.0. If you ingest pre-built bars instead of ticks, `candlestick()` converts open, high, low, close and volume columns into the same aggregate form. More in the [hyperfunctions guide](https://www.tigerdata.com/learn/time-series-data-analysis-hyperfunctions) and [hyperfunctions docs](https://www.tigerdata.com/docs/build/data-management/hyperfunctions). ## Multi-timeframe candles with continuous aggregates Recomputing 1-minute, 1-hour and 1-day candles from raw ticks on every dashboard load scans far more data than needed. A [continuous aggregate](https://www.tigerdata.com/learn/continuous-aggregates-timescaledb) is an incrementally refreshed materialized view that updates only the time buckets that changed. See the [continuous aggregates documentation](https://www.tigerdata.com/docs/learn/continuous-aggregates) for the full model and [why continuous aggregates beat materialized views](https://www.tigerdata.com/blog/materialized-views-the-timescale-way) for background. Pre-aggregation moves work out of the query path, so measure the effect on your own data. Here is a 1-minute candle view using the plain `first`/`max`/`min`/`last `shape. A continuous aggregate requires `time_bucket()`: `CREATE MATERIALIZED VIEW one_min_candle 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(size) AS volume FROM crypto_ticks GROUP BY bucket, symbol; SELECT add_continuous_aggregate_policy('one_min_candle', start_offset => INTERVAL '1 day', end_offset => INTERVAL '1 minute', schedule_interval => INTERVAL '1 minute');` The offsets are example values; the same pattern appears in the [Tiger Cloud essentials walkthrough](https://www.tigerdata.com/docs/learn/tiger-cloud/tiger-cloud-essentials). `start_offset` and `end_offset` define the refresh window relative to when the policy runs. Ticks that arrive late but inside that window are picked up on the next refresh, while data older than `start_offset` needs a manual `refresh_continuous_aggregate` call. A bucket still open at `end_offset` is not materialized. That matters for charts, because the latest candle is the one users watch. Real-time aggregation combines materialized buckets with the newest raw data so it stays current. In TimescaleDB 2.13 and later it is off by default, so enable it explicitly: `ALTER MATERIALIZED VIEW one_min_candle SET (timescaledb.materialized_only = false);` The [real-time aggregates docs](https://www.tigerdata.com/docs/learn/continuous-aggregates/real-time-aggregates) explain the tradeoff. The plain view works, but a daily `first` over rows that are already aggregates needs care. `candlestick_agg()` handles that case. Build the 1-minute base with it, then stack coarser views with `rollup()`: `CREATE MATERIALIZED VIEW candlestick_1m WITH (timescaledb.continuous) AS SELECT time_bucket('1 minute', "time") AS ts, symbol, candlestick_agg("time", price, size) AS candlestick FROM crypto_ticks GROUP BY ts, symbol; -- Hourly candles stacked on the minute view (hierarchical continuous aggregate) CREATE MATERIALIZED VIEW candlestick_1h WITH (timescaledb.continuous) AS SELECT time_bucket('1 hour', ts) AS ts, symbol, rollup(candlestick) AS candlestick FROM candlestick_1m GROUP BY 1, 2; -- Daily OHLC and volume-weighted average price, rolled up at query time SELECT time_bucket('1 day', ts) AS day, symbol, open(rollup(candlestick)) AS open, high(rollup(candlestick)) AS high, low(rollup(candlestick)) AS low, close(rollup(candlestick)) AS close, vwap(rollup(candlestick)) AS vwap FROM candlestick_1h WHERE symbol = 'BTC/USD' AND ts > now() - INTERVAL '1 month' GROUP BY day, symbol ORDER BY day;` Add refresh policies to `candlestick_1m` and `candlestick_1h` as above. Rolling up works because candles compose: a high is the maximum of the highs, a low the minimum of the lows, the open the earliest open and the close the latest close, and the stored timestamps tell `rollup()` which is which. The [hierarchical continuous aggregates docs](https://www.tigerdata.com/docs/learn/continuous-aggregates/hierarchical-continuous-aggregates) cover the stacking rules, including bucket-width requirements. ## Compressing and retaining tick data Raw ticks are the largest table and the least queried once candles exist, so the usual pattern is to compress older raw data and keep candles longer. Details are in the [hypercore overview](https://www.tigerdata.com/docs/learn/columnar-storage/understand-hypercore) and the [columnstore setup guide](https://www.tigerdata.com/docs/build/columnar-storage/setup-hypercore). A hypertable created with `CREATE TABLE ... WITH (tsdb.hypertable)` already gets a default columnstore policy that converts chunks after one chunk interval, 7 days by default. To choose your own threshold, replace the policy: `CALL remove_columnstore_policy('crypto_ticks'); CALL add_columnstore_policy('crypto_ticks', after => INTERVAL '3 days'); SELECT pg_size_pretty(before_compression_total_bytes) AS before, pg_size_pretty(after_compression_total_bytes) AS after FROM hypertable_columnstore_stats('crypto_ticks');` On compression numbers, the Tiger Docs state that converting data to the columnstore compresses it by more than 90% in typical cases, and by up to 98%. The docs' `hypertable_columnstore_stats` example shows 194 MB before and 24 MB after, but that is sample output from a tutorial dataset, not an expectation. Results depend on how repetitive and ordered your data is, so run the stats query on your own data before planning capacity. Retention is the other half. A retention policy drops raw chunks after N days while continuous aggregates keep the candle history: `SELECT add_retention_policy('crypto_ticks', INTERVAL '90 days');` Keep the retention interval longer than the continuous aggregate refresh window, or a refresh can drop aggregate rows whose source ticks are gone. The docs explain this in [data retention with continuous aggregates](https://www.tigerdata.com/docs/learn/data-lifecycle/data-retention/data-retention-with-continuous-aggregates), and the [data lifecycle overview](https://www.tigerdata.com/docs/learn/data-lifecycle) shows where retention fits. The crypto trading platform Pintu described its setup in a [Tiger Data case study](https://www.tigerdata.com/blog/saving-devs-time-and-compute-power-with-retention-policies-the-story-of-crypto-trading-platform-pintu). According to that post, Pintu inserts about five million rows every 30 minutes and reports queries of around 100 ms against a raw table of roughly 1.4 billion rows, with around one billion rows deleted per day across the raw table and its seven continuous aggregates. These are Pintu's figures for its own workload, not a benchmark. ## Querying candlesticks and charting them Reading candles back out is an ordinary query: filter by symbol and time range, order by bucket, and limit to the latest N. `SELECT bucket, open, high, low, close, volume FROM one_min_candle WHERE symbol = 'BTC/USD' AND bucket > now() - INTERVAL '6 hours' ORDER BY bucket DESC LIMIT 100;` For the single latest candle per symbol, with a display name from a metadata table: `SELECT DISTINCT ON (c.symbol) c.symbol, a.name, c.bucket, c.open, c.high, c.low, c.close, c.volume FROM one_min_candle c JOIN crypto_assets a ON a.symbol = c.symbol ORDER BY c.symbol, c.bucket DESC;` Grafana's candlestick panel reads open, high, low, close and volume columns straight from a Postgres data source. The [Grafana tutorial for financial tick data](https://www.tigerdata.com/docs/learn/tutorials/analyze-financial-tick-data) has the step-by-step setup, and here's the video that walks through the pipeline: [From Raw Ticks to Candlesticks](https://www.youtube.com/embed/iJEKaoYkK-g) Any client that reads Postgres, such as pandas, can pull the same view and pass it to Plotly or a JavaScript chart library. For a larger dataset, see the [S&P 500 stock data tutorial](https://www.tigerdata.com/docs/learn/tutorials/analyze-stock-market-data). ## When PostgreSQL is enough, and when to look elsewhere PostgreSQL on Tiger Cloud tends to fit teams that want SQL, joins to reference data, and one operational database for ticks, candles and application data. It may not fit ultra-low-latency, in-memory order book processing, sub-microsecond capture at exchange co-location scale, or teams that only need a licensed historical feed. Those cases usually call for a specialized system or a data vendor. Common ways in: load CSV tick files with `COPY`, convert an existing Postgres tick table with [`create_hypertable()`](https://www.tigerdata.com/docs/reference/timescaledb/hypertables/create_hypertable) (the reference warns that migrating data in place can lock a large table for a long time), or replay from a feed handler. The Tiger Docs [migration approach guide](https://www.tigerdata.com/docs/migrate/choose-your-approach) helps you pick a path for your source. If you are evaluating options, the [time-series database comparison](https://www.tigerdata.com/learn/the-best-time-series-databases-compared) covers the landscape, and the guide to [PostgreSQL compression options](https://www.tigerdata.com/learn/postgresql-compression) goes deeper on storage. ### Decision framework - **Choose Tiger Cloud (PostgreSQL), or self-hosted TimescaleDB with the Toolkit extension, if** you need SQL and joins across ticks, candles and reference data, want continuous aggregates and `candlestick_agg()` for multi-timeframe bars, and your ingest is high but not exchange-colocation extreme. - **Choose plain PostgreSQL if** your volume is modest, such as a few symbols at low frequency, and an indexed table with `date_trunc` or `date_bin` aggregation is fast enough today. - **Choose a specialized low-latency system if** you need in-memory, microsecond-scale processing for live order book logic, with SQL analytics secondary. - **Choose a data vendor feed if** you need licensed historical exchange data and no storage of your own. To try the pattern end to end, create a [Tiger Cloud service](https://www.tigerdata.com/docs/get-started/quickstart/create-service) and load the sample crypto tick dataset from the [financial data tutorial](https://www.tigerdata.com/docs/learn/tutorials/ingest-real-time-financial-data).