Índices parciales para soft deletes

Descripción de la cápsula

Los índices parciales son una feature de PostgreSQL que la guía #12 (módulo 3) introdujo como técnica: un índice que solo cubre filas que cumplen un predicado. Útil pero abstracto si nunca tuviste un caso concreto. Soft delete es el caso canónico — la justificación más limpia para que existan los partial indexes en primer lugar. Esta cápsula los aplica a soft delete específicamente, midiendo cómo el planner los aprovecha cuando el filtro WHERE deleted_at IS NULL viene del listener automático que armaste en la cápsula 04.

Vas a ver el ciclo completo: definir el índice parcial declarativamente en SQLAlchemy, verificar con EXPLAIN ANALYZE que el planner lo elige, comparar con índices alternativos (índice normal, multicolumn, expression index), y entender cuándo agregar más de un índice parcial sobre la misma tabla. Al terminar tendrás reflejo de "agregaste deleted_at, ahora agrega el índice parcial" sin pensarlo, y criterios para diseñar índices parciales más allá del caso canónico.

Esta cápsula es relativamente compacta porque construye sobre fundamentos ya cubiertos. La novedad está en la integración: índice parcial + listener automático + Pydantic response models trabajando juntos, ejecutando SQL óptimo sin que el desarrollador escriba el filtro a mano.

El framing: el índice parcial como "índice solo de lo que importa"

Un índice normal es un mapa de toda la tabla: cada fila tiene una entrada. Un índice parcial es un mapa de solo las filas que cumplen un predicado: las que NO cumplen, no aparecen.

Índice normal sobre (author_id, created_at):
┌─────────────────────────────────────────────┐
│ Entrada por cada fila de tasks (1M total)   │
│ - 400k filas activas + 600k borradas        │
│ - Tamaño: ~32 MB                            │
│ - Lookup recorre activas Y borradas         │
└─────────────────────────────────────────────┘

Índice parcial WHERE deleted_at IS NULL:
┌─────────────────────────────────────────────┐
│ Entrada solo por filas activas (400k)       │
│ - Las 600k borradas no existen aquí         │
│ - Tamaño: ~13 MB                            │
│ - Lookup recorre solo activas               │
└─────────────────────────────────────────────┘

El índice parcial es 60% más chico (proporcional al ratio de filas filtradas) y, lo más importante, el lookup no toca las filas excluidas. PostgreSQL no las lee, no las descarta, no las menciona en el plan. Es como si no existieran para queries que coincidan con el predicado del índice.

Profundización en cómo PostgreSQL elige usar un índice parcial (cuándo el predicado del WHERE de la query implica el predicado del índice) está en la guía #12 módulo 3. Aquí lo aplicamos sin re-explicar la mecánica.

Cómo el planner detecta que puede usar el índice parcial

El planner usa el índice parcial cuando puede probar que el predicado del índice es siempre verdadero para las filas que la query solicita. La regla práctica:

-- Definición del índice
CREATE INDEX idx_tasks_active
  ON tasks (author_id, created_at DESC)
  WHERE deleted_at IS NULL;

-- Queries que el planner SÍ usa el índice parcial:
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 que NO usa el índice parcial:
SELECT * FROM tasks WHERE author_id = 42;  -- sin filtro de deleted_at
SELECT * FROM tasks WHERE deleted_at IS NOT NULL;  -- predicado opuesto
SELECT * FROM tasks WHERE deleted_at = '2026-01-01';  -- predicado distinto

La regla: la query debe incluir literalmente el predicado del índice (deleted_at IS NULL) o algo que el planner pueda demostrar que lo implica. PostgreSQL no es capaz de inferir relaciones complejas; necesita el match exacto o casi exacto.

Por qué el listener automático es la combinación perfecta

Aquí cierra el círculo de la cápsula 04. El listener inyecta WHERE deleted_at IS NULL automáticamente en cada SELECT. Con el índice parcial, el planner detecta el predicado y usa el índice. El desarrollador no escribió el filtro y sin embargo el plan es óptimo.

# Lo que el dev escribió:
result = await session.execute(
    select(Task).where(Task.author_id == 42).order_by(Task.created_at.desc()).limit(50)
)

# Lo que SQLAlchemy generó (después del 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

# Lo que PostgreSQL ejecutó (después del planner):
Index Scan using idx_tasks_active on tasks
  Index Cond: (author_id = 42)

Esa cadena de tres pasos (código limpio → SQL filtrado → plan óptimo) es lo que define el patrón "soft delete bien hecho". Cualquier eslabón roto lo degrada.

Caso desarrollado: definir el índice parcial en SQLAlchemy

En la cápsula 03 vimos el SQL puro. Ahora veámoslo declarativo en SQLAlchemy 2.0.

Definición del índice en el modelo

# 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__ = (
        # Índice parcial: caso canónico para soft delete.
        # `postgresql_where` es el predicado del índice.
        Index(
            "idx_tasks_author_created_active",
            "author_id",
            "created_at",
            postgresql_where=text("deleted_at IS NULL"),
        ),
    )

Detalles importantes:

• postgresql_where es el parámetro específico de PostgreSQL. Otros backends (MySQL, SQLite) ignoran el predicado y crean un índice normal. Si tu app es portable, esto es algo a considerar (y posiblemente generar el índice via Alembic con SQL puro).
• text("deleted_at IS NULL") es SQL crudo dentro del declarativo. SQLAlchemy no intenta parsearlo; lo pasa directo al CREATE INDEX. Esto da flexibilidad pero también responsabilidad de que el predicado sea sintácticamente correcto.
• Orden de columnas: author_id primero porque es el filtro de cardinalidad alta (mucha selectividad por valor); created_at segundo porque es el ORDER BY. Esto sigue el patrón clásico de composite indexes (guía #12 módulo 3).

Verificar que el índice se creó

-- En psql después de correr Base.metadata.create_all
\d tasks

-- Output esperado:
--                        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

La línea WHERE deleted_at IS NULL confirma que es índice parcial.

Verificar que el planner lo usa

Con el listener instalado de la cápsula 04, ejecuta:

# 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())

Output esperado:

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

Lo crítico:

• Index Scan using idx_tasks_author_created_active — usó el índice parcial.
• Sin línea Filter: — no hay filtro post-scan.
• Buffers: shared hit=54 — leyó solo lo necesario.

Cuándo agregar más de un índice parcial sobre la misma tabla

Una tabla puede tener múltiples índices parciales si los queries más comunes acceden a subconjuntos distintos. Ejemplos:

-- Para queries de un author específico (filtro frecuente)
CREATE INDEX idx_tasks_author_active
  ON tasks (author_id, created_at DESC)
  WHERE deleted_at IS NULL;

-- Para queries de tasks por proyecto (otro filtro frecuente)
CREATE INDEX idx_tasks_project_active
  ON tasks (project_id, created_at DESC)
  WHERE deleted_at IS NULL;

-- Para queries de auditoría que SÍ ven borrados (ratio bajo de uso)
CREATE INDEX idx_tasks_deleted_audit
  ON tasks (deleted_at DESC)
  WHERE deleted_at IS NOT NULL;

Regla operacional: un índice parcial vale la pena si:

• La query que lo usa es frecuente (ejecutada >100 veces/día como referencia gruesa).
• El subconjunto cubierto es significativamente menor al total (típicamente <50%).
• El costo de mantener el índice (escrituras adicionales en INSERT/UPDATE) es compensado por el speedup en lecturas.

No vale la pena cuando:

• La query es rara (un report mensual, por ejemplo).
• El subconjunto cubierto es casi toda la tabla (>80%).
• La tabla tiene altísimo volumen de escrituras y el índice extra ralentiza inserts.

Tradeoff de write amplification: cada índice adicional es trabajo extra en cada INSERT y UPDATE de la tabla. La guía #12 módulo 5 (write performance) lo profundiza.

Comparación con alternativas

vs índice normal (no parcial)

CREATE INDEX idx_tasks_author_normal ON tasks (author_id, created_at);

Cubre todas las filas. PostgreSQL puede usarlo para queries que filtran por author_id, pero el filtro deleted_at IS NULL se aplica post-scan. Con 60% de filas borradas, la latencia es ~60x peor que con índice parcial (medido en cápsula 03).

Cuándo elegir: si la mayoría de queries necesitan ver activas Y borradas (raro), un índice normal es más versátil. Caso de uso: tabla con audit_log donde la mitad de las queries son auditoría.

vs índice multicolumn que incluye deleted_at

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

Incluye deleted_at como columna del índice. Funciona, pero es subóptimo:

• Más espacio (tres columnas vs dos).
• El planner puede seguir aplicando filtro post-scan en algunos planes complejos.
• No tiene la ventaja del índice parcial de excluir físicamente las filas borradas.

Cuándo elegir: rara vez. Solo si tu carga de queries varía mucho entre "ver activas" y "ver borradas" en el mismo ratio.

vs expression index sobre (deleted_at IS NULL)

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

Indexa el resultado booleano de la expresión deleted_at IS NULL. Funciona pero es overkill: el predicado del índice parcial es exactamente esa expresión, y elegir entre activos/borrados es una operación booleana trivial. El expression index agrega complejidad sin beneficio sobre el parcial.

Cuándo elegir: casi nunca para soft delete. Expression indexes brillan cuando indexas funciones (LOWER(email), extract('year' FROM created_at)).

Crear el índice en producción sin downtime

En la cápsula 03 viste el SQL: CREATE INDEX CONCURRENTLY. En 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 no puede ejecutarse en transacción.
    # Usar autocommit_block para sacar la operación del scope transaccional.
    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 puede tardar minutos en tablas grandes. La app sigue sirviendo durante.
• Si la creación falla a mitad (OOM, conflicto), el índice queda en estado INVALID. Hay que dropearlo manualmente y reintentar.
• Verifica con \d+ tasks o:

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

El módulo 5 (Zero-Downtime Migrations) profundiza este patrón con casos reales: cómo manejar fallos parciales, cómo detectar índices INVALID, cómo combinar con lock_timeout.

¿Por qué importa esta cápsula en el trabajo real?

1. El índice parcial es lo que hace que soft delete sea sostenible a escala. Sin él, soft delete en una tabla de 10M filas con 70% borradas es N+1 disfrazado: cada query escanea 7M de filas inútiles. Con él, el patrón escala a tablas mucho más grandes antes de necesitar archive table o partitioning (cápsula 07).

2. Es la integración más limpia con el listener. El dev escribe queries sin filtro, el listener inyecta el predicado, el planner usa el índice parcial. Cada pieza es necesaria para que el patrón funcione. Quitar el índice parcial degrada a 60ms; quitar el listener introduce bugs semánticos.

3. Diagnóstico de producción es más fácil con índices parciales documentados. Cuando un endpoint se vuelve lento, el primer reflejo es revisar el plan. Si ves Filter: (deleted_at IS NULL) Rows Removed by Filter: 5000, sabes que falta el índice parcial. Sin esa firma, el diagnóstico requiere más investigación.

4. Code reviews se vuelven más rápidos. Cuando alguien agrega un nuevo modelo con SoftDeleteMixin, el reviewer solo verifica que el índice parcial esté en __table_args__. Es un check de 5 segundos. Sin el patrón, cada modelo con soft delete requiere review más profundo.

Trampas y errores comunes

Error 1 (conceptual): asumir que índice parcial mejora todas las queries

Síntoma: creas el índice parcial pero algunas queries siguen lentas. EXPLAIN muestra Seq Scan.

Por qué pasa: el índice parcial solo se usa cuando el predicado del WHERE de la query implica el predicado del índice. Una query sin WHERE deleted_at IS NULL no califica.

Cómo distinguir:

• ¿El listener está instalado? Verifica con echo=True en el engine y revisa el SQL real.
• ¿La query usa text() con SQL crudo? El listener no intercepta SQL crudo.
• ¿La query es UPDATE/DELETE? El listener no las intercepta (intencional).

Cómo corregir: asegúrate de que el filtro deleted_at IS NULL esté en el SQL real (no solo en el código Python). Si la query es por diseño "ver borrados", usa el escape hatch en lugar de bypass involuntario.

Error 2 (práctico): el índice parcial NO se crea automáticamente con Base.metadata.create_all()

Síntoma: corres create_all() en setup pero \d tasks no muestra el índice parcial.

Por qué pasa: revisa que __table_args__ esté correctamente definido. Si lo escribiste como atributo de clase normal sin tuple, SQLAlchemy lo ignora silenciosamente.

Cómo distinguir:

# ❌ Mal: dict directo (sintaxis válida pero distinta semántica)
__table_args__ = {"comment": "Tasks table"}

# ❌ Mal: índice como variable suelta
my_index = Index("idx_tasks_active", ...)

# ✅ Bien: tuple con índices y opcionalmente dict de opciones al final
__table_args__ = (
    Index("idx_tasks_active", "author_id", postgresql_where=text("deleted_at IS NULL")),
)

# ✅ También válido: tuple con índices + dict al final
__table_args__ = (
    Index("idx_tasks_active", "author_id", postgresql_where=text("deleted_at IS NULL")),
    {"comment": "Tasks with soft delete"},
)

Cómo corregir: asegurarte de la sintaxis tuple correcta. Verifica con \d tasks después de create_all().

Error 3 (edge case): el predicado del índice cambia y el índice viejo queda invalidado lógicamente

Síntoma: decides que tu modelo de soft delete cambia (por ejemplo, ahora deleted_at IS NULL OR archived = false). Modificas el modelo. El índice viejo sigue ahí pero el planner ya no lo usa.

Por qué pasa: los índices son catalogados con su predicado exacto. Cambiar la definición lógica en el modelo no recrea el índice; ese cambio requiere migración explícita.

Cómo distinguir: después de cambiar el modelo, ejecuta:

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

Si el indexdef muestra el predicado viejo, no se actualizó.

Cómo corregir: generar migración Alembic explícita: DROP INDEX CONCURRENTLY ... + CREATE INDEX CONCURRENTLY ... WHERE .... Nunca confíes en que cambios en el modelo se reflejen automáticamente en índices.

Error 4 (práctico): índice parcial sobre columna nullable sin orden de columnas correcto

Síntoma: creas CREATE INDEX ... ON tasks (created_at) WHERE deleted_at IS NULL pensando que es óptimo para "últimas tasks activas". Las queries por author específico siguen lentas.

Por qué pasa: el índice solo tiene created_at. Una query WHERE author_id = 42 AND deleted_at IS NULL ORDER BY created_at DESC puede usarlo, pero PostgreSQL tiene que filtrar por author_id post-scan (recorre todas las filas activas del índice).

Cómo distinguir: EXPLAIN ANALYZE mostrará Index Scan con Filter: (author_id = 42) y Rows Removed by Filter alto.

Cómo corregir: el índice debe tener las columnas del WHERE primero, luego las del ORDER BY. Para queries por author_id, el composite es (author_id, created_at DESC) con predicado de soft delete. Tal como mostramos en la cápsula.

Error 5 (conceptual): no entender el ahorro de espacio del índice parcial

Síntoma: equipo no quiere usar índices parciales "porque añaden complejidad". Mantienen índices normales en tablas grandes con alto ratio de borrados.

Por qué es erróneo: el índice parcial no añade complejidad operacional significativa (es un parámetro extra en el CREATE INDEX). Pero ahorra:

• Espacio en disco (proporcional al ratio de filas filtradas).
• Espacio en shared_buffers (mejor cache locality).
• Tiempo de VACUUM (menos páginas que limpiar).
• Tiempo de cada INSERT/UPDATE (menos índice que actualizar).

Cómo distinguir: mide el tamaño del índice normal vs el parcial. Calcula el ratio de borrados de la tabla. Si el ratio es >30%, el índice parcial es pareto-mejor.

Cómo corregir: propón el cambio con números concretos en code review (ahorro de MB en disco, mejora de latencia en queries).

Ejercicios

Ejercicio 1: definir índice parcial en SQLAlchemy y verificar plan

Define un modelo Comment con SoftDeleteMixin, task_id, body, created_at e índice parcial sobre (task_id, created_at). Crea las tablas, siembra 100k comments con 50% borrados, y verifica con EXPLAIN ANALYZE que el planner usa el índice parcial cuando el listener inyecta el filtro.

# 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

Ejercicio 2: comparar latencia con y sin índice parcial

Sobre la tabla del ejercicio 1, mide la diferencia entre:

a) Sin índice (DROP INDEX existente). b) Con índice normal (no parcial). c) Con índice parcial.

Reporta Execution Time y Buffers: shared hit para una query típica.

-- (a) Sin índice
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) Índice normal
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) Índice parcial
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;

Ejercicio 3: crear migración Alembic con CREATE INDEX CONCURRENTLY

Genera una migración Alembic que añada el índice parcial a comments sin tomar lock exclusivo. Incluye el downgrade correctamente.

# 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 no puede correr en una transacción.
    # autocommit_block() saca esta operación del scope transaccional de Alembic.
    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

-- El índice queda en estado INVALID
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';

-- Si indisvalid = false, dropear y reintentar:
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;

Ejercicio 4: decidir cuántos índices parciales necesita una tabla

Tu tabla tasks tiene estos patrones de query frecuentes:

a) WHERE author_id = $1 AND deleted_at IS NULL ORDER BY created_at DESC (50% del tráfico). b) WHERE project_id = $1 AND deleted_at IS NULL ORDER BY due_date ASC (30% del tráfico). c) WHERE assignee_id = $1 AND status = 'open' AND deleted_at IS NULL (15% del tráfico). d) WHERE deleted_at IS NOT NULL ORDER BY deleted_at DESC LIMIT 100 (5% del tráfico, dashboard de auditoría).

¿Qué índices crearías y por qué? La tabla tiene 5M filas, 70% borradas.

-- (1) Para query (a) - 50% del tráfico
CREATE INDEX idx_tasks_author_created_active
  ON tasks (author_id, created_at DESC)
  WHERE deleted_at IS NULL;

-- (2) Para query (b) - 30% del tráfico
CREATE INDEX idx_tasks_project_due_active
  ON tasks (project_id, due_date ASC)
  WHERE deleted_at IS NULL;

-- (3) Para query (c) - 15% del tráfico
-- Predicado más estrecho: incluir status en el filtro del índice parcial
CREATE INDEX idx_tasks_assignee_open_active
  ON tasks (assignee_id)
  WHERE deleted_at IS NULL AND status = 'open';

-- (4) Para query (d) - 5% del tráfico (auditoría)
-- Índice parcial sobre el opuesto: solo borrados
CREATE INDEX idx_tasks_deleted_audit
  ON tasks (deleted_at DESC)
  WHERE deleted_at IS NOT NULL;

Resumen y siguiente paso

En esta cápsula aprendiste:

• El índice parcial es el caso canónico de partial indexes en PostgreSQL. Soft delete justifica su existencia con un caso de uso realista y medible.
• El planner usa el índice parcial cuando el WHERE de la query incluye el predicado del índice. El listener automático de la cápsula 04 garantiza esto sin esfuerzo del dev.
• Sintaxis declarativa en SQLAlchemy: Index(..., postgresql_where=text("deleted_at IS NULL")). Fácil de leer, fácil de mantener.
• Crear el índice en producción sin downtime: CREATE INDEX CONCURRENTLY con Alembic + autocommit_block(). El módulo 5 lo profundiza.
• Múltiples índices parciales sobre la misma tabla son válidos cuando hay varios patrones de query frecuentes. La regla: el índice debe pagar su costo de mantenimiento con el speedup en queries.
• Comparación clara: índice parcial > índice normal > expression index > sin índice. Para soft delete, parcial es siempre la respuesta cuando el ratio de borrados es alto.

Antes de avanzar deberías poder:

• Definir índice parcial en SQLAlchemy con postgresql_where=text(...) sin documentación.
• Verificar con EXPLAIN ANALYZE que el planner lo elige y leer el plan correctamente.
• Decidir cuántos índices parciales merece una tabla basándote en patrones de query y ratios de filtrado.
• Generar migración Alembic con CREATE INDEX CONCURRENTLY usando autocommit_block().

Siguiente cápsula — Anti-patterns: WHERE deleted_at IS NULL everywhere. Aunque automatizamos el filtro con el listener, hay anti-patterns adicionales que aparecen en codebases reales: el filtro olvidado en queries de stats (que cuentan borrados sin querer), cascade silencioso en foreign keys, queries de JOIN que pierden filas legítimas por filtrar de más, y código que abusa del escape hatch include_deleted=True. Vas a aprender a detectarlos en code review y refactorizarlos al patrón correcto.

Recursos

1. PostgreSQL Documentation — Partial Indexes — la doc oficial. Léela completa: tiene casos de uso (incluyendo soft delete) y advertencias sobre cuándo el planner NO usa el índice.
2. SQLAlchemy 2.0 — Index and postgresql_where — referencia específica del dialect PostgreSQL para índices parciales.
3. SQLAlchemy 2.0 — Schema definition with __table_args__ — referencia oficial para la sintaxis tuple correcta.
4. Alembic — Operations: op.execute and autocommit_block — para CREATE INDEX CONCURRENTLY en migraciones.
5. Crunchy Data — "Partial Indexes in PostgreSQL" — análisis de cuándo el planner los elige y cuándo no.
6. Markus Winand — "Indexing IS NULL" — cómo PostgreSQL maneja IS NULL en índices.
7. Cybertec — "PostgreSQL: When are partial indexes useful?" — guía operacional con benchmarks.

Módulo 2 — SQL Patterns for Production APIs Guide

Siguiente cápsula: Anti-patterns — WHERE deleted_at IS NULL everywhere y otros errores comunes.