Array Indexing: Creating GIN and BTREE Indexes

Imagine you have a table with millions of rows, and one of the columns stores arrays. For example, let's say we have a products table, and each product can belong to several categories:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    categories TEXT[] -- Array of strings to store product categories
);

Let's say you want to find all products that belong to the electronics category. If you just use the @> operator to search, it might trigger a full table scan:

SELECT *
FROM products 
WHERE categories @> ARRAY['electronics'];

A full scan (Seq Scan) is slow. Especially if your table is huge. Indexes come to the rescue to turn this into a much faster search.

Index Types for Arrays

PostgreSQL supports two main types of indexes you can use for arrays:

1. GIN (Generalized Inverted Index) — perfect for searching when you need to quickly find elements in an array or check for overlaps.
2. BTREE (Binary Tree) — good for other operations, like exact array comparison.

Let's break each one down a bit more.

1. GIN Index: Fast as lightning

GIN (Generalized Inverted Index) is an index that's awesome for working with operators like:

• @> (array contains an element or another array),
• <@ (array is contained in another array),
• && (arrays overlap).

Here's how you can create a GIN index for our categories column:

CREATE INDEX idx_categories_gin
ON products USING gin(categories);

Once the index is created, your queries will run way faster. For example, this query:

SELECT *
FROM products 
WHERE categories @> ARRAY['electronics'];

will use your GIN index.

Fun fact: The GIN index works like an inverted list — it keeps track of which elements (like strings) are in which records. It's kinda like the index at the back of a book that helps you find a topic by page number. Pretty handy, right?

2. BTREE Index: When order matters

BTREE (Binary Tree) is the standard index used in most databases. It's good for operations that need exact array comparison, like:

• Checking if arrays are equal =,
• Comparing arrays by element order (>, <).

You can create a BTREE index for an array like this:

CREATE INDEX idx_categories_btree
ON products USING btree(categories);

Here's an example query that can use a BTREE index:

SELECT *
FROM products
WHERE categories = ARRAY['electronics', 'gadgets'];

But keep in mind, BTREE indexes aren't good for operators like @> or <@. For those, stick with GIN.

Index Usage Examples

Now let's mix some theory with practice and check out a few examples.

1. Finding array overlaps

Say you want to find all products that are linked to the categories electronics and smartphones, using the && (array overlap) operator:

SELECT *
FROM products
WHERE categories && ARRAY['electronics', 'smartphones'];

For this, the GIN index you created earlier is perfect:

CREATE INDEX idx_categories_gin
ON products USING gin(categories);

With this index, the query will run way faster thanks to the inverted list.

2. Comparing arrays for equality

If you need to find products that belong to only the categories electronics and gadgets (in that order), then a BTREE index is your best bet:

SELECT *
FROM products
WHERE categories = ARRAY['electronics', 'gadgets'];

Create the right index for that:

CREATE INDEX idx_categories_btree
ON products USING btree(categories);

Index Performance

Indexes help speed up queries, but there's a flip side. For example:

• Creating an index takes time and resources. If your table is really big, building the index can take a while.
• Updating the table. Every time you insert new rows or update existing data, the indexes get updated too. This can slow down INSERT and UPDATE operations.

Still, in most cases, the speed boost for queries is totally worth it.

How to Choose: GIN or BTREE?

Here's a quick table to help you pick the right index for the job: