Henrietta Dombrovskaya continues a series on Postgres:
What is an index? One might assume that any person who works with databases knows what an index is. However, a surprising number of people, including database developers and report writers and, in some cases, even DBAs, use indexes, even create indexes, with only a vague understanding of what indexes are and how they are structured. Having this in mind, let’s start with defining what is an index.
Since there are many different index types in PostgreSQL (and new index types are constantly created) we won’t focus on structural properties to produce an index definition. Instead, we define an index based on its usage.
Indexing is one area in which SQL Server and Postgres differ, as SQL Server relies on clustered indexes for storage and “default” operations, whereas Postgres has a different model.