ODDS HISTORY

Store Pinnacle odds history yourself.

The Pinnacle odds API serves current prices and states plainly that it has no historical archive. A line's history, a closing price, the shape of a market over a week: all of it exists only if you recorded it as you polled. This page gives a table design, a deduplication rule and the retention arithmetic for a store that stays small.

What to record

Two tables cover it. One holds the events, written once and updated when the start time or the status changes: event_id, sport_id, league, home, away and starts. The other holds price changes, one row each time a selection's price differs from the last row you hold for it: which event, which period, which market, which side, the price and when you observed it.

CREATE TABLE price_changes (
  event_id    bigint      NOT NULL,
  period      smallint    NOT NULL,
  market      text        NOT NULL,   -- money_line, spread, total
  side        text        NOT NULL,   -- home, draw, away, over, under
  points      numeric(6,2),           -- spread or total line, else NULL
  price       numeric(8,3),           -- NULL when the market closed
  observed_at timestamptz NOT NULL,
  cursor_last bigint      NOT NULL,   -- the response's last value
  PRIMARY KEY (event_id, period, market, side, points, observed_at)
);

Store the price as decimal odds exactly as served, and keep the null when a market is suspended or closed. A null row is information: it records when the market went away, which is what a closing-price query needs.

Write only what changed

The since cursor already limits a poll to the events that changed, but inside a changed event most selections are usually the same as before. Compare each selection against the latest row you hold and insert only on a difference. A small in-memory map of the latest price per selection makes the comparison free; rebuild it from the table at startup. Without this rule a live soccer board writes every selection on every poll, and the table grows by the poll rate rather than by the market's actual movement.

Capturing the close

The closing price is the last price observed before the event's start time, and it only exists in your store if the poller was running then. Poll prematch boards right up to the start, since the cursor keeps the cost low, and let a job that runs a few minutes after each start mark the final prematch row per selection as the close. Why the close deserves its own column is set out in the closing line value guide on pnclDATA: it is the benchmark most bettors measure their prices against.

Retention arithmetic

The store's growth depends on movement, not on polling, once deduplication is in. The figures below are an illustration of the arithmetic, not a measurement of the feed; substitute your own counts after a week of logging.

Assumption	Value	Result
Price changes written per day, five sports, live and prematch	200,000	Rows per day
Bytes per row with index	120	24 MB a day
Raw rows kept	90 days	About 2.2 GB
Hourly last price per selection beyond 90 days	1 row an hour	Well under a tenth of the raw size

Partition the changes table by month and drop partitions instead of deleting rows. Keep event rows for as long as the changes that reference them exist.

Gaps stay gaps

Nothing on the API replays the past. A poller that was down for an hour has an hour with no rows, and the next poll returns the current state, not the path that led to it. Record outages in a separate table so a query over the history can tell a quiet market from a missing one, and treat the day you start recording as the first day your history covers. What to check before trusting anyone else's archive of these prices is on the historical odds data guide on pnclAPI.

Get API access

Questions before the first row

Can the drops endpoint backfill history?

Only a few minutes of it. The REST drops endpoint returns recent moves inside a bounded window, which helps after a short outage and not beyond it.

Should I store the whole response as JSON?

Keep a compressed copy of raw responses for a few days if you want to re-derive rows after a parser bug, but query the normalised table. Raw JSON grows with the poll rate; the changes table grows with the market.

Does the free key allow this?

For one board polled every 15 minutes, yes, and the history will be coarse. A useful line history needs the paid REST plans, whose per-second rate is the number to size from.

The absence of an archive and the cursor behaviour follow the vendor's published documentation, checked on 26 September 2026. Published 29 September 2026.