Index creation and maintenance

Creating an index is one of the most intensive operations you can do on a database. When populating a database using tools such as pg_restore, the time spent building indexes can be the longest part of the data loading. And you can't necessarily just ignore them afterwards. Index rebuilding can be an expected part of regular database maintenance, particularly in the case where many rows (but not all of them) are being deleted from a section of an index. There's more information about that topic back in Chapter 7, Routine Maintenance.

Unique indexes

A unique indexes enforces that you won't have more than one row containing a particular key value. These are quite common in proper database design, both for improving performance—an indexed unique lookup is normally fast as well as data integrity, preventing erroneous duplications of data. Only B-tree indexes can currently be used as unique ones.

There are three ways you can create a unique index, only two of which...