Faster queries with index on JSONB columns in Postgres

The JSONB column in Postgres allows you to store JSON objects while being indexable for fast queries. In this video, Tyler Shukert walks you through adding indexes to a JSONB column so you can run different types of queries efficiently.
Learn more about JSON and JSONB columns: supabase.com/docs/guides/data...
Indexes are an essential part of managing databases as they grow. While both JSON and JSONB allows you to store JSON data, only JSONB columns is indexable in Postgres using a Generalized Inverted Index or GIN index. You can even create indexes for a field within the JSONB columns if you know you only query a certain sub-path.
00:00 Intro
00:10 Go over the table
00:30 Start writing SQL
03:23 Set an index on a sub-field
04:38 Outro
💻 Videos to watch next:
▶ How to store and query JSON data in Postgres: • How to store and query...
▶ Use this to lock down the schema if you have JSON or JSONB columns: • Use this to lock down ...
▶ Create PostgreSQL Functions with Supabase: • Create PostgreSQL Func...
👇 Learn more about Supabase 👇
🕸 Website: supabase.com/
🏁 Get started: app.supabase.com/
📄 Docs: supabase.com/docs
🔔 Subscribe for more tutorials and feature updates from Supabase: / @supabase
📱 Connect with Us:
🐙 Github: www.github.com/supabase
💬 Discord: www.discord.supabase.com/
🐦 Twitter: / supabase
▶ Instagram (follow for memes): / supabasecom
ABOUT SUPABASE:
Supabase is the open source Firebase alternative. Supabase provides a full Postgres database for every project with pgvector, backups, realtime, and more. Add and manage email and password, passwordless, OAuth, and mobile logins to your project through a suite of identity providers and APIs.
Build in a weekend, scale to millions.
#Supabase #AppDevelopment #RealtimeApps #DeveloperTools

[@dshukertjr]

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 👇

[@JonMeyers]

This is sick! Makes working with json so much more performant! 🙌

[@dshukertjr]

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!

[@bepamungkas]

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.

[@nicolascalderon9366]

Awesome, thanks! that is exactly what we need

[@mikejohneviota9293]

i have tried it and it's freaking awsome than mongodb 😂

[@alinagy]

Just starting to use Supabase in a client project and it’s too good

[@sumitpurohit8849]

Please create a video on hybrid search and it's indexing.

[@Supabase]

Thanks for the suggestion! Just to be sure, hybrid search as in semantic search + full text search? supabase.com/docs/guides/ai/hybrid-search

[@sayyidj6406]

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?

[@dshukertjr]

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?

[@nicolascalderon9366]

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

[@beertocode]

This is great! I wonder does an index on jsonb fields take a lot of space?

[@dshukertjr]

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.

[@davemarko9480]

First here 😊

[@dshukertjr]

You beat me to it 😂