The metadata DB stores Airflow internal state: DAG runs, task instances, states, connections, variables, XCom metadata.
Production: PostgreSQL / MySQL. SQLite: local learning only.
It becomes a bottleneck with thousands of DAGs, millions of task instances, heavy scheduler traffic, and many UI users. Treat it like a critical production database: size it, tune it, monitor it, and back it up.