What Is a Database Index? A Plain-English Guide

A database index is a separate lookup structure that lets the database find matching rows without reading every row in the table. Without one, a query like "find the order with this ID" has to check each row in turn. With one, the database jumps close to the answer, the way you use the index at the back of a book instead of reading every page.

That book comparison comes from the PostgreSQL documentation itself. Its introduction to indexes compares an index to the alphabetic index at the back of a non-fiction book: readers scan the index and go to the relevant pages instead of reading the whole book.

What happens without an index

The PostgreSQL docs use a plain example. Given a table like this:

CREATE TABLE test1 (
    id integer,
    content varchar
);

a query such as SELECT content FROM test1 WHERE id = constant; forces a sequential scan of every row in the table, unless an index exists. On a table with a hundred rows you will never notice. On a table with millions of rows, that scan is why a page that was instant in development takes seconds in production.

What creating an index looks like

One line does it. This is the docs' own example:

CREATE INDEX test1_id_index ON test1 (id);

The name is arbitrary, but the docs advise picking one that helps you remember its purpose. You can remove an index with DROP INDEX, and you can add or remove indexes at any time.

Once an index exists, the database keeps it up to date automatically when the table changes, and uses it when it judges that cheaper than scanning the whole table. You do not have to rewrite your queries.

The cost nobody mentions first

Indexes are not free. The PostgreSQL documentation says: "After an index is created, the system has to keep it synchronized with the table. This adds overhead to data manipulation operations."

Every insert, update, and delete now has more work to do. Add five indexes to a table and each write maintains five extra structures. For a table that is read constantly and written rarely, that trade is excellent. For a table that takes thousands of writes a minute, it is a real cost.

The same page adds that indexes can prevent certain optimizations on updates, and that indexes that are rarely or never used should be dropped. An index nobody queries is pure overhead.

There is also a build-time cost. By default, while an index is being created, reads can continue but inserts, updates, and deletes are blocked until it finishes. The docs describe a concurrent build option that lets writes proceed, with caveats listed under CREATE INDEX. On a live app, read those caveats before you run the command.

Which queries an index helps

The docs say an index helps with WHERE and JOIN clauses where an indexed column is compared with a value. It can also speed up UPDATE and DELETE statements that use search conditions or joins. It does not help a query that does not filter on the indexed column.

A simple rule of thumb follows from that: index the columns you filter or join on, in the queries you run most often, on tables large enough for a scan to hurt.

When your AI-built app needs one

AI coding tools tend to create tables and queries without thinking about scale, because everything is fast with test data. The symptom is an app that was quick on day one and drags once real users arrive. The usual cause is a missing index on a column that every request filters by.

Before adding one blindly, find out which query is slow. Our post on debugging a slow database query with AI shows how to read the query plan, and how to prompt AI to design a database schema covers deciding on indexes up front. If you are still choosing where your data lives, start with how to choose a database for an AI-built app. For the queries themselves, see how to prompt AI to write a SQL query.

The PostgreSQL docs also recommend running ANALYZE regularly, which helps the planner make good decisions about whether to use an index.

For the wider picture of building with AI, the guide to building an app with AI is the place to start.

Frequently asked questions

What is a database index in simple terms?

It is a lookup structure that lets the database find rows by a column value without scanning the whole table, like the index at the back of a book.

Do indexes make a database faster?

For reads that filter or join on the indexed column, yes. For writes, they add overhead, because the index must be kept in sync with the table.

How many indexes should a table have?

As few as your real queries need. The PostgreSQL docs say rarely used indexes should be dropped, because each one adds cost to every write.

Does my AI-built app need an index?

If a page slows down as your data grows, and the slow query filters on a column with no index, then yes. Confirm with the query plan first.