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} .…