Create Postgres Index on ObjectRocket
Introduction
If you’re using a PostgreSQL database to store your data, it’s important to do what you can to optimize query performance. One easy way to boost performance is to create an index on a specific column in a table. In this article, we’ll show you how to create a Postgres index in ObjectRocket using the CREATE INDEX
command.
Prerequisite
A few key prerequisites need to be in place before proceeding with this tutorial:
PostgreSQL 11 needs to be installed and configured.
You’ll need to create an instance of Postgres on your ObjectRocket account using the Mission Control panel.
What Are Indexes?
An index is a structure associated with a particular view or table that speeds up the retrieval of rows. You can compare a database index to the index at the back of a book– both structures allow you to find the information you need quickly and efficiently. When you’re looking for a specific piece of information in a book, you don’t randomly flip through each page of the book until you find it; instead, you consult the index, which is organized in alphabetical order, and you locate the topic for which you were looking. You can then go to the correct page quickly. Put simply, you can think of an index as a pointer to data in a PostgreSQL table.
What Is CREATE INDEX in Postgres?
The basic form of the CREATE INDEX
command is:
1 2 3 4 5 6 | CREATE INDEX indexName ON TableName [USING method] ( columnName [ASC | DESC] [NULLS {FIRST | LAST }], ... ); |
There’s a lot going on in this command syntax. Let’s look at it in a bit more detail:
First, we call the
INDEX NAME
clause followed by the desired index name. We then specify the name of the table to which that index belongs.Second, we indicate the method that the index will use. Shown below are the available methods that we can use:
- spgist
- gist
- hash
- btree
- gin
- brin
Keep in mind that the method btree
is the default method.
Finally, we list one or more columns to be stored within the index. We can use the sorting operators ASC
and DESC
as well.
Sample Dataset
Let’s take a look at the sample dataset we’ll be using in our examples:
1 2 3 4 5 6 7 8 | id | name | price | quantity ----+---------------------+-------+---------- 1 | close-up toothpaste | 5 | 25 2 | Oatmeal | 15 | 30 3 | Yogurt | 10 | 13 4 | Mineral Water | 20 | 100 5 | donut | 20 | 100 (5 rows) |
PostgreSQL CREATE INDEX Example
In this example, we’ll be using the CREATE INDEX
clause to create an index on the id
column.
To do this, we use the following statement:
1 2 | CREATE INDEX stock_id ON items(id); |
Let’s check the items
table to see if the index was created successfully. We can do this using the psql
command \d
.
NOTE: The psql
command \d
describes a Postgres table, showing its table structure, its type, any column modifiers and existing indexes associated with the table.
1 | \d items; |
The result will look like this:
1 2 3 4 5 6 7 8 9 10 11 | stocksdb=> \d items; Table "public.items" Column | Type | Collation | Nullable | Default ----------+------------------------+-----------+----------+----------------------------------- id | integer | | not null | nextval('items_id_seq'::regclass) name | character varying(100) | | | price | integer | | | quantity | integer | | | Indexes: "items_pkey" PRIMARY KEY, btree (id) "stock_id" btree (id) |
We can see that our new stock_id
index is now included in the description of this table’s structure.
Conclusion
If you’re looking for ways to boost your query performance, adding an index to a specific column in a table can be an effective way to get the job done. In this article, we showed you how to create a Postgres index on ObjectRocket. After taking a careful look at the syntax of the CREATE INDEX
command, we walked through a simple example to demonstrate how the command is used. With this tutorial to guide you, you’ll be able to create and implement indexes in your own PostgreSQL environment.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started