Hacker Newsnew | past | comments | ask | show | jobs | submitlogin

If 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




That works for lookups but not for foreign key constraints.

Ah, makes sense. Thanks!

..and it does not make "certain queries easier" (quote from the article).

You 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.

Yes, 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.




Guidelines | FAQ | Lists | API | Security | Legal | Apply to YC | Contact

Search: