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; 

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; 

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.