I am designing a schema for a product catalog where different items have wildly different attributes. I'm debating whether to use a standard relational table with many nullable columns or just store the attributes in a JSONB column. What are the performance implications for indexing and querying these unstructured data types compared to fixed primitive types like VARCHAR or INT?
3 answers
The rule of thumb is: use traditional columns for data you query frequently or use in JOINs, and JSONB for "cold" or highly variable metadata. JSONB is fantastic because it supports GIN indexing, allowing you to search inside the JSON blob efficiently. However, it lacks the strict schema enforcement of primitive types. If your data has a consistent structure, sticking to INT or VARCHAR is better for data integrity and storage size. If you have 50 different attributes that change every week, the flexibility of JSONB outweighs the slight overhead in processing power required to parse the JSON during a query.
Are you planning on doing a lot of mathematical aggregations on the numbers stored inside that JSONB field?
I always go with a hybrid approach. Store core search filters as columns and miscellaneous "extra" info in a JSONB field.
This is exactly what we do. It gives us the "best of both worlds" regarding speed for the main search and flexibility for the UI.
Jeffrey, yes, we need to sum up some weight and price attributes. Is that slow in JSONB? It can be slower because the DB has to cast the JSON value to a numeric type every time. If you’re doing heavy math, you’re much better off pulling those specific fields out into dedicated NUMERIC columns while keeping the rest in JSONB.