# Index Patterns Reference

## B-tree (Default)

The default index type. Efficient for equality and range queries.

```elixir
# Single column
create index(:users, [:email])

# Composite — column order matters: put high-cardinality / most-filtered first
create index(:orders, [:tenant_id, :status, :inserted_at])

# Descending (for ORDER BY ... DESC queries)
create index(:events, ["inserted_at DESC NULLS LAST"])
```

### When to Use B-tree

- Equality: `WHERE email = ?`
- Range: `WHERE inserted_at > ?`
- Sorting: `ORDER BY inserted_at DESC`
- Prefix of composite: `WHERE tenant_id = ? AND status = ?` (uses first N columns)

### Composite Index Column Order

The leftmost columns must appear in the query's WHERE/ORDER for the index to be used.

```
Index: (tenant_id, status, inserted_at)

✅ WHERE tenant_id = ?
✅ WHERE tenant_id = ? AND status = ?
✅ WHERE tenant_id = ? AND status = ? ORDER BY inserted_at
❌ WHERE status = ?                    (skips tenant_id)
❌ WHERE inserted_at > ?               (skips both)
```

**Rule of thumb:** Most selective column first, but respect query patterns.

## Unique Index

Enforces uniqueness at the database level. Always prefer over application-level checks.

```elixir
# Single column
create unique_index(:users, [:email])

# Composite (unique within scope)
create unique_index(:users, [:tenant_id, :email])

# With name
create unique_index(:accounts, [:slug], name: :accounts_slug_unique)
```

### Partial Unique Index

Unique constraint only on a subset of rows:

```elixir
# Only one active subscription per user
create unique_index(:subscriptions, [:user_id],
  where: "status = 'active'",
  name: :subscriptions_one_active_per_user
)

# Unique email only for non-deleted users
create unique_index(:users, [:email],
  where: "deleted_at IS NULL",
  name: :users_email_active_unique
)
```

## Partial Index

Index only rows matching a condition. Smaller and faster than full indexes.

```elixir
# Index only unprocessed jobs
create index(:jobs, [:priority, :inserted_at],
  where: "status = 'pending'",
  name: :jobs_pending_priority
)

# Index only non-null values
create index(:users, [:phone],
  where: "phone IS NOT NULL",
  name: :users_phone_not_null
)

# Index recent orders only
create index(:orders, [:status],
  where: "inserted_at > '2024-01-01'",
  name: :orders_recent_status
)
```

## GIN Index

Generalized Inverted Index. For containment queries on JSONB, arrays, and full-text search.

```elixir
# JSONB — query: WHERE metadata @> '{"key": "value"}'
create index(:events, [:metadata], using: :gin)

# Array — query: WHERE 'tag' = ANY(tags)
create index(:posts, [:tags], using: :gin)

# Full-text search (tsvector column)
create index(:articles, [:search_vector], using: :gin)

# JSONB path ops (smaller, only supports @> operator)
execute """
CREATE INDEX events_metadata_gin ON events USING gin (metadata jsonb_path_ops)
"""
```

### When to Use GIN

- JSONB containment: `@>`, `?`, `?|`, `?&`
- Array containment: `@>`, `<@`, `&&`
- Full-text search: `@@`

### GIN vs GiST for JSONB

| Feature | GIN | GiST |
|---------|-----|------|
| Query speed | Faster | Slower |
| Insert speed | Slower | Faster |
| Size | Larger | Smaller |
| Best for | Read-heavy, containment | Write-heavy, range |

## Concurrent Index Creation

For large tables in production — avoids table locks.

**Must be in its own migration with DDL transactions disabled.**

```elixir
defmodule MyApp.Repo.Migrations.IndexUsersEmailConcurrently do
  use Ecto.Migration

  @disable_ddl_transaction true
  @disable_migration_lock true

  def change do
    create index(:users, [:email], concurrently: true)
  end
end
```

**Rules:**
- One index per migration (if it fails, you can retry just that one).
- `@disable_ddl_transaction true` — required for CONCURRENTLY.
- `@disable_migration_lock true` — prevents migration lock conflicts.
- Cannot be inside a transaction block.

## Foreign Key Index

Always index foreign key columns. Postgres does NOT auto-index FKs.

```elixir
add :team_id, references(:teams, type: :binary_id, on_delete: :delete_all), null: false

# Always add this:
create index(:users, [:team_id])
```

Without this index, DELETE on the parent table scans the entire child table.

## Multi-Tenant Indexes

Always make `tenant_id` the first column in composite indexes:

```elixir
# Good — tenant_id first
create index(:orders, [:tenant_id, :status])
create unique_index(:users, [:tenant_id, :email])
create index(:events, [:tenant_id, :inserted_at])

# Bad — tenant_id not first (index won't be used for tenant queries)
create index(:orders, [:status, :tenant_id])
```

## Expression Index

Index on a computed expression:

```elixir
# Case-insensitive email lookup
execute """
CREATE INDEX users_email_lower ON users (lower(email))
""", """
DROP INDEX users_email_lower
"""

# Date truncation for grouping
execute """
CREATE INDEX events_date ON events (date_trunc('day', inserted_at))
""", """
DROP INDEX events_date
"""

# JSONB nested field
execute """
CREATE INDEX events_type ON events ((metadata->>'type'))
""", """
DROP INDEX events_type
"""
```

## Index Sizing Guidelines

| Table Size | Index Strategy |
|-----------|----------------|
| < 10K rows | Only unique + FK indexes |
| 10K–1M rows | Add indexes for common queries |
| 1M–100M rows | Partial indexes, expression indexes, analyze query plans |
| > 100M rows | Partitioning, partial indexes, consider covering indexes |

## Checking Index Usage

```sql
-- Unused indexes
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND indexrelname NOT LIKE '%_pkey';

-- Index sizes
SELECT indexname, pg_size_pretty(pg_relation_size(indexname::regclass))
FROM pg_indexes WHERE tablename = 'your_table';

-- Query plan check
EXPLAIN ANALYZE SELECT * FROM users WHERE tenant_id = 'xxx' AND email = 'yyy';
```