Competitor Price History Storage: Database Schemas and Time Series

Features of Storing Price Data

Prices collected from competitor websites are a classic example of a time series. Each record has a timestamp, product ID, and a numeric price value. In addition to the price, the following can be recorded: product availability, currency, promotional price, delivery region. Key characteristics of such a data stream:

• High frequency of incoming data — parsers can update prices dozens of times per day for a single product;
• Predominance of insert operations — data is constantly appended, deletions and updates are rare;
• “Window” analytics — changes over a day, week, month are most often requested;
• Need for compressing old data — to save space and speed up queries.

Ignoring these features leads to database bloat, performance degradation, and high storage costs. Therefore, ESK Solutions engineers, when developing SaaS solutions for price monitoring, always rely on relational DBMSs with time series support and apply cloud technologies for flexible scaling.

Database Schemas in PostgreSQL

The classic approach is to use a normalized structure with separate tables for products, competitors, and actual prices. An example schema:

Table products

• product_id (UUID or BIGINT) — unique identifier;
• sku, name, category, url on the competitor's website;
• metadata (JSONB) — additional attributes.

Table competitors

• competitor_id, name, domain, activity flag.

Table price_snapshots — main time series table

• snapshot_id (BIGINT, primary key), timestamp (TIMESTAMPTZ), product_id, competitor_id;
• price (NUMERIC), currency (VARCHAR), in_stock (BOOLEAN), promo_flag (BOOLEAN);
• raw_data (JSONB) — full parser response (may be useful for auditing).

For high-load systems, such a table needs to be partitioned by time (for example, by month or week). PostgreSQL version 10 and later offers declarative partitioning, which simplifies management and speeds up queries through partition pruning.

Indexing and Optimization

You cannot create a single index on all fields: that will slow down inserts. A minimal recommended set:

• Composite index (product_id, timestamp) — covers typical queries “product price over a period”;
• Index on timestamp for cleaning old data and time slices;
• Partial index on in_stock to filter products in stock.

When using JSONB, it's best to avoid scanning the entire document — it's better to extract frequently queried fields, as shown above. A web analytics system will be able to use these indexes to build dashboards without unnecessary delays.

Compression and Time Series: TimescaleDB and Native Compression

Even with partitioning, data volume grows linearly. The TimescaleDB extension comes to the rescue — an add-on over PostgreSQL that turns it into a full-fledged time-series database. Key features:

• Automatic chunking (time segments) instead of manual partitioning;
• Built-in hypertables with clear syntax;
• Native compression with segment compression (up to 90-98% space savings);
• Continuous Aggregates — materialized views updated incrementally;
• Policy for automatic compression of old data and retention policy.

Example of creating a hypertable from price_snapshots:

SELECT create_hypertable('price_snapshots', 'timestamp');

Then, compression is enabled for segments older than 7 days:

ALTER TABLE price_snapshots SET (timescaledb.compress);

SELECT add_compression_policy('price_snapshots', INTERVAL '7 days');

Queries on compressed data execute transparently. This architecture works excellently as part of SaaS solutions for price monitoring, where fast response times for any sampling window are critical.

Compression Rate Estimation Example

Suppose 5 million price records are loaded daily, resulting in 150 million rows per month. Without compression, each record could take ~100 bytes, totaling 15 GB per month. After applying TimescaleDB compression, the volume drops to 0.3–0.5 GB. The savings in disk space and acceleration of full-scan queries make this solution cost-effective.

Analytics Based on Price History

Collected data is just raw material. Business value emerges after applying analytical tools. Key use cases:

• Calculating the competitor's weighted average price over a period;
• Identifying the frequency and depth of discounts;
• Comparing your own prices with the market for dynamic pricing;
• Building trends and forecasts.

In PostgreSQL, this is implemented using window functions and aggregates. For example, a query showing the price change relative to the previous snapshot:

SELECT price - LAG(price) OVER (PARTITION BY product_id ORDER BY timestamp) AS price_diff ...

If the database is deployed in the cloud with autoscaling, the load from complex analytical queries does not interfere with operational processing — experience with ESK cloud services confirms that separating compute resources for analytics using read replicas prevents conflicts.

Frequently Asked Questions

Can price history be stored without specialized extensions using plain PostgreSQL?

Yes, for small volumes (up to a few tens of millions of records), built-in tools suffice: partitioning, proper indexes, and aggressive VACUUM. However, as data grows, you will inevitably encounter performance degradation, and adding TimescaleDB becomes a logical step.

How often should time series compression be run?

It is optimal to configure an automatic segment compression policy, for example, 7 days after the time segment closes. In active systems, you can compress daily on a schedule. The key is not to compress segments that may still receive writes: TimescaleDB blocks this automatically.

Does compression affect analytics accuracy?

No. TimescaleDB compression uses lossless algorithms, and continuous aggregates recalculate exact values. No single price numeric value is rounded or lost.

Should hot data be moved to RAM?

Yes, for frequently queried periods, it makes sense to keep the most recent weeks in uncompressed tables, and everything older in compressed form. This gives you a balance between speed and storage cost. We help design such solutions as part of our services for comprehensive price parsing and storage.