How should you store OHLCV market data so the numbers are still trustworthy a year from now? Most developers building on a market data API hit this question early: the API hands you clean bars, you write them to Postgres, and the first version works in an afternoon. The bugs arrive later, and they're quiet. A join that's off by one bar because two tables keyed their timestamps differently. A backtest nobody can reproduce because a stock split rewrote history in place. A gap that got forward-filled into a trade that never happened. This post walks through a candlestick data storage schema that avoids those failures: the right primary key, honest gap handling, UPSERT for provider corrections, and an idempotent backfill loop.
The OHLCV table: one row per symbol, interval, and bar open time#
CREATE TABLE bars (
symbol text NOT NULL,
interval text NOT NULL, -- '1m', '5m', '1h', '1d'
open_time timestamptz NOT NULL, -- bar OPEN, always UTC
open numeric NOT NULL,
high numeric NOT NULL,
low numeric NOT NULL,
close numeric NOT NULL,
volume numeric NOT NULL DEFAULT 0,
updated_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (symbol, interval, open_time)
);
Three decisions in that table do most of the work.
The primary key is (symbol, interval, open_time). Every bar a provider publishes is uniquely identified by those three values, and making them the key lets the database enforce what ingest code would otherwise have to promise: no duplicate bars, ever. It also enables ON CONFLICT as a correction mechanism, which matters below.
open_time is the bar's open, stored as timestamptz, which Postgres keeps as an absolute UTC instant. Key on the open because that's the convention nearly every API follows, and because bars of different intervals share opens: a day's 1d bar and its first 1h bar start at the same instant, so joins across intervals line up on plain equality. Key on the close instead and every such join needs interval-specific arithmetic. Key on local time and daylight saving hands you a missing hour each spring and a duplicated hour each autumn, which the primary key turns into a 2 a.m. constraint violation.
Prices and volume are numeric, the exact decimal type. double precision can't represent most decimal fractions exactly, so summed volumes drift and equality comparisons between stored values and freshly fetched ones misfire, which matters once the upsert below starts diffing rows. Precision needs also vary too much across asset classes to pick one float format: US stocks quote to four decimals, EURUSD to five, and a low-priced token like DOGEUSD needs eight or more. Unconstrained numeric stores exactly the digits the API sent. If aggregations over millions of rows get slow, cast to double precision at read time and keep the storage exact.
Missing bars: detect gaps at query time#
The tempting move when a fetch comes back with holes is to fill them: copy the previous close into a flat synthetic bar so downstream code sees an unbroken series. Don't. A filled row is indistinguishable from a real bar six months later, and a gap has more than one meaning. In a 1m crypto series a hole might be an outage on your side. In a thin pair it might be a minute with no trades. In US stocks it might be a holiday. Filling at write time bakes one interpretation into the data forever and hides that anything was missing, which is the class of fault covered in why two backtests of the same strategy disagree.
Store only bars the provider actually published, and find holes with a window function when you need them:
SELECT prev_open, open_time, open_time - prev_open AS hole
FROM (
SELECT open_time,
lag(open_time) OVER (ORDER BY open_time) AS prev_open
FROM bars
WHERE symbol = 'BTCUSD' AND interval = '1m'
) t
WHERE open_time - prev_open > interval '1 minute';
That query is both a data-quality report and a backfill worklist. Whether a given hole should be forward-filled, zero-filled, or left alone becomes a per-query decision made by code that knows the context, and each consumer can decide differently.
Corrections: UPSERT, never blind INSERT#
Historical bars are less immutable than they look. Providers restate: a late print raises a high, a bad tick gets filtered on a re-run, yesterday's volume grows after a venue delivers late. A loader doing plain INSERT throws a duplicate-key error the second time it sees a bar and dies. With ON CONFLICT DO NOTHING it survives and quietly keeps the first value it ever saw, so the store drifts away from the provider with no error anywhere. The correct write treats the provider as the source of truth:
INSERT INTO bars (symbol, interval, open_time, open, high, low, close, volume)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
ON CONFLICT (symbol, interval, open_time) DO UPDATE
SET open = EXCLUDED.open, high = EXCLUDED.high, low = EXCLUDED.low,
close = EXCLUDED.close, volume = EXCLUDED.volume, updated_at = now()
WHERE (bars.open, bars.high, bars.low, bars.close, bars.volume)
IS DISTINCT FROM
(EXCLUDED.open, EXCLUDED.high, EXCLUDED.low, EXCLUDED.close, EXCLUDED.volume);
The WHERE clause skips no-op writes, so updated_at moves only when a bar actually changed. That makes it a free audit trail: any row whose updated_at is later than its first load is a restatement, worth alerting on.
One thing the upsert must never write is adjusted prices. Store bars exactly as published, unadjusted, and apply split and dividend factors at read time from a separate table keyed by symbol and effective date. Factors change retroactively every time a new corporate action lands; if the stored prices are pre-adjusted, every split forces a full-history rewrite and no past query is reproducible. The arithmetic is covered in adjusted vs unadjusted stock prices.
Incremental backfill and the live edge#
With idempotent writes, backfill collapses to a two-step loop. Ask the table what it already has:
SELECT COALESCE(max(open_time), timestamptz '2025-08-25 00:00+00') AS since
FROM bars
WHERE symbol = 'AAPL' AND interval = '1d';
Then fetch from that point, deliberately including the last stored bar rather than the one after it, since the newest bar is the likeliest to have been restated. Overlap costs nothing when the write is an upsert, and a crashed job needs no checkpoint file: run it again and it resumes from wherever max(open_time) says it stopped.
Against SiftingIO, historical bar endpoints require gzip, so send the request compressed:
curl --compressed -H "X-API-Key: $SIFTING_KEY" \
"https://api.sifting.io/v1/hist/stocks/AAPL/bars?interval=1d"
Responses are cursor-paginated; follow meta.next_cursor until it returns null, upserting each page (range parameters are in the docs). The same shape works for forex (/v1/hist/forex/EURUSD/bars) and crypto (/v1/hist/crypto/BTCUSD/bars), so one loader covers a mixed watchlist. For the live edge, connect to wss://stream.sifting.io/ws/v1?key=$SIFTING_KEY, send {"op":"subscribe","product":"us","symbols":["AAPL"]}, build the in-progress bar from ticks in memory, and write it through the same upsert when it closes. The next REST backfill repairs anything the stream missed while you were disconnected. Deriving 5m or 1h bars from stored 1m bars is its own topic, covered in how to resample minute bars. SiftingIO's free tier includes REST history and a streaming connection with no credit card, enough to run this whole pattern on a small watchlist.
Common pitfalls#
- Declaring open_time as timestamp instead of timestamptz. Postgres silently discards the offset when casting an RFC 3339 string like 2026-08-24T13:30:00Z to plain timestamp, so a loader running with a non-UTC session timezone writes shifted bars and raises no error.
- Treating ON CONFLICT DO NOTHING as idempotent. It is safe to re-run, and it also permanently freezes the first-seen value of every bar, so two databases backfilled a week apart disagree forever. You want re-runs that converge on the provider's current values, and only DO UPDATE gives you that.
- Requesting historical bars without gzip. Heavy endpoints return 406 gzip_required instead of an uncompressed body. In curl, compressed fixes it; most HTTP libraries negotiate gzip by default, which is why this error tends to appear only in hand-rolled clients.
A bar store built this way is boring to operate, which is the goal: the schema enforces uniqueness, the upsert absorbs restatements, gaps stay visible until a query decides what they mean, and adjustments stay out of the raw record. Start building free to point the loader at live data.
