How to store price time series: schema, SQL and deduplication

Why append-only beats a current-price table

• History is the product. Trends, seasonality and “lowest price in 30 days” checks all need past values.
• Alerts become auditable. Every alert points at two stored observations.
• Bugs become recoverable. With a hash of each raw response and the parser version, you can re-parse a period after fixing a bug.
• Failures stay visible. A status column distinguishes out-of-stock, blocked and parse errors from real prices.

A table that holds only the current price answers one question and loses the evidence for every other one.

The observation model

The context column keeps one schema for many sources; if a dimension is queried constantly — check-in date for hotels, say — promote it to its own column. Why market and currency must be explicit is covered in comparing prices across markets and point of sale.

Exact amounts and currencies

PostgreSQL's documentation recommends numeric especially for monetary amounts and other quantities where exactness is required, and explains that real and double precision are inexact: some values are stored as approximations, so storing and retrieving can show small discrepancies. In application code, the equivalent is Python's Decimal, never float.

Store the currency the page displayed and the amount as displayed. Do not convert at ingest: exchange rates change, and a converted figure cannot be recomputed with a different rate rule. Put conversions in a view that joins dated reference rates. Record a tax_included flag as well, since the same product can be shown gross in one market and net in another.

Deduplication: idempotent inserts and unchanged prices

Two different things get called duplicates. The first is the same observation ingested twice — a retried batch, a queue that redelivers. Prevent it with a natural primary key over item, market, currency, context and observed_at, and insert with ON CONFLICT DO NOTHING; PostgreSQL's documentation describes that clause as avoiding the insert instead of raising a unique-violation error.

The second is the same price observed many times. Those rows are not duplicates — they prove the price was still valid at each check, which matters for freshness and for 30-day minimums. If storage becomes a concern, derive a compact table of price runs (item, market, price, valid_from, valid_to) for reporting, and keep the raw observations for a defined retention period.

Duplicate items — the same flat or hotel listed under two IDs or on two sites — are a separate problem; see deduplicating listings.

Indexes, partitions and retention

• The primary key doubles as the index for “history of this item in this market”.
• A BRIN index on `observed_at`. PostgreSQL describes BRIN as designed for very large tables where a column correlates with physical position — exactly what an append-only timestamp does. It is tiny compared with a B-tree.
• Monthly partitions by observed_at once the table is large: old months can be compressed, archived or dropped as a unit.
• Retention. Keep raw responses for days or weeks, their hashes and the parsed rows for as long as the analysis needs.

The queries you will run most

• Current price per item and market: DISTINCT ON the key, ordered by observed_at DESC, filtered to status = 'ok'.
• Changes only: the window function lag() over the key, keeping rows where the price differs from the previous successful one.
• Lowest price in the 30 days before an observation: a correlated min() over the preceding 30 days — the reference point for checking announced discounts, as described in travel price transparency rules.

All three are in the SQL below, tested on PostgreSQL 16. For a complete collector that feeds such a table, see how to build a flight price tracker; for running it across a catalogue, price monitoring.