A database index is a separate data structure that the database maintains alongside a table to speed up lookups by a particular column. Without one, answering a query like “find the row where id equals some value” forces the database to read every row in the table, a so-called sequential scan. The PostgreSQL documentation describes the alternative: if the system maintains an index on the column, “it can use a more efficient method for locating matching rows. For instance, it might only have to walk a few levels deep into a search tree.”
Most indexes are built as B-trees, which keep the indexed values sorted and balanced so the database can find any value in a small number of steps. PostgreSQL notes that its standard btree index can be applied to “any data type that can be sorted into a well-defined linear order,” which makes B-tree indexes the default choice for equality and range queries alike.
Indexes are the single biggest lever on read performance, but they are not free. The index is extra data that consumes storage, and the database must update it on every insert, update, or delete that touches the indexed column. That is the central trade-off: indexes make reads dramatically faster while making writes somewhat slower and using more disk. Designing which columns to index, and which index type to use, is therefore one of the core tuning tasks in any database system.