10. The metadata database

Airflow’s metadata database is a PostgreSQL instance holding a few dozen tables. It is also, in almost every installation that outgrows its first year, the thing that makes everything slow with no error message anywhere.

Why the scheduler is database-bound

Go back to chapter 2’s loop. Every few seconds, for every active DAG run, the scheduler asks the database questions like:

-- which task instances are ready to be scheduled?
SELECT ... FROM task_instance ti
JOIN dag_run dr ON ...
WHERE ti.state IS NULL AND dr.state = 'running'
ORDER BY ti.priority_weight DESC
FOR UPDATE SKIP LOCKED;

Every state transition — scheduled, queued, running, success — is an UPDATE. Every heartbeat is an UPDATE. Every XCom is an INSERT. With several schedulers, all of this is happening under row locks.

   200 DAGs × 30 tasks × 24 runs/day = 144,000 task instances/day
   each with ~6 state transitions     = ~900,000 writes/day
   plus heartbeats, XComs, logs

The scheduler does almost no computation. It is a database client. Which means every performance question about Airflow’s scheduling is a question about PostgreSQL.

How the tables grow

   task_instance      DAGs × tasks × runs        ← the big one
   task_instance_history  + every retry
   xcom               tasks that return values
   dag_run            DAGs × runs
   log                every UI action and state change
   job                every scheduler/worker heartbeat record
   rendered_task_instance_fields   templated fields, per task instance
   task_reschedule    every sensor poke in reschedule mode  ← surprising

Two of these surprise people.

log records every state change and every UI interaction. On a busy installation it is frequently the largest table, and nothing in Airflow reads it in normal operation.

task_reschedule gets a row for every poke of a sensor in reschedule mode. A sensor with poke_interval=60 waiting six hours writes 360 rows. Twenty such sensors daily is 7,200 rows a day from waiting for files.

`airflow db clean`

The single most important maintenance command in Airflow, and the one nobody runs until something is already wrong.

# see what would go
airflow db clean --clean-before-timestamp 2026-01-01 --dry-run

# do it
airflow db clean --clean-before-timestamp 2026-01-01 --yes

# specific tables only
airflow db clean --clean-before-timestamp 2026-01-01 \
  --tables log,xcom,task_reschedule --yes

Run it on a schedule. The obvious way is as an Airflow DAG:

@dag(schedule="0 3 * * 0", start_date=..., catchup=False, tags=["maintenance"])
def db_maintenance():
    BashOperator(
        task_id="clean",
        bash_command=(
            "airflow db clean "
            "--clean-before-timestamp '{{ macros.ds_add(ds, -90) }}' --yes"
        ),
    )

Retention guidance:

Table	Keep	Why
`log`	30 days	nothing reads it
`task_reschedule`	30 days	pure noise after the fact
`xcom`	30–90 days	grows fastest if misused
`task_instance`	90–180 days	you look at this history
`dag_run`	90–180 days	same
`job`	30 days	heartbeat records

Take a database backup before your first db clean. It is a DELETE, it is not reversible, and the first run on an installation that has never been cleaned can remove tens of millions of rows.

That first run also deserves care for a different reason: a single enormous DELETE takes locks and generates a lot of WAL. Delete in chunks by month rather than clearing three years at once — the PostgreSQL locking chapter explains why a long-running statement on a busy table is its own kind of outage.

Archiving instead of deleting

airflow db clean --clean-before-timestamp 2026-01-01 \
  --skip-archive false --yes          # copies to _airflow_deleted__* tables

By default rows are archived into _airflow_deleted__<table>__<timestamp> tables rather than dropped. Useful once; a problem forever, because those tables never go away.

airflow db export-archived --output-path /backups --drop-archives

Check for archive tables on any installation you inherit. Finding 40 GB of _airflow_deleted__log__20230411 is common.

Vacuum

PostgreSQL’s DELETE does not free space; it marks tuples dead (this is MVCC). After a large db clean:

VACUUM (VERBOSE, ANALYZE) task_instance;
VACUUM (VERBOSE, ANALYZE) log;
VACUUM (VERBOSE, ANALYZE) xcom;

VACUUM FULL reclaims disk properly but takes an ACCESS EXCLUSIVE lock — Airflow stops entirely while it runs. Do it in a maintenance window or not at all; regular autovacuum plus ANALYZE is usually enough.

Autovacuum’s defaults are tuned for tables that change slowly. For Airflow’s hot tables, make it more aggressive:

ALTER TABLE task_instance SET (
  autovacuum_vacuum_scale_factor = 0.02,     -- default 0.2
  autovacuum_analyze_scale_factor = 0.01
);

Without this, task_instance accumulates dead tuples between vacuums and the scheduler’s queries get progressively slower in a way that looks like load.

Sizing

Rough figures for a self-managed installation:

Scale	Task instances/day	Database
small	< 10,000	2 vCPU, 8 GB, SSD
medium	10,000–100,000	4 vCPU, 16 GB, SSD
large	100,000–500,000	8 vCPU, 32 GB, fast SSD, tuned autovacuum
very large	500,000+	16 vCPU, 64 GB, dedicated, PgBouncer

The settings that matter most for Airflow’s workload:

   max_connections       ← see below
   shared_buffers        25% of RAM
   work_mem              modest; many small queries, not big sorts
   effective_cache_size  ~75% of RAM

Connections are the constraint people hit first. Count them honestly:

   3 schedulers × sql_alchemy_pool_size (5) + overflow (10)   = 45
   1 API server × pool                                         = 15
   10 Celery workers × 16 concurrency × 1 connection each      = 160
                                                              ─────
                                                               220

Against a default max_connections of 100, that fails — and the symptom is FATAL: sorry, too many clients already appearing intermittently on tasks, which reads like a task bug.

Two fixes, and you want both:

PgBouncer in transaction mode. Hundreds of client connections multiplex onto a few dozen server ones. The official Airflow Helm chart ships it and it should be on by default for anything non-trivial.

Airflow 3’s Task Execution API (chapter 2) removes worker database connections entirely — that 160 in the table above becomes 0. It is the largest single reduction available.

Symptoms of a struggling database

The reason this chapter exists: the database rarely fails. It gets slow, and slow looks like everything else.

Symptom	Likely cause
tasks slow to move `scheduled` → `queued`	scheduler loop slowed by query time
long gaps between tasks in a DAG	same
UI pages take many seconds	large `task_instance` / `log` scans
`too many clients already`	connection exhaustion — PgBouncer
everything degrades gradually over months	no `db clean` has ever run
scheduler CPU low but nothing happens	waiting on the database

That last row is the diagnostic tell. A scheduler at 10% CPU with tasks queueing up is waiting on the database, not on itself. Adding schedulers makes it worse.

The metrics to watch:

   airflow_scheduler_scheduler_loop_duration    ← THE number
   airflow_dagrun_schedule_delay
   airflow_executor_queued_tasks
   pg_stat_activity: active connections, longest query
   pg_stat_user_tables: n_dead_tup on task_instance

scheduler_loop_duration is the health metric for Airflow’s scheduling. Under a second is healthy; several seconds means tasks are waiting that long between transitions; tens of seconds means the installation feels broken. Alert on it.

Keeping it small in the first place

Prevention beats cleanup:

Fewer, chunkier tasks. A DAG of 200 tiny tasks writes 200 rows and 1,200 state transitions per run. Ten tasks doing the same work write 60. Airflow’s per-task overhead is a few seconds and several database writes — do not split work into tasks finer than that overhead.

Map over batches, not items (chapter 3).

Do not put data in XComs (chapter 9).

Prefer deferrable operators over reschedule sensors — no task_reschedule rows at all (chapter 11).

Do not schedule more often than you need. Hourly instead of every five minutes is a twelve-fold reduction in every table.

What to take from this chapter

The scheduler is a database client. Every scheduling question is a PostgreSQL question.
task_instance grows as DAGs × tasks × runs; log is often the largest table and nothing reads it; task_reschedule gets a row per sensor poke.
Run airflow db clean on a schedule — as an Airflow DAG. Nobody does until something is wrong.
Back up before the first clean, and delete in chunks rather than one enormous statement.
Check for _airflow_deleted__* archive tables on any installation you inherit; they are never removed automatically.
VACUUM ANALYZE after a large clean, and make autovacuum more aggressive on task_instance — the defaults are wrong for this workload.
Connection exhaustion is the first wall. Use PgBouncer; Airflow 3’s Task Execution API removes worker connections entirely.
The database gets slow, it does not fail. A scheduler at low CPU with tasks queueing is waiting on the database. Watch scheduler_loop_duration — under a second is healthy.
Prevention: fewer chunkier tasks, map over batches, no data in XComs, deferrable over reschedule, and do not schedule more often than you need.

Next: deferrable operators and the triggerer — how a task can wait for six hours without occupying anything.

Written by Sepuri Sai Krishna