PostgreSQL index

PostgreSQL index: You will learn how to create, delete, and rename indexes in PostgreSQL with syntax and examples.

What is an index in PostgreSQL?

An index is a performance tuning method that allows you to extract records more quickly. An index creates a record for each value that appears in the indexed columns.

Create an index

You can create an index in PostgreSQL using the CREATE INDEX operator.

The syntax for the CREATE INDEX operator in PostgreSQL

CREATE [UNIQUE] INDEX [CONCURRENTLY] index_name
[ USING BTREE | HASH | GIST | SPGIST | GIN ]
ON table_name
(index_col1 [ASC | DESC],
index_col2 [ASC | DESC],
…
index_col_n [ASC | DESC]);

• UNIQUE – Optional. The UNIQUE modifier indicates that the combination of values in indexed columns must be unique.
• CONCURRENTLY – Optional. When an index is created, it will not lock the table. By default, the table is locked when an index is created.
• index_name – The name to be assigned to an index.
• table_name – The name of the table where the index is created.
• index_col1, index_col2,… index_col_n – Columns for use in an index.
• ASC – Optional. The index is sorted in ascending order for this column.
• DESC – Optional. The index is sorted in descending order for this column.

Let’s look at an example of how to create an index in PostgreSQL.
For example:

CREATE INDEX order_details_idx
ON order_details (order_date);

In this example, the CREATE INDEX operator will create an index with the name order_details_idx, which consists of the order_date field.

UNIQUE INDEX

To create a unique index for a table, you must specify the UNIQUE keyword when creating the index.
For example:

CREATE UNIQUE INDEX order_details_idx
ON order_details (order_date, note);

In this example, we have created a unique index for the order_details table, which consists of the order_date and note fields, so that the combination of these fields must always contain a unique value without duplicates.

This is a great way to ensure the integrity of your database if you require unique values in columns that are not part of your primary key.

Drop Index

You can remove an index in PostgreSQL using the DROP INDEX operator.

The syntax for removing an index using the DROP INDEX operator in PostgreSQL

DROP INDEX [CONCURRENTLY] [IF EXISTS] index_name
[ CASCADE | ];

• CONCURRENTLY – Optional. When an index is deleted, it does not block the table. By default, the table is locked while the index is removed from the table.
• IF EXISTS – Optional. If specified, the DROP INDEX operator will not cause an error if no index exists.
• index_name – The name of the index to be deleted.
• CASCADE – Optional. All objects that depend on this index are also deleted.
• RESTRICT – Optional. The index will not be deleted if there are objects that depend on the index.

Let’s look at an example of how to remove an index in PostgreSQL.
For example:

DROP INDEX order_details_idx;

In this example, we removed an index with the name website_idx from the site’s table.

Rename the index

You can rename an index into PostgreSQL using the ALTER INDEX operator.

The syntax for renaming an index using the ALTER INDEX operator

ALTER INDEX [IF EXISTS] index_name,
RENAME TO new_index_name;

• IF EXISTS – Optional. If specified, the ALTER INDEX operator will not cause an error if no index exists.
• index_name – The name of the index that you want to rename.
• new_index_name – The new name for an index.

Let’s look at an example of how to rename an index in PostgreSQL.
For example:

ALTER INDEX order_details_idx
RENAME TO od_new_index;

In this example, we renamed the index with the name order_details_idx to od_new_index.

PostgreSQL Indexing: How, why, and when