Postgresql

PostgreSQL CREATE INDEX Guide with psql and DbSchema

For a developer who writes SQL and is about to add an index to a PostgreSQL table, and wants to know what each option in the statement does.

by Malik Umair Sajjad · 16-May-2023 · Updated 05-Sep-2026

On this page

Asked for the rows where a column equals a value, PostgreSQL reads the table from the first row to the last unless something tells it where to look. The manual calls that "clearly an inefficient method" when a query returns only a few rows out of many. An index is that something, and creating one is a single statement.

The examples run on PostgreSQL 18 against this table:

CREATE TABLE users (
  user_id serial PRIMARY KEY,
  email   text NOT NULL
);
INSERT INTO users (email) VALUES
  ('[email protected]'),
  ('[email protected]'),
  ('[email protected]');

CREATE INDEX idx_users_email ON users (email);

What the index gives the planner

An index is a second structure, stored separately from the table, that keeps the indexed values in an order the database can search. With one on email, PostgreSQL "might only have to walk a few levels deep into a search tree" to reach the row for a given address, instead of comparing every row it holds.

CREATE INDEX builds a B-tree unless you ask for another method with USING, and B-tree is the one that answers the everyday comparisons: equality, <, >, BETWEEN, and LIKE anchored at the start of the string. The other built-in methods are hash, gist, spgist, gin and brin, each built for a kind of value a B-tree cannot order usefully, such as the elements inside a jsonb document, which is what gin is for.

A B-tree also holds its entries sorted, so an ORDER BY email can read the index instead of sorting the rows it selected. That is a second use for an index you created for filtering, and it costs nothing extra.

What an index buys and what it costs

Three things come out of the index above. A WHERE email = ... finds its rows without a full scan. An ORDER BY email can skip the sort. And if the index is unique, the database refuses a duplicate address, which is a rule you no longer have to enforce in application code.

The cost is paid on writes and on disk. The index lives in its own files, so it takes its own space. Every insert, update and delete that touches email has to update the index too, because the system "has to keep it synchronized with the table". PostgreSQL's own advice follows from that: "indexes that are seldom or never used in queries should be removed".

Index the columns your queries filter, join, and sort on, and leave the rest alone. A column you only ever read back as part of a row gains nothing from an index, and a table you write to far more often than you read is the case where an extra index can cost more than it returns.

Building the index costs something too. A plain CREATE INDEX takes a SHARE lock on the table, which conflicts with writes while the build runs: readers continue, and anything trying to insert, update or delete waits. On a large production table, use CONCURRENTLY for that reason.

Unique indexes and duplicate values

UNIQUE makes the index reject a second row carrying a value it already holds:

CREATE UNIQUE INDEX idx_users_email_unique ON users (email);

PostgreSQL checks for duplicates while it builds the index, and again on every insert and update afterwards, so an address that is already in the table raises an error instead of being stored twice. Only B-tree supports unique indexes, so this is one place where USING has nothing to offer. Null values are the exception to the rule: by default they count as distinct from each other, so a nullable column can hold several rows with no value at all. Adding NULLS NOT DISTINCT changes that and allows one null row only.

A unique index on a column that is also NOT NULL gives you what a primary key gives you. The difference is direction: declaring PRIMARY KEY on the table makes PostgreSQL build the unique index itself, under a name derived from the table.

The options CREATE INDEX takes

The statement has one required shape, CREATE INDEX name ON table (columns), and a handful of options around it:

Option	What it does
UNIQUE	Rejects a value already present in the indexed columns
CONCURRENTLY	Builds the index without locking out writes
IF NOT EXISTS	Issues a notice instead of an error when the name is taken
name	Names the index; omit it and PostgreSQL derives one from the table and columns
INCLUDE	Adds non-key columns, so an index-only scan can return them
ONLY	Skips the partitions of a partitioned table

CONCURRENTLY is the one with strings attached. It scans the table twice instead of once, and between the scans it waits for the transactions already running to finish, so the build takes longer. It cannot run inside a transaction block. If the build fails, for example on a duplicate value in a unique index, it leaves behind an index marked invalid, which still adds work to every update until you drop it and try again.

IF NOT EXISTS needs the name spelled out, because PostgreSQL has to know what to look for before it decides whether to build anything.

Storage parameters, by index type

WITH takes parameters that change how the index is built and maintained. Each one belongs to particular index types, and passing it to another type is an error:

Parameter	Index types	What it sets
fillfactor	B-tree, hash, GiST, SP-GiST	How full each page is packed, from 10 to 100
deduplicate_items	B-tree	Whether repeated values are stored once
buffering	GiST	Whether the build buffers entries instead of inserting one by one
fastupdate	GIN	Whether new entries wait in a pending list
gin_pending_list_limit	GIN	How large that pending list may grow
pages_per_range	BRIN	How many table pages one summary entry covers
autosummarize	BRIN	Whether a range is summarized as soon as it fills

A lower fillfactor leaves room on each page for later updates, which delays page splits on a table that is updated often. On a table that is written once and read afterwards, the default packing is the one you want, because it makes the index smaller.

Run the statement from psql

Connect to the database that holds the table. If you do not have one yet, creating a PostgreSQL database takes a single statement, and creating a table is the step after it.

psql -U username -d databasename

The CREATE INDEX from the top of this page is the statement to run there:

CREATE INDEX idx_users_email ON users (email);

CREATE INDEX

The tag is all psql prints, so ask the catalog what the table now carries:

SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'users' ORDER BY indexname;

indexname	indexdef
idx_users_email	CREATE INDEX idx_users_email ON public.users USING btree (email)
users_pkey	CREATE UNIQUE INDEX users_pkey ON public.users USING btree (user_id)

Two indexes for one CREATE INDEX, because the primary key brought its own. The definitions come back fully spelled out, including the btree that neither statement mentioned. For the names alone, psql has \di, and \d users prints the table's indexes underneath its columns.

Create the index in DbSchema

DbSchema is a PostgreSQL client and visual designer, and its diagram is where you reach the indexes of a table without remembering their names.

Open a PostgreSQL connection in DbSchema and the schema arrives as a diagram, tables and foreign keys already laid out. Double-click the header of a table there and DbSchema opens the Table Dialog; the Indexes tab holds the primary key, the unique indexes and the normal ones. Add an index there, name it, and tick the columns it covers, and DbSchema puts it on the table.

Where that change lands depends on the connection. With the database connected, DbSchema executes the statement against PostgreSQL as soon as you confirm and lists it in the SQL History panel. Working disconnected, DbSchema writes the index to the design model only, and Schema → Synchronize Model with Database generates the SQL for it later, for you to review before it runs.

An index is cheap to add and easy to forget, which is why it helps to see the ones a table already has next to the columns they cover. Download DbSchema at https://dbschema.com/download.html and open your PostgreSQL schema in it: connecting, the diagram and the SQL editor are in the free Community Edition, while saving the design to a file and synchronizing it with the database are in Pro.