Partial, Expression & Generated-Column Indexes

9 min read

Partial, Expression & Generated-Column Indexes

Beyond plain B-tree indexes, PostgreSQL supports three powerful index variants that let you index exactly what your queries need — nothing more, nothing less.

Expression Indexes

An expression index stores the result of an arbitrary expression rather than a raw column value. The optimizer will use it when the same expression appears in a WHERE clause.

sql

-- Case-insensitive email lookup
CREATE UNIQUE INDEX idx_users_email_ci
  ON users (lower(email));

-- Query must use the same expression to hit the index
SELECT * FROM users WHERE lower(email) = lower('Alice@Example.com');

-- Index on a JSONB extracted field
CREATE INDEX idx_orders_country
  ON orders ((metadata ->> 'country'));

-- Query
SELECT * FROM orders WHERE (metadata ->> 'country') = 'DE';

Partial Indexes

A partial index only includes rows that satisfy a WHERE predicate. This makes the index smaller, faster to scan, and cheaper to maintain.

sql

-- Only index active, unverified users — the common query target
CREATE INDEX idx_users_pending_verification
  ON users (email)
  WHERE is_active = true AND email_verified = false;

-- Unique partial index: enforce uniqueness only for published slugs
CREATE UNIQUE INDEX idx_posts_published_slug
  ON posts (slug)
  WHERE published = true;

-- Partial index on a soft-delete column
CREATE INDEX idx_orders_open
  ON orders (created_at)
  WHERE deleted_at IS NULL;

Generated Columns

A generated column's value is automatically computed from other columns. PostgreSQL only supports STORED generated columns (the value is persisted on disk). You can index a generated column just like any other.

sql

ALTER TABLE users
  ADD COLUMN email_lower text
    GENERATED ALWAYS AS (lower(email)) STORED;

CREATE UNIQUE INDEX idx_users_email_lower
  ON users (email_lower);

-- Another example: full name for display
ALTER TABLE users
  ADD COLUMN full_name_search tsvector
    GENERATED ALWAYS AS (
      to_tsvector('english', coalesce(first_name, '') || ' ' || coalesce(last_name, ''))
    ) STORED;

CREATE INDEX idx_users_name_fts ON users USING GIN (full_name_search);

Generated columns replace the pattern of maintaining a redundant computed column via application code or triggers. Because the database owns the computation, the value is always consistent and never accidentally stale.

Ready to test yourself?

Practice PostgreSQL— quiz & coding exercises