The short answer
The metadata DB stores Airflow internal state: runs, , 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.