Retention and partitioning of audit logs

Capsule overview

The five previous capsules taught you to generate the audit log: what to audit (capsule 02), three approaches in SQL (capsules 03-05), and one in Python (capsule 06). They all assume an implicit detail that becomes painful in production: the audit log grows forever. A table with 10k changes a day generates 3.6M rows a year. In five years, 18M. If you audit several tables (tasks, comments, contracts, users), multiply by N. Eventually the audit log weighs more than the audited tables combined, the INSERTs feel the weight of the indexes, and queries that used to be fast start scanning enormous tables.

This capsule teaches you to handle that problem from day one, not as a reactive mitigation. You're going to learn to partition audit.task_log by month with PostgreSQL 16+'s declarative partitioning — a single DDL turns the table into a collection of partitions by date range. You're going to see how that allows operations that would otherwise be impossible: deleting an entire month with DROP PARTITION (instantaneous) instead of DELETE WHERE changed_at < ... (hours of I/O), or moving old partitions to S3 without touching the active ones. You're going to design retention policies that satisfy compliance (7 years under SOX, 6 under HIPAA, whatever applies) without killing the DB's performance. And you're going to learn the bridge to guide #14 — declarative partitioning is an advanced PostgreSQL feature that that guide goes deeper into; here you apply it to the audit log's specific case.

By the end you'll have the complete pattern: the partitioned schema, an automated function that creates future partitions, an archive job that moves old partitions to S3 (parquet), and optimized queries that take advantage of partition pruning. It's the capsule that prepares you to take an audit log to production long term.

The problem: the table that grows without limit

Let's start by seeing how the problem manifests in concrete figures.

Imagine TaskFlow in production after three years:

• 50,000 active tasks (the source table).
• 5,000 changes a day on average (a mix of UPDATE + INSERT + DELETE).
• 3 years × 365 days × 5,000 = 5.5M rows in audit.task_log.
• Average size per row: ~500 bytes (including the diff's JSONB).
• Audit log storage: ~2.7GB.

Is that a problem? By itself, no. But combine it with the indexes capsule 02 proposed:

• idx_audit_task_log_entity_time: ~280MB.
• idx_audit_task_log_changed_by_time: ~120MB.
• idx_audit_task_log_diff_gin: ~1.2GB (GIN over JSONB is heavy).

Total with indexes: ~4.3GB. The source table tasks measures ~30MB. The audit log is 140x bigger.

The operational consequences:

• Slow INSERTs: each INSERT into audit.task_log updates three indexes, one of them a GIN (expensive). You went from 0.5ms to 4ms per INSERT into the log.
• Slow audit queries: "what changes did this task have in the last 30 days?" does an index lookup but the data is scattered across an enormous table. It isn't a scan, but it isn't optimal.
• VACUUM and ANALYZE more expensive: autovacuum's maintenance takes longer, the stats get updated less often.
• Backups grow: every pg_dump has to process GBs of audit log.
• Impossible to delete old rows efficiently: DELETE FROM audit.task_log WHERE changed_at < '2023-01-01' is an hours-long operation with massive I/O and bloat.

Multiply by 5-10 years and the problem becomes critical.

Mental model: physical files instead of a single table

Think about a company that stores paper invoices. There are two ways to organize the physical archive:

Way A (no partitioning): a single giant box. All the invoices, in order of arrival. To find the invoice from March 15, 2025, you have to search through all of them. To throw out the 2018 invoices (legally no longer required to keep), you have to find them one by one and throw them out. The box grows without stopping.

Way B (with partitioning): one box per month. January 2024 in its box, February 2024 in another, etc. To find the one from March 15, 2025, you go straight to the "March 2025" box. To throw out 2018, you simply discard the boxes labeled "2018-XX" — a trivial physical operation, no need to open each box.

PostgreSQL declarative partitioning is way B. What conceptually is "one audit.task_log table" is physically N tables (one per month), where PostgreSQL's engine automatically decides which table each row goes to based on changed_at. Queries that filter by changed_at only access the relevant partitions (that's called partition pruning). Deleting a partition is a DROP TABLE — instantaneous, with no row-by-row I/O.

Implementing the partitioning

PostgreSQL 16+ supports declarative partitioning natively (since 10.x, improved in every version). Let's apply date-range (monthly) partitioning to audit.task_log.

The partitioned schema

-- The partitioned version of audit.task_log
DROP TABLE IF EXISTS audit.task_log CASCADE;

CREATE TABLE audit.task_log (
    id BIGSERIAL,
    entity_id BIGINT NOT NULL,
    action TEXT NOT NULL CHECK (action IN ('INSERT', 'UPDATE', 'DELETE')),
    changed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    changed_by BIGINT NULL,
    diff JSONB NOT NULL,
    request_id UUID NULL,
    source TEXT NULL,
    -- Important: the primary key has to include the partitioning column
    PRIMARY KEY (id, changed_at)
) PARTITION BY RANGE (changed_at);

-- Create partitions for specific months
CREATE TABLE audit.task_log_y2026m01
    PARTITION OF audit.task_log
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE audit.task_log_y2026m02
    PARTITION OF audit.task_log
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

-- ... more partitions

-- The default partition: catches any row with a date outside the defined ranges.
-- Useful as a safety net but it must NOT be used in normal operation — you have to create
-- specific partitions before the rows arrive.
CREATE TABLE audit.task_log_default
    PARTITION OF audit.task_log
    DEFAULT;

-- Indexes: created on the parent table, applied to every partition
CREATE INDEX idx_audit_task_log_entity_time
    ON audit.task_log (entity_id, changed_at DESC);

CREATE INDEX idx_audit_task_log_changed_by_time
    ON audit.task_log (changed_by, changed_at DESC)
    WHERE changed_by IS NOT NULL;

-- The GIN index also applies to each partition
CREATE INDEX idx_audit_task_log_diff_gin
    ON audit.task_log USING GIN (diff jsonb_path_ops);

The schema's decisions explained:

• PARTITION BY RANGE (changed_at): PostgreSQL offers RANGE, LIST, and HASH partitioning. For audit logs, RANGE by timestamp is the natural choice — each partition covers a date range, and queries typically filter by date.
• PRIMARY KEY (id, changed_at): partitions require the primary key to include the partitioning column. You can't have PRIMARY KEY (id) alone. That's an engine restriction.
• Monthly partitions: the typical choice for audit logs. More granular (week, day) generates too many partitions (overhead). Less granular (quarter, year) limits the usefulness for retention.
• The default partition: catches rows with unexpected dates. It must not be used in normal operation. If it fills up, it means you forgot to create future partitions — a pending maintenance operation.

Verification: the inserts go to the right partition

-- Insert rows with different dates
INSERT INTO audit.task_log (entity_id, action, changed_at, diff)
VALUES
    (1, 'INSERT', '2026-01-15 10:00:00', '{"title": [null, "X"]}'),
    (1, 'UPDATE', '2026-02-10 14:00:00', '{"title": ["X", "Y"]}'),
    (1, 'UPDATE', '2026-02-25 09:00:00', '{"status": ["open", "closed"]}');

-- Check where each row physically went
SELECT
    tableoid::regclass AS partition,
    COUNT(*) AS rows
FROM audit.task_log
WHERE entity_id = 1
GROUP BY tableoid::regclass;

            partition            | rows
---------------------------------+------
 audit.task_log_y2026m01         |    1
 audit.task_log_y2026m02         |    2

PostgreSQL routed each row to the right partition automatically, based on changed_at. The application inserts into audit.task_log (the "virtual table") without knowing about the partitions.

Partition pruning in queries

-- A query that filters by date
EXPLAIN ANALYZE
SELECT * FROM audit.task_log
WHERE entity_id = 1 AND changed_at >= '2026-02-01' AND changed_at < '2026-03-01';

QUERY PLAN
-----------------------------------------------------------------------
 Append  (cost=0.00..15.10 rows=2 width=136)
   ->  Seq Scan on audit_task_log_y2026m02  (cost=0.00..15.00 rows=2 width=136)
         Filter: ((entity_id = 1) AND (changed_at >= ...))

It only accessed the y2026m02 partition. January's partition wasn't even touched. That's partition pruning — the foundation of partitioning's value.

Automation: creating future partitions

Without automation, somebody has to create audit.task_log_y2026m05, _y2026m06, etc. If you forget, May's inserts go to the default partition (not what you want). Let's automate it.

A PL/pgSQL function that creates partitions

CREATE OR REPLACE FUNCTION audit.create_partition_for_month(
    p_year INTEGER,
    p_month INTEGER
)
RETURNS TEXT AS $$
DECLARE
    v_partition_name TEXT;
    v_start_date DATE;
    v_end_date DATE;
    v_sql TEXT;
BEGIN
    v_partition_name := format('audit.task_log_y%sm%s',
        p_year::TEXT,
        lpad(p_month::TEXT, 2, '0')
    );

    v_start_date := make_date(p_year, p_month, 1);
    v_end_date := v_start_date + INTERVAL '1 month';

    -- Create the partition if it doesn't exist
    v_sql := format(
        'CREATE TABLE IF NOT EXISTS %s PARTITION OF audit.task_log FOR VALUES FROM (%L) TO (%L)',
        v_partition_name, v_start_date, v_end_date
    );
    EXECUTE v_sql;

    RETURN v_partition_name;
END;
$$ LANGUAGE plpgsql;

A job that creates the upcoming partitions

# jobs/maintain_audit_partitions.py
"""
Monthly job: create partitions for the next 3 months.
Idempotent: if the partition already exists, it doesn't fail.
"""
import asyncio
from datetime import date

from sqlalchemy import text

from app.db import AsyncSessionLocal


PARTITIONS_AHEAD = 3  # create the next 3 months


async def main():
    today = date.today()

    async with AsyncSessionLocal() as session:
        for offset in range(PARTITIONS_AHEAD + 1):  # the current month + the next 3
            target_year = today.year
            target_month = today.month + offset
            while target_month > 12:
                target_month -= 12
                target_year += 1

            result = await session.execute(text("""
                SELECT audit.create_partition_for_month(:year, :month) AS partition
            """), {"year": target_year, "month": target_month})
            partition = result.scalar()
            print(f"Partition ready: {partition}")

        await session.commit()


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

Schedule: this job runs on the 1st of every month. It guarantees there are always partitions available for the next 3 months. If it failed one month, the next one recovers it.

An alternative: the pg_partman extension. There's an extension that automates this whole pattern (creation, retention, maintenance). If your DB allows it, consider using it instead of a custom implementation. This capsule shows the manual approach so you understand what it's doing underneath.

Retention: deleting old partitions

Once partitioned, deleting audit logs from 5 years ago is trivial:

-- Delete January 2021's audit logs
DROP TABLE audit.task_log_y2021m01;

Comparison with DELETE:

DROP PARTITION is a filesystem operation. PostgreSQL doesn't need to process row by row. If the partition has 5M rows, they get removed instantly.

An automated retention job

# jobs/retention_audit_log.py
"""
Monthly job: delete old partitions from audit.task_log.
Policy: keep the last N months (satisfies compliance).
"""
import asyncio
from datetime import date, timedelta

from sqlalchemy import text

from app.db import AsyncSessionLocal


# Retention policy: keep N months
RETENTION_MONTHS = 84  # 7 years (satisfies SOX and most regulations)


async def main():
    today = date.today()

    # Compute the cutoff
    cutoff_year = today.year
    cutoff_month = today.month - RETENTION_MONTHS
    while cutoff_month <= 0:
        cutoff_month += 12
        cutoff_year -= 1

    async with AsyncSessionLocal() as session:
        # List partitions older than the cutoff
        result = await session.execute(text("""
            SELECT
                child.relname AS partition_name,
                pg_get_expr(child.relpartbound, child.oid) AS partition_bound
            FROM pg_inherits
            JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
            JOIN pg_class child ON pg_inherits.inhrelid = child.oid
            JOIN pg_namespace nmsp_parent ON nmsp_parent.oid = parent.relnamespace
            WHERE nmsp_parent.nspname = 'audit'
              AND parent.relname = 'task_log'
              AND child.relname ~ '^task_log_y\\d+m\\d+$'
            ORDER BY child.relname
        """))

        for row in result:
            # Parse the year and month from "task_log_y2021m01"
            partition_name = row.partition_name
            year = int(partition_name[10:14])
            month = int(partition_name[15:17])

            # Compare against the cutoff
            if (year, month) < (cutoff_year, cutoff_month):
                # Before deleting, archive to S3 (the next section)
                await archive_to_s3(session, partition_name)

                # Delete
                drop_sql = f"DROP TABLE audit.{partition_name}"
                await session.execute(text(drop_sql))
                print(f"Dropped {partition_name}")

        await session.commit()


async def archive_to_s3(session, partition_name):
    """The implementation is in the next section."""
    pass


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

Typical retention policy by industry:

• SOX (financial, USA): 7 years.
• HIPAA (health, USA): 6 years from creation or last use.
• GDPR (EU): indefinite if justified, at minimum whatever the purpose requires; generally 6 years for processing records.
• PCI-DSS: 1 year minimum of logs immediately available, 3 months online (RAM), 1 year in accessible storage.
• Typical B2B SaaS: 3-7 years, according to the customer contract.

Configure RETENTION_MONTHS according to your specific compliance.

Archive: moving old partitions to S3

Deleting old partitions with DROP TABLE is efficient, but it loses the information forever. The common practice is to archive before deleting: export the partition to an efficient format (parquet) and upload it to S3 (or equivalent). If that audit log is ever needed (a retroactive audit, a lawsuit), it can be recovered.

The strategy: export to parquet + upload to S3

# jobs/archive_audit_partition.py
"""
Exports a partition to parquet and uploads it to S3.
"""
import boto3
import asyncio
import pandas as pd
from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncSession


S3_BUCKET = "taskflow-audit-archive"
S3_PREFIX = "audit/task_log/"


async def archive_to_s3(session: AsyncSession, partition_name: str) -> None:
    """
    Reads the whole partition, writes parquet, uploads to S3.
    Verifies the upload succeeded before returning (important so as not to lose data).
    """
    # 1. Read the complete partition
    result = await session.execute(text(f"""
        SELECT
            id, entity_id, action, changed_at, changed_by,
            diff::TEXT AS diff_json,
            request_id::TEXT AS request_id,
            source
        FROM audit.{partition_name}
    """))
    rows = list(result)

    if not rows:
        print(f"Partition {partition_name} empty, skipping archive")
        return

    # 2. Convert to a DataFrame and then to parquet
    df = pd.DataFrame(rows, columns=[
        "id", "entity_id", "action", "changed_at", "changed_by",
        "diff_json", "request_id", "source"
    ])

    parquet_path = f"/tmp/{partition_name}.parquet"
    df.to_parquet(parquet_path, compression="zstd", index=False)

    # 3. Upload to S3
    s3_key = f"{S3_PREFIX}{partition_name}.parquet"
    s3 = boto3.client("s3")
    s3.upload_file(parquet_path, S3_BUCKET, s3_key)

    # 4. Verify the file is in S3 (a HEAD request)
    response = s3.head_object(Bucket=S3_BUCKET, Key=s3_key)
    uploaded_size = response["ContentLength"]
    local_size = os.path.getsize(parquet_path)
    if uploaded_size != local_size:
        raise RuntimeError(f"S3 upload size mismatch for {s3_key}")

    print(f"Archived {partition_name}: {len(rows)} rows -> s3://{S3_BUCKET}/{s3_key}")

The archive's decisions:

• Parquet format: columnar, compressed, with ZSTD compression. Typically 10-20x smaller than the original SQL. Efficient for analytical queries if you need to read it later with tools like Athena, DuckDB, Spark.
• JSONB serialized to a string: parquet doesn't support JSONB natively. Serialize it as TEXT and deserialize on read. Minimal loss of usefulness for retroactive audit cases.
• Post-upload verification: check that the file uploaded correctly (HEAD, size comparison) before deleting the partition. Without this verification, there's a risk of losing data from an incomplete upload.

Typical bucket structure:

s3://taskflow-audit-archive/
├── audit/
│   ├── task_log/
│   │   ├── task_log_y2018m01.parquet
│   │   ├── task_log_y2018m02.parquet
│   │   ├── ...
│   │   └── task_log_y2025m12.parquet
│   ├── comment_log/
│   └── contract_log/

Configuring an S3 lifecycle: after N years, the oldest partitions can be moved to S3 Glacier (very cheap storage, slow retrieval), reducing the cost even further.

{
  "Rules": [{
    "Id": "audit-archive-glacier",
    "Status": "Enabled",
    "Prefix": "audit/",
    "Transitions": [
      {"Days": 365, "StorageClass": "STANDARD_IA"},
      {"Days": 1095, "StorageClass": "GLACIER"}
    ]
  }]
}

Audit logs older than 1 year go to Standard-IA (Infrequent Access), audit logs 3+ years old go to Glacier. Combined cost: ~$0.001 per GB-month for the oldest ones. Practically free storage.

Why does this matter in real work?

1. It's the operational problem that kills audit logs in production. Without partitioning + retention, the audit log becomes a growing operational burden. Slow INSERTs, slow queries, giant backups. Eventually someone proposes "let's disable the audit log temporarily" and you lose the pattern.

2. It's what separates "I implemented an audit log" from "I have an audit log in production that scales." Implementing the triggers (capsule 03) is trivial compared to the long-term operation. This capsule is the one that closes the loop.

3. It's a measurable cost lever. Without retention, an audit log's storage can grow to 100GB+ in active systems. With retention and archiving to S3 + Glacier, the cost becomes trivial. The difference is hundreds of dollars a month on any cloud.

4. It's a prerequisite for passing a compliance audit. Regulatory frameworks typically ask for "audit logs available for X years." Without partitioning, keeping X years in the DB is operationally expensive. With partitioning + archive, it's trivial: the recent partitions in the DB, the old ones in S3 (recoverable if needed).

Traps and common mistakes

Mistake 1 (operational): forgetting to create future partitions

Symptom: the 1st of the month arrives and the new inserts start going to the default partition. The query SELECT * FROM audit.task_log_default returns rows — a sign that partitions are missing.

Why it happens: without a job that creates future partitions, you reach the moment where there's no partition for "this month." PostgreSQL puts them in the default (a safety net), but there the queries optimized by partition pruning don't apply.

How to tell: monitor:

-- Alert if the default has any recent rows
SELECT COUNT(*) FROM audit.task_log_default
WHERE changed_at >= NOW() - INTERVAL '1 day';

If it returns > 0, a partition needs creating.

How to fix it:

1. Immediately create the missing partitions (with the create_partition_for_month function).
2. Move the rows from the default into their correct partition:

-- After creating audit.task_log_y2026m05
WITH moved AS (
    DELETE FROM audit.task_log_default
    WHERE changed_at >= '2026-05-01' AND changed_at < '2026-06-01'
    RETURNING *
)
INSERT INTO audit.task_log_y2026m05 SELECT * FROM moved;

3. Make sure the job's cron is active and monitored. Hitting this bug means the automation failed.

Mistake 2 (conceptual): indexes that aren't partitioned

Symptom: after partitioning, a simple query (SELECT * FROM audit.task_log WHERE id = 12345) scans every partition, doesn't use an index. Performance worse than before.

Why it happens: the indexes defined on the parent table get created automatically on every new partition. But the primary key includes (id, changed_at) (the partitioning column). If you search only by id, PostgreSQL doesn't know which partition to search — it has to check all of them.

How to tell: EXPLAIN on the query:

EXPLAIN SELECT * FROM audit.task_log WHERE id = 12345;

If you see an "Append" over multiple partitions with no partition pruning, there's a problem.

How to fix it:

1. Always include the partitioning column in queries when you can:

-- Better:
SELECT * FROM audit.task_log
WHERE id = 12345 AND changed_at >= '2026-01-01';

2. If you can't (e.g. a lookup by id without knowing the date), accept the cost. The lookup scans every partition but with an index it's fast (microseconds per partition × N partitions).

3. Consider creating a global index if you need frequent lookups by id:

-- An index on `id` alone (without the partitioning column).
-- Cost: it gets maintained on every partition, with maintenance overhead.
CREATE INDEX idx_audit_task_log_id ON audit.task_log (id);

Mistake 3 (operational): retention with no archive — you lose data

Symptom: the team configures DROP PARTITION in the retention job. It doesn't archive to S3 first. Six months later, a lawsuit asks for the audit log from 5 years ago. That data no longer exists.

Why it happens: rushing to implement retention. It "works" because the DB frees up, but you lost the information.

How to tell: review the retention job. If it has a DROP TABLE with no archive_to_s3() before it, this is the bug.

How to fix it: always archive before deleting. The operational rule:

async def retention_safe(session, partition_name):
    # 1. Archive
    await archive_to_s3(session, partition_name)

    # 2. Verify the archive succeeded (an S3 head request)
    if not await verify_archive_in_s3(partition_name):
        raise RuntimeError(f"Archive verification failed for {partition_name}, NOT dropping")

    # 3. Only after a successful verification, delete
    await session.execute(text(f"DROP TABLE audit.{partition_name}"))

Without the intermediate verification, there's a risk of losing data to archive bugs.

Mistake 4 (conceptual): assuming partitioning fixes slow queries that don't filter by date

Symptom: the team partitions audit.task_log expecting every query to be fast. A query "show me every change made by user 47 in the last 5 years" is still slow.

Why it happens: partition pruning only works when the query filters by the partitioning column (changed_at). A query for "every change by user 47" has no date filter; PostgreSQL has to scan every partition.

How to tell: EXPLAIN on the query:

EXPLAIN SELECT * FROM audit.task_log WHERE changed_by = 47;

If you see an "Append" over every partition, there's no pruning.

How to fix it:

1. Add a date filter when possible:

SELECT * FROM audit.task_log
WHERE changed_by = 47
  AND changed_at >= NOW() - INTERVAL '90 days';

2. Accept the cost for full-retention queries (rare, typically annual reports).

3. Consider additional partitioning (e.g. hash partitioning by changed_by on top of the range by changed_at). Complex, rarely worth it.

Mistake 5 (operational): schema changes that don't replicate to existing partitions

Symptom: the team adds a tenant_id column to audit.task_log with an ALTER TABLE. The new partitions have the column; the old ones don't. Queries using tenant_id fail on old rows.

Why it happens: PostgreSQL is inconsistent about this. ALTERs on the parent table propagate to new partitions but sometimes not to existing ones (it depends on the ALTER and the version).

How to tell: after an ALTER, review each partition's schema:

\d audit.task_log_y2025m12

If the column is missing from some, there's divergence.

How to fix it: apply the ALTER to the parent table AND to each existing partition:

ALTER TABLE audit.task_log ADD COLUMN tenant_id BIGINT;

-- On PostgreSQL 16+, this typically propagates.
-- If not, manually:
DO $$
DECLARE
    p RECORD;
BEGIN
    FOR p IN
        SELECT child.relname AS partition_name
        FROM pg_inherits
        JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
        JOIN pg_class child ON pg_inherits.inhrelid = child.oid
        JOIN pg_namespace nmsp ON nmsp.oid = parent.relnamespace
        WHERE nmsp.nspname = 'audit' AND parent.relname = 'task_log'
    LOOP
        EXECUTE format('ALTER TABLE audit.%I ADD COLUMN IF NOT EXISTS tenant_id BIGINT', p.partition_name);
    END LOOP;
END $$;

Operational lesson: ALTERs on partitioned tables require extra care. Test schema parity after any migration.

Exercises

Exercise 1: partition an existing audit table

Your audit.user_log table (not partitioned) has 50M rows. Design the migration plan to a monthly-partitioned table, runnable with no downtime.

-- Create the new partitioned table with a temporary name
CREATE TABLE audit.user_log_new (
    id BIGSERIAL,
    entity_id BIGINT NOT NULL,
    action TEXT NOT NULL,
    changed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    changed_by BIGINT NULL,
    diff JSONB NOT NULL,
    request_id UUID NULL,
    source TEXT NULL,
    PRIMARY KEY (id, changed_at)
) PARTITION BY RANGE (changed_at);

-- Create partitions for every month (based on the original table's MIN/MAX)
-- ... (a script that generates CREATE TABLE PARTITION OF for each month from MIN(changed_at) to NOW + 3 months)

-- Create the indexes
CREATE INDEX ... ON audit.user_log_new (...);

# Script: copy the old data to the new table, without blocking inserts on the old one
async def migrate_in_batches():
    batch_size = 100_000
    last_id = 0

    while True:
        result = await session.execute(text("""
            INSERT INTO audit.user_log_new
            SELECT * FROM audit.user_log
            WHERE id > :last_id
            ORDER BY id
            LIMIT :batch_size
            RETURNING id
        """), {"last_id": last_id, "batch_size": batch_size})
        rows = list(result)
        if not rows:
            break
        last_id = rows[-1].id
        await session.commit()
        print(f"Migrated up to id {last_id}")

BEGIN;
-- Rename atomically
ALTER TABLE audit.user_log RENAME TO user_log_old;
ALTER TABLE audit.user_log_new RENAME TO user_log;
COMMIT;

-- Verify the counts
SELECT (SELECT COUNT(*) FROM audit.user_log) AS new_count,
       (SELECT COUNT(*) FROM audit.user_log_old) AS old_count;
-- They should match or the new one should have slightly more (post-cutover inserts).

-- After validating, remove the old table
DROP TABLE audit.user_log_old;

Exercise 2: implement the complete maintenance job

Implement the job that: a) creates future partitions, b) archives old partitions to S3, c) deletes the archived partitions.

# jobs/audit_maintenance.py
"""
Monthly audit log maintenance job.
Runs: create future partitions, archive old ones, delete the archived ones.
"""
import asyncio
import os
from datetime import date

import boto3
import pandas as pd
from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncSession

from app.db import AsyncSessionLocal


PARTITIONS_AHEAD = 3
RETENTION_MONTHS = 84  # 7 years
S3_BUCKET = "taskflow-audit-archive"
S3_PREFIX = "audit/task_log/"


async def create_future_partitions(session):
    today = date.today()
    for offset in range(PARTITIONS_AHEAD + 1):
        target_year = today.year
        target_month = today.month + offset
        while target_month > 12:
            target_month -= 12
            target_year += 1

        result = await session.execute(text(
            "SELECT audit.create_partition_for_month(:y, :m)"
        ), {"y": target_year, "m": target_month})
        partition = result.scalar()
        print(f"[create] {partition}")


async def list_partitions_older_than(session, year, month):
    """Lists partitions older than the (year, month) cutoff."""
    result = await session.execute(text("""
        SELECT child.relname AS name
        FROM pg_inherits
        JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
        JOIN pg_class child ON pg_inherits.inhrelid = child.oid
        JOIN pg_namespace nmsp ON nmsp.oid = parent.relnamespace
        WHERE nmsp.nspname = 'audit'
          AND parent.relname = 'task_log'
          AND child.relname ~ '^task_log_y\\d+m\\d+$'
        ORDER BY child.relname
    """))

    old_partitions = []
    for row in result:
        # Parse "task_log_y2021m01" -> (2021, 1)
        p_year = int(row.name[10:14])
        p_month = int(row.name[15:17])
        if (p_year, p_month) < (year, month):
            old_partitions.append(row.name)

    return old_partitions


async def archive_partition(session, partition_name):
    """Exports to parquet, uploads to S3, verifies."""
    print(f"[archive] {partition_name} -> exporting...")

    result = await session.execute(text(f"""
        SELECT id, entity_id, action, changed_at, changed_by,
               diff::TEXT AS diff_json, request_id::TEXT, source
        FROM audit.{partition_name}
    """))
    rows = [dict(r._mapping) for r in result]

    if not rows:
        print(f"[archive] {partition_name} empty, skipping")
        return True

    df = pd.DataFrame(rows)
    parquet_path = f"/tmp/{partition_name}.parquet"
    df.to_parquet(parquet_path, compression="zstd", index=False)

    s3_key = f"{S3_PREFIX}{partition_name}.parquet"
    s3 = boto3.client("s3")
    s3.upload_file(parquet_path, S3_BUCKET, s3_key)

    # Verify
    response = s3.head_object(Bucket=S3_BUCKET, Key=s3_key)
    uploaded_size = response["ContentLength"]
    local_size = os.path.getsize(parquet_path)
    if uploaded_size != local_size:
        print(f"[archive] FAILED size mismatch for {partition_name}")
        return False

    os.remove(parquet_path)
    print(f"[archive] {partition_name} -> s3://{S3_BUCKET}/{s3_key} ({len(rows)} rows)")
    return True


async def drop_partition(session, partition_name):
    await session.execute(text(f"DROP TABLE audit.{partition_name}"))
    print(f"[drop] audit.{partition_name}")


async def main():
    today = date.today()

    # Compute the retention cutoff
    cutoff_year = today.year
    cutoff_month = today.month - RETENTION_MONTHS
    while cutoff_month <= 0:
        cutoff_month += 12
        cutoff_year -= 1

    async with AsyncSessionLocal() as session:
        # Phase 1: create future partitions
        await create_future_partitions(session)

        # Phase 2: list the partitions for archive + drop
        old_partitions = await list_partitions_older_than(session, cutoff_year, cutoff_month)

        for partition in old_partitions:
            success = await archive_partition(session, partition)
            if success:
                await drop_partition(session, partition)

        await session.commit()


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

Exercise 3: an analytical query that takes advantage of partition pruning

Write the query "how many changes were there per month in the last 12 months, grouped by action." Verify with EXPLAIN that it uses partition pruning.

-- The query
SELECT
    DATE_TRUNC('month', changed_at) AS month,
    action,
    COUNT(*) AS changes
FROM audit.task_log
WHERE changed_at >= NOW() - INTERVAL '12 months'
  AND changed_at < NOW()
GROUP BY DATE_TRUNC('month', changed_at), action
ORDER BY month DESC, action;

EXPLAIN SELECT
    DATE_TRUNC('month', changed_at) AS month,
    action,
    COUNT(*) AS changes
FROM audit.task_log
WHERE changed_at >= NOW() - INTERVAL '12 months'
  AND changed_at < NOW()
GROUP BY DATE_TRUNC('month', changed_at), action
ORDER BY month DESC, action;

HashAggregate
  ->  Append
      ->  Seq Scan on audit_task_log_y2025m05
            Filter: ((changed_at >= ...) AND (changed_at < ...))
      ->  Seq Scan on audit_task_log_y2025m06
            Filter: ...
      ...
      ->  Seq Scan on audit_task_log_y2026m04
            Filter: ...

   month     | action | changes
-------------+--------+---------
 2026-04-01  | INSERT |    420
 2026-04-01  | UPDATE |   3,810
 2026-04-01  | DELETE |     12
 2026-03-01  | INSERT |    380
 ...

Exercise 4: compute the storage cost with and without retention

You're given these parameters: 10k changes/day in tasks, ~500 bytes average per row, a 7-year retention target. Compute:

a) The storage if you do NOT apply retention. b) The storage with a 12-month retention in the DB + archiving to S3 Standard-IA + Glacier after 3 years. c) The estimated monthly cost on AWS (RDS PostgreSQL gp3 + S3) for both cases.

Exercise 5: defend the "partition from day 1" decision in a code review

Your teammate proposes: "Let's do the audit log with no partitioning. When it grows too much, we'll partition it then." Argue why from day 1 is the right decision.

Summary and next step

In this capsule you learned:

• Audit logs grow without limit and eventually impact performance, storage, and operations (backups, queries, maintenance). Without retention, the log becomes a growing burden.
• PostgreSQL 16+'s declarative partitioning turns the virtual table into a collection of physical partitions. Each insert gets routed automatically to the right partition based on the partitioning column (changed_at).
• Partition pruning is the foundation of the value: queries that filter by the partitioning column only access the relevant partitions, ignoring the rest.
• DROP PARTITION replaces DELETE for retention. It's a filesystem operation (instantaneous) instead of a row-by-row one (hours).
• Automation with a PL/pgSQL function + a monthly cron job creates future partitions before they're needed. Without this, the system fails when the 1st of the new month arrives.
• Archiving to S3 before deleting preserves the log for retroactive auditing. Parquet + ZSTD compresses ~10x. S3 Standard-IA + Glacier make the cost trivial.
• A retention policy according to compliance: 7 years SOX, 6 years HIPAA, configurable for your industry. Configure RETENTION_MONTHS appropriately.
• Partition from day 1, not later. Migrating an existing audit log to partitioned is a heavy, multi-day operation. The cost of doing it from the start: ~100 extra lines.
• Partitioning's limitations: queries that don't filter by the partitioning column scan every partition. Schema changes require propagation to every partition. ALTERs are sensitive.

Before moving on you should be able to:

• Implement the complete pattern (partitioned schema + function + maintenance job) on a new audit table.
• Design a retention policy according to your industry's specific compliance.
• Configure the archive to S3 with post-upload verification.
• Defend the "partition from day 1" decision against the incremental approach.
• Diagnose and fix common bugs (a full default partition, missing indexes, unpropagated ALTERs).

Connection with guide #14 (Advanced PostgreSQL for Backend): declarative partitioning is an advanced PostgreSQL feature that guide #14 goes deeper into with more complex cases (LIST and HASH partitioning, sub-partitioning, integration with custom types, partitioning large existing tables with advanced techniques). This capsule gives you the case applied to the audit log; guide #14 gives you the general pattern.

Next capsule — Project: an audit trail in TaskFlow. It's the module's closing capsule. You're going to integrate everything you learned into a runnable project: a standalone tasks table with an audit log using PostgreSQL triggers (capsule 03), monthly partitioning (this capsule), a FastAPI dependency for SET LOCAL, and endpoints that consume the audit. It's the complete pattern, ready to be extended in module 8 with multi-tenancy. You're going to have a GitHub repo with runnable code, tests, and comparative benchmarks.

Resources

1. PostgreSQL Documentation — Table Partitioning — the complete official reference. Required reading, especially sections 5.11.1 (Overview) and 5.11.2 (Declarative).
2. PostgreSQL Documentation — pg_partman — an extension that automates partitioning + retention + maintenance. If your DB allows it, an alternative to this capsule's custom implementation.
3. AWS S3 — Storage Classes — the reference for Standard, Standard-IA, Glacier, Glacier Deep Archive. Useful for designing the audit archive's lifecycle policy.
4. PostgreSQL Documentation — Partition Pruning — the mechanism's details and the cases where it works or doesn't.
5. Apache Parquet — Format specification — the format's reference. Relevant for understanding why it's efficient for archiving.
6. Crunchy Data — "PostgreSQL Partitioning" — a practical analysis with benchmarks. Good complementary reading.
7. Hussein Nasser — "Database Partitioning Explained" — a visual explanation of the concept. Useful for teaching the pattern to your team.
8. Citus Data — "PostgreSQL partitioning, the missing manual" — a deeper reference with advanced cases.
9. GitLab Database Guide — "Partitioning Strategy" — GitLab's real implementation for massive tables. Inspiration for cases at scale.
10. Compliance frameworks — retention requirements — an overview of the typical requirements per regulatory framework.

Module 3 — SQL Patterns for Production APIs Guide

Next capsule: Module project — a runnable audit trail in TaskFlow.