Indexing JSONB with GIN in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the purpose of using a GIN index on a JSONB column in PostgreSQL?

A GIN index on a JSONB column helps speed up searches for keys and values inside the JSON data, making queries faster when filtering or searching within JSON documents.

beginner

How do you create a GIN index on a JSONB column named 'data' in a table called 'items'?

Use the SQL command:

CREATE INDEX idx_items_data ON items USING GIN (data);

This creates a GIN index on the 'data' JSONB column to speed up queries.

intermediate

What kind of queries benefit most from a GIN index on JSONB?

Queries that use the containment operator @> to check if a JSONB column contains a specific key or value, or queries that search for existence of keys or values inside JSON documents.

beginner

True or False: GIN indexes on JSONB columns can index nested keys and values inside the JSON structure.

True. GIN indexes can index nested keys and values, allowing efficient queries deep inside JSON documents.

intermediate

What is a downside of using GIN indexes on JSONB columns?

GIN indexes can be slower to update when data changes, so write operations may be slower. Also, they use more disk space compared to regular indexes.

Which SQL command creates a GIN index on a JSONB column named 'info' in table 'users'?

A CREATE INDEX idx_users_info ON users (info);

B CREATE INDEX idx_users_info ON users USING BTREE (info);

C CREATE INDEX idx_users_info ON users USING HASH (info);

D CREATE INDEX idx_users_info ON users USING GIN (info);

GIN indexes are created using 'USING GIN' syntax. BTREE and HASH are different index types not optimized for JSONB.

What operator is commonly used in queries that benefit from a GIN index on JSONB?

A <>

B =

C @>

D LIKE

The containment operator '@>' checks if the JSONB column contains a specified JSON value and is optimized by GIN indexes.

True or False: GIN indexes on JSONB columns can speed up searches for nested keys.

A True

B False

C Only for top-level keys

D Only for arrays

GIN indexes index nested keys and values, allowing efficient queries inside complex JSON structures.

What is a common trade-off when using GIN indexes on JSONB columns?

A Faster writes but slower reads

B Slower writes but faster reads

C No difference in performance

D Slower reads and writes

GIN indexes improve read query speed but can slow down write operations due to index maintenance.

Which PostgreSQL data type is required to use GIN indexing for JSON data?

A JSONB

B TEXT

C JSON (plain)

D VARCHAR

GIN indexing works with the JSONB data type, which stores JSON in a binary format optimized for indexing.

Explain how a GIN index improves query performance on a JSONB column.

Think about how searching inside a big JSON document can be slow without an index.

Describe the trade-offs of using a GIN index on JSONB data in PostgreSQL.

Consider what happens when data changes and the index needs to be updated.