Postgres JSONB Index

PostgreSQL provides the JSON array data type that allows us to store and query the JSON data quickly and efficiently. A JSON array is similar to a regular array in other programming languages but is developed to work with the PostgreSQL database engine.

Like other PostgreSQL data types, we can index the JSONB data types to enhance the performance and improve the queries involving JSON data by providing faster searches, filtering, sorting, and more.

In this tutorial, we will learn how we can create and work with the JSONB index in PostgreSQL databases.

Create a JSON Table

The following query demonstrates how to create a table with a JSONB column and add the JSON array data:

CREATE TABLE json_table (
id serial PRIMARY KEY,
data jsonb
);

This should create a new table with the id and data columns. The data column has a JSONB data type which allows us to store the JSON data in the table.

We can then insert a sample data into the table as shown in the following:

INSERT INTO json_table (data) VALUES
('[1, 2, 3]'),
('["apple", "banana", "cherry"]'),
('[{"name": "John", "age": 30}, {"name": "Jane", "age": 25}]');

The resulting table is as follows:

Create a JSONB Index

PostgreSQL uses the CREATE INDEX statement with the USING GIN clause to create a JSONB index. The syntax is expressed in the following:

CREATE INDEX index_name ON table_name USING GIN (column_name);

Replace the index_name with a desired index name, the table_name with the target table, and the column_name with the name of the JSONB column that you wish to index.

For example:

CREATE INDEX idx_data_details ON json_table USING GIN (data);

Using the data column should create a JSONB index on the json_table index.

Query the JSONB Indexes

Once we create a JSONB index, we can tell PostgreSQL to utilize it to optimize the queries that involve the JSON data that is stored in that table.

The following example shows how to use the JSONB index to perform a simple equality search:

SELECT * FROM json_table WHERE data->>'name' = 'Jane';

To search for a key, we can run the query as follows:

SELECT * FROM json_table WHERE data ? 'name';

To search for a key with specific values, we can run the query as follows:

SELECT * FROM json_table WHERE data @> '{"name": "Jane"}';

To search for a key with value in an array, we can run the query as follows:

SELECT * FROM json_table WHERE data->'name' ? 'Age';

Monitor the JSONB Index Usage

In some cases, we may need to monitor the usage of a JSONB index. This can help us identify whether the index is effectively speeding up the queries. Luckily, PostgreSQL provides us with the pg_stat_user_index system view which lets us fetch the statistics about the index usage.

We can run the query as follows:

SELECT * FROM pg_stat_user_indexes WHERE indexrelname = <index_name>;

Example:

SELECT * FROM pg_stat_user_indexes WHERE indexrelname = 'idx_data_details';

Resulting Output:

The given query returns the statistics such as the number of scans, tuples read, and fetched for the specified index.

Drop the JSONB Index

If we no longer need a JSONB index, we can drop it using the DROP INDEX statement. The command syntax is as follows:

DROP INDEX index_name;

Example:

DROP INDEX idx_data_details;

Conclusion

We explored how to work with JSONB indexes in PostgreSQL which allow us to speed up the queries involving the JSON data that is stored in the database.