8 ms·
I thought this was common practice, generated columns for JSON performance. I've even used this (although it was in Postgres) to maintain foreign key constraint
by jelder 9mo ago
I thought this was common practice, generated columns for JSON performance. I've even used this (although it was in Postgres) to maintain foreign key constraints where the key is buried in a JSON column. What we were doing was slightly cursed but it worked perfectly.
- sigwinch 9mo agoIt is. I’d wondered if STORED is necessary and this example uses VIRTUAL.
- ramon156 9mo agoIt works until you realize some of these usages would've been better as individual key/value rows. For example, if you want to store settings as JSON, you first have to parse it through e.g. Zod, hope that it isn't failing due to schema changes (or write migrations and hope that succeeds). When a simple key/value row just works fine, and you can even do partial fetches / updates
- mickeyp 9mo agoEAV data models are kinda cursed in their own right, too, though.
- jelder 9mo agoThe necessity of using a JSON column was outside of my control, but Zod etc. are absolutely required, I think, in most projects. I wrote more about that here: https://www.jacobelder.com/2025/01/31/where-shift-left-fails-type-theater.html https://www.jacobelder.com/2025/01/31/where-shift-left-fails...
- deleted 9mo ago[deleted]
- jasonthorsness 9mo agoThis is the typical practice for most index types in SingleStore as well except with the Multi-Value Hash Index which is defined over a JSON or BSON path
- craftkiller 9mo agoIf you're using postgres, couldn't you just create an index on the field inside the JSONB column directly? What advantage are you getting from extracting it to a separate column? CREATE INDEX idx_status_gin ON my_table USING gin ((data->'status')); ref: https://www.crunchydata.com/blog/indexing-jsonb-in-postgres https://www.crunchydata.com/blog/indexing-jsonb-in-postgres
- jelder 9mo agoThat works for lookups but not for foreign key constraints.
- craftkiller 9mo agoAh, makes sense. Thanks!
- cies 9mo ago..and it does not make "certain queries easier" (quote from the article).
- morshu9001 9mo agoYou only need gin if you want to index the entire jsonb. For a specific attribute, you can use the default (btree) which I'm guessing is faster.
- a-priori 9mo agoYes, as far as indices go, GIN indices are very expensive especially on modification. They're worthwhile in cases where you want to do arbitrary querying on JSON data, but you definitely don't want to overuse them. If you can get away with a regular index on either a generated column or an expression, then you absolutely should.
- morshu9001 9mo agoDoesn't sound very cursed, standard normalized relations for things that need it and jsonb for the big bags of attributes you don't care to split apart