Lesson 4.3: JSONB and Semi-Structured Data
When to Use JSONB
Good use cases:
-- 1. Flexible attributes (vary by row)
CREATE TABLE products (
id BIGSERIAL PRIMARY KEY,
name TEXT NOT NULL,
category TEXT NOT NULL,
attributes JSONB -- Different per category
);
-- Electronics: {"brand": "Apple", "warranty_years": 2}
-- Clothing: {"size": "L", "color": "blue", "material": "cotton"}
-- 2. API responses (store as-is)
CREATE TABLE api_logs (
id BIGSERIAL PRIMARY KEY,
endpoint TEXT,
request JSONB,
response JSONB,
created_at TIMESTAMPTZ
);
-- 3. Model configurations
CREATE TABLE ml_models (
id BIGSERIAL PRIMARY KEY,
name TEXT,
config JSONB -- Hyperparameters, architecture
);
Bad use cases:
-- 1. Structured data that should be normalized
-- Bad:
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
data JSONB -- {"email": "...", "name": "...", "age": 30}
);
-- Good:
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
email TEXT,
name TEXT,
age INT
);
-- 2. Data you need to JOIN on (put in columns)
-- 3. Data that needs strong typing (use columns)
JSONB Performance
Indexing JSONB:
-- GIN index for containment queries
CREATE INDEX products_attrs_gin ON products USING GIN(attributes);
-- Fast queries
SELECT * FROM products WHERE attributes @> '{"brand": "Apple"}';
SELECT * FROM products WHERE attributes ? 'color';
-- Index specific field
CREATE INDEX products_price_idx ON products((attributes->>'price')::numeric);
SELECT * FROM products WHERE (attributes->>'price')::numeric < 100;
JSONB vs columns:
-- Test data
INSERT INTO products_jsonb SELECT
generate_series(1, 1000000),
'Product ' || generate_series(1, 1000000),
'{"price": 99.99, "brand": "Apple"}'::jsonb;
-- Query JSONB (slower)
SELECT * FROM products_jsonb
WHERE attributes->>'brand' = 'Apple';
-- Time: ~300ms
-- Query column (faster)
SELECT * FROM products_cols WHERE brand = 'Apple';
-- Time: ~50ms (6x faster with same index)
Rule: Use JSONB for truly flexible data. Use columns for anything you query frequently.
Key Takeaways
- JSONB is perfect for flexible attributes that vary by row
- Use JSONB for API responses and model configurations
- Don't use JSONB for structured data that should be in columns
- JSONB queries are 6x slower than column queries even with indexes
- GIN indexes enable fast JSONB containment queries
- Index specific JSONB fields with expression indexes for better performance
- Use columns for frequently queried data, JSONB for everything else
التالي · الدرس 25 من 45
Soft Deletes and Audit Tables
قراءة 2 دقيقة
أنهيت 24 من 45 درسًا

اسأل Byte عن هذا الدرس
محادثة خاصة حول هذا الدرس. قرأ Byte الدرس كاملًا، ويجيب خلال ثوانٍ، ويُظهر القسم الذي تأتي منه كل إجابة. يمكنك السؤال بأي لغة.
الأسئلة والأجوبة
أسئلة عامة حول هذا الدرس. يجيب AI Tutor فورًا بلغتك، ويراجع فريق FreeAcademy الإجابات، ويمكن لكل المتعلمين قراءتها.

