JSON vs JSONB, and views vs materialized views?
Understand the concept
JSON stores JSON input text, while JSONB stores a processed representation that supports efficient operations and indexing. A view stores a query definition, whereas a materialized view stores the query's result for later reads. These are separate choices about data representation and precomputed results.
Important ideas
-
JSON preserves input text; JSONB stores a processed format with useful indexing support.
-
A view stores a query; a materialized view stores its results and needs refreshing.
-
Use regular columns for data that needs strong relational rules and common filters.
-
A materialized view can become stale until refreshed; define freshness and refresh costs before relying on it for reports.
Can you explain it without the notes?
JSON vs JSONB, and views vs materialized views?
Use your own words. Explain an example and one limitation before checking the explanation.
Check your explanation
JSON stores JSON input text, while JSONB stores a processed representation that supports efficient operations and indexing. A view stores a query definition, whereas a materialized view stores the query's result for later reads. These are separate choices about data representation and precomputed results.
Turn this concept into a clear answer.
- Flexible fields and cached reports are useful, but frequently filtered business keys often belong in well-constrained relational columns.
Keep connecting the ideas.
Next lesson: What are schemas, roles, sequences, functions and triggers?Adapted from 05_SQL_POSTGRESQL.md · “JSON vs JSONB, and views vs materialized views?”. Examples are study fragments; framework behavior can depend on your version.