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.

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:

👇 Learn more about Supabase 👇

📱 Connect with Us:

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! 🙌

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

[nicolascalderon]

Awesome, thanks! that is exactly what we need

[alinagy]

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

[nicolascalderon]

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

[sumitpurohit]

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

[sayyidj]

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?

[mikejohneviota]

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

[beertocode]

This is great!

I wonder does an index on jsonb fields take a lot of space?