Today, I was reading through PostgreSQL’s documentation and I stumbled upon something interesting called the INCLUDE clause. Let’s dig deeper.
The queries you fire on the database will execute the fastest if your database can resolve the queries just by using indexes. Such indexes are called covering indexes.
To achieve this, we almost always add all the columns in the index and create a composite index. For example, an index on (author, published_at) on the blogs table will be efficient in getting blogs by author ordered by published time.
Given that we will almost always need the title of the blog while rendering the list, it makes sense to make it part of the index. However, adding title in the index key would make the index less efficient and affect the uniqueness. Essentially, we need the index key as (author, published_at) but also include the title column in the index. This is where PostgreSQL’s INCLUDE clause comes in pretty handy.
The INCLUDE clause allows you to add extra columns to the index that are not part of the index’s key but are included for efficiency in certain queries. These extra columns make the index covering and hence some queries can complete their execution without needing to access the actual table.
CREATE INDEX idx_blogs_author_include
ON blogs (author, published_at) INCLUDE (title);The above query creates the index that stores (author, published_at) for fast lookup and includes title so that if a query requires all three, PostgreSQL doesn’t have to retrieve the title from the main table.
Databases are fascinating and going through these details shows the kind of performance we could get from the databases if we know them in and out.
⚡ I keep writing and sharing my practical experience and learnings every day, so if you resonate then follow along. I keep it no fluff.
youtube.com/c/ArpitBhayani