Alternatives: archive tables and partitioning

Capsule overview

Soft delete with a partial index and an automatic filter scales well — up to a point. When a table crosses 100M rows with more than 70% deleted, the pattern starts to suffer: autovacuum takes hours, the partial indexes are still proportionally large in absolute terms, migrations get painful, and the accumulated bloat weighs on every operation. At that point soft delete stops being the solution and starts being the problem.

There are two established alternatives: archive tables (moving deleted records to a separate table with a periodic job, running queries with a UNION when you need the history) and partitioning by state (PostgreSQL 11+ allows PARTITION BY LIST to physically separate active from deleted rows without moving them manually). Each one has its own operational cost, but both solve the fundamental problem: the operational app's queries do NOT touch deleted rows at the physical level, not just the logical one.

In this capsule you're going to understand when you cross the threshold where these alternatives are worth it, how each one is implemented at the schema and process level, and what the operational trade-offs are that keep you awake the night of the go-live. You aren't going to implement full partitioning (that's guide #14), but you're going to come out with criteria for making the right architectural decision.

The framing: storage tiers by temperature

Think of your app's data like an office archive:

• Hot data: what gets queried daily. Active tasks, recent comments, live sessions. It's on your desk, accessible in seconds.
• Warm data: queried occasionally. Tasks deleted in the last 30 days, audit logs from the last quarter. It's in the desk drawer, accessible in a minute.
• Cold data: rarely queried. Tasks deleted 2 years ago, audit logs from 2020. It's in the basement, accessible in hours if someone asks for it.

Soft delete puts all the data on the same desk. It works if your desk is big (a small table). It fails once you have 100M papers and nobody fits.

Archive tables move the warm data to the drawer. The operational queries (the hot stuff) are fast because the main table is small. The audit queries (the warm stuff) have a bit of overhead from doing a JOIN or UNION with the archive table, but they're rare.

Partitioning by state splits the same table into separate physical partitions. PostgreSQL handles the routing automatically. The queries that only touch active rows go to the active partition; the ones that see deleted rows touch both. The split is transparent.

Both are a natural evolution of the pattern. Soft delete was the first step; these are the next ones once the first step no longer holds up.

When do you cross the threshold?

There's no magic number. There are operational signals that tell you "plain soft delete no longer holds up":

Signal 1: autovacuum takes longer than the maintenance window

SELECT
  schemaname || '.' || relname AS table,
  last_autovacuum,
  EXTRACT(EPOCH FROM (NOW() - last_autovacuum)) / 60 AS minutes_since,
  n_dead_tup,
  n_live_tup,
  ROUND(100.0 * n_dead_tup / NULLIF(n_dead_tup + n_live_tup, 0), 1) AS dead_pct
FROM pg_stat_user_tables
WHERE relname = 'tasks';

If dead_pct > 30% consistently and last_autovacuum shows the last pass was hours ago (not minutes), the pattern is suffering. Autovacuum isn't managing to keep it clean.

Signal 2: table > 100M rows with > 70% deleted

SELECT
  COUNT(*) AS total,
  COUNT(*) FILTER (WHERE deleted_at IS NOT NULL) AS deleted,
  ROUND(100.0 * COUNT(*) FILTER (WHERE deleted_at IS NOT NULL) / COUNT(*), 1) AS deleted_pct,
  pg_size_pretty(pg_relation_size('tasks')) AS table_size,
  pg_size_pretty(pg_indexes_size('tasks')) AS indexes_size
FROM tasks;

If the operational table weighs tens of GB and only 20-30% is "live," the bloat is structural. Cleaning it with VACUUM FULL requires downtime.

Signal 3: operational queries have unstable latency

The p95 of queries that use the partial index fluctuates between 5ms and 200ms with no apparent reason. It indicates that the partial index's cache hit ratio isn't stable because the index itself is large compared to shared_buffers.

Signal 4: migrations take hours

Simple ALTER TABLEs (even "fast" ones like adding a nullable column) take minutes or more, not milliseconds. It indicates the table is so big that any schema operation touches too many pages.

Threshold summary

These numbers are indicative. What's critical is the combination: a large table + a high delete ratio + a saturated autovacuum = consider the alternatives.

Option 1: archive tables

Concept

You have two tables: tasks (operational, active rows only) and archive.tasks (history, deleted rows only). When a task gets deleted, the app moves it from one to the other (in the same transaction). Operational queries touch only tasks. Audit queries touch archive.tasks or both with a UNION.

Schema

-- Operational table: active rows only. No deleted_at.
CREATE TABLE tasks (
  id BIGSERIAL PRIMARY KEY,
  author_id BIGINT NOT NULL,
  title TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_tasks_author_created ON tasks (author_id, created_at DESC);

-- A separate schema for archives
CREATE SCHEMA archive;

-- Archive table: same structure + when and why it was deleted
CREATE TABLE archive.tasks (
  id BIGINT PRIMARY KEY,
  author_id BIGINT NOT NULL,
  title TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL,
  archived_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  archived_reason TEXT  -- optional: reason for the deletion
);

CREATE INDEX idx_archive_tasks_archived ON archive.tasks (archived_at DESC);
CREATE INDEX idx_archive_tasks_author ON archive.tasks (author_id);

The delete operation

Replace UPDATE SET deleted_at = NOW() with:

async def archive_task(session: AsyncSession, task_id: int, reason: str | None = None) -> None:
    """
    Moves a task from operational to archive.
    An atomic operation: INSERT into archive + DELETE from operational, in one transaction.
    """
    # Load the task
    task = await session.get(Task, task_id)
    if task is None:
        raise ValueError(f"Task {task_id} not found")

    # Insert into archive
    archive_task = ArchiveTask(
        id=task.id,
        author_id=task.author_id,
        title=task.title,
        created_at=task.created_at,
        archived_reason=reason,
    )
    session.add(archive_task)

    # Remove from operational
    await session.delete(task)

    # Atomic commit
    await session.commit()

Notes:

• The operation is atomic: the transaction guarantees the row appears in archive BEFORE disappearing from operational, or nothing happens.
• The archive table's id is the same one it had in operational (it preserves references).
• Foreign keys from other tables that pointed at tasks.id now point at a nonexistent row (unless you change their FK policy). You have to think the cascade through explicitly.

Operational queries

No change from a table with no soft delete:

result = await session.execute(
    select(Task).where(Task.author_id == 42).order_by(Task.created_at.desc()).limit(50)
)
# The table only has active rows. No soft delete filters. No listener.

Queries that need to see deleted rows

A UNION with the archive table:

from sqlalchemy import union_all, literal_column, select

async def list_all_tasks_history(
    session: AsyncSession, author_id: int
) -> list[dict]:
    """Lists active + archived rows for the author."""
    active_query = select(
        Task.id,
        Task.title,
        Task.created_at,
        literal_column("'active'").label("status"),
    ).where(Task.author_id == author_id)

    archive_query = select(
        ArchiveTask.id,
        ArchiveTask.title,
        ArchiveTask.created_at,
        literal_column("'archived'").label("status"),
    ).where(ArchiveTask.author_id == author_id)

    combined = union_all(active_query, archive_query)
    result = await session.execute(combined.order_by("created_at"))
    return [dict(r._mapping) for r in result]

The maintenance job

In the "soft delete + archive" model, the app can do the operation on-demand (at delete time) or in batch (a nightly job that moves soft-deleted rows past a certain age):

async def archive_old_soft_deleted(session: AsyncSession, days: int = 30) -> int:
    """
    A job that runs every night.
    Moves tasks with a soft delete older than N days to archive.
    Assumes the operational table uses temporary soft delete before archiving.
    """
    cutoff = datetime.now(timezone.utc) - timedelta(days=days)

    # Load the candidates
    result = await session.execute(
        select(Task)
        .where(Task.deleted_at.is_not(None), Task.deleted_at < cutoff)
        .execution_options(include_deleted=True)
        .limit(10000)  # batch so as not to take a long-lived lock
    )
    candidates = result.scalars().all()

    moved = 0
    for task in candidates:
        archive = ArchiveTask(
            id=task.id,
            author_id=task.author_id,
            title=task.title,
            created_at=task.created_at,
            archived_reason="auto-archived after grace period",
        )
        session.add(archive)
        await session.delete(task)
        moved += 1

    await session.commit()
    return moved

Advantages of archive tables

• A small operational table: queries and autovacuum fly.
• Faster backups: the operational dump is small; archives get backed up less frequently.
• A clear separation: the archive is read-mostly, which allows different optimizations (more indexes, compression, a separate tablespace on slower disk).
• Compliance friendly: the archive table can have a different retention policy (example: hard-delete from archive after 7 years) without touching the operational one.

Disadvantages of archive tables

• More operational complexity: two tables to maintain, two schemas to migrate.
• History queries require a UNION: slower than a simple SELECT, and they require indexes on both.
• Foreign keys are problematic: tables that pointed at tasks.id can't point at archive.tasks.id with a standard FK (PostgreSQL doesn't support polymorphic FKs). You have to decide: drop the FK, handle it at the application level, or duplicate the row in both tables.
• The archive job can fail and leave inconsistency: if the batch gets interrupted, some rows may be in both tables or in neither. Idempotence is critical.

Option 2: partitioning by state

Concept

PostgreSQL 11+ supports declarative partitioning. One logical "table" is really N physical tables (partitions), and PostgreSQL routes reads and writes to the right partition based on a column.

For soft delete, you partition by a derived boolean column (or by the deleted_at range). Operational queries touch only the active partition; history queries touch both (or only the deleted one).

Schema (PARTITION BY LIST on a derived column)

-- Partitioned table (it's metadata; it doesn't store data directly)
CREATE TABLE tasks (
  id BIGSERIAL,
  author_id BIGINT NOT NULL,
  title TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at TIMESTAMPTZ NULL,
  is_active BOOLEAN GENERATED ALWAYS AS (deleted_at IS NULL) STORED
) PARTITION BY LIST (is_active);

-- Physical partitions
CREATE TABLE tasks_active PARTITION OF tasks FOR VALUES IN (true);
CREATE TABLE tasks_archived PARTITION OF tasks FOR VALUES IN (false);

-- Per-partition indexes
CREATE INDEX idx_tasks_active_author_created
  ON tasks_active (author_id, created_at DESC);
CREATE INDEX idx_tasks_archived_archived_at
  ON tasks_archived (deleted_at DESC);

-- The composite primary key includes the partition column (a PG limitation)
ALTER TABLE tasks ADD PRIMARY KEY (id, is_active);

Notes:

• is_active is a generated always column: PostgreSQL computes it automatically on insert/update. Don't set it manually.
• The primary key has to include the partition column. This is a PostgreSQL restriction: the PK has to contain every column that defines the partition.
• Each partition has its own indexes, optimized for its access patterns.

The delete operation

UPDATE tasks SET deleted_at = NOW() WHERE id = 1234;

What happens internally:

1. PostgreSQL detects that deleted_at changed.
2. is_active is recomputed (from true to false).
3. The row is physically moved from tasks_active to tasks_archived. This is relatively expensive: a DELETE in one partition + an INSERT in the other, within the same operation.
4. The indexes of both partitions get updated.

Cost of the soft delete UPDATE: higher than with a pure partial index (because it moves the row physically). Mitigated by the fact that the operational queries get much faster.

Operational queries

-- Implicitly filters by the active partition
SELECT * FROM tasks WHERE author_id = 42 AND deleted_at IS NULL ORDER BY created_at DESC LIMIT 50;

-- PostgreSQL does "partition pruning": it detects that deleted_at IS NULL implies is_active = true,
-- and only scans tasks_active.

EXPLAIN ANALYZE shows the partition pruning explicitly:

Limit  (cost=0.42..68.42 rows=50 width=20)
  ->  Index Scan using idx_tasks_active_author_created on tasks_active tasks
        Index Cond: (author_id = 42)

Note tasks_active tasks — the specific partition it scanned. The other one (tasks_archived) wasn't touched.

Queries that see deleted rows

SELECT * FROM tasks WHERE author_id = 42;
-- No deleted_at filter: PostgreSQL scans BOTH partitions.

That double scan is transparent but slower. For audit queries it may be acceptable.

Advantages of partitioning

• Transparency for the app: the queries are SELECT * FROM tasks like always. PostgreSQL does the routing automatically. There are no UNIONs or different names.
• Foreign keys keep working: other tables point at tasks.id just like before. PostgreSQL handles the integrity across the partitions.
• Excellent operational performance: queries that only touch active rows are as fast as on a small table.
• Independent maintenance per partition: you can run VACUUM FULL on tasks_archived only (during off-hours) without touching tasks_active.
• The possibility of detaching the archive: ALTER TABLE tasks DETACH PARTITION tasks_archived; disconnects the deleted-rows partition, which lets you move it to another tablespace, export it, or drop it without affecting the operation.

Disadvantages of partitioning

• The soft delete UPDATE is more expensive: moving the row between partitions costs more than just updating the column in a normal table with a partial index.
• The PRIMARY KEY has to include the partition column: a PostgreSQL restriction. Foreign keys from other tables are to (id, is_active), not just id. That breaks existing queries that assumed id was a simple PK.
• Partitioning limitations in PG: some operations (global UNIQUE constraints, EXCLUDE constraints) aren't supported across partitions.
• Schema complexity: more concepts to understand (PARTITION BY, partitions, partition pruning).
• The initial migration is non-trivial: converting an existing table into a partitioned one requires recreating the table, copying data, and doing a cutover. It's an hours-long operation in production.

A deeper treatment of declarative partitioning (PARTITION BY RANGE for dates, sub-partitions, maintenance with pg_partman) is in guide #14 (Advanced PostgreSQL for Backend), in the corresponding module. Here we present it as an alternative to soft delete; the deep how-to goes there.

Decision matrix: plain soft delete vs archive vs partitioning

Practical rule:

• Start with soft delete + a partial index. It covers 90% of cases.
• Migrate to an archive table when the operational table becomes an operational problem but the history queries are rare.
• Consider partitioning when an archive table isn't viable (frequent history queries, complex FKs, very heterogeneous queries).

Worked case: when to pick each one

Case A: a tasks app, 5M rows, 30% deleted

State: soft delete with a partial index is enough. Maintenance with normal autovacuum. No sign of degradation.

Recommendation: stay with soft delete. No change.

Case B: a chat app, 80M messages, 60% deleted (users delete old chats)

State: the table weighs ~50 GB. Autovacuum takes 20-30 min. Operational queries (recent chats) are OK with the partial index. Audit queries (the history view) are slow.

Recommendation: an archive table. Move messages deleted >30 days ago to archive.messages. The operational table drops to ~20 GB, autovacuum to <5 min. The history view requires a UNION but that's acceptable because it's a low-usage admin endpoint.

Case C: an IoT events app, 500M rows/month, 95% get "deleted" after being processed

State: a 6 TB table. Partitioning by month is obvious (the classic PARTITION BY RANGE case). But also: within each month, separate "raw events" from "processed events."

Recommendation: composite partitioning. Partition by month (PARTITION BY RANGE on created_at) AND by state (sub-PARTITION BY LIST on is_processed). Each month-and-state is an independent physical partition. Removing an old month is a DROP TABLE of the partition (instantaneous). Removing the current month's processed events is DROP PARTITION processed_2026_05. This is what guide #14 goes deeper into.

Case D: a B2B app with compliance, 50M tasks, mandatory 7-year retention

State: soft delete accumulates data for compliance. The operational table suffers from the size.

Recommendation: an archive table with an explicit retention policy. The operational one is read-write, the archive is read-only for compliance. The archive table can live on cheap tablespace (HDD) and have a monthly pg_dump to S3 Glacier. The operational one lives on fast tablespace (NVMe). Cost separation.

Why does this capsule matter in real work?

1. It's the capsule that separates the senior from the staff engineer. Deciding between plain soft delete, archive, and partitioning is an architectural decision with multi-year impact. Getting it right requires seeing the operation 3 years out (expected size, churn ratio, query patterns), not just the current moment.

2. It's the capsule that gets asked in senior interviews. "Your users table has 200M rows with soft delete and autovacuum is drowning — what do you do?" — the expected answer is "it depends, I'd ask X and Y, I'd consider archive vs partitioning with these trade-offs."

3. The migration is expensive, and doing it badly is far more expensive. Migrating from soft delete to a badly designed archive table (FKs not thought through, broken job idempotence) can cause data loss. Doing the migration with confidence requires having thought it through completely beforehand.

4. PostgreSQL's partitioning feature is underused. Few developers outside the DBA role know about it. Knowing when to apply it gives you levers the team doesn't have.

Traps and common mistakes

Mistake 1 (conceptual): jumping to partitioning without having tried archive

Symptom: the team reads about partitioning, finds it elegant, decides to migrate straight to it.

Why it's wrong: partitioning has complexity (a composite PK, required FKs, a costly initial migration) that an archive table avoids. For many apps, an archive table covers the case with less complexity.

How to tell: are the history queries frequent (>20% of traffic)? If so, partitioning offers transparency. If not, an archive table is simpler.

How to fix it: try an archive table first. If after 6 months you find the UNION queries are a problem, migrate to partitioning.

Mistake 2 (practical): an archive job with no idempotence

Symptom: the job that moves soft-deleted rows to archive fails halfway. On retry, some rows show up duplicated in archive (or fail with a PK violation).

Why it happens: the job did the INSERT into archive and crashed before the DELETE from operational. On retry, the INSERT fails because the row is already in archive.

How to tell: monitor the job. If you see PK violation or "duplicate key" errors, this is it.

How to fix it: the operation has to be idempotent:

async def archive_task_idempotent(session: AsyncSession, task_id: int) -> None:
    # 1. INSERT with ON CONFLICT DO NOTHING
    await session.execute(
        text("""
            INSERT INTO archive.tasks (id, author_id, title, created_at)
            SELECT id, author_id, title, created_at
            FROM tasks WHERE id = :task_id AND deleted_at IS NOT NULL
            ON CONFLICT (id) DO NOTHING
        """),
        {"task_id": task_id},
    )

    # 2. DELETE from operational (only if it's in archive)
    await session.execute(
        text("DELETE FROM tasks WHERE id = :task_id"),
        {"task_id": task_id},
    )

    await session.commit()

Mistake 3 (conceptual): assuming partition pruning always works

Symptom: your table is partitioned, but EXPLAIN shows it scanning both partitions for a query you expected would filter down to one.

Why it happens: PostgreSQL only does partition pruning if it can prove (at planning time) which partitions to touch. If the query uses prepared parameters with an unknown value at planning time, or complex expressions, it may not prune.

How to tell: EXPLAIN ANALYZE shows an Append with every partition. If you expected only one to appear, there was no pruning.

How to fix it: make sure the filter is simple and direct (WHERE deleted_at IS NULL, not WHERE deleted_at IS NULL OR ...). PostgreSQL >= 13 improved pruning for prepared statements, but edge cases still exist.

Mistake 4 (practical): broken foreign keys after migrating to archive

Symptom: after moving a task to archive, queries that JOIN with comments (which pointed at tasks.id) fail or return NULL.

Why it happens: comments.task_id REFERENCES tasks(id) ends up pointing at a nonexistent row. If the FK was RESTRICT, the task's DELETE fails. If it was SET NULL, the comments are left orphaned.

How to tell: check what the FK does when the task is deleted. If the app uses archive, you have to decide explicitly.

How to fix it (options):

• Move the comments to archive too (cascade in archive). More coherence, more complexity.
• Break the FK and handle the integrity in the application. It lets you move only tasks. You lose the DB's guarantee.
• Soft delete on comments too, archive only tasks. An acceptable hybrid.

Mistake 5 (conceptual): partitioning as a solution to problems that aren't about scale

Symptom: the team partitions a 1M-row table "to anticipate future growth."

Why it's wrong: partitioning adds complexity (a composite PK, FKs with the same problem, partition maintenance). It's only worth it when you already have the problems it solves. Partitioning just in case is premature optimization.

How to tell: does the current table have the symptoms (slow autovacuum, unstable queries, painful migrations)? If not, don't partition it.

How to fix it: soft delete + a partial index + monitoring. When you detect the symptoms, consider the migration. Not before.

Exercises

Exercise 1: diagnose whether your table needs to migrate

For a tasks table with soft delete that you have (real or from the module's examples), run the diagnostic queries. Decide: is plain soft delete still appropriate, or did you cross the threshold into archive/partitioning?

-- Size and delete ratio
SELECT
  pg_size_pretty(pg_relation_size('tasks')) AS table_size,
  pg_size_pretty(pg_indexes_size('tasks')) AS indexes_size,
  COUNT(*) AS total_rows,
  COUNT(*) FILTER (WHERE deleted_at IS NOT NULL) AS deleted_rows,
  ROUND(100.0 * COUNT(*) FILTER (WHERE deleted_at IS NOT NULL) / COUNT(*), 1) AS deleted_pct
FROM tasks;

-- Autovacuum state
SELECT
  last_autovacuum,
  EXTRACT(EPOCH FROM (NOW() - last_autovacuum)) / 60 AS minutes_since,
  n_dead_tup,
  n_live_tup,
  ROUND(100.0 * n_dead_tup / NULLIF(n_dead_tup + n_live_tup, 0), 1) AS dead_pct
FROM pg_stat_user_tables
WHERE relname = 'tasks';

-- Latency of a typical query
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title FROM tasks
WHERE author_id = 42 AND deleted_at IS NULL
ORDER BY created_at DESC LIMIT 50;

If:
  table_size < 10 GB
  AND deleted_pct < 50%
  AND dead_pct < 20%
  AND query latency < 10 ms (p95)
→ Soft delete is still OK. Don't migrate.

If:
  table_size 10-100 GB
  OR deleted_pct 50-80%
  OR autovacuum minutes_since > 60
  OR query latency 50-200 ms
→ Consider an archive table.

If:
  table_size > 100 GB
  OR deleted_pct > 80%
  OR autovacuum never finishes
  OR ALTER TABLE takes hours
→ Consider partitioning.

Exercise 2: implement an archive table in the small

Create the two tables (tasks operational + archive.tasks), implement the archive_task_idempotent function, and verify with a test that:

a) A task gets moved correctly. b) Calling archive again is idempotent (it doesn't duplicate or fail). c) An operational query (SELECT * FROM tasks) doesn't show archived rows. d) A query with a UNION shows both.

CREATE TABLE tasks (
  id BIGSERIAL PRIMARY KEY,
  author_id BIGINT NOT NULL,
  title TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at TIMESTAMPTZ NULL  -- used only during the grace period before archiving
);

CREATE SCHEMA archive;
CREATE TABLE archive.tasks (
  id BIGINT PRIMARY KEY,
  author_id BIGINT NOT NULL,
  title TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL,
  archived_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncSession


async def archive_task_idempotent(session: AsyncSession, task_id: int) -> bool:
    """
    Moves a task to archive idempotently.
    Returns True if it moved it, False if it was already archived or didn't exist.
    """
    # 1. INSERT with ON CONFLICT DO NOTHING
    insert_result = await session.execute(
        text("""
            INSERT INTO archive.tasks (id, author_id, title, created_at)
            SELECT id, author_id, title, created_at
            FROM tasks WHERE id = :task_id
            ON CONFLICT (id) DO NOTHING
            RETURNING id
        """),
        {"task_id": task_id},
    )
    inserted = insert_result.scalar_one_or_none()

    if inserted is None:
        # Nothing was inserted: either it doesn't exist in operational, or it's already in archive.
        # Check whether it's in archive (the job re-run case)
        check = await session.execute(
            text("SELECT 1 FROM archive.tasks WHERE id = :task_id"),
            {"task_id": task_id},
        )
        if check.scalar_one_or_none() is None:
            await session.commit()
            return False  # It doesn't exist anywhere

    # 2. DELETE from operational
    await session.execute(
        text("DELETE FROM tasks WHERE id = :task_id"),
        {"task_id": task_id},
    )
    await session.commit()
    return True

import pytest

pytestmark = pytest.mark.asyncio


async def test_archive_mueve_correctamente(session):
    await session.execute(text(
        "INSERT INTO tasks (id, author_id, title) VALUES (100, 1, 'T1')"
    ))
    await session.commit()

    moved = await archive_task_idempotent(session, 100)
    assert moved is True

    op_count = await session.scalar(
        text("SELECT COUNT(*) FROM tasks WHERE id = 100")
    )
    arch_count = await session.scalar(
        text("SELECT COUNT(*) FROM archive.tasks WHERE id = 100")
    )
    assert op_count == 0
    assert arch_count == 1


async def test_archive_es_idempotente(session):
    await session.execute(text(
        "INSERT INTO tasks (id, author_id, title) VALUES (200, 1, 'T2')"
    ))
    await session.commit()

    # First call: it moves
    assert await archive_task_idempotent(session, 200) is True

    # Second call: it doesn't fail, it doesn't duplicate
    assert await archive_task_idempotent(session, 200) is False

    arch_count = await session.scalar(
        text("SELECT COUNT(*) FROM archive.tasks WHERE id = 200")
    )
    assert arch_count == 1  # NOT 2


async def test_query_operacional_no_ve_archived(session):
    await session.execute(text(
        "INSERT INTO tasks (id, author_id, title) VALUES (300, 1, 'Active'), (301, 1, 'ToArchive')"
    ))
    await session.commit()

    await archive_task_idempotent(session, 301)

    result = await session.execute(text("SELECT id, title FROM tasks WHERE author_id = 1"))
    rows = result.all()
    assert len(rows) == 1
    assert rows[0].id == 300


async def test_union_ve_ambas(session):
    await session.execute(text(
        "INSERT INTO tasks (id, author_id, title) VALUES (400, 1, 'Active'), (401, 1, 'ToArchive')"
    ))
    await session.commit()

    await archive_task_idempotent(session, 401)

    result = await session.execute(text("""
        SELECT id, title, 'active' AS status FROM tasks WHERE author_id = 1
        UNION ALL
        SELECT id, title, 'archived' AS status FROM archive.tasks WHERE author_id = 1
        ORDER BY id
    """))
    rows = result.all()
    assert len(rows) == 2
    assert rows[0].id == 400 and rows[0].status == "active"
    assert rows[1].id == 401 and rows[1].status == "archived"

Exercise 3: compare the cost of the soft delete UPDATE vs archive vs partitioning

For a delete operation over 1000 rows, measure:

a) The time of UPDATE SET deleted_at = NOW() on a table with a partial index. b) The time of archive_task (INSERT into archive + DELETE from operational). c) The time of UPDATE SET deleted_at = NOW() on a table partitioned by state.

-- Soft delete table
CREATE TABLE soft_tasks (id BIGSERIAL PRIMARY KEY, title TEXT, deleted_at TIMESTAMPTZ);
CREATE INDEX idx_soft_active ON soft_tasks (id) WHERE deleted_at IS NULL;
INSERT INTO soft_tasks (title) SELECT 'task_' || g FROM generate_series(1, 100000) g;

-- Archive table (same schema + a separate archive)
CREATE TABLE arch_tasks (id BIGSERIAL PRIMARY KEY, title TEXT);
CREATE TABLE archive_arch_tasks (id BIGINT PRIMARY KEY, title TEXT, archived_at TIMESTAMPTZ DEFAULT NOW());
INSERT INTO arch_tasks (title) SELECT 'task_' || g FROM generate_series(1, 100000) g;

-- Partitioned table
CREATE TABLE part_tasks (
  id BIGSERIAL,
  title TEXT,
  deleted_at TIMESTAMPTZ NULL,
  is_active BOOLEAN GENERATED ALWAYS AS (deleted_at IS NULL) STORED,
  PRIMARY KEY (id, is_active)
) PARTITION BY LIST (is_active);
CREATE TABLE part_tasks_active PARTITION OF part_tasks FOR VALUES IN (true);
CREATE TABLE part_tasks_archived PARTITION OF part_tasks FOR VALUES IN (false);
INSERT INTO part_tasks (title) SELECT 'task_' || g FROM generate_series(1, 100000) g;

\timing

-- (a) Plain soft delete
UPDATE soft_tasks SET deleted_at = NOW() WHERE id BETWEEN 1 AND 1000;
-- Time: ~15-30 ms

-- (b) Archive (in one transaction)
BEGIN;
INSERT INTO archive_arch_tasks (id, title)
SELECT id, title FROM arch_tasks WHERE id BETWEEN 1 AND 1000;
DELETE FROM arch_tasks WHERE id BETWEEN 1 AND 1000;
COMMIT;
-- Time: ~50-80 ms (more expensive: two operations)

-- (c) Partitioning (UPDATE = move row between partitions)
UPDATE part_tasks SET deleted_at = NOW() WHERE id BETWEEN 1 AND 1000;
-- Time: ~80-150 ms (physically moving rows between partitions is more costly)

Exercise 4: design the migration from soft delete to an archive table with no downtime

You have a tasks table in production with 50M rows, 70% deleted, struggling. Design the steps of the migration to an archive table with no downtime. (No implementation; just the plan.)

Summary and next step

In this capsule you learned:

• Soft delete + a partial index scales up to a point. At larger scales (>100M rows, >70% deleted), operational problems appear: a saturated autovacuum, accumulated bloat, slow migrations.
• Archive tables move the deleted records to a separate table with a periodic job. A small operational table, fast queries, history queries with a UNION.
• Partitioning by state (PARTITION BY LIST on a derived is_active) splits the table into physical partitions. Transparent for the app, independent maintenance, FKs keep working with the composite PK caveat.
• A decision matrix based on size, delete ratio, and query patterns: soft delete <10GB, archive 10-100GB, partitioning >100GB.
• The migration isn't trivial: archive requires an idempotent job, partitioning requires recreating the table. Both are weeks-long operations in production.
• The pattern: start with soft delete + a partial index. Migrate when the operational symptoms appear, not before.

Before moving on you should be able to:

• Identify the operational signals that indicate plain soft delete no longer holds up.
• Design the archive table schema with an idempotent job for a specific table.
• Understand the trade-offs between archive and partitioning with concrete criteria.
• Estimate the cost and risk of migrating from soft delete to the right alternative.

Next capsule — Project: soft delete in TaskFlow. You're going to apply the whole module in a measurable project: refactor a tasks table with 1M rows, 60% deleted, measuring the before and after. You'll implement the complete pattern (mixin + listener + partial index) and produce a BENCHMARKS.md with concrete numbers. It's the exercise that closes the module and sets the stage for the capstone project of module 8.

Resources

1. PostgreSQL Documentation — Table Partitioning — the official docs. Required reading if you're going to implement partitioning.
2. Brandur Leach — "Soft deletion probably isn't worth it" — a real case from Stripe about migrating from soft delete to an archive table.
3. GitLab Engineering — "Database partitioning" — an operational guide to how GitLab partitions large tables in production.
4. Crunchy Data — "PostgreSQL partitioning best practices" — an analysis of when and how to partition.
5. pg_partman documentation — an extension that automates partition maintenance (creation, dropping old ones).
6. pg_repack documentation — for VACUUM FULL with no downtime.
7. PostgreSQL Documentation — INSERT ... ON CONFLICT — for idempotence in archive jobs.
8. Heroku Engineering — "How to migrate to partitioned tables" — a real migration case with minimal downtime.

Module 2 — SQL Patterns for Production APIs Guide

Next capsule: Module project — soft delete in TaskFlow.