Indexing Strategy

Coming up with an indexing strategy is an important step in terms of mastering our PostgreSQL database. It means that we’re in a position to make an informed choice about which indexes we need and don’t need in our application. A PostgreSQL index allows the system to have new options to find the data our queries need. In the absence of an index, the only option available to our database is a sequential scan of our tables. The index access methods are meant to be faster than a sequential scan by fetching the data directly where it is.

Indexing is often thought of as a data modeling activity. When using PostgreSQL, some indexes are necessary to ensure data consistency (the C in ACID). Constraints such as UNIQUE, PRIMARY KEY, or EXCLUDE USING are only possible to implement in PostgreSQL with a backing index. When an index is used as an implementation detail to ensure data consistency, then the indexing strategy is indeed a data modeling activity.

In all other cases, the indexing strategy is meant to enable methods for faster access methods to data. Those methods are only going to be exercised in the context of running a SQL query. If writing the SQL queries is the job of a developer, they should also be well-acquainted with coming up with the right indexing strategy for an application.

Indexing for constraints

When using PostgreSQL, some SQL modeling constraints can only be handled with the ...