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.
Top comments (0)