JSONB for Flexible Product Catalog

intermediate

E-commerce platform with 100K products. Each product has different attributes (electronics have warranty, clothes have size/color, books have ISBN). Challenge: Schema changes for every new attribute type are expensive. Solution: Use JSONB for flexible attributes without schema changes.

-- Problem: Rigid schema requires ALTER TABLE for new attributes

CREATE TABLE products_rigid (

product_id SERIAL PRIMARY KEY,

name VARCHAR(200),

price DECIMAL(10,2),

-- Electronics

warranty_months INTEGER,

-- Clothes

size VARCHAR(10),

color VARCHAR(50),

-- Books

isbn VARCHAR(20),

author VARCHAR(100)

);

-- Problem: Most columns NULL for most products

-- Adding new attribute = ALTER TABLE (expensive)

-- Solution: JSONB for flexible attributes

CREATE TABLE products (

product_id SERIAL PRIMARY KEY,

name VARCHAR(200),

price DECIMAL(10,2),

category VARCHAR(50),

attributes JSONB -- Flexible attributes

);

-- Insert electronics product

INSERT INTO products (name, price, category, attributes) VALUES (

'Wireless Headphones',

99.99,

'electronics',

'{

"brand": "Sony",

"color": "black",

"warranty_months": 24,

"features": ["noise-canceling", "bluetooth", "40-hour-battery"],

"rating": 4.5,

"reviews_count": 1250

}'