With the JSON query video, pg_jsonschema video, and this indexing JSONB video, learn JSON in Postgres series is concluded. Let us know in the comments below what other Postgres topics you would you like us to cover in future videos 👇
to add some tips: if the data is stored as jsonb, and the structure is standardized (e.g with some kind of schema), and we only do positive lookup; we can forego "key exists" operators for smaller index size and faster index lookup using jsonb_path_ops. With default (jsonb_ops) indexing, the index will contain both key and value to allows keys lookup. Without those operators, postgres will only index the value of each key/ path. One gotcha of above index type is when you do negative lookup (e.g filtering for empty field) the query basically do full index scan to get list of unindexed rows.
TY for the explination on indexing further into the json object. I've looked at a number of tutorials/documentation around indexing jsonb, and have never seen anything actually go into that particular topic. It always stops at indexing root level keys.
Hey @dshukertjr, hope you're having a great afternoon! こんにちは！ May I ask you a question? I've got some data that's a bit repetitive, stored as a string. It's basically JSON, but I don't need to directly search through it because another column handles that. So, I'm thinking of compressing and encrypting it to save space and keep it secure. Once I do that, it turns into a bunch of bytes. Usually, I'd save it as a base64 encoded string, but I'm wondering if it's possible - or even a good idea - to save it as bits instead. What do you think?
One question, I noticed the gin index worked when you did the query like this "... where metadata @>", but not when you did it on this way "... where metadata->>". So my question is, what of the two options are executed by supabaseClient? for example doing a query like this "supabase.ilike( `metadata->>${search.field}`, `%${searchValue}%` )"... in order to know which of the two indexing approaches I should use
Great question, and generally yes. That is why if you only query with certain subfields, you should create indexes for them rather than creating one for all the columns.
With the JSON query video, pg_jsonschema video, and this indexing JSONB video, learn JSON in Postgres series is concluded. Let us know in the comments below what other Postgres topics you would you like us to cover in future videos 👇
This is sick! Makes working with json so much more performant! 🙌
Yeah, I genuenly thought indexing JSONB columns was going to be more complex, but it turns out Postgres handles a lot of the complexity for us!
to add some tips:
if the data is stored as jsonb, and the structure is standardized (e.g with some kind of schema), and we only do positive lookup; we can forego "key exists" operators for smaller index size and faster index lookup using jsonb_path_ops. With default (jsonb_ops) indexing, the index will contain both key and value to allows keys lookup. Without those operators, postgres will only index the value of each key/ path.
One gotcha of above index type is when you do negative lookup (e.g filtering for empty field) the query basically do full index scan to get list of unindexed rows.
TY for the explination on indexing further into the json object. I've looked at a number of tutorials/documentation around indexing jsonb, and have never seen anything actually go into that particular topic. It always stops at indexing root level keys.
Awesome, thanks! that is exactly what we need
This is awesome! Thank you much for sharing. Any performance issues with indexing json you can think of?
Hey @dshukertjr, hope you're having a great afternoon! こんにちは！
May I ask you a question? I've got some data that's a bit repetitive, stored as a string. It's basically JSON, but I don't need to directly search through it because another column handles that. So, I'm thinking of compressing and encrypting it to save space and keep it secure. Once I do that, it turns into a bunch of bytes. Usually, I'd save it as a base64 encoded string, but I'm wondering if it's possible - or even a good idea - to save it as bits instead. What do you think?
It sounds like with what you are currently doing, you are able to satisfy your technical requirements, and saving space in your DB, so why not?
One question, I noticed the gin index worked when you did the query like this "... where metadata @>", but not when you did it on this way "... where metadata->>". So my question is, what of the two options are executed by supabaseClient? for example doing a query like this "supabase.ilike(
`metadata->>${search.field}`,
`%${searchValue}%`
)"... in order to know which of the two indexing approaches I should use
How to run the query?
This is great!
I wonder does an index on jsonb fields take a lot of space?
Great question, and generally yes. That is why if you only query with certain subfields, you should create indexes for them rather than creating one for all the columns.
Please create a video on hybrid search and it's indexing.
Thanks for the suggestion! Just to be sure, hybrid search as in semantic search + full text search? supabase.com/docs/guides/ai/hybrid-search
i have tried it and it's freaking awsome than mongodb 😂
Just starting to use Supabase in a client project and it’s too good
First here 😊
You beat me to it 😂