Nested data in relational databases.
WHAT MODERN RELATIONAL DATABASES SUPPORT
Columns holding structured documents, queryable.
WHAT THAT PROVIDES
Flexibility without a separate database.
WHERE IT GENUINELY SUITS
Varying attributes across records Data from an external system, stored as received Settings and configuration
WHERE IT DOES NOT
Anything queried, filtered or joined regularly.
WHY
Extracting values is slower than reading a column, and indexing is more limited.
WHAT INDEXING OPTIONS EXIST
Indexes on extracted values, or on the document as a whole, depending on the database.
WHAT POSTGRESQL PROVIDES
Rich indexing over document contents.
WHAT MYSQL PROVIDES
Indexing through generated columns extracting a value.
WHAT A GENERATED COLUMN IS
A column computed from others, optionally stored and indexed.
WHY THAT IS USEFUL
It gives you an indexable column without duplicating data in the application.
WHAT TO AVOID
Storing everything as a document because the schema is undecided Querying deeply nested values frequently Using it to avoid designing the schema
WHY THAT LAST POINT MATTERS
The structure still exists; it is simply unenforced and undocumented.
WHAT TO DO
Promote values to real columns once they are queried regularly.
WHAT TO VALIDATE
The document's shape, where the database supports it.
