Database backend optimization in Apache Airflow - Commands & Configuration

Introduction

Airflow uses a database to keep track of tasks and workflows. Optimizing this database backend helps Airflow run faster and handle more work without slowing down.

When Airflow tasks are running slower than expected due to database delays

When the Airflow web UI takes too long to load task or DAG information

When you want to reduce database load during peak workflow execution times

When scaling Airflow to support more users and workflows without performance drops

When cleaning up old task records to keep the database size manageable

Config File - airflow.cfg

airflow.cfg

[core]
executor = LocalExecutor
sql_alchemy_conn = postgresql+psycopg2://airflow:airflow@localhost:5432/airflow

[database]
sql_alchemy_pool_size = 10
sql_alchemy_max_overflow = 20
sql_alchemy_pool_recycle = 1800

[scheduler]
max_threads = 4

[logging]
logging_level = INFO

[metrics]
enable = True

The sql_alchemy_conn sets the connection string to the PostgreSQL database used by Airflow.

The sql_alchemy_pool_size and sql_alchemy_max_overflow control how many database connections Airflow keeps open to improve speed.

The sql_alchemy_pool_recycle helps avoid stale connections by recycling them every 1800 seconds.

Adjusting max_threads in the scheduler controls how many tasks Airflow can schedule in parallel, affecting database load.

Enabling metrics helps monitor database performance over time.

Commands

This command upgrades the Airflow database schema to the latest version to ensure compatibility and performance improvements.

Terminal

airflow db upgrade

Expected Output

INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.

INFO  [alembic.runtime.migration] Will assume transactional DDL.

INFO  [alembic.runtime.migration] Running upgrade  -> 2.5.0, Add new columns and indexes

INFO  [alembic.runtime.migration] Running upgrade 2.5.0 -> 2.6.0, Optimize task instance queries

Upgrade successful

This command checks the current state of the Airflow database to detect any issues or inconsistencies.

Terminal

airflow db check

Expected Output

INFO  [airflow.utils.db] Database connection successful

INFO  [airflow.utils.db] Database schema is up to date

This command cleans up old task instance records before the specified date to reduce database size and improve performance.

Terminal

airflow db cleanup --clean-before-timestamp 2023-01-01T00:00:00

Expected Output

INFO  [airflow.utils.db] Deleted 1500 old task instance records before 2023-01-01T00:00:00

Cleanup complete

→

--clean-before-timestamp - Specify the cutoff date for cleaning old records

This runs the Airflow scheduler once to test if the database connection and configuration are optimized for task scheduling.

Terminal

airflow scheduler --num_runs 1

Expected Output

INFO  [airflow.scheduler] Starting scheduler

INFO  [airflow.scheduler] Scheduler ran 1 iteration(s)

INFO  [airflow.scheduler] Scheduler exiting

→

--num_runs - Limit the scheduler to run a fixed number of iterations for testing

Key Concept

If you remember nothing else from database backend optimization, remember: tuning connection pooling and cleaning old data keeps Airflow fast and stable.

Common Mistakes

Not upgrading the database schema after Airflow updates

Old schemas can cause slow queries and errors because new features expect updated tables and indexes

Always run 'airflow db upgrade' after upgrading Airflow to keep the database schema current

Setting the connection pool size too low or too high without testing

Too low causes slow task scheduling due to waiting for connections; too high wastes resources and can overload the database

Start with moderate pool sizes like 10 and 20 overflow, then adjust based on monitoring metrics

Ignoring old task records buildup in the database

Large numbers of old records slow down queries and increase storage needs

Regularly run 'airflow db cleanup' with appropriate timestamps to remove old data

Summary

Run 'airflow db upgrade' to keep the database schema updated for performance.

Configure connection pooling in airflow.cfg to balance speed and resource use.

Clean old task records regularly to keep the database size manageable and queries fast.

Test scheduler runs to verify database optimizations improve task scheduling speed.

Practice

1. What is the main benefit of optimizing the Airflow database backend?

easy

A. Increases the number of DAGs you can create

B. Improves task scheduling and UI speed

C. Automatically fixes all DAG errors

D. Enables Airflow to run without a scheduler

Solution

1. Step 1: Understand Airflow database role

   The Airflow database stores metadata about tasks and DAGs, which affects scheduling and UI responsiveness.

2. Step 2: Connect optimization to performance

   Optimizing the database backend reduces delays in task scheduling and speeds up the UI loading times.

3. Final Answer:

   Improves task scheduling and UI speed -> Option B

4. Quick Check:

   Database optimization = better scheduling and UI [OK]

Hint: Think about what slows down task scheduling and UI [OK]

Common Mistakes:

• Confusing database optimization with adding more DAGs
• Assuming it fixes DAG code errors automatically
• Thinking it removes the need for a scheduler

2. Which command correctly upgrades the Airflow database schema?

easy

A. airflow db refresh

B. airflow db migrate

C. airflow db update

D. airflow db upgrade

Solution

1. Step 1: Recall Airflow database commands

   The official command to upgrade the Airflow database schema is airflow db upgrade.

2. Step 2: Verify command correctness

   Other options like migrate, update, or refresh are not valid Airflow CLI commands for database upgrade.

3. Final Answer:

   airflow db upgrade -> Option D

4. Quick Check:

   Upgrade command = airflow db upgrade [OK]

Hint: Remember 'upgrade' is the official schema update command [OK]

Common Mistakes:

• Using 'migrate' instead of 'upgrade'
• Confusing with generic database commands
• Typing 'update' or 'refresh' which are invalid

3. Given this airflow.cfg snippet, what is the effect of setting sql_alchemy_pool_size = 10?

[database]
sql_alchemy_pool_size = 10

medium

A. Limits the number of simultaneous DB connections to 10

B. Sets the max retries for DB connections to 10

C. Defines the timeout in seconds for DB queries

D. Enables 10 read replicas for the database

Solution

1. Step 1: Understand sql_alchemy_pool_size meaning

   This setting controls the maximum number of open connections in the SQLAlchemy connection pool.

2. Step 2: Interpret the value 10

   Setting it to 10 means Airflow can have up to 10 simultaneous database connections open.

3. Final Answer:

   Limits the number of simultaneous DB connections to 10 -> Option A

4. Quick Check:

   sql_alchemy_pool_size = max DB connections [OK]

Hint: Pool size means max open DB connections [OK]

Common Mistakes:

• Confusing pool size with retry count
• Thinking it sets query timeout
• Assuming it controls DB replicas

4. You run airflow db check and get a connection timeout error. What is the most likely cause?

medium

A. Database server is unreachable or down

B. The airflow.cfg file is missing

C. The DAG files have syntax errors

D. The Airflow scheduler is not running

Solution

1. Step 1: Analyze the error type

   A connection timeout error means Airflow cannot reach the database server.

2. Step 2: Rule out other options

   Missing config or DAG syntax errors do not cause connection timeouts. Scheduler status is unrelated to DB connection.

3. Final Answer:

   Database server is unreachable or down -> Option A

4. Quick Check:

   Connection timeout = DB unreachable [OK]

Hint: Timeout means no DB response, check server status [OK]

Common Mistakes:

• Blaming missing config for connection timeout
• Thinking DAG errors cause DB connection issues
• Assuming scheduler status affects DB connection

5. To improve Airflow database performance under heavy load, which combination of settings is best to tune in airflow.cfg?

hard

A. Increase sql_alchemy_pool_size and reduce sql_alchemy_max_overflow

B. Disable connection pooling by setting sql_alchemy_pool_size to 1

C. Decrease sql_alchemy_pool_size and increase sql_alchemy_max_overflow

D. Set sql_alchemy_pool_size and sql_alchemy_max_overflow both to zero

Solution

1. Step 1: Understand pool size and max overflow

   sql_alchemy_pool_size sets base DB connections; max_overflow allows extra temporary connections beyond pool size.

2. Step 2: Tune for heavy load

   Decreasing pool size but increasing max_overflow lets Airflow handle bursts by opening extra connections temporarily.

3. Step 3: Evaluate other options

   Setting both to zero disables pooling (bad), increasing pool size but reducing overflow limits burst handling, disabling pooling hurts performance.

4. Final Answer:

   Decrease sql_alchemy_pool_size and increase sql_alchemy_max_overflow -> Option C

5. Quick Check:

   Pool size low + max overflow high = better burst handling [OK]

Hint: Use small pool + high overflow for burst DB load [OK]

Common Mistakes:

• Setting both pool size and overflow to zero disables pooling
• Increasing pool size but limiting overflow reduces flexibility
• Disabling pooling hurts DB performance