Indexes¶

Indexes are handled by IndexHandler during the DIFF phase.

Supported Index Types¶

Declaring Indexes¶

Model-level indexes use PgIndexSpec:

from dbwarden.databases.pgsql import PgIndexSpec

class Meta(PGTableMeta):
    pg_indexes = [
        PgIndexSpec("ix_users_email", ["email"], unique=True, using="btree"),
        PgIndexSpec("ix_users_data", ["data"], using="gin",
            postgresql_ops={"data": "jsonb_path_ops"}),
    ]

PgIndexSpec supports:

Partial Indexes¶

PgIndexSpec("ix_users_active", ["email"], where="active = true")

CREATE INDEX CONCURRENTLY ix_users_active ON users (email) WHERE active = true;

Expression Indexes¶

PgIndexSpec("ix_users_lower_email", columns=[], expression="lower(email)")

CREATE INDEX CONCURRENTLY ix_users_lower_email ON users (lower(email));

Expression indexes are stored with empty columns and the expression text in the expression field. Changes to the expression are detected and produce a DROP INDEX + CREATE INDEX cycle.

Covering Indexes (INCLUDE)¶

Non-key columns stored in the index to enable index-only scans:

PgIndexSpec("ix_users_email_covering", ["email"],
    include=["name", "avatar_url"])

CREATE INDEX CONCURRENTLY ix_users_email_covering ON users (email) INCLUDE (name, avatar_url);

INCLUDE columns are not used for index scans but are available as output columns without a table lookup. This makes index-only scans possible for queries that select only email, name, and avatar_url.

Multi-Column Indexes¶

Column order matters for query planning:

PgIndexSpec("ix_orders_user_date", ["user_id", "created_at DESC"])

B-tree multi-column indexes support queries on the leftmost columns. This index supports: - WHERE user_id = 1 (leftmost prefix) - WHERE user_id = 1 AND created_at > '2024-01-01' (range on second column) - Does NOT support: WHERE created_at > '2024-01-01' (no leftmost column)

Operator Classes¶

PgIndexSpec("ix_users_data", ["data"],
    using="gin",
    postgresql_ops={"data": "jsonb_path_ops"})

CREATE INDEX CONCURRENTLY ix_users_data ON users USING GIN (data jsonb_path_ops);

Common Operator Classes¶

Column Sorting¶

PgIndexSpec("ix_orders_date", ["created_at DESC NULLS LAST", "id ASC"])

CREATE INDEX CONCURRENTLY ix_orders_date ON users (created_at DESC NULLS LAST, id ASC);

BRIN Parameters¶

BRIN indexes accept access-method-specific storage parameters:

PgIndexSpec("ix_logs_created_at", ["created_at"],
    using="brin",
    with_params={"pages_per_range": 64, "autosummarize": True})

REINDEX¶

DBWarden does not auto-generate REINDEX statements. When an index becomes corrupted or bloated, recreate it manually:

REINDEX INDEX CONCURRENTLY ix_users_email;
REINDEX TABLE CONCURRENTLY users;
REINDEX DATABASE CONCURRENTLY mydb;

CONCURRENTLY avoids locking, but requires extra resources and can fail if the index is a unique index with duplicates.

NULLS NOT DISTINCT¶

PgIndexSpec("ix_users_email_unique", ["email"],
    unique=True, nulls_not_distinct=True)

Without NULLS NOT DISTINCT, a unique index allows multiple NULL values (PostgreSQL treats NULLs as distinct by default). With NULLS NOT DISTINCT (PG 15+), only one NULL is permitted.

Unique Constraint vs Unique Index¶

For declaring uniqueness, DBWarden offers two paths:

See Constraints for the full comparison and guidance.

Migration Safety¶

See Migration Safety for the full classification table.