A clean price history is not something you download — it is something you build. Between a raw market feed and a chart you can trust sits a pipeline: a sequence of steps that ingest events, throw out the bad ones, force everything into one consistent shape, and store it so a query comes back in milliseconds instead of minutes. Skip any of those steps and your analysis is quietly built on sand. This is a walkthrough of that pipeline — ingest, clean, normalize, store, query — treating market data as a pure data-engineering problem. No strategies, no calls, no predictions. Just the plumbing that decides whether the numbers you analyze are real.
We build pipelines for a living — order events, delivery scans, store transactions across the 30+ platforms we've shipped. A market feed is the same problem wearing a more intimidating costume: a high-volume stream of timestamped events that has to land in a database without drifting, duplicating, or lying. The discipline transfers directly. So does the failure mode — a pipeline that looks fine while silently corrupting history is far more dangerous than one that loudly crashes.
The shape of the pipeline
Before any code, get the stages clear in your head. A market data pipeline is a one-way flow with five distinct jobs, and the reason to name them separately is that each fails differently and each deserves its own tests.
- Ingest — connect to the feed (websocket or REST poll), receive raw messages, and get them off the wire and into a durable buffer before you do anything clever with them.
- Validate & clean — reject malformed messages, flag outliers and bad ticks, detect gaps in the sequence, and decide what to do about each.
- Normalize — map every source's idiosyncratic format onto one internal schema: consistent field names, units, timezone, and instrument identifiers.
- Store — write the normalized events into time-series tables built for the read patterns you actually have.
- Serve — expose fast queries and pre-computed aggregates so analysis doesn't re-scan raw history every time.
Keep these as separable steps even in a small project. The temptation is to fuse ingest-and-store into one function that parses a websocket message and slams it into a table. It works until the day the feed changes a field name or sends you a duplicate, and now your corruption is already committed to the database with no buffer stage where you could have caught it. Boundaries between stages are where you put your guardrails.
Ingest: get it off the wire, durably
Most live market data arrives over a websocket as a stream, or is pulled from a REST endpoint on an interval. Either way, the first rule of ingestion is to separate receiving from processing. The socket's only job is to take messages off the wire as fast as they arrive and hand them to a buffer. If your message handler also validates, normalizes, and writes to Postgres inline, a slow database write will back-pressure the socket, you'll fall behind the feed, and eventually the provider drops you for being too slow to drain.
The pattern is the same one we use for webhook ingestion on The Mover Guy's store: accept fast, acknowledge, then process asynchronously. Land the raw message in a queue or an append-only raw_events table first — untouched, exactly as received. That raw landing zone is your insurance. If a downstream bug mangles data, you can replay from the raw events instead of re-fetching history you may no longer be able to get.
// Ingest: receive fast, buffer, process out of band. Never block the socket.
import WebSocket from "ws";
const ws = new WebSocket(FEED_URL);
const buffer: RawMessage[] = [];
ws.on("message", (data) => {
// Do the absolute minimum here: timestamp arrival + enqueue.
buffer.push({ received_at: Date.now(), payload: data.toString() });
// No parsing, no DB write, no validation on the hot path.
});
// A separate loop drains the buffer at its own pace.
setInterval(async () => {
const batch = buffer.splice(0, 500); // pull a chunk
if (batch.length === 0) return;
await landRaw(batch); // append-only insert into raw_events
await process(batch); // validate -> normalize -> store
}, 250);
// If the DB stalls, the buffer grows but the socket keeps draining.
// You trade memory for not getting kicked off the feed.Clean: gaps, duplicates, and bad ticks
Raw market data is dirty. Not occasionally — structurally. Feeds drop messages on reconnect, replay duplicates after a hiccup, and occasionally emit a bad tick: a single trade printed at an absurd price because of a fat-finger order, a venue glitch, or a feed error. If you store these as-is, a single garbage tick becomes a spike on every chart and a phantom high or low in every aggregate that touches that window. Cleaning is not optional polish; it is the difference between history and noise.
Detecting gaps
If the feed numbers its messages, a gap is trivial to catch: the sequence skipped. If it doesn't, you infer gaps from time — bars that should exist for a liquid instrument during market hours but don't. Either way, the correct response to a gap is the same as in any market-structure feed: do not interpolate fake data to fill it. Record the gap explicitly so downstream analysis knows the history is incomplete there, and where possible backfill from the provider's REST history endpoint. A known hole is honest; an invented value is a lie you'll forget you told.
Killing duplicates
Duplicates are best handled at the database boundary, not in application logic where a retry can slip past. Give each event a natural key — instrument, source, and the event's own timestamp or sequence id — and let Postgres enforce uniqueness. An idempotent upsert means replaying the same message twice is a no-op, which is exactly what you want when a reconnect re-sends the last few seconds of a stream.
Flagging bad ticks
Outlier detection is where judgment lives. A crude but effective first pass: reject or flag any tick that deviates from the recent median by more than a sane threshold, or that prints outside the day's plausible range. The key word is flag, not silently delete — soft-delete or mark suspect ticks so they're excluded from aggregates but still auditable. You want to be able to answer 'why did you drop this?' six months later, and a deleted row can't answer.
-- Idempotent ingest: duplicates collapse, late corrections overwrite.
-- One natural key per (instrument, source, event time) kills replays.
INSERT INTO ticks (instrument, source, ts, price, size, suspect)
VALUES ($1, $2, $3, $4, $5, $6)
ON CONFLICT (instrument, source, ts)
DO UPDATE SET
price = EXCLUDED.price,
size = EXCLUDED.size,
suspect = EXCLUDED.suspect
WHERE ticks.suspect = true; -- only overwrite if we'd flagged it bad
-- A bad tick is flagged, never deleted: still queryable, just excluded.
UPDATE ticks SET suspect = true
WHERE instrument = $1
AND ts = $2
AND abs(price - $3) / $3 > 0.10; -- >10% off the reference: review itNormalize: one schema to rule the feeds
Every data source has its own dialect. One sends prices as strings, another as floats. One stamps time in milliseconds since epoch, another in an ISO string in some unstated timezone. One calls the instrument BTC-USD, another BTCUSD, another tBTCUSD. The job of normalization is to make all of that disappear behind a single internal representation, so everything downstream — storage, queries, analysis — speaks one language and never has to care which provider an event came from.
- Time — convert everything to UTC, stored as a real timestamp type, at a consistent resolution. Never store local time, never store a naive string. Timezone bugs in market data are silent and devastating; a one-hour offset puts trades in the wrong session.
- Numbers — parse strings to a precise numeric type. For prices, prefer fixed-point or
NUMERICover floating point where rounding matters — accumulating float error across millions of rows is a real source of drift. - Instrument identifiers — map every source's symbol onto one canonical id via a lookup table. This is the join key for everything; get it consistent once and every later query is simpler.
- Field names and units —
size,volume,qty,amountall become one chosen name with one chosen unit. Document the choice. Future-you will not remember which feed meant what.
Normalization is boring and it is the whole game. The pipeline that cleans best is the one whose downstream code never has to know where the data came from.
— Studio principle
Centralize the mapping. Each source gets one adapter function whose entire responsibility is raw message in, canonical event out. When a provider changes its format — and they do, usually without warning — you fix one adapter, not a hundred call sites scattered across your analysis code. This is the same per-vertical adapter discipline behind Supreme Suite's pack architecture: one shared core, thin source-specific adapters at the edge.
// One canonical event. Every source adapter must produce exactly this.
interface CanonicalTick {
instrument: string; // canonical id, e.g. "BTC-USD"
ts: string; // ISO-8601 UTC, always
price: number; // parsed, validated
size: number; // one unit, documented
source: string; // provenance — never drop this
}
// Adapter per feed. Isolation means a format change touches one function.
function fromAcmeFeed(raw: AcmeMessage): CanonicalTick {
return {
instrument: SYMBOL_MAP[raw.s] ?? raw.s, // map "BTCUSD" -> "BTC-USD"
ts: new Date(raw.t).toISOString(), // ms epoch -> UTC ISO
price: Number(raw.p), // string -> number
size: Number(raw.q),
source: "acme", // keep provenance always
};
}Store: time-series in Postgres, done right
You do not need an exotic database to store market data well. Postgres — the same Supabase Postgres we run under most of our platforms — handles time-series workloads cleanly if you model for the access pattern. The defining trait of this data is that it's append-heavy, queried by time range, and almost always filtered by instrument. Design the table and indexes around exactly that.
- Index on (instrument, ts). Nearly every query is 'this instrument, this time window.' A composite index in that order makes those range scans fast and keeps the data physically clustered the way you read it.
- Partition by time. Range-partition large tables by day or month. Old partitions can be compressed or detached, and queries that hit a recent window never scan ancient data they don't need.
- Store raw events and aggregates separately. Keep the immutable tick/trade history in one table; keep derived OHLCV bars in their own. Never overwrite raw data with summaries — you can always rebuild bars from ticks, never the reverse.
- Use the right numeric types.
NUMERICfor prices where precision is load-bearing,timestamptzfor time, generous-but-not-wasteful integer types for sequence ids and sizes.
The single most useful storage trick is pre-aggregation. Analysis rarely wants raw ticks — it wants candles: open, high, low, close, volume bucketed into time windows. Computing those on every read by scanning millions of raw rows is slow and wasteful. Compute them once, on write or on a schedule, and store them. A materialized view or a scheduled rollup job turns an expensive GROUP BY over raw history into a cheap lookup.
-- Roll raw ticks into 1-minute OHLCV bars. Compute once, query forever.
-- Excludes flagged bad ticks so one garbage print can't move a candle.
SELECT
instrument,
date_trunc('minute', ts) AS bucket,
(array_agg(price ORDER BY ts))[1] AS open,
max(price) AS high,
min(price) AS low,
(array_agg(price ORDER BY ts DESC))[1] AS close,
sum(size) AS volume
FROM ticks
WHERE suspect = false -- bad ticks never touch an aggregate
AND ts >= now() - interval '1 day'
GROUP BY instrument, date_trunc('minute', ts);
-- Materialize this (view or scheduled job) so reads are O(rows returned),
-- not O(rows scanned). Bars are derived data: always rebuildable from ticks.Serve, monitor, and trust the result
A pipeline you can't see into is a pipeline you can't trust. Markets run continuously, and the failures that hurt most are the quiet ones — the socket that reconnected and silently skipped a resync, the adapter that started returning nulls after a format change, the gap nobody noticed until a backtest came back strangely clean. Treat observability as part of the build, not an afterthought.
- Freshness checks — alert when the newest row for an active instrument is older than it should be. Stale data is the most common silent failure.
- Gap monitoring — count detected gaps per source per day. A rising number means the feed or your ingest is degrading.
- Reconciliation — periodically compare your stored aggregates against the provider's own published bars. Disagreement is a bug, surfaced early.
- Provenance everywhere — every row carries its source. When two numbers disagree, you can trace each back to where it came from instead of guessing.
None of this is unique to markets. Ingest fast, buffer durably, validate at the boundary, normalize to one schema, store for your read pattern, and watch the whole thing for silent drift — that's the same discipline we apply to a courier's delivery events or a store's order stream. Markets just raise the stakes and the volume, which makes the cost of a sloppy pipeline impossible to hide. Build the pipeline like the data has to be right, because for analysis to mean anything, it does.
Building a market data tool, a research dashboard, or any system that has to store events you can actually trust? That's the kind of careful, source-aware data engineering we do — educational tooling, not advice.
Start a projectQuestions, answered
- Why normalize market data into one schema instead of querying each source directly?
- Because every feed speaks its own dialect — different field names, units, timezones, and instrument symbols. If that variation leaks into your analysis code, every query has to special-case each provider, and a single format change can break logic scattered across your whole codebase. Normalizing to one canonical schema at the edge means downstream code never has to know or care where an event came from: you fix the one adapter when a source changes, and everything else keeps working. This is engineering hygiene, not investment guidance.
- How should I handle gaps and bad ticks in a market data pipeline?
- Detect gaps from sequence numbers (a skipped id) or from time (missing bars during active hours), record them explicitly, and backfill from the provider's REST history where possible — never interpolate fake values to paper over a hole. For bad ticks (absurd prices from glitches or fat-finger orders), flag rather than delete: mark them suspect so they're excluded from aggregates but still auditable later. A known gap is honest; an invented value is a silent lie. Note this is data-handling practice only, not a trading method.
- Is Postgres good enough for storing time-series market data?
- Yes, for most needs, if you model for the access pattern. Index on (instrument, ts) since nearly every query is 'one instrument, one time window'; partition large tables by time so old data can be compressed or detached; keep immutable raw ticks separate from derived OHLCV bars; and pre-aggregate candles into materialized views so reads don't re-scan millions of rows. Specialized time-series databases exist, but a well-modeled Postgres handles serious volume cleanly — and it's the same database we run under most of our platforms.
The stack we actually use
The tools below run our own systems and the ones we build for clients. A few are affiliate links — they support The Signal at no cost to you.
Some links on this site are affiliate links. If you click through and sign up or buy, J Supreme Tech may earn a commission at no extra cost to you. We only recommend tools we actually use to build and run our own systems. Full disclosure.