Indexing beyond btree

Where this rule comes from

Postgres has six index types. Btree is the default and the one most Rails developers know. The others (Hash, GiST, SP-GiST, GIN, BRIN) exist because btree is wrong for problem shapes that show up regularly: containment queries on JSONB, full-text search, geometric overlap, append-only time-series data.

Beyond the index type itself, Postgres supports modifiers that change what an index covers: partial indexes (only the rows you care about), expression indexes (indexed on a function of a column), and the INCLUDE clause for covering indexes (columns stored in the index for index-only scans).

The senior move is matching the index to the query shape. The wrong index is worse than no index: it pays the write cost without paying back at read time. The right index can take a query from 4 seconds to 4 milliseconds without changing a line of application code.

The anti-pattern: btree everything

A team builds a settings system that stores per-user preferences in a JSONB column:

class AddSettingsToUsers < ActiveRecord::Migration[7.1]
  def change
    add_column :users, :settings, :jsonb, default: {}, null: false
    add_index  :users, :settings
  end
end

The team writes a query: User.where("settings ->> 'plan' = ?", 'pro'). They expect the index to make it fast. EXPLAIN shows a seq scan. The index is never used.

The default btree index on a JSONB column indexes the column's binary representation, treating it as an opaque value. Equality on the whole object works (rare in practice); containment queries do not. The right tool is GIN, with the right opclass for the access pattern. A btree on a JSONB column is wasted disk space and write overhead, and it gives no read benefit.

Three things are wrong with the default reflex. First, every index has a write cost. INSERT and UPDATE have to update every index on the table. An unused index is a tax on every write. Second, the planner has to consider every index for every query. Twenty indexes on one table makes planning slower. Third, the wrong index hides the right one. A team that "added an index" stops looking, and the actual fix (a different index type, a partial index, an expression index) never gets in.

Partial indexes

A partial index covers only the rows matching a predicate. The classic case is a status column with a heavy tail. Most rows are completed; queries almost always look for pending or failed:

add_index :jobs, :scheduled_at,
  where: "status IN ('pending', 'failed')",
  name:  "index_jobs_active_by_scheduled_at"

The index is one-tenth the size of a full one. Lookups for active jobs are faster because the index covers only relevant rows. Crucially, INSERTs and UPDATEs of completed jobs do not touch this index at all. The cost paid by writes is proportional to the rows actually queried, not the whole table.

Partial indexes also work for soft-deleted records (where: "deleted_at IS NULL") and any other "most queries care about this subset" pattern. If a query's WHERE clause consistently includes a predicate, that predicate belongs in a partial index.

Expression indexes

Indexes on functions of columns. The most common Rails case is case-insensitive email lookup:

add_index :users, "LOWER(email)", unique: true,
  name: "index_users_on_lower_email"

Queries written as User.where("LOWER(email) = ?", email.downcase) use this index. Queries written as User.where(email: email) do not, because the column is being compared directly and the index is on LOWER(email), not email. The match has to be exact.

Other useful expressions: DATE(created_at) when grouping by day, md5(large_text) for fingerprint matching, (metadata->>'key') for indexing one JSONB field instead of the whole document. An expression index works on anything Postgres can compute deterministically from a row's columns.

GIN and GIST for JSONB and full-text

GIN (Generalized Inverted Index) is the right tool for "is this small thing inside that big thing" queries. JSONB containment, array overlap, full-text search via tsvector: all GIN.

-- JSONB containment queries
add_index :users, :settings, using: :gin

# Now this is index-supported:
User.where("settings @> ?", { plan: 'pro' }.to_json)

-- Full-text search on a tsvector column
execute %q{
  CREATE INDEX index_posts_on_tsv ON posts USING GIN (to_tsvector('english', body));
}

# Index-supported:
Post.where("to_tsvector('english', body) @@ plainto_tsquery(?)", 'rails postgres')

GIN indexes are slower to write than btree (each indexed term is its own entry). For JSONB columns updated frequently, the write cost adds up. The jsonb_path_ops opclass is a smaller, faster GIN variant that supports only the @> operator (containment), which is usually what JSONB queries need.

GiST (Generalized Search Tree) is the cousin for geometric and range data: PostGIS, range types like tsrange and int4range, similarity search via pg_trgm. The "find all bookings whose date range overlaps this week" query is a GiST query.

BRIN for time-series

BRIN (Block Range INdex) stores summary stats per range of physical pages: the min and max value of the indexed column in each 128-page block. A btree on a 100GB events table is itself many gigabytes. The equivalent BRIN is megabytes.

BRIN works when the indexed column is correlated with physical storage order. The classic case is created_at on an append-only table. New rows go at the end, in time order, so the table is naturally sorted by created_at on disk. A BRIN index on that column gives Postgres enough information to skip whole page ranges that cannot contain matches.

add_index :events, :created_at, using: :brin,
  name: "index_events_on_created_at_brin"

On a 50GB events table, the BRIN is around 100KB. Query performance for date-range filters is close to btree, with one-thousandth the disk and write cost. The trap is using BRIN on columns that are not physically sorted: a UUID column, a user_id column on a busy multi-tenant table. The BRIN ranges become useless and the planner falls back to a seq scan.

Covering indexes with INCLUDE

Postgres 11 added the INCLUDE clause for btree indexes. Columns listed in INCLUDE are stored in the index but are not part of the search key. The benefit is enabling index-only scans: queries that need only the indexed columns (key + included) finish without touching the heap.

add_index :posts, :user_id, include: [:title, :created_at, :published]

A query like Post.select(:title, :created_at, :published).where(user_id: 1234) can now answer entirely from the index. EXPLAIN shows Index Only Scan instead of Index Scan. The heap is not read at all. On a wide table where most queries pull a small set of columns, this is the difference between 50ms and 5ms.

The cost is index size. Included columns inflate every index entry, and they have to be updated whenever those columns change. Include columns that the query reads but does not filter on, and only when the table is large enough that heap fetches dominate the query time. For a 10,000-row table, covering indexes buy nothing. For a 50,000,000-row table, they can buy an order of magnitude.

The principle at play

Indexes are not free. Each one pays a write cost on every INSERT and UPDATE, takes disk space, and crowds the planner's decision tree. The senior move is to look at the query first, the access pattern second, and the index type third. The right index is whichever one makes the query's plan node a single small scan and contributes the least to write overhead.

Five rules of thumb from the patterns above:

• If the WHERE clause always includes the same predicate, use a partial index.
• If the query compares a function of a column, use an expression index.
• If the column is JSONB, array, or full-text, use GIN (or GIST for ranges and geometry).
• If the table is huge and naturally sorted by an indexed column, use BRIN.
• If the query reads a few extra columns but filters on one, use INCLUDE for an index-only scan.