A current price answers one question: what does this listing cost now? A price-history tool has a harder job. It must keep the observation's date, location, marketplace, product identity, and availability together, then make those records quick to explore.
AlgoFly's Retail Analytics App | Products Price History video describes processing and indexing millions of scraped records to look up history across marketplaces, dates, and locations. The public description does not disclose its full database schema or benchmark setup. This article is an engineering companion to that problem: the examples below are a reference design, not a claim that the private demo used a particular database.
Store an observation rather than overwriting a product
Separate the product catalogue from the observations collected about it. A catalogue entry represents a product or variant. A listing represents a marketplace's offer. An observation records what that offer looked like at a particular moment.
For example, “500 ml original formula” and “1 litre original formula” should not collapse into one history just because their names are similar. A sale price at one location should not overwrite a regular price observed elsewhere. Keeping the dimensions explicit makes later comparisons explainable.
Useful observation fields include an internal listing identifier, observed time, marketplace, location, currency, displayed price, availability, source URL, and ingestion batch. Retaining a reference to the raw collected record helps investigate a suspicious drop without guessing what the scraper saw.
Resolve the product before comparing its prices
Search text is convenient for discovery but fragile as an identity key. Titles change, punctuation varies, and bundles often reuse a base product's name. Prefer stable marketplace identifiers for listings and a separate mapping to canonical product variants.
Matching across marketplaces is a distinct process. Brand, model, package size, barcode where legitimately available, and unit count can provide evidence. A confidence score is useful only when its meaning is documented; uncertain matches should remain reviewable rather than silently producing a misleading price chart.
Preserve the difference between an exact match and a possible match. The interface can show a related item without treating its observations as part of the same historical series.
Design the index around the questions people ask
A common interaction selects a product, then a marketplace and location, then a time range. A reference relational query might look like this:
SELECT observed_at, price_minor, currency, available
FROM price_observations
WHERE listing_id = :listing_id
AND location_key = :location_key
AND observed_at >= :from_time
AND observed_at < :to_time
ORDER BY observed_at;
CREATE INDEX price_history_lookup
ON price_observations (listing_id, location_key, observed_at);
This is an illustrative PostgreSQL-style schema and query. The composite index follows the equality filters before the time range. Whether it is right for an actual workload depends on selectivity, write volume, other queries, and the query planner. Inspect a plan on representative data before assuming an index made a dashboard fast.
Keyword search and historical retrieval can be separate paths. A search index may help someone find the product, while a history query reads observations by stable identifiers. Re-running fuzzy title search across every historical record is usually a poor substitute for identity resolution.
Make retries safe and missing values explicit
Collectors fail, pages change, and a batch can be retried. Use an idempotency key derived from the source listing, collection window, and relevant collection context so a retry does not create duplicate points. Keep ingestion time separate from observation time; a late-arriving batch should not look like today's price.
Store money in a representation with defined precision, such as integer minor units with currency metadata. Do not silently interpret a missing price as zero. Likewise, a missing observation, an unavailable product, and a failed scrape are three different states.
For source data collected through authorized access, retain enough collection metadata to diagnose format changes. A parser that accidentally reads a financing amount as a purchase price can create a very convincing but entirely false price drop.
Keep location, packaging, and currency attached
A chart should make its filters visible. If the selected location changes halfway through the range, either split the series or explain the change. If currency changes, do not join raw amounts into one line. If a pack changes size, a unit-price comparison needs a reliable quantity conversion.
Observed price is not necessarily the final checkout amount. Delivery fees, membership rules, quantity discounts, taxes, and personalized offers can alter it. Name the measured field precisely rather than presenting every collected number as a universal landed cost.
A useful first interface can show a compact history table, an availability indicator, and links back to source observations before adding advanced charts. The audit trail matters more than a decorative line that hides the context needed to interpret it.
Reduce repeated work without losing the underlying facts
For large ranges, precompute summaries at a documented grain: for example, daily minimum, maximum, last observed price, and observation count per listing and location. Those summaries make broad views cheaper while raw observations remain available for inspection.
Choose the aggregation according to the question. A daily minimum answers a different question from the last observed price. Neither should be labeled simply “the daily price” without explanation. Gaps should remain gaps unless an explicit carry-forward rule is applied.
Measure query latency with realistic filters and history lengths. Separate collector throughput, ingestion lag, search latency, and chart-rendering time. “Millions of records” describes the scale of the video's problem; it does not by itself prove that a particular storage design will perform well.
The interesting product is not a table full of scraped numbers. It is a history a person can interrogate: which item, which offer, where, when, and why this point belongs in the comparison.