Does raising max_tis_per_query reduce Airflow burst scheduling latency, or does it move the bottleneck to the metadata database?

Answer first

Raising max_tis_per_query can reduce burst scheduling latency up to a point, then it moves the bottleneck to the metadata database. The setting controls how many TaskInstance rows the scheduler fetches per DB round-trip. A larger batch means fewer scheduler loop iterations to discover runnable tasks, which lowers queueing delay during a burst. A larger batch also means larger SELECT/UPDATE payloads, longer transaction hold times and more DB CPU per query. When query execution time plus lock contention exceeds the time saved by fewer loops, end-to-end task start latency stops improving and can worsen.

Confirmed facts

• max_tis_per_query is a scheduler batch size for task instance queries. Increasing it reduces the number of iterations needed to find runnable tasks.
• Global and per-DAG concurrency limits are hard ceilings. parallelism and max_active_tasks_per_dag can keep tasks in a non-runnable state. If those limits are saturated, raising the batch size does not reduce task start latency.
• Executor choice changes where the queue forms. Local and Celery executors queue tasks for workers; Kubernetes Executor creates pods per task. Scheduler batch tuning is dwarfed by executor-side overhead in those cases.
• In HA scheduler deployments multiple schedulers compete for the same task instances, increasing lock contention on the metadata database regardless of batch size. Single-scheduler behavior does not generalize to HA.
• Behavior is version-dependent. Scheduler architecture and defaults for batching and locking have changed across Airflow releases.

Likely explanation, not confirmed

Under a burst, the scheduler loop is often the first visible bottleneck because many TIs become runnable at once. Increasing the batch size reduces loop iterations and can improve the time from "queued" to "scheduled". The trade-off is higher per-query cost and longer row-level lock hold times in the metadata DB. The optimal point is where scheduler loop duration is balanced with DB query latency and lock wait time. Excessively high values may risk DB connection timeouts or memory pressure on the DB server.

Steps needed for this case

1. Verify current configuration. Check airflow.cfg or environment variables for max_tis_per_query, parallelism, and max_active_tasks_per_dag.
2. Establish a baseline during a burst. Record scheduler loop duration from logs and task start timestamps from the metadata DB.
3. Monitor metadata DB during the burst. Track DB CPU, lock wait time, and query duration for scheduler queries. Increment max_tis_per_query in small steps and re-measure.
4. Confirm concurrency limits are not the limiter. If tasks remain in queued due to parallelism or per-DAG limits, raise those limits first.

One diagnostic detail that changes the recommendation: the current scheduler deployment mode and metadata database engine. Single scheduler vs HA changes lock contention risk, and DB engine changes lock and query scaling characteristics. If you can share that, the safe tuning range can be narrowed.