Skip to content
AllTick
Integration Tutorials

Market Data Storage: Time & Deduplication

Design PostgreSQL schemas for OHLCV market data using UTC timestamps, numeric precision, idempotent upserts, and calendar-aware gap detection.

Updated:
Maintained by:AllTick Technical Team
Audience:Data engineers building market data pipelines and time-series storage

Core Takeaway · 30-Second Brief

Reliable market data storage follows three principles: store all timestamps in UTC (timestamptz) and prices as exact numeric/decimal types, enforce a composite primary key on (code, period, bar_time) for idempotent upserts, and run calendar-aware gap detection to backfill missing bars via REST.

01

Recommended Table Schema

PostgreSQL OHLCV schemasql
CREATE TABLE kline (
  code        text        NOT NULL,
  period      text        NOT NULL,
  bar_time    timestamptz NOT NULL,
  open        numeric(20, 8) NOT NULL,
  high        numeric(20, 8) NOT NULL,
  low         numeric(20, 8) NOT NULL,
  close       numeric(20, 8) NOT NULL,
  volume      numeric(28, 4),
  turnover    numeric(28, 4),
  source      text        NOT NULL DEFAULT 'alltick',
  ingested_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (code, period, bar_time)
);
  • Use numeric/decimal for prices and volumes to prevent floating-point rounding drift.
  • Use timestamptz for bar_time, converting Unix millisecond timestamps to UTC on ingestion.
  • Retain source and ingested_at metadata for lineage auditing and pipeline debugging.

02

Idempotent Writes & Deduplication

sqlsql
INSERT INTO kline (code, period, bar_time, open, high, low, close, volume, turnover)
VALUES ($1, $2, to_timestamp($3 / 1000.0), $4, $5, $6, $7, $8, $9)
ON CONFLICT (code, period, bar_time)
DO UPDATE SET open = EXCLUDED.open, high = EXCLUDED.high, low = EXCLUDED.low,
              close = EXCLUDED.close, volume = EXCLUDED.volume, turnover = EXCLUDED.turnover;

An open (in-progress) K-line bar updates continuously until the interval closes. Using ON CONFLICT DO UPDATE overwrites stale values cleanly and prevents duplicate rows during reconnect backfills or overlapping pagination.

03

Calendar-Aware Gap Detection

1Generate expected bar timestamps for a symbol using its exchange trading calendar.
2Diff expected timestamps against stored bar_time rows to identify candidate gaps.
3Exclude market closures, midday lunch breaks, and public holidays to avoid false alarms.
4Backfill genuine gaps via the REST K-line endpoint using kline_timestamp_end.
5Log backfill outcomes and flag persistent gaps for manual review.

04

Verify Storage & Redistribution Licensing

Long-term persistence, redistribution, and public display of market data are governed by provider terms of service. Before archiving feeds in production, confirm that your subscription plan permits database storage and downstream usage.

Sources & Verification · 2026-10-13

Relevant endpoint, field, and exchange rules were checked against the primary specifications linked below.

Authenticated AllTick requests and production end-to-end tests were not run for this review. Record your token plan, library versions, market session, ret/trace, and observed results when reproducing these checks.

Reproduce before deployment

  • Replay a batch twice and confirm stable row counts; then replay a corrected bar and inspect the upsert. Test rollback after transaction interruption.
  • Preserve timestamp units and decimals, distinguish market closure from missing data, and store source, adjustment mode, and instrument identity with the series.