Database Design for Market Data

Market data grows fast. One liquid crypto pair can produce millions of trades a day; a few years of minute bars across thousands of stocks reaches billions of rows. A well designed database keeps queries fast, prevents duplicate or contradictory records and preserves the history needed for honest backtests. A poorly designed one becomes slow, hard to trust and painful to fix later. The good news is that a few design principles cover most trading use cases.

Core tables#

Use an instrument ID, not just the symbol#

Ticker symbols change and get reused. A company can change its ticker, and a delisted ticker can later be assigned to a different company. Storing a stable internal instrument_id, with a separate history of which symbol applied when, prevents mixing two different companies' prices. See Survivorship and Selection Bias and Point-in-Time and Survivorship-Free Data.

Primary keys and uniqueness#

Unique constraints stop duplicates when a feed resends data or a loader runs twice. Combined with "insert, or ignore if it already exists" logic, loads become safe to repeat. See Data Pipelines and ETL.

Data types#

Indexes and partitioning#

• Index (instrument_id, ts) on bars and trades, since nearly every query filters by both.
• Partition by time, for example one partition per month, so queries for recent data scan less and old data can be archived easily.
• Avoid too many indexes on write heavy tables; each one slows inserts.

Raw versus adjusted prices#

Store raw, unadjusted prices plus a corporate actions table, then compute adjusted prices when needed. If you only store adjusted prices, every new split or dividend silently rewrites history, and you can no longer see what prices actually traded. See Splits and Dividends in Price Data and Corporate Actions, Delistings and Rolls in Backtests.

Orders and fills: keep state history#

Rather than overwriting an order's status, record each status change with a timestamp: new, acknowledged, partially filled, filled, cancelled. This history forms part of your audit trail and makes debugging possible. See Logging, Audit Trails and Incident Response.

Common mistakes#

1. Symbol as the only key, mixing different companies over time.
2. Local time timestamps that break around daylight saving changes.
3. No unique constraints, allowing duplicate bars.
4. Storing only adjusted prices.
5. One giant unpartitioned table that becomes slow to query and maintain.

Frequently asked questions#

How should I store stock price data in a database?#

Use an instruments table with stable IDs, a bars table keyed by instrument, interval and UTC timestamp, raw prices plus a corporate actions table, and indexes on instrument and time.

Should I store prices as floats or decimals?#

Floats are fine for research; use exact decimals for accounting values such as fills, fees and balances.

When should I partition a table?#

When it grows into hundreds of millions of rows or when you regularly query recent periods and archive old ones.

Next, learn TradingView's scripting language in Pine Script Basics.