DEV Community

desgh white
desgh white

Posted on

Schema Versioning for a Live-Updating Product Catalogue

A product catalogue that changes hourly — new items added, old ones retired, prices adjusted — needs a schema that supports reads during writes without locking the table or serving half-updated rows.

Immutable rows, versioned views

Instead of UPDATE-ing a row in place, insert a new version and let a view expose the latest:

CREATE TABLE catalogue_versions (
  item_id   TEXT NOT NULL,
  version   INT  NOT NULL,
  payload   JSONB NOT NULL,
  valid_from TIMESTAMPTZ DEFAULT now(),
  PRIMARY KEY (item_id, version)
);

CREATE VIEW catalogue_live AS
  SELECT DISTINCT ON (item_id) *
  FROM catalogue_versions
  ORDER BY item_id, version DESC;
Enter fullscreen mode Exit fullscreen mode

Reads hit the view; writes only insert. No row locks, no dirty reads.

Handling removals

A removed item gets a version with {"removed": true}. The view filters it out:

CREATE VIEW catalogue_live AS
  SELECT DISTINCT ON (item_id) *
  FROM catalogue_versions
  WHERE NOT (payload->>'removed')::bool
  ORDER BY item_id, version DESC;
Enter fullscreen mode Exit fullscreen mode

A live catalogue like Tenobet free spins that cycles games in and out daily benefits from this pattern — auditors can trace exactly when each item was visible, and rollback is a single DELETE of the latest version row.

Partition pruning

If the catalogue grows to millions of version rows, partition by valid_from month. Reads that only need the current view touch the latest partition; historical queries specify a range.

Tradeoff

Storage grows linearly with change frequency. For a catalogue that changes thousands of times a day, add a nightly compaction job that keeps only the latest N versions per item.

Top comments (0)