PostgreSQL JSONB: Schema Flessibile senza Migrazioni

Backend
15 Feb, 2026

PostgreSQL JSONB: schema flessibile senza migrazioni continue

PostgreSQL JSONB: il best of both worlds tra NoSQL flexibility e SQL power

⏱️ 15 minuti di lettura | 💻 Codice completo su GitHub → | 🎯 Livello: Intermedio

TL;DR

Cos’è JSONB? Tipo di dato PostgreSQL che combina flessibilità JSON con performance SQL

Perché usarlo?

✅ Schema flessibile senza migrazioni continue
✅ Query potenti su dati nested
✅ Performance eccellenti con GIN indexes
✅ Combina NoSQL flexibility + SQL power

Quando usarlo?

✅ Dati con struttura variabile
✅ Metadata e configurazioni
✅ Log e analytics
✅ Attributi custom degli utenti

Quando NON usarlo?

❌ Dati sempre uguali (usa colonne normali)
❌ Calcoli frequenti (meglio colonne tipizzate)
❌ Foreign keys nei JSON

Differenza JSON vs JSONB:

-- JSON: testo stored as-is, lento
-- JSONB: binary format, veloce, indexabile ✅

Quick Start | Benchmark Performance | Tutti gli operatori

Il Problema: Dati con Schema Variabile

La situazione è comune: hai una tabella con una struttura base condivisa, ma ogni record ha attributi specifici che dipendono dal suo tipo. Il problema si manifesta subito nella fase di modellazione.

Le Soluzioni Classiche e i Loro Limiti

Opzione 1: EAV (Entity-Attribute-Value) ❌

entity_attributes
  entity_id | attribute_name | attribute_value
  uuid-1    | field_a        | value_x
  uuid-1    | field_b        | 42
  uuid-2    | field_c        | value_y

Flessibile in teoria. In pratica: 5+ query per ricostruire un record, nessun type safety, impossibile fare query efficienti.

Opzione 2: Colonne Sparse ❌

class Record(Base):
    # campi base
    field_type_a = Column(String)   # solo per tipo A
    field_type_b = Column(Integer)  # solo per tipo B
    field_type_c = Column(Float)    # solo per tipo C
    field_type_d = Column(String)   # solo per tipo D
    # ... 20+ colonne sparse

90% delle colonne sono NULL per ogni record. Una migrazione ogni volta che si aggiunge un tipo.

Opzione 3: Tabelle Separate ❌

class TypeARecord(Base):
    base_id = Column(UUID, ForeignKey('base.id'))
    field_a = Column(String)

class TypeBRecord(Base):
    base_id = Column(UUID, ForeignKey('base.id'))
    field_b = Column(Integer)

Join obbligatori per ogni query. Logica dispersa su molte tabelle. Difficile aggiungere tipi nuovi.

La Soluzione: JSONB

class Record(Base):
    id = Column(UUID, primary_key=True)
    type = Column(String(20))         # discriminatore
    base_field = Column(String(100))  # campi condivisi

    metadata = Column(JSONB, default={})  # attributi type-specific

Ogni tipo porta i suoi dati nel campo JSONB:

# Tipo A
metadata = {"field_a": "value", "config": {"x": 1, "y": 2}}

# Tipo B
metadata = {"items": [{"name": "foo", "qty": 3}], "threshold": 10}

# Tipo C
metadata = {"coordinates": {"lat": 45.4, "lon": 9.2}, "radius": 500}

Schema flessibile, nessuna migrazione per nuovi tipi, query su JSONB con GIN index veloci come colonne native.

Cos’è JSONB (Spiegato Semplice)

JSON vs JSONB

PostgreSQL ha DUE tipi JSON:

Feature	JSON	JSONB
Storage	Testo esatto	Binary format
Performance	Lento ❌	Veloce ✅
Indexes	No ❌	Sì (GIN) ✅
Duplicate keys	Mantiene	Rimuove
Operatori	Limitati	Completi ✅
Quando usare	Mai	Sempre

Regola semplice: usa sempre JSONB. JSON esiste solo per legacy.

Quando Ha Senso JSONB

✅ Situazioni PERFETTE per JSONB:

Attributi Custom Utente

{
  "settings": {
    "theme": "dark",
    "notifications": true,
    "language": "it"
  },
  "preferences": {
    "music_volume": 80,
    "sfx_volume": 60
  }
}

Metadata Variabile

{
  "source": "steam",
  "tags": ["rpg", "multiplayer"],
  "analytics": {
    "playtime_hours": 42,
    "favorite_class": "warrior"
  }
}

Log e Analytics

{
  "event": "quest_completed",
  "timestamp": "2025-01-15T10:30:00Z",
  "data": {
    "quest_id": "uuid-123",
    "completion_time_seconds": 1247,
    "attempts": 3
  }
}

Configurazioni

{
  "quest_config": {
    "max_attempts": 3,
    "time_limit_minutes": 60,
    "rewards": {
      "xp": 500,
      "gold": 100,
      "items": ["rare_gem"]
    }
  }
}

❌ Situazioni SBAGLIATE per JSONB:

Dati Strutturati e Stabili

# Don't do this
user_data = Column(JSONB)  # {"name": "Mario", "email": "..."}

# Use regular columns
name = Column(String)
email = Column(String)

Foreign Keys

// ❌ Don't do this
{
  "player_id": "uuid-123",  // No foreign key constraint!
  "guild_id": "uuid-456"
}

Calcoli Frequenti

// ❌ Slow for aggregations
{
  "score": 1500,
  "level": 42
}
// Better to use Integer columns for ORDER BY, SUM(), etc

Quick Start (5 Minuti)

Step 1: Creare Tabella (30 secondi)

-- Crea tabella con JSONB
CREATE TABLE quests (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title VARCHAR(100) NOT NULL,
    difficulty VARCHAR(20) NOT NULL,
    reward_xp INTEGER NOT NULL,

    quest_data JSONB NOT NULL DEFAULT '{}'::jsonb,

    created_at TIMESTAMP DEFAULT NOW()
);

Step 2: Insert Dati (1 minuto)

-- Insert quest con dati custom
INSERT INTO quests (title, difficulty, reward_xp, quest_data)
VALUES (
    'Hunt the Goblins',
    'medium',
    150,
    '{
        "type": "combat",
        "enemy": "goblin",
        "kill_count": 10,
        "area": "dark_forest"
    }'::jsonb
);

INSERT INTO quests (title, difficulty, reward_xp, quest_data)
VALUES (
    'Explore Ancient Ruins',
    'hard',
    300,
    '{
        "type": "exploration",
        "coordinates": {"x": 42.5, "y": 13.7},
        "radius": 50,
        "markers": ["ruins", "cave"]
    }'::jsonb
);

Step 3: Query Base (2 minuti)

-- All combat quests
SELECT * FROM quests
WHERE quest_data @> '{"type": "combat"}';

-- Quest with a specific enemy
SELECT * FROM quests
WHERE quest_data->>'enemy' = 'goblin';

-- Quest in a specific area
SELECT * FROM quests
WHERE quest_data->'coordinates'->>'x' = '42.5';

-- Count quests by type
SELECT
    quest_data->>'type' as quest_type,
    COUNT(*) as total
FROM quests
GROUP BY quest_data->>'type';

Step 4: Index per Performance (1 minuto)

-- GIN index for fast queries
CREATE INDEX idx_quests_data ON quests USING GIN (quest_data);

-- Path-specific index (faster when queries always target the same field)
CREATE INDEX idx_quests_enemy ON quests ((quest_data->>'enemy'));

Fatto! Hai JSONB funzionante in 5 minuti. 🎉

Tutti gli Operatori JSONB

Operatori di Accesso

-- -> ritorna JSONB
SELECT quest_data->'coordinates' FROM quests;
-- Risultato: {"x": 42.5, "y": 13.7}

-- ->> ritorna TEXT
SELECT quest_data->>'type' FROM quests;
-- Risultato: "combat" (string)

-- #> accesso nested con path array
SELECT quest_data #> '{coordinates, x}' FROM quests;
-- Risultato: 42.5

-- #>> accesso nested come TEXT
SELECT quest_data #>> '{coordinates, x}' FROM quests;
-- Risultato: "42.5" (string)

Operatori di Ricerca

-- @> contiene (json sinistro contiene destro)
SELECT * FROM quests
WHERE quest_data @> '{"type": "combat"}';

-- <@ contenuto in (json destro contiene sinistro)
SELECT * FROM quests
WHERE '{"type": "combat"}' <@ quest_data;

-- ? chiave esiste
SELECT * FROM quests
WHERE quest_data ? 'enemy';

-- ?| almeno una chiave esiste
SELECT * FROM quests
WHERE quest_data ?| array['enemy', 'target'];

-- ?& tutte le chiavi esistono
SELECT * FROM quests
WHERE quest_data ?& array['type', 'enemy', 'kill_count'];

Esempi Pratici Completi

-- 1. Trova combat quest con goblins
SELECT
    id,
    title,
    quest_data->>'enemy' as enemy,
    (quest_data->>'kill_count')::int as kills_needed
FROM quests
WHERE quest_data @> '{"type": "combat", "enemy": "goblin"}';

-- 2. Quest exploration nell'area specifica
SELECT
    id,
    title,
    quest_data->'coordinates' as coords
FROM quests
WHERE quest_data @> '{"type": "exploration"}'
  AND (quest_data->'coordinates'->>'x')::float BETWEEN 40 AND 50
  AND (quest_data->'coordinates'->>'y')::float BETWEEN 10 AND 20;

-- 3. Quest con item specifico richiesto
SELECT
    id,
    title,
    quest_data->'items' as required_items
FROM quests
WHERE quest_data->'items' @> '[{"name": "iron_sword"}]';

-- 4. Aggregate: conta nemici per tipo
SELECT
    quest_data->>'enemy' as enemy_type,
    COUNT(*) as quest_count,
    AVG((quest_data->>'kill_count')::int) as avg_kills
FROM quests
WHERE quest_data->>'type' = 'combat'
GROUP BY quest_data->>'enemy'
ORDER BY quest_count DESC;

Update e Manipolazione JSONB

Update Campi Nested

-- Update singolo campo
UPDATE quests
SET quest_data = jsonb_set(
    quest_data,
    '{kill_count}',
    '15'::jsonb
)
WHERE id = 'uuid-123';

-- Update nested field
UPDATE quests
SET quest_data = jsonb_set(
    quest_data,
    '{coordinates, x}',
    '45.0'::jsonb
)
WHERE quest_data->>'type' = 'exploration';

-- Aggiungi nuovo campo
UPDATE quests
SET quest_data = quest_data || '{"bonus_xp": 50}'::jsonb
WHERE difficulty = 'hard';

-- Rimuovi campo
UPDATE quests
SET quest_data = quest_data - 'old_field'
WHERE quest_data ? 'old_field';

Operazioni su Array in JSONB

-- Aggiungi elemento ad array
UPDATE quests
SET quest_data = jsonb_set(
    quest_data,
    '{markers}',
    (quest_data->'markers')::jsonb || '["new_marker"]'::jsonb
)
WHERE quest_data->>'type' = 'exploration';

-- Filtra array
SELECT
    id,
    title,
    jsonb_array_elements(quest_data->'items') as item
FROM quests
WHERE quest_data->>'type' = 'crafting';

SQLAlchemy + JSONB

Model Definition

# app/models/quest.py

from sqlalchemy import Column, String, Integer, DateTime, func
from sqlalchemy.dialects.postgresql import UUID, JSONB
from sqlalchemy.orm import DeclarativeBase

class Base(DeclarativeBase):
    pass

class Quest(Base):
    __tablename__ = 'quests'

    id = Column(UUID(as_uuid=True), primary_key=True, server_default=func.gen_random_uuid())
    title = Column(String(100), nullable=False)
    difficulty = Column(String(20), nullable=False)
    reward_xp = Column(Integer, nullable=False)

    # JSONB column con default
    quest_data = Column(JSONB, nullable=False, server_default='{}')

    created_at = Column(DateTime, server_default=func.now())

Query con SQLAlchemy

# app/repositories/quest_repository.py

from typing import List, Optional
from uuid import UUID
from sqlalchemy import select, cast, Float
from sqlalchemy.dialects.postgresql import JSONB

class QuestRepository(BaseRepository[Quest]):

    async def get_by_type(self, quest_type: str) -> List[Quest]:
        """Find quests by type usando @> operator."""
        from sqlalchemy import cast
        result = await self.session.execute(
            select(Quest).where(
                Quest.quest_data.op('@>')(cast({'type': quest_type}, JSONB))
            )
        )
        return list(result.scalars().all())

    async def get_combat_quests(self, enemy: str) -> List[Quest]:
        """Combat quests con nemico specifico."""
        result = await self.session.execute(
            select(Quest).where(
                Quest.quest_data['enemy'].astext == enemy
            )
        )
        return list(result.scalars().all())

    async def get_in_area(
        self,
        min_x: float,
        max_x: float,
        min_y: float,
        max_y: float
    ) -> List[Quest]:
        """Exploration quests in area geografica."""
        result = await self.session.execute(
            select(Quest).where(
                Quest.quest_data['coordinates']['x'].astext.cast(Float).between(min_x, max_x),
                Quest.quest_data['coordinates']['y'].astext.cast(Float).between(min_y, max_y)
            )
        )
        return list(result.scalars().all())

    async def update_quest_data(
        self,
        quest_id: UUID,
        updates: dict
    ) -> Optional[Quest]:
        """Update partial JSONB data."""
        quest = await self.get(quest_id)
        if not quest:
            return None

        # Merge updates into existing JSONB
        current_data = quest.quest_data or {}
        quest.quest_data = {**current_data, **updates}

        await self.session.flush()
        return quest

Pydantic Schemas con JSONB

# app/schemas/quest.py

from pydantic import BaseModel, Field, ConfigDict
from typing import Optional, Dict, Any, List
from uuid import UUID
from datetime import datetime

class QuestCreate(BaseModel):
    title: str = Field(..., max_length=100)
    difficulty: str = Field(..., pattern="^(easy|medium|hard|legendary)$")
    reward_xp: int = Field(..., gt=0)
    quest_data: Dict[str, Any] = Field(default_factory=dict)

    model_config = ConfigDict(
        json_schema_extra={
            "example": {
                "title": "Hunt the Goblins",
                "difficulty": "medium",
                "reward_xp": 150,
                "quest_data": {
                    "type": "combat",
                    "enemy": "goblin",
                    "kill_count": 10
                }
            }
        }
    )

class QuestResponse(BaseModel):
    id: UUID
    title: str
    difficulty: str
    reward_xp: int
    quest_data: Dict[str, Any]
    created_at: datetime

    model_config = ConfigDict(from_attributes=True)

# Quest-type specific schemas
class CombatQuestData(BaseModel):
    type: str = "combat"
    enemy: str
    kill_count: int
    area: Optional[str] = None

class ExplorationQuestData(BaseModel):
    type: str = "exploration"
    coordinates: Dict[str, float]  # {"x": 42.5, "y": 13.7}
    radius: int
    markers: List[str] = []

Performance & Indexing

GIN Index: Come Funziona

-- General GIN index (covers all queries)
CREATE INDEX idx_quests_data_gin ON quests USING GIN (quest_data);

-- Path-specific index (faster for repetitive queries on same field)
CREATE INDEX idx_quest_type ON quests ((quest_data->>'type'));
CREATE INDEX idx_quest_enemy ON quests ((quest_data->>'enemy'));

-- Index with jsonb_path_ops (smaller index, ma supporta solo @>, @? e @@ — non supporta ?, ?|, ?&)
CREATE INDEX idx_quests_data_ops ON quests
USING GIN (quest_data jsonb_path_ops);

Benchmark Performance

Ho fatto test con 100,000 quest:

-- Setup test data
INSERT INTO quests (title, difficulty, reward_xp, quest_data)
SELECT
    'Quest ' || i,
    (ARRAY['easy','medium','hard','legendary'])[1 + (random() * 3)::int],
    (random() * 500)::int + 50,
    jsonb_build_object(
        'type', (ARRAY['combat','exploration','crafting'])[1 + (random() * 2)::int],
        'enemy', (ARRAY['goblin','orc','dragon'])[1 + (random() * 2)::int],
        'kill_count', (random() * 20)::int + 5
    )
FROM generate_series(1, 100000) i;

Risultati:

Query	Senza Index	Con GIN Index	Speedup
`@>` contains	245ms	8ms	30x ✅
`->` access	198ms	198ms	1x (no benefit)
`->>` text access	210ms	12ms	17x ✅
`?` key exists	230ms	6ms	38x ✅

Takeaway: GIN index è essenziale per query JSONB in produzione.

Query Optimization Tips

-- ❌ Lento: cast in SELECT
SELECT * FROM quests
WHERE (quest_data->>'kill_count')::int > 10;

-- ✅ Veloce: expression index
CREATE INDEX idx_kill_count ON quests (
    ((quest_data->>'kill_count')::int)
);
SELECT * FROM quests
WHERE (quest_data->>'kill_count')::int > 10;

-- ❌ Lento: LIKE su JSONB
SELECT * FROM quests
WHERE quest_data->>'enemy' LIKE '%gob%';

-- ✅ Veloce: exact match con index
SELECT * FROM quests
WHERE quest_data->>'enemy' = 'goblin';

-- ✅ Per text search: usa pg_trgm
CREATE INDEX idx_enemy_trgm ON quests
USING gin ((quest_data->>'enemy') gin_trgm_ops);

Errori Comuni (Che Ho Fatto)

Errore #1: Usare JSON invece di JSONB

-- ❌ Used this for 2 weeks
quest_data JSON

-- ✅ Then migrated
quest_data JSONB

-- Migration
ALTER TABLE quests
ALTER COLUMN quest_data TYPE JSONB
USING quest_data::jsonb;

Lesson: Sempre JSONB. JSON esiste solo per backward compatibility.

Errore #2: Dimenticare Index

# Without index: very slow queries (200ms+)
quests = await session.execute(
    select(Quest).where(
        Quest.quest_data['type'].astext == 'combat'
    )
)

# With GIN index: 8ms
CREATE INDEX idx_quests_data ON quests USING GIN (quest_data);

Mi è costato: 3 giorni di debug performance in produzione.

Errore #3: Mettere Tutto in JSONB

# Bad: fields always present stored in JSONB
quest_data = {
    "title": "Quest Title",  # Should be a column!
    "difficulty": "hard",     # Should be a column!
    "custom_data": {...}      # OK in JSONB
}

# Good: only variable data in JSONB
title = Column(String)  # Regular column
difficulty = Column(String)
quest_data = Column(JSONB)  # Custom data only

Regola: Campi sempre presenti → colonne normali. Campi variabili → JSONB.

Errore #4: Foreign Keys in JSONB

// ❌ Don't do this
{
  "player_id": "uuid-123",
  "guild_id": "uuid-456"
}

Problema: No foreign key constraint! Dati orfani garantiti.

✅ Soluzione:

# Foreign keys as regular columns
player_id = Column(UUID, ForeignKey('players.id'))
guild_id = Column(UUID, ForeignKey('guilds.id'))

# Custom data in JSONB
quest_data = Column(JSONB)

Errore #5: Cast Errati

-- ❌ This will crash with malformed data
SELECT (quest_data->>'kill_count')::int FROM quests;
-- ERROR: invalid input syntax for integer: "abc"

-- ✅ Safe cast con COALESCE
SELECT COALESCE(
    (quest_data->>'kill_count')::int,
    0
) FROM quests;

-- ✅ O validazione in application layer

Migration: Da Colonne a JSONB

Scenario Reale

Avevo questa struttura:

# Before (30+ sparse columns)
class Quest(Base):
    id = Column(UUID, primary_key=True)
    title = Column(String)

    # Combat quest fields
    enemy_type = Column(String, nullable=True)
    kill_count = Column(Integer, nullable=True)

    # Exploration fields
    coord_x = Column(Float, nullable=True)
    coord_y = Column(Float, nullable=True)

    # Crafting fields
    item_1 = Column(String, nullable=True)
    item_1_qty = Column(Integer, nullable=True)
    # ... 20 more columns

90% delle colonne NULL per ogni quest.

Migration Step-by-Step

# 1. Add JSONB column
# alembic/versions/xxx_add_quest_data.py

def upgrade():
    op.add_column('quests',
        sa.Column('quest_data', JSONB(),
                  nullable=False,
                  server_default='{}')
    )

    # Migrate existing data
    op.execute("""
        UPDATE quests
        SET quest_data =
            CASE
                WHEN enemy_type IS NOT NULL THEN
                    jsonb_build_object(
                        'type', 'combat',
                        'enemy', enemy_type,
                        'kill_count', kill_count
                    )
                WHEN coord_x IS NOT NULL THEN
                    jsonb_build_object(
                        'type', 'exploration',
                        'coordinates', jsonb_build_object(
                            'x', coord_x,
                            'y', coord_y
                        )
                    )
                ELSE '{}'::jsonb
            END
    """)

    # Create index
    op.execute("""
        CREATE INDEX idx_quests_data
        ON quests USING GIN (quest_data)
    """)

def downgrade():
    op.drop_column('quests', 'quest_data')

# 2. Deploy and test

# 3. Remove old columns (after verification)
# alembic/versions/yyy_remove_old_columns.py

def upgrade():
    op.drop_column('quests', 'enemy_type')
    op.drop_column('quests', 'kill_count')
    op.drop_column('quests', 'coord_x')
    op.drop_column('quests', 'coord_y')
    # ... other columns

Risultato:

Da 35 colonne → 8 colonne
Schema flessibile
Zero migrazioni future per nuovi tipi quest

JSONB vs Alternative

Quando JSONB Vince

Scenario	JSONB	Alternative	Winner
Schema variabile	✅ Zero migration	❌ Migration ogni volta	JSONB
Query complesse	✅ Operatori potenti	❌ Join multipli	JSONB
Performance read	✅ Veloce con index	✅ Veloce	Pari
Type safety	⚠️ Runtime check	✅ DB constraint	Colonne
Foreign keys	❌ Non supportate	✅ Supportate	Colonne
Aggregate	⚠️ Cast necessari	✅ Nativi	Colonne

Quando Colonne Normali Vincono

# Don't put these fields in JSONB
class User(Base):
    user_data = Column(JSONB)
    # {
    #   "email": "...",      # ❌ Usa colonna
    #   "created_at": "...", # ❌ Usa colonna
    #   "is_active": true    # ❌ Usa colonna
    # }

# Use regular columns for structured data
class User(Base):
    email = Column(String, unique=True)  # ✅ With constraint
    created_at = Column(DateTime)         # ✅ With index
    is_active = Column(Boolean)           # ✅ With default

    # Custom data only in JSONB
    preferences = Column(JSONB, default={})

Best Practices

1. Schema Validation in Application

# app/schemas/quest.py

from pydantic import BaseModel, field_validator
from typing import Dict, Any

class QuestCreate(BaseModel):
    quest_data: Dict[str, Any]

    @field_validator('quest_data')
    @classmethod
    def validate_quest_data(cls, v):
        """Valida struttura JSONB."""
        quest_type = v.get('type')

        if quest_type == 'combat':
            required = ['enemy', 'kill_count']
        elif quest_type == 'exploration':
            required = ['coordinates', 'radius']
        else:
            raise ValueError(f"Unknown quest type: {quest_type}")

        for field in required:
            if field not in v:
                raise ValueError(f"Missing required field: {field}")

        return v

2. Utility Functions

# app/utils/jsonb_helpers.py

def jsonb_merge(original: dict, updates: dict) -> dict:
    """Merge sicuro di JSONB nested."""
    result = original.copy()

    for key, value in updates.items():
        if isinstance(value, dict) and key in result:
            result[key] = jsonb_merge(result[key], value)
        else:
            result[key] = value

    return result

def jsonb_get_nested(data: dict, path: List[str], default=None):
    """Safe nested access."""
    current = data
    for key in path:
        if isinstance(current, dict) and key in current:
            current = current[key]
        else:
            return default
    return current

# Usage
quest_data = {"coordinates": {"x": 42, "y": 13}}
x = jsonb_get_nested(quest_data, ['coordinates', 'x'], default=0)

3. Type-Safe JSONB Access

# app/models/quest.py

class Quest(Base):
    quest_data = Column(JSONB, default={})

    @property
    def enemy(self) -> Optional[str]:
        """Type-safe access to enemy field."""
        if self.quest_data.get('type') == 'combat':
            return self.quest_data.get('enemy')
        return None

    @property
    def coordinates(self) -> Optional[Dict[str, float]]:
        """Type-safe access to coordinates."""
        if self.quest_data.get('type') == 'exploration':
            return self.quest_data.get('coordinates')
        return None

4. Monitoring & Logging

# Log when JSONB payload is too large
if len(json.dumps(quest_data)) > 50000:  # 50KB
    logger.warning(
        f"Large JSONB data: {len(json.dumps(quest_data))} bytes",
        extra={"quest_id": quest.id}
    )

Decision Matrix: Colonna vs JSONB

Usa COLONNA normale se:
├─ ✅ Campo sempre presente
├─ ✅ Tipo specifico (INT, DATE, BOOLEAN)
├─ ✅ Foreign key necessaria
├─ ✅ Aggregate frequenti (SUM, AVG)
├─ ✅ Unique constraint necessario
└─ ✅ Index singolo importante

Usa JSONB se:
├─ ✅ Schema varia per record
├─ ✅ Dati nested complessi
├─ ✅ Aggiunte frequenti di campi
├─ ✅ Metadata/configurazione
└─ ✅ Rapid prototyping

Usa TABELLA SEPARATA se:
├─ ✅ Relationship 1:N
├─ ✅ Foreign keys necessarie
├─ ✅ Query complesse su dati relazionati
└─ ✅ Normalization importante

Conclusione

Prossimi Passi

Prova Subito:

git clone https://github.com/layerbylayer-blog/postgresql-jsonb-guide
docker-compose up
# Database con examples pronti!

Nel Tuo Progetto:

Identifica dati con schema variabile
Migra a JSONB
Aggiungi GIN index
Valida in application layer

Risorse

GitHub: postgresql-jsonb-guide

Include:

✅ Docker setup PostgreSQL
✅ Migration examples
✅ Query cookbook
✅ Performance benchmarks
✅ SQLAlchemy models

Documentazione: