Page MenuHomePhabricator
Create Task
Maniphest T411992

Reduce main Airflow DB size and consider splitting heavy workloads into separate instances
Open, Needs TriagePublic
Actions

Description

Context:
The main Airflow Postgres database is growing large due to:

  • Accumulation of old DAG runs, task instances, logs, XComs, etc.
  • All workloads sharing a single Airflow instance

A large, monolithic DB negatively affects performance (larger indexes, slower vacuum/analyze, longer backups) and makes debugging and maintenance harder.

We could improve the situation by:

  • Reduce the size of the existing Airflow DB by cleaning up old/non-essential data. It could be done with a dedicated cleanup DAG e.g.https://github.com/teamclairvoyant/airflow-maintenance-dags/blob/master/db-cleanup/airflow-db-cleanup.py With a dag.yaml/config file defining retention policies by DAG/criticality.
  • Evaluate and, if beneficial, implement a split of the main Airflow instance into smaller, workload-focused instances.
    • we may regroup dumps 1.0 and file exporter into an independent airflow instance
    • same with all Cassandra/Druid exporter dags

Risks:

  • Over-aggressive cleanup may delete data needed analysis, backfilling, compliance.
  • More instances to maintain (upgrades, monitoring, alerting). Some SRE works are needed for automation.
  • Possible cross-instance orchestration complexity (dependencies, shared resources).

Details

Related Changes in Gerrit:
SubjectRepoBranchLines +/-
Grow storage on dse-k8s pg_airflow_searchoperations/deployment-chartsmaster+3 -1
Customize query in gerrit

Related Objects
Search...

Event Timeline

Antoine_Quhen created this task.Dec 8 2025, 10:02 AM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptDec 8 2025, 10:02 AM
Antoine_Quhen added a parent task: T411988: Airflow main performance instance optimization.Dec 8 2025, 10:02 AM
BTullis subscribed.Dec 8 2025, 1:18 PM

We're also investigating the possibility of the Airflow metadata DB being implicated in: T412003: Airflow-main scheduler loop sometimes slows down markedly

For reference, we also have an Airflow DB maintenance DAG already tested and ready.
https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/blob/main/test_k8s/dags/cleanup_airflow_db_dag.py

So far, however, it has only been deployed to the test_k8s airflow instance.

CodeReviewBot added a project: Patch-For-Review.Dec 24 2025, 3:29 PM

aqu opened https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/1899

Add monthly Airflow DB cleaner DAG to all instances

Antoine_Quhen claimed this task.Mon, Jan 12, 10:21 PM
Antoine_Quhen edited projects, added Data-Engineering (Q3 FY25/26 January 1st - March 31th); removed Data-Engineering.
Antoine_Quhen moved this task from Next Up to In Review on the Data-Engineering (Q3 FY25/26 January 1st - March 31th) board.Mon, Jan 12, 10:26 PM
CodeReviewBot added a comment.Thu, Jan 15, 9:59 AM

aqu merged https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/1899

Add monthly Airflow DB cleaner DAG to all instances

Maintenance_bot removed a project: Patch-For-Review.Thu, Jan 15, 10:31 AM
CodeReviewBot added a project: Patch-For-Review.Thu, Jan 15, 8:13 PM

aqu opened https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/1922

Add DB cleaner dag to search instance

CodeReviewBot added a comment.Thu, Jan 15, 8:14 PM

aqu closed https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/1922

Add DB cleaner dag to search instance

CodeReviewBot added a comment.Thu, Jan 15, 8:15 PM

aqu opened https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/1923

Add DB cleaner dag to search instance

CodeReviewBot added a comment.Mon, Jan 19, 8:24 AM

aqu merged https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/1923

Add DB cleaner dag to search instance

Maintenance_bot removed a project: Patch-For-Review.Mon, Jan 19, 8:30 AM
Antoine_Quhen moved this task from In Review to Done on the Data-Engineering (Q3 FY25/26 January 1st - March 31th) board.Mon, Jan 19, 10:31 AM
Antoine_Quhen added a subscriber: brouberol.

Deployment of db_cleaner dag on Airflow instances went mostly well.

The exception was search instance. The DB was too tight on resources for the airflow db clean script. The later is moving the deleted rows into a temporary table, later removed. When cleaning table logs, this process generated a 11M rows temporary table, crashing the DB.

Thanks to @brouberol the cluster was put back up and the tmp tables manually deleted.

gerritbot added a comment.Mon, Jan 19, 11:01 AM

Change #1228445 had a related patch set uploaded (by Joal; author: Joal):

[operations/deployment-charts@master] Grow walStorage on dse-k8s pg_airflow_search

https://gerrit.wikimedia.org/r/1228445

gerritbot added a project: Patch-For-Review.Mon, Jan 19, 11:01 AM
gerritbot added a comment.Mon, Jan 19, 11:21 AM

Change #1228445 merged by Brouberol:

[operations/deployment-charts@master] Grow storage on dse-k8s pg_airflow_search

https://gerrit.wikimedia.org/r/1228445

Maintenance_bot removed a project: Patch-For-Review.Mon, Jan 19, 11:30 AM
Antoine_Quhen closed this task as Resolved.Wed, Jan 28, 7:01 PM

Closing. Next optimization could be splitting from main instance file_exporters job. Should be done in another ticket.

CodeReviewBot added a project: Patch-For-Review.Thu, Feb 5, 12:23 PM

aqu opened https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/1975

Switch strategy for Airflow db cleanup

Antoine_Quhen reopened this task as Open.Mon, Feb 9, 8:33 AM
Antoine_Quhen moved this task from Done to In Review on the Data-Engineering (Q3 FY25/26 January 1st - March 31th) board.Mon, Feb 9, 8:42 AM

search and analytics_test are running on small DB instances with no free headroom.
During deletes on large tables, Airflow’s current strategy stages deleted rows into a temporary table before removing them. That briefly requires ~2x table storage, which causes the database to run out of space and crash.

This failed again in the 2026-02-03 run. Since the same script already ran in January, this looks like a recurring capacity/strategy issue rather than a one-time first-run effect.

I’ll monitor the 2026-03-03 run with SRE.

In parallel, I prepared a fix to switch to a direct delete strategy (no temp-table copy). The tradeoff is custom logic that is coupled to the current Airflow version.

Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL · Credits