Menu

Schema Versioning for a Live-Updating Product Catalogue
📰

Schema Versioning for a Live-Updating Product Catalogue

DEV Community: database·desgh white·about 3 hours ago
#A5KSh7xW
#devtodatabase#devto#dev#version#view
Reading 0:00
15s threshold

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

Continue reading — create a free account

Join HashtagPLUS to read full articles, follow hashtags, vote, and join the conversation.