Indexes

PostgreSQL provides a variety of index types, all following the same fundamental principle: each index acts as a reference to a specific row within a table. This allows for direct access to the required data without having to scan the entire table. However, utilizing indexes introduces extra workload to database systems, thus it is crucial to implement them judiciously.

Indexes can be organized in various manners based on the values being indexed. The selection of index type is determined by the nature of the values. The most prevalent index type, and the default in Postgres, is the B-Tree. A B-Tree is a generalized version of a binary search tree, allowing nodes to have multiple children.

Create Index

create table occupants (
  id bigint generated by default as identity primary key,
  age int,
  height int,
  weight int,
  name text,
  deceased boolean
);

It may be necessary to regularly request information from users pertaining to their age.

select name from occupants where age = 32;

Postgres would need to search through each individual row in the table without an index in order to locate exact age matches. You can verify this by doing an explain on the query:

explain select name from persons where age = 32;

//Output
Seq Scan on persons  (cost=0.00..22.75 rows=x width=y)
Filter: (age = 32)

To add a simple B-Tree index you can run:

create index idx_occupants_age on occupants (age);

Building indexes on extensive collections of data may consume a substantial amount of time, and the create index command typically locks the table to prevent write operations during this process. Fortunately, Postgres offers the "create index concurrently" feature, which effectively prevents write operations from being blocked on the table. However, this process may entail a slightly longer duration for completion.

Partial Indexes

If you are consistently searching for a specific subset of rows, it could be advantageous to create a partial index. For instance, suppose we only need to filter by age when the deceased status is false. In this case, we can construct a partial index:

create index idx_living_occupants_age on occupants (age)
where deceased is false;

Ordering indexes

B-Tree indexes are typically organized in ascending order, but there may be instances where a different sorting arrangement is desirable. For example, in certain cases within our application, such as showcasing the top 10 eldest occupants, a descending order and placement of NULL values at the end may be preferred. In such situations, the following method can be employed:

create index idx_occupants_age_desc on occupants (age desc nulls last);

Reindexing

Over time, indexes in a database may lose their effectiveness and require regeneration. Postgres offers a reindex command for this purpose; however, considering that Postgres locks the index during this operation, it may be advisable to utilize the concurrent keyword to avoid interruptions.

reindex index concurrently idx_occupants_age;

Another option is to rebuild all indexes associated with a specific table.

reindex table concurrently occupants;

Please be aware that the reindex command can be executed within a transaction, while the reindex option with the 'concurrently' keyword is not compatible with transactions.