Drop Postgres Index on ObjectRocket

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

If you’re managing a PostgreSQL database, there may be times when you need to remove an index from a table. Unused or unnecessary indexes don’t serve any practical purpose, and you can free up space by deleting them; however, it’s important to know how to handle the removal safely. In this article, we’ll show you how to drop a Postgres index on ObjectRocket with step-by-step instructions and simple examples to illustrate the process.

Prerequisites

Before moving forward with the instructions in this tutorial, make sure that the following prerequisites are in place:

  • You need to have PostgreSQL 11 installed and configured on your machine.

  • You must create an instance of Postgres on your ObjectRocket account using the Mission Control panel.

What Is Postgres DROP INDEX?

The Postgres DROP INDEX statement allows us to remove a specific index associated with a Postgres table.

Shown below is the basic form of the command’s syntax:

1
2
3
DROP INDEX [ CONCURRENTLY]
[ IF EXISTS ] indexName
[ CASCADE | RESTRICT ];

Let’s take a closer look at this syntax and see what’s going on:

  • First, we call the DROP INDEX clause, followed by the name of the index.

  • The IF EXISTS clause is an optional clause. It ensures that the specified index name will only be deleted if it exists within the database. If you use the IF EXISTS clause while trying to remove a nonexistent index, a notification will be returned instead of an error.

  • The CONCURRENTLY clause is used to avoid locking out concurrent Postgres transactions. It will wait for any ongoing transactions to complete before removing the index.

  • The RESTRICT option does exactly what its name suggests– it restricts a particular index from being dropped if it has one or more dependent objects. This option is enabled by default in the DROP INDEX statement.

  • The CASCADE open cascades the effects of the DROP INDEX statement, dropping all of the index’s dependent objects.

Sample Data Set

Let’s take a moment to review the sample table we’ll be using in our examples:

1
2
3
4
5
6
7
 id |  name   | age
----+---------+-----
  1 | jame    |  13
  2 | ann     |  16
  3 | ruby    |  32
  4 | jovanny |  32
(4 rows)

Create Postgres Index Using CREATE INDEX Statement

Before we can use the DROP INDEX statement to remove an index, we need to have an existing index associated with the target table. We’ll use the following statement to create one:

1
2
CREATE INDEX index_person_name
ON person (name);

The above statement simply creates the index_person_name index on the name column in the person table.

Due to the small size of the data stored in this table, there would be no actual performance benefit to indexing it in real life; however, the purpose of this table is to help us demonstrate the process of dropping an index, so the performance aspect of the index itself is irrelevant.

Let’s take a look at the query below:

1
SELECT * FROM person WHERE name = 'ann';

The output look like this:

1
2
3
4
id | name | age
----+------+-----
  2 | ann  |  16
(1 row)

Since Postgres provides built-in query optimizers, Postgres may realize that there are more efficient ways to query than using the Postgres index. Ultimately, it will choose the most efficient query plan.

We can see how Postgres processes the above query using the EXPLAIN clause. This clause will provide details on how much effort it takes to process the SELECT query. We’ll use the following statement:

1
EXPLAIN SELECT * FROM person WHERE name = 'ann';

We’ll get a result that looks like this:

1
2
3
4
5
                       QUERY PLAN
---------------------------------------------------------
 Seq Scan ON person  (cost=0.00..12.12 ROWS=1 width=426)
   FILTER: ((name)::text = 'ann'::text)
(2 ROWS)

We can see that the index index_person_name isn’t really necessary or effective in this situation, so we decide to delete or drop using the DROP INDEX command.

Before we attempt to drop it, let’s look at the table structure using the psql command \d followed by the table name person:

1
2
3
4
5
6
7
8
9
10
persondb=> \d person
                      TABLE "public.person"
 COLUMN |          TYPE          | Collation | NULLABLE | DEFAULT
--------+------------------------+-----------+----------+---------
 id     | INTEGER                |           | NOT NULL |
 name   | CHARACTER VARYING(200) |           |          |
 age    | INTEGER                |           |          |
Indexes:
    "person_pkey" PRIMARY KEY, btree (id)
    "index_person_name" btree (name)

Now, we’ll use the following statement to remove the index called index_person_name:

1
DROP INDEX index_person_name;

Let’s verify that our index deletion was successful by using the same psql command as before.

The result now looks like this:

1
2
3
4
5
6
7
8
9
persondb=> \d person
                      TABLE "public.person"
 COLUMN |          TYPE          | Collation | NULLABLE | DEFAULT
--------+------------------------+-----------+----------+---------
 id     | INTEGER                |           | NOT NULL |
 name   | CHARACTER VARYING(200) |           |          |
 age    | INTEGER                |           |          |
Indexes:
    "person_pkey" PRIMARY KEY, btree (id)

We can see that the index was removed successfully.

Conclusion

You may identify some indexes in your PostgreSQL database that are no longer used or unnecessary. In those situations, it makes sense to remove the indexes that aren’t needed. This tutorial explained how to drop a Postgres index on ObjectRocket using the DROP INDEX command. If you’ve been following along with our instructions and examples, you’ll have no trouble deleting an index in your own PostgreSQL environment.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.