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?
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.
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
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.
Awesome, thanks! that is exactly what we need
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.
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?
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.
i have tried it and it's freaking awsome than mongodb 😂
How to run the query?
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
Just starting to use Supabase in a client project and it’s too good
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
First here 😊
You beat me to it 😂