PostgreSQL Indexing: A Practical Guide for Rails Developers

Rails makes it easy to add an index and hard to know whether you needed one. add_index is a single line, migrations run green, and nothing tells you that the index you just created will never be used — or that the one you actually needed is still missing.

This is the mental model I use when indexing a PostgreSQL database behind a Rails application.

What an index costs#

An index is a copy of part of your data, kept sorted. That buys fast lookups and costs you:

• Write throughput. Every INSERT, UPDATE and DELETE must update every affected index.
• Disk and cache. Indexes compete with table data for your buffer cache. An unused index still evicts useful pages.
• Migration time. Building an index on a large table reads the whole table.

So the goal is not "index everything". It is to index the columns your queries actually filter, join and sort on, and nothing else.

Start from the query plan, not from intuition#

EXPLAIN ANALYZE is the only reliable input. Run it against production-like data volumes — plans change completely between a thousand rows and a million.

puts Post.where(published: true).order(created_at: :desc).limit(25).explain(analyze: true)

An unindexed query looks like this:

Limit  (cost=18543.21..18546.13 rows=25 width=284) (actual time=412.882..412.897 rows=25 loops=1)
  ->  Sort  (cost=18543.21..18693.44 rows=60092 width=284) (actual time=412.880..412.888 rows=25 loops=1)
        Sort Key: created_at DESC
        Sort Method: top-N heapsort  Memory: 42kB
        ->  Seq Scan on posts  (cost=0.00..16884.00 rows=60092 width=284) (actual time=0.031..381.204 rows=59873 loops=1)
              Filter: published
              Rows Removed by Filter: 40127
Planning Time: 0.104 ms
Execution Time: 412.943 ms

Three things to read here:

• Seq Scan — PostgreSQL read the entire table.
• Rows Removed by Filter: 40127 — it discarded 40% of what it read.
• Sort Method: top-N heapsort — it sorted 60,000 rows to return 25.

The fix is an index that satisfies both the filter and the sort:

class AddPublishedCreatedAtIndexToPosts < ActiveRecord::Migration[7.1]
  disable_ddl_transaction!
 
  def change
    add_index :posts, [:published, :created_at],
              order: { created_at: :desc },
              algorithm: :concurrently,
              if_not_exists: true
  end
end

Limit  (cost=0.42..8.71 rows=25 width=284) (actual time=0.038..0.061 rows=25 loops=1)
  ->  Index Scan using index_posts_on_published_and_created_at on posts
        (cost=0.42..19921.32 rows=60092 width=284) (actual time=0.036..0.055 rows=25 loops=1)
        Index Cond: (published = true)
Planning Time: 0.121 ms
Execution Time: 0.084 ms

No sort node at all — the index already stores the rows in the required order. 412ms became 0.08ms.

Composite indexes and column order#

Column order in a composite index is not cosmetic. PostgreSQL can use a leading prefix of the columns, so an index on (a, b, c) serves queries filtering on a, on (a, b), and on (a, b, c) — but not on b alone.

add_index :orders, [:user_id, :status, :created_at]

The practical ordering rule: equality columns first, then the range or sort column last. This query

Order.where(user_id: 42, status: "paid").order(created_at: :desc)

wants (user_id, status, created_at). Putting created_at in the middle would force a sort.

A useful consequence is that a well-ordered composite index makes narrower single-column indexes redundant. If you have (user_id, status), a separate index on user_id is dead weight — drop it.

The index Rails does not create for you#

add_reference and belongs_to create an index on the foreign key column by default in modern Rails, but there are two gaps worth checking.

Foreign keys added by hand#

# No index — the foreign key constraint does not create one
add_column :comments, :post_id, :bigint
add_foreign_key :comments, :posts

Without an index on comments.post_id, deleting a post forces a sequential scan of comments to enforce the constraint. On a large child table that turns a single delete into a multi-second lock.

Uniqueness validations#

A validates :uniqueness is not a constraint. It runs a SELECT before the INSERT, which is both slow and racy — two concurrent requests can both pass the check and both insert.

class User < ApplicationRecord
  validates :email, presence: true, uniqueness: { case_sensitive: false }
end

Back it with a real unique index. Because the validation is case-insensitive, the index has to be too:

class AddUniqueEmailIndexToUsers < ActiveRecord::Migration[7.1]
  disable_ddl_transaction!
 
  def change
    add_index :users, "lower(email)",
              unique: true,
              name: "index_users_on_lower_email",
              algorithm: :concurrently
  end
end

Keep the validation for the friendly error message, and rescue the constraint violation for correctness:

def create
  @user = User.new(user_params)
  @user.save!
  redirect_to @user
rescue ActiveRecord::RecordNotUnique
  @user.errors.add(:email, :taken)
  render :new, status: :unprocessable_entity
end

Partial indexes#

If your queries only ever touch a slice of the table, index only that slice. Partial indexes are smaller, faster to scan, and cheaper to maintain.

A common case is soft deletion. Most queries filter deleted_at IS NULL, so there is no reason to index the deleted rows:

add_index :posts, [:published, :created_at],
          where: "deleted_at IS NULL",
          name: "index_live_posts_on_published_and_created_at",
          algorithm: :concurrently

Another is enforcing conditional uniqueness — one default payment method per user, where the constraint should only apply to the row marked default:

add_index :payment_methods, :user_id,
          unique: true,
          where: "is_default = TRUE",
          name: "index_one_default_payment_method_per_user",
          algorithm: :concurrently

That is a guarantee the database enforces. A Rails callback that unsets the other rows is not.

For a partial index to be used, the planner must be able to prove your query implies the index predicate — so the query needs the same deleted_at IS NULL condition, ideally via a default scope or an explicit scope.

Choosing an index type#

The default is B-tree, and it is correct the overwhelming majority of the time. The exceptions are worth knowing:

GIN — for containment queries. JSONB columns and array columns:

add_index :events, :payload, using: :gin

Event.where("payload @> ?", { status: "failed" }.to_json)

GIN with pg_trgm — for LIKE '%term%'. A B-tree cannot serve a leading wildcard. Trigram indexes can:

class AddTrigramIndexToProducts < ActiveRecord::Migration[7.1]
  disable_ddl_transaction!
 
  def up
    enable_extension "pg_trgm" unless extension_enabled?("pg_trgm")
    add_index :products, :name,
              using: :gin,
              opclass: :gin_trgm_ops,
              algorithm: :concurrently
  end
 
  def down
    remove_index :products, :name
  end
end

BRIN — for very large, naturally ordered tables. Append-only event tables where you query by time range. A BRIN index is a tiny fraction of the size of the equivalent B-tree:

add_index :events, :created_at, using: :brin

Adding indexes without downtime#

add_index takes an ACCESS EXCLUSIVE lock, which blocks reads and writes for the duration of the build. On a large production table that is an outage.

algorithm: :concurrently builds the index without blocking writes. It requires two things:

class AddIndexToLargeTable < ActiveRecord::Migration[7.1]
  disable_ddl_transaction! # concurrent builds cannot run inside a transaction
 
  def change
    add_index :events, [:account_id, :created_at],
              algorithm: :concurrently,
              if_not_exists: true # concurrent builds can fail and leave a partial index
  end
end

A concurrent build that fails leaves behind an INVALID index. It is not used by the planner but still costs you on writes, so check for them after a failed migration:

SELECT indexrelid::regclass AS index_name, indrelid::regclass AS table_name
FROM pg_index
WHERE NOT indisvalid;

Drop and rebuild anything that shows up.

Finding what to delete#

Unused indexes are pure cost. PostgreSQL tracks how often each one has been read:

SELECT
  s.relname            AS table_name,
  s.indexrelname       AS index_name,
  s.idx_scan           AS times_used,
  pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique
  AND NOT i.indisprimary
ORDER BY pg_relation_size(s.indexrelid) DESC;

Two caveats before you drop anything. The counters are cumulative since the last pg_stat_reset, so make sure you are looking at a meaningful window — a full business cycle, including monthly reporting jobs. And exclude unique and primary indexes, which exist to enforce constraints rather than to be scanned.

Duplicate indexes are the other easy win. Any index whose columns are a leading prefix of another index is redundant:

SELECT indrelid::regclass AS table_name, array_agg(indexrelid::regclass) AS indexes
FROM pg_index
GROUP BY indrelid, indkey
HAVING COUNT(*) > 1;

A checklist#

• Index every foreign key you filter or join on, and every foreign key with a constraint.
• Order composite indexes as equality columns first, range or sort column last.
• Back every uniqueness validation with a unique index.
• Use partial indexes when queries only touch a subset of rows.
• Build concurrently on anything large, with disable_ddl_transaction! and if_not_exists.
• Re-run EXPLAIN ANALYZE after each change and confirm the plan actually improved.
• Review pg_stat_user_indexes quarterly and drop what nothing reads.

The last point is the one people skip. Indexing is not a one-off task at launch; query patterns drift as features ship, and an index that was essential last year may be dead weight today.