Partial indexes for soft deletes

Capsule overview

Partial indexes are a PostgreSQL feature that guide #12 (module 3) introduced as a technique: an index that only covers rows satisfying a predicate. Useful but abstract if you never had a concrete case. Soft delete is the canonical case — the cleanest justification for partial indexes existing in the first place. This capsule applies them to soft delete specifically, measuring how the planner takes advantage of them when the WHERE deleted_at IS NULL filter comes from the automatic listener you built in capsule 04.

You're going to see the full cycle: define the partial index declaratively in SQLAlchemy, verify with EXPLAIN ANALYZE that the planner picks it, compare it with alternative indexes (normal index, multicolumn, expression index), and understand when to add more than one partial index on the same table. By the end you'll have the reflex of "you added deleted_at, now add the partial index" without thinking, plus criteria for designing partial indexes beyond the canonical case.

This capsule is relatively compact because it builds on fundamentals already covered. The novelty is in the integration: partial index + automatic listener + Pydantic response models working together, running optimal SQL without the developer writing the filter by hand.

The framing: the partial index as an "index of only what matters"

A normal index is a map of the whole table: every row has an entry. A partial index is a map of only the rows that satisfy a predicate: the ones that don't, don't show up.

Normal index on (author_id, created_at):
┌─────────────────────────────────────────────┐
│ One entry per row of tasks (1M total)       │
│ - 400k active rows + 600k deleted           │
│ - Size: ~32 MB                              │
│ - Lookup walks active AND deleted           │
└─────────────────────────────────────────────┘

Partial index WHERE deleted_at IS NULL:
┌─────────────────────────────────────────────┐
│ One entry only per active row (400k)        │
│ - The 600k deleted ones don't exist here    │
│ - Size: ~13 MB                              │
│ - Lookup walks only active rows             │
└─────────────────────────────────────────────┘

The partial index is 60% smaller (proportional to the ratio of filtered rows) and, most importantly, the lookup doesn't touch the excluded rows. PostgreSQL doesn't read them, doesn't discard them, doesn't mention them in the plan. It's as if they didn't exist for queries that match the index's predicate.

A deeper treatment of how PostgreSQL decides to use a partial index (when the query's WHERE predicate implies the index's predicate) is in guide #12, module 3. Here we apply it without re-explaining the mechanics.

How the planner detects it can use the partial index

The planner uses the partial index when it can prove the index's predicate is always true for the rows the query is asking for. The practical rule:

-- Index definition
CREATE INDEX idx_tasks_active
  ON tasks (author_id, created_at DESC)
  WHERE deleted_at IS NULL;

-- Queries where the planner DOES use the partial index:
SELECT * FROM tasks WHERE author_id = 42 AND deleted_at IS NULL;
SELECT * FROM tasks WHERE deleted_at IS NULL ORDER BY created_at DESC LIMIT 10;
SELECT COUNT(*) FROM tasks WHERE author_id IN (1,2,3) AND deleted_at IS NULL;

-- Queries where it does NOT use the partial index:
SELECT * FROM tasks WHERE author_id = 42;  -- no deleted_at filter
SELECT * FROM tasks WHERE deleted_at IS NOT NULL;  -- opposite predicate
SELECT * FROM tasks WHERE deleted_at = '2026-01-01';  -- different predicate

The rule: the query has to literally include the index's predicate (deleted_at IS NULL) or something the planner can prove implies it. PostgreSQL isn't capable of inferring complex relationships; it needs an exact or near-exact match.

Why the automatic listener is the perfect combination

This is where capsule 04 comes full circle. The listener injects WHERE deleted_at IS NULL automatically into every SELECT. With the partial index, the planner detects the predicate and uses the index. The developer didn't write the filter and yet the plan is optimal.

# What the dev wrote:
result = await session.execute(
    select(Task).where(Task.author_id == 42).order_by(Task.created_at.desc()).limit(50)
)

# What SQLAlchemy generated (after the listener):
SELECT tasks.id, tasks.author_id, tasks.title, tasks.created_at, tasks.deleted_at
FROM tasks
WHERE tasks.author_id = 42 AND tasks.deleted_at IS NULL
ORDER BY tasks.created_at DESC
LIMIT 50

# What PostgreSQL ran (after the planner):
Index Scan using idx_tasks_active on tasks
  Index Cond: (author_id = 42)

That three-step chain (clean code → filtered SQL → optimal plan) is what defines the "soft delete done right" pattern. Any broken link degrades it.

Worked case: defining the partial index in SQLAlchemy

In capsule 03 we saw the pure SQL. Now let's see it declaratively in SQLAlchemy 2.0.

Defining the index on the model

# app/models/task.py
from datetime import datetime
from sqlalchemy import BigInteger, Index, String, DateTime, func, text
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column

from app.models.mixins import SoftDeleteMixin


class Base(DeclarativeBase):
    pass


class Task(Base, SoftDeleteMixin):
    __tablename__ = "tasks"

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    author_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    title: Mapped[str] = mapped_column(String(200), nullable=False)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        nullable=False,
        server_default=func.now(),
    )

    __table_args__ = (
        # Partial index: the canonical case for soft delete.
        # `postgresql_where` is the index's predicate.
        Index(
            "idx_tasks_author_created_active",
            "author_id",
            "created_at",
            postgresql_where=text("deleted_at IS NULL"),
        ),
    )

Important details:

• postgresql_where is the PostgreSQL-specific parameter. Other backends (MySQL, SQLite) ignore the predicate and create a normal index. If your app is portable, that's something to consider (and possibly generate the index via Alembic with pure SQL).
• text("deleted_at IS NULL") is raw SQL inside the declarative layer. SQLAlchemy doesn't try to parse it; it passes it straight through to the CREATE INDEX. That gives flexibility but also the responsibility of making sure the predicate is syntactically correct.
• Column order: author_id first because it's the high-cardinality filter (a lot of selectivity per value); created_at second because it's the ORDER BY. This follows the classic composite index pattern (guide #12, module 3).

Verify the index got created

-- In psql after running Base.metadata.create_all
\d tasks

-- Expected output:
--                        Table "public.tasks"
--    Column    |           Type           | Nullable |        Default
-- -------------+--------------------------+----------+------------------------
--  id          | bigint                   | not null | nextval('tasks_id_seq')
--  author_id   | bigint                   | not null |
--  title       | character varying(200)   | not null |
--  created_at  | timestamp with time zone | not null | now()
--  deleted_at  | timestamp with time zone |          |
-- Indexes:
--     "tasks_pkey" PRIMARY KEY, btree (id)
--     "idx_tasks_author_created_active" btree (author_id, created_at)
--         WHERE deleted_at IS NULL

The WHERE deleted_at IS NULL line confirms it's a partial index.

Verify the planner uses it

With the listener from capsule 04 installed, run:

# scripts/explain_query.py
import asyncio
from sqlalchemy import text
from app.db import AsyncSessionLocal


async def main():
    async with AsyncSessionLocal() as session:
        result = await session.execute(
            text("""
                EXPLAIN (ANALYZE, BUFFERS)
                SELECT id, title, created_at FROM tasks
                WHERE author_id = 42 AND deleted_at IS NULL
                ORDER BY created_at DESC LIMIT 50
            """)
        )
        for row in result:
            print(row[0])


if __name__ == "__main__":
    asyncio.run(main())

Expected output:

Limit  (cost=0.42..68.42 rows=50 width=20) (actual time=0.025..0.412 rows=50 loops=1)
  Buffers: shared hit=54
  ->  Index Scan using idx_tasks_author_created_active on tasks
        (cost=0.42..548.32 rows=400 width=20)
        (actual time=0.024..0.402 rows=50 loops=1)
        Index Cond: (author_id = 42)
        Buffers: shared hit=54
Planning Time: 0.115 ms
Execution Time: 0.475 ms

What's critical:

• Index Scan using idx_tasks_author_created_active — it used the partial index.
• No Filter: line — there's no post-scan filter.
• Buffers: shared hit=54 — it read only what was needed.

When to add more than one partial index on the same table

A table can have multiple partial indexes if the most common queries hit different subsets. Examples:

-- For queries on a specific author (a frequent filter)
CREATE INDEX idx_tasks_author_active
  ON tasks (author_id, created_at DESC)
  WHERE deleted_at IS NULL;

-- For queries on tasks by project (another frequent filter)
CREATE INDEX idx_tasks_project_active
  ON tasks (project_id, created_at DESC)
  WHERE deleted_at IS NULL;

-- For audit queries that DO see deleted rows (low usage ratio)
CREATE INDEX idx_tasks_deleted_audit
  ON tasks (deleted_at DESC)
  WHERE deleted_at IS NOT NULL;

Operational rule: a partial index is worth it if:

• The query that uses it is frequent (run >100 times/day as a rough reference).
• The covered subset is significantly smaller than the total (typically <50%).
• The cost of maintaining the index (extra writes on INSERT/UPDATE) is offset by the speedup on reads.

It isn't worth it when:

• The query is rare (a monthly report, for example).
• The covered subset is almost the whole table (>80%).
• The table has an extremely high write volume and the extra index slows down inserts.

Write amplification trade-off: each additional index is extra work on every INSERT and UPDATE of the table. Guide #12, module 5 (write performance) goes deeper.

Comparison with the alternatives

vs a normal (non-partial) index

CREATE INDEX idx_tasks_author_normal ON tasks (author_id, created_at);

It covers every row. PostgreSQL can use it for queries that filter by author_id, but the deleted_at IS NULL filter is applied post-scan. With 60% of rows deleted, the latency is ~60x worse than with a partial index (measured in capsule 03).

When to pick it: if most queries need to see active AND deleted rows (rare), a normal index is more versatile. Use case: an audit_log table where half the queries are auditing.

vs a multicolumn index that includes deleted_at

CREATE INDEX idx_tasks_author_active_inc
  ON tasks (author_id, deleted_at, created_at);

It includes deleted_at as an index column. It works, but it's suboptimal:

• More space (three columns vs two).
• The planner may still apply a post-scan filter in some complex plans.
• It doesn't have the partial index's advantage of physically excluding the deleted rows.

When to pick it: rarely. Only if your query load varies a lot between "see active" and "see deleted" at the same ratio.

vs an expression index on (deleted_at IS NULL)

CREATE INDEX idx_tasks_active_expr
  ON tasks ((deleted_at IS NULL), author_id, created_at);

It indexes the boolean result of the deleted_at IS NULL expression. It works but it's overkill: the partial index's predicate is exactly that expression, and choosing between active/deleted is a trivial boolean operation. The expression index adds complexity with no benefit over the partial one.

When to pick it: almost never for soft delete. Expression indexes shine when you index functions (LOWER(email), extract('year' FROM created_at)).

Creating the index in production with no downtime

In capsule 03 you saw the SQL: CREATE INDEX CONCURRENTLY. In SQLAlchemy + Alembic:

# alembic/versions/abc123_add_partial_index.py
"""Add partial index on tasks for active rows.

Revision ID: abc123
"""
from alembic import op


def upgrade() -> None:
    # CREATE INDEX CONCURRENTLY can't run inside a transaction.
    # Use autocommit_block to take the operation out of the transactional scope.
    with op.get_context().autocommit_block():
        op.execute(
            """
            CREATE INDEX CONCURRENTLY idx_tasks_author_created_active
            ON tasks (author_id, created_at DESC)
            WHERE deleted_at IS NULL
            """
        )


def downgrade() -> None:
    with op.get_context().autocommit_block():
        op.execute("DROP INDEX CONCURRENTLY IF EXISTS idx_tasks_author_created_active")

Caveats:

• CONCURRENTLY can take minutes on large tables. The app keeps serving throughout.
• If the creation fails halfway (OOM, a conflict), the index is left in an INVALID state. You have to drop it manually and retry.
• Verify with \d+ tasks or:

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'tasks' AND indexname = 'idx_tasks_author_created_active';

Module 5 (Zero-Downtime Migrations) goes deeper into this pattern with real cases: how to handle partial failures, how to detect INVALID indexes, how to combine it with lock_timeout.

Why does this capsule matter in real work?

1. The partial index is what makes soft delete sustainable at scale. Without it, soft delete on a 10M-row table with 70% deleted is a disguised N+1: every query scans 7M useless rows. With it, the pattern scales to much larger tables before needing an archive table or partitioning (capsule 07).

2. It's the cleanest integration with the listener. The dev writes queries with no filter, the listener injects the predicate, the planner uses the partial index. Each piece is necessary for the pattern to work. Remove the partial index and it degrades to 60ms; remove the listener and you introduce semantic bugs.

3. Production diagnosis is easier with documented partial indexes. When an endpoint gets slow, the first reflex is to check the plan. If you see Filter: (deleted_at IS NULL) Rows Removed by Filter: 5000, you know the partial index is missing. Without that signature, the diagnosis requires more investigation.

4. Code reviews get faster. When someone adds a new model with SoftDeleteMixin, the reviewer just checks that the partial index is in __table_args__. It's a 5-second check. Without the pattern, every model with soft delete requires a deeper review.

Traps and common mistakes

Mistake 1 (conceptual): assuming a partial index improves every query

Symptom: you create the partial index but some queries are still slow. EXPLAIN shows a Seq Scan.

Why it happens: the partial index is only used when the query's WHERE predicate implies the index's predicate. A query without WHERE deleted_at IS NULL doesn't qualify.

How to tell:

• Is the listener installed? Check with echo=True on the engine and review the actual SQL.
• Does the query use text() with raw SQL? The listener doesn't intercept raw SQL.
• Is the query an UPDATE/DELETE? The listener doesn't intercept those (by design).

How to fix it: make sure the deleted_at IS NULL filter is in the actual SQL (not just in the Python code). If the query is "see deleted rows" by design, use the escape hatch instead of an involuntary bypass.

Mistake 2 (practical): the partial index is NOT created automatically with Base.metadata.create_all()

Symptom: you run create_all() in setup but \d tasks doesn't show the partial index.

Why it happens: check that __table_args__ is defined correctly. If you wrote it as a normal class attribute without a tuple, SQLAlchemy silently ignores it.

How to tell:

# ❌ Wrong: a bare dict (valid syntax but different semantics)
__table_args__ = {"comment": "Tasks table"}

# ❌ Wrong: the index as a loose variable
my_index = Index("idx_tasks_active", ...)

# ✅ Right: a tuple with indexes and optionally an options dict at the end
__table_args__ = (
    Index("idx_tasks_active", "author_id", postgresql_where=text("deleted_at IS NULL")),
)

# ✅ Also valid: a tuple with indexes + a dict at the end
__table_args__ = (
    Index("idx_tasks_active", "author_id", postgresql_where=text("deleted_at IS NULL")),
    {"comment": "Tasks with soft delete"},
)

How to fix it: make sure you have the correct tuple syntax. Verify with \d tasks after create_all().

Mistake 3 (edge case): the index's predicate changes and the old index is logically invalidated

Symptom: you decide your soft delete model changes (for example, now it's deleted_at IS NULL OR archived = false). You modify the model. The old index is still there but the planner no longer uses it.

Why it happens: indexes are catalogued with their exact predicate. Changing the logical definition in the model doesn't recreate the index; that change requires an explicit migration.

How to tell: after changing the model, run:

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'tasks';

If the indexdef shows the old predicate, it wasn't updated.

How to fix it: generate an explicit Alembic migration: DROP INDEX CONCURRENTLY ... + CREATE INDEX CONCURRENTLY ... WHERE .... Never trust that model changes will be reflected automatically in indexes.

Mistake 4 (practical): a partial index on a nullable column with the wrong column order

Symptom: you create CREATE INDEX ... ON tasks (created_at) WHERE deleted_at IS NULL thinking it's optimal for "latest active tasks." Queries for a specific author are still slow.

Why it happens: the index only has created_at. A WHERE author_id = 42 AND deleted_at IS NULL ORDER BY created_at DESC query can use it, but PostgreSQL has to filter by author_id post-scan (it walks every active row in the index).

How to tell: EXPLAIN ANALYZE will show an Index Scan with Filter: (author_id = 42) and a high Rows Removed by Filter.

How to fix it: the index has to have the WHERE columns first, then the ORDER BY ones. For queries by author_id, the composite is (author_id, created_at DESC) with the soft delete predicate. Exactly as we showed in the capsule.

Mistake 5 (conceptual): not understanding the partial index's space saving

Symptom: the team doesn't want to use partial indexes "because they add complexity." They keep normal indexes on large tables with a high delete ratio.

Why it's wrong: the partial index doesn't add significant operational complexity (it's one extra parameter on the CREATE INDEX). But it saves:

• Disk space (proportional to the ratio of filtered rows).
• shared_buffers space (better cache locality).
• VACUUM time (fewer pages to clean).
• Time on every INSERT/UPDATE (less index to update).

How to tell: measure the size of the normal index vs the partial one. Compute the table's delete ratio. If the ratio is >30%, the partial index is pareto-better.

How to fix it: propose the change with concrete numbers in a code review (MB saved on disk, latency improvement in queries).

Exercises

Exercise 1: define a partial index in SQLAlchemy and verify the plan

Define a Comment model with SoftDeleteMixin, task_id, body, created_at, and a partial index on (task_id, created_at). Create the tables, seed 100k comments with 50% deleted, and verify with EXPLAIN ANALYZE that the planner uses the partial index when the listener injects the filter.

# app/models/comment.py
from datetime import datetime
from sqlalchemy import BigInteger, Index, String, DateTime, func, text
from sqlalchemy.orm import Mapped, mapped_column

from app.models.task import Base
from app.models.mixins import SoftDeleteMixin


class Comment(Base, SoftDeleteMixin):
    __tablename__ = "comments"

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    task_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    body: Mapped[str] = mapped_column(String(1000), nullable=False)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        nullable=False,
        server_default=func.now(),
    )

    __table_args__ = (
        Index(
            "idx_comments_task_created_active",
            "task_id",
            "created_at",
            postgresql_where=text("deleted_at IS NULL"),
        ),
    )

# scripts/seed_comments.py
import asyncio
import random
from datetime import datetime, timedelta, timezone

from app.db import AsyncSessionLocal, engine
from app.models.task import Base
from app.models.comment import Comment


async def main():
    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.create_all)

    now = datetime.now(timezone.utc)
    async with AsyncSessionLocal() as s:
        for batch_start in range(0, 100_000, 5000):
            batch = []
            for i in range(5000):
                idx = batch_start + i
                deleted = (random.random() < 0.5)
                c = Comment(
                    task_id=random.randint(1, 1000),
                    body=f"comment_{idx}",
                    created_at=now - timedelta(seconds=random.randint(0, 3600 * 24 * 90)),
                )
                if deleted:
                    c.deleted_at = c.created_at + timedelta(hours=1)
                batch.append(c)
            s.add_all(batch)
            await s.commit()
            print(f"  inserted {batch_start + 5000} / 100000")


asyncio.run(main())

# scripts/verify_index.py
import asyncio
from sqlalchemy import text
from app.db import AsyncSessionLocal


async def main():
    async with AsyncSessionLocal() as s:
        await s.execute(text("ANALYZE comments"))

        result = await s.execute(
            text("""
                EXPLAIN (ANALYZE, BUFFERS)
                SELECT id, body FROM comments
                WHERE task_id = 42 AND deleted_at IS NULL
                ORDER BY created_at DESC LIMIT 20
            """)
        )
        for row in result:
            print(row[0])


asyncio.run(main())

Limit  (cost=0.42..28.45 rows=20 width=20) (actual time=0.022..0.198 rows=20 loops=1)
  Buffers: shared hit=24
  ->  Index Scan using idx_comments_task_created_active on comments
        (cost=0.42..145.30 rows=100 width=20)
        (actual time=0.020..0.190 rows=20 loops=1)
        Index Cond: (task_id = 42)
        Buffers: shared hit=24
Planning Time: 0.105 ms
Execution Time: 0.235 ms

Exercise 2: compare latency with and without the partial index

On the table from exercise 1, measure the difference between:

a) With no index (DROP the existing INDEX). b) With a normal (non-partial) index. c) With the partial index.

Report Execution Time and Buffers: shared hit for a typical query.

-- (a) No index
DROP INDEX IF EXISTS idx_comments_task_created_active;
DROP INDEX IF EXISTS idx_comments_task_normal;
ANALYZE comments;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM comments WHERE task_id = 42 AND deleted_at IS NULL
ORDER BY created_at DESC LIMIT 20;

-- (b) Normal index
CREATE INDEX idx_comments_task_normal ON comments (task_id, created_at);
ANALYZE comments;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM comments WHERE task_id = 42 AND deleted_at IS NULL
ORDER BY created_at DESC LIMIT 20;

-- (c) Partial index
DROP INDEX idx_comments_task_normal;
CREATE INDEX idx_comments_task_active
  ON comments (task_id, created_at DESC) WHERE deleted_at IS NULL;
ANALYZE comments;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM comments WHERE task_id = 42 AND deleted_at IS NULL
ORDER BY created_at DESC LIMIT 20;

Exercise 3: create an Alembic migration with CREATE INDEX CONCURRENTLY

Generate an Alembic migration that adds the partial index to comments without taking an exclusive lock. Include the downgrade correctly.

# alembic/versions/xyz789_add_partial_index_comments.py
"""Add partial index on comments for active rows

Revision ID: xyz789
Revises: previous_revision
Create Date: 2026-05-02 14:00:00.000000

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers
revision = 'xyz789'
down_revision = 'previous_revision'
branch_labels = None
depends_on = None


def upgrade() -> None:
    # CREATE INDEX CONCURRENTLY can't run inside a transaction.
    # autocommit_block() takes this operation out of Alembic's transactional scope.
    with op.get_context().autocommit_block():
        op.execute(
            """
            CREATE INDEX CONCURRENTLY idx_comments_task_created_active
            ON comments (task_id, created_at DESC)
            WHERE deleted_at IS NULL
            """
        )


def downgrade() -> None:
    with op.get_context().autocommit_block():
        op.execute(
            "DROP INDEX CONCURRENTLY IF EXISTS idx_comments_task_created_active"
        )

alembic upgrade head

INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade previous_revision -> xyz789, ...

\d comments
-- Indexes:
--   ...
--   "idx_comments_task_created_active" btree (task_id, created_at DESC) WHERE deleted_at IS NULL

-- The index is left in an INVALID state
SELECT indexname, indexrelid::regclass, indisvalid
FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid
WHERE c.relname = 'idx_comments_task_created_active';

-- If indisvalid = false, drop it and retry:
DROP INDEX CONCURRENTLY idx_comments_task_created_active;

SELECT
  schemaname || '.' || tablename AS table,
  indexname,
  indexdef
FROM pg_indexes pi
JOIN pg_class pc ON pc.relname = pi.indexname
JOIN pg_index pgi ON pgi.indexrelid = pc.oid
WHERE NOT pgi.indisvalid;

Exercise 4: decide how many partial indexes a table needs

Your tasks table has these frequent query patterns:

a) WHERE author_id = $1 AND deleted_at IS NULL ORDER BY created_at DESC (50% of traffic). b) WHERE project_id = $1 AND deleted_at IS NULL ORDER BY due_date ASC (30% of traffic). c) WHERE assignee_id = $1 AND status = 'open' AND deleted_at IS NULL (15% of traffic). d) WHERE deleted_at IS NOT NULL ORDER BY deleted_at DESC LIMIT 100 (5% of traffic, an audit dashboard).

What indexes would you create and why? The table has 5M rows, 70% deleted.

-- (1) For query (a) - 50% of traffic
CREATE INDEX idx_tasks_author_created_active
  ON tasks (author_id, created_at DESC)
  WHERE deleted_at IS NULL;

-- (2) For query (b) - 30% of traffic
CREATE INDEX idx_tasks_project_due_active
  ON tasks (project_id, due_date ASC)
  WHERE deleted_at IS NULL;

-- (3) For query (c) - 15% of traffic
-- A narrower predicate: include status in the partial index's filter
CREATE INDEX idx_tasks_assignee_open_active
  ON tasks (assignee_id)
  WHERE deleted_at IS NULL AND status = 'open';

-- (4) For query (d) - 5% of traffic (audit)
-- Partial index on the opposite: only deleted rows
CREATE INDEX idx_tasks_deleted_audit
  ON tasks (deleted_at DESC)
  WHERE deleted_at IS NOT NULL;

Summary and next step

In this capsule you learned:

• The partial index is the canonical case of partial indexes in PostgreSQL. Soft delete justifies their existence with a realistic, measurable use case.
• The planner uses the partial index when the query's WHERE includes the index's predicate. The automatic listener from capsule 04 guarantees this with no effort from the dev.
• Declarative syntax in SQLAlchemy: Index(..., postgresql_where=text("deleted_at IS NULL")). Easy to read, easy to maintain.
• Creating the index in production with no downtime: CREATE INDEX CONCURRENTLY with Alembic + autocommit_block(). Module 5 goes deeper.
• Multiple partial indexes on the same table are valid when there are several frequent query patterns. The rule: the index has to pay for its maintenance cost with the speedup in queries.
• A clear comparison: partial index > normal index > expression index > no index. For soft delete, partial is always the answer when the delete ratio is high.

Before moving on you should be able to:

• Define a partial index in SQLAlchemy with postgresql_where=text(...) without documentation.
• Verify with EXPLAIN ANALYZE that the planner picks it and read the plan correctly.
• Decide how many partial indexes a table deserves based on query patterns and filter ratios.
• Generate an Alembic migration with CREATE INDEX CONCURRENTLY using autocommit_block().

Next capsule — Anti-patterns: WHERE deleted_at IS NULL everywhere. Even though we automated the filter with the listener, there are additional anti-patterns that show up in real codebases: the forgotten filter in stats queries (which count deleted rows unintentionally), the silent cascade in foreign keys, JOIN queries that lose legitimate rows by over-filtering, and code that abuses the include_deleted=True escape hatch. You're going to learn to spot them in code review and refactor them into the correct pattern.

Resources

1. PostgreSQL Documentation — Partial Indexes — the official docs. Read it in full: it has use cases (including soft delete) and warnings about when the planner does NOT use the index.
2. SQLAlchemy 2.0 — Index and postgresql_where — the PostgreSQL dialect's specific reference for partial indexes.
3. SQLAlchemy 2.0 — Schema definition with __table_args__ — the official reference for the correct tuple syntax.
4. Alembic — Operations: op.execute and autocommit_block — for CREATE INDEX CONCURRENTLY in migrations.
5. Crunchy Data — "Partial Indexes in PostgreSQL" — an analysis of when the planner picks them and when it doesn't.
6. Markus Winand — "Indexing IS NULL" — how PostgreSQL handles IS NULL in indexes.
7. Cybertec — "PostgreSQL: When are partial indexes useful?" — an operational guide with benchmarks.

Module 2 — SQL Patterns for Production APIs Guide

Next capsule: Anti-patterns — WHERE deleted_at IS NULL everywhere and other common mistakes.