# Database Design for Market Data

> How to design database tables for bars, ticks, symbols, orders and fills. Learn keys, indexes, partitioning, data types and how to avoid common design mistakes.

Source: https://learn.tradelabsai.com/programming/database-design-for-market-data/  
Track: Programming and Data · Level: Intermediate · Updated: 2026-10-03  
Publisher: TradeLabs AI (https://tradelabsai.com). Education, not financial advice.  
Cite as: TradeLabs Learn, "Database Design for Market Data", https://learn.tradelabsai.com/programming/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

| Table | Purpose | Key columns |
|---|---|---|
| instruments | One row per tradable instrument | instrument_id, symbol, exchange, asset class, tick size, currency, listed and delisted dates |
| bars | OHLCV candles | instrument_id, interval, ts, open, high, low, close, volume |
| trades | Raw executions from the feed | instrument_id, ts, price, size, trade_id |
| corporate_actions | Splits and dividends | instrument_id, ex_date, type, ratio or amount |
| orders | Your orders | order_id, client_order_id, instrument_id, side, qty, type, price, status, timestamps |
| fills | Your executions | fill_id, order_id, qty, price, fee, ts |

## 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](https://learn.tradelabsai.com/research/survivorship-and-selection-bias/) and [Point-in-Time and Survivorship-Free Data](https://learn.tradelabsai.com/programming/point-in-time-data/).

## Primary keys and uniqueness

| Table | Natural unique key |
|---|---|
| bars | (instrument_id, interval, ts) |
| trades | (instrument_id, trade_id) or (instrument_id, ts, sequence) |
| fills | fill_id from the broker |

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](https://learn.tradelabsai.com/programming/data-pipelines-and-etl/).

## Data types

| Field | Recommended type | Why |
|---|---|---|
| Timestamps | Timestamp with time zone, stored in UTC | Avoids daylight saving confusion. See [Timestamps, Time Zones and Daylight Saving](https://learn.tradelabsai.com/programming/timestamps-and-time-zones/) |
| Prices in research tables | Double precision | Fast and adequate for analysis |
| Prices and money in accounting tables | Fixed precision decimal | Exact sums with no floating point drift |
| Volume | Big integer or decimal | Crypto quantities can be fractional |

## 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.

**Example: Sizing a minute bar table**
Suppose you store one minute bars for 3,000 US stocks. A regular session is 390 minutes, so that is 1,170,000 rows per trading day. With about 252 trading days a year, that is roughly 295 million rows a year. At an estimated 60 bytes per row before indexes, raw storage is about 18 GB per year. That fits easily in PostgreSQL with time partitioning; at tick level it would be much larger, which is where columnar formats help. See [Data Storage, Compression and Caching](https://learn.tradelabsai.com/programming/data-storage/).

## 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](https://learn.tradelabsai.com/programming/adjusted-prices/) and [Corporate Actions, Delistings and Rolls in Backtests](https://learn.tradelabsai.com/research/corporate-actions-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](https://learn.tradelabsai.com/algo-trading/audit-trails/).

## 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](https://learn.tradelabsai.com/programming/pine-script-basics/).

## Continue learning

- Next lesson: [Pine Script Basics](https://learn.tradelabsai.com/programming/pine-script-basics/)
- Previous lesson: [SQL for Trading Data](https://learn.tradelabsai.com/programming/sql-for-trading-data/)
- Related: [SQL for Trading Data](https://learn.tradelabsai.com/programming/sql-for-trading-data/): Learn the SQL queries traders use most: filtering bars, aggregating trades into candles, joining fills to orders, window functions for returns and daily P&L.
- Related: [Data Storage, Compression and Caching](https://learn.tradelabsai.com/programming/data-storage/): Compare ways to store market data: CSV, Parquet, HDF5, PostgreSQL, time series and columnar databases. Learn compression, partitioning and how to choose.
- Related: [Redis and PostgreSQL for Trading](https://learn.tradelabsai.com/infrastructure/redis-and-postgresql-for-trading/): How Redis and PostgreSQL work together in trading systems: Redis for live prices, caches and state, PostgreSQL for durable orders, fills and history.
- Related: [Point-in-Time and Survivorship-Free Data](https://learn.tradelabsai.com/programming/point-in-time-data/): Point in time data records what was known on each date, including restated figures and index changes. Learn why it matters and how to build point in time datasets.
- Related: [Corporate Actions, Delistings and Rolls in Backtests](https://learn.tradelabsai.com/research/corporate-actions-in-backtests/): Splits, dividends, mergers, spin offs and delistings change prices and holdings. Learn how each affects backtests, how to adjust data and the errors to avoid.