Creating Indexes ( CREATE INDEX) and Indexing Options ( UNIQUE, CONCURRENTLY)

We’ve already talked a bunch about how indexes speed up selects and help your database avoid scanning everything. Now it’s time to figure out how exactly they’re created, what options the CREATE INDEX command has, and when you should use stuff like UNIQUE or CONCURRENTLY. All this is super important if you want to not just use indexes, but actually manage them like a pro.

CREATE INDEX Syntax

You can create an index using the CREATE INDEX command. Here’s the basic syntax:

CREATE INDEX index_name
ON table_name (column_name);

• index_name — The name of the index. It’s best if it reflects what the index is for, like idx_users_email for an index on the email column in the users table.
• table_name — The name of the table you’re creating the index for.
• column_name — The column you want to index.

Let’s check out a simple example. Say we have a users table:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255),
    age INT
);

We want to speed up searching for users by the email field. Let’s create an index:

CREATE INDEX idx_users_email
ON users (email);

Now, when you run queries like:

SELECT * FROM users WHERE email = 'example@example.com';

PostgreSQL will use the idx_users_email index to quickly find the right row.

Unique Indexes (UNIQUE)

A unique index is a guarantee that values in the specified column(s) will be unique. If you try to insert a duplicate value, PostgreSQL won’t let you do it.

Unique indexes are often used for keys like email, username, or other identifiers that shouldn’t be duplicated.

Unique Index Syntax

The syntax for creating a unique index is almost the same as a regular index, except you add the UNIQUE keyword:

CREATE UNIQUE INDEX index_name
ON table_name (column_name);

Let’s say in our users table, the email field should be unique so you can’t have two users with the same address. Here’s how you do it:

CREATE UNIQUE INDEX idx_users_email_unique
ON users (email);

Now, if you try to run something like:

INSERT INTO users (name, email, age) VALUES ('John', 'john@example.com', 30);
INSERT INTO users (name, email, age) VALUES ('Jane', 'john@example.com', 25);

PostgreSQL will throw an error, because email has to be unique.

Creating Indexes with the CONCURRENTLY Option

Imagine you’re working with a huge table in production, and there are always operations happening (like new data being inserted). Creating an index in the standard way (CREATE INDEX) locks the table, so other queries can’t insert, update, or delete data. That can be a disaster for a live system. To avoid this, you can create an index “asynchronously” using the CONCURRENTLY option.

Syntax

CREATE INDEX CONCURRENTLY index_name
ON table_name (column_name);

The CONCURRENTLY keyword tells PostgreSQL to create the index in parallel, without locking the table.

Let’s say we have an orders table with millions of rows and new orders coming in all the time:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    order_number VARCHAR(50) NOT NULL,
    order_date DATE NOT NULL,
    customer_id INT NOT NULL
);

You want to create an index to speed up searches by order_date, but without locking the table:

CREATE INDEX CONCURRENTLY idx_orders_order_date
ON orders (order_date);

Now the database will create the index without locking the table, and your users won’t even notice it’s happening.

Some things to know about CONCURRENTLY:

1. The index is created slower than in the regular mode, because PostgreSQL does it in several steps.
2. If there are errors while creating the index (like duplicate data), you’ll have to drop it manually and create it again.

Extra Indexing Options

PostgreSQL lets you add extra options when creating indexes. For example, you can index multiple columns at once. This is handy when you often run queries filtering by several fields.

CREATE INDEX idx_users_name_email
ON users (name, email);

Now queries with WHERE name = 'John' AND email = 'john@example.com' will run faster.

Two separate indexes on single columns are not the same as one index on two columns! A multi-column index speeds up searches where all those columns are in the WHERE clause.

Common Errors and How to Fix Them

You might run into a few errors when creating indexes. Here are the most common ones:

Duplicate insert error when creating a unique index. If your table already has duplicate rows, PostgreSQL won’t be able to create a unique index. In that case, you need to delete or fix the duplicates first.

DELETE FROM users
WHERE email IN (
    SELECT email
    FROM users
    GROUP BY email
    HAVING COUNT(email) > 1
);

Locking error when creating indexes. If you use regular index creation on a live database, clients might notice delays or errors. Use the CONCURRENTLY option to avoid this problem.

Now imagine you’re working at a company and they trust you to optimize a database with millions of records. You can use indexes to find bottlenecks and speed up the user experience. For example, by adding the right index, you can cut query time from 10 seconds to just a few milliseconds. How cool is that?