Things I don’t want
to debug twice.

Problem

After upgrading to Airflow 3.3.1, one of the DAG list API requests became unexpectedly slow. A request using dag_runs_limit=1 was taking roughly 3.7 seconds.

The interesting part was dag_runs_limit=1. Without it, the request was significantly cheaper. With it enabled, Airflow needed to retrieve the most recent DAG run for every DAG displayed in the UI.

Query pattern

The relevant database access was effectively:

SELECT ...
FROM dag_run
WHERE dag_id = ?
ORDER BY run_after DESC
LIMIT 1;

The database already had indexes covering the individual columns, but those indexes did not match the complete access pattern: filter by dag_id, then find the newest row by run_after.

Fix

A composite index matching the query was added:

CREATE INDEX idx_dag_run_dag_id_run_after
    ON dag_run (dag_id, run_after);

The important part is the column order: (dag_id, run_after). The database can locate rows for one DAG and immediately walk the index in run_after order.

Result

No frontend changes. No Airflow configuration changes. No caching workaround. The slowdown came from a database access pattern without an index matching it.

Why dag_runs_limit=1 exposed it

Asking for one DAG run sounds cheap, but the actual problem is repeated lookup: the newest run for every DAG in the result set.

WHERE dag_id = ?
ORDER BY run_after DESC
LIMIT 1

A query that is only slightly inefficient once becomes very visible when it is repeated across a page of DAGs inside a single API request.

Takeaway

When debugging slow endpoints, “the query has indexes” is not enough. The useful question is: does an index match the complete access pattern?

For queries shaped like WHERE a = ? ORDER BY b LIMIT 1, separate indexes on a and b may still be insufficient. A composite (a, b) index can make the difference.

In this case: ~3.7 s → < 0.7 s. From one index.

Problem

I needed to grant permission to reload dictionaries while limiting the privilege to only a couple of databases. The obvious attempt was to scope the grant:

GRANT SYSTEM RELOAD DICTIONARY
ON analytics.*
TO some_role;

That does not work, because this privilege is not defined at database or table level.

Check the privilege level

ClickHouse exposes privilege metadata through system.privileges. For SYSTEM RELOAD DICTIONARY, the privilege level is GLOBAL.

SYSTEM RELOAD DICTIONARY  →  GLOBAL

Correct grant

A global privilege must be granted globally:

GRANT SYSTEM RELOAD DICTIONARY
ON *.*
TO some_role;

Trying to replace *.* with analytics.* or events.* does not narrow the scope. It makes the grant invalid for this privilege.

Takeaway

Do not assume the ON database.* part of a ClickHouse GRANT can be used with every privilege. First check the privilege level.

If the privilege is GLOBAL, the scope is global too.

Problem

Our local Airflow setup used to install additional Python packages when the containers started, using runtime configuration such as:

_PIP_ADDITIONAL_REQUIREMENTS: >
  clickhouse-connect
  pytest
  pytest-mock
  freezegun

It was convenient, but startup now depended on package repositories, network access and dependency resolution. The image itself was no longer the complete description of the runtime.

The change

I moved the dependencies into a custom Airflow image:

FROM apache/airflow:3.0.6

COPY requirements.txt /requirements.txt

RUN pip install --no-cache-dir -r /requirements.txt

The important part was separating build from runtime:

build
  → install dependencies
  → immutable image

up
  → start existing containers

make up should start the environment. It should not silently build images, pull dependencies or install Python packages.

Production parity vs reproducibility

Keeping local development close to production is useful, but an exact copy is not always necessary. What matters more is that the runtime is predictable.

If two developers start the same image, they should get the same Python environment.

Result

Startup was not dramatically faster. The bigger improvement was that the environment stopped rebuilding itself every time it started.

Once the image existed locally, the stack could start without reaching package repositories or resolving dependencies again.

Takeaway

Runtime dependency installation is useful for experiments. For a development environment used every day, I prefer:

build once → run predictably

over:

start → download → install → hope

Reproducibility matters more than convenience.

Problem

An upstream task legitimately skipped when there was nothing to process. A downstream metrics task used trigger_rule="none_failed", so it was still allowed to run.

rows = load()
metrics(rows)

I initially handled the missing value inside the task:

rows = rows or 0

It did not help.

Why

The value passed between TaskFlow tasks is an XComArg. Before Airflow calls the Python function, it resolves that argument from XCom.

A skipped upstream task never pushed the return value, so resolution failed before the first line of metrics() executed.

Fix

Keep the dependency, but stop passing the upstream return value as a function argument:

load_result >> metrics()

Then inspect the upstream state explicitly and only read XCom when a return value should exist:

if load_state == "skipped":
    return

rows = ti.xcom_pull(
    task_ids="load",
    key="return_value",
) or 0

Takeaway

If an Airflow TaskFlow argument comes from XCom, handling None inside the task may already be too late.

Problem

A repository was created for every batch:

batch
  → repository
  → ClickHouse client
  → process
  → close

With around 74 batches, the pipeline created dozens of repository and client instances even though only five batches could run concurrently.

The resource lifetime was tied to the batch instead of the worker.

Why not share one client?

Using one repository for all threads was not safe either. The ClickHouse client maintained session state, so concurrent use from multiple threads could result in session-related errors.

The correct ownership boundary was:

one client per worker thread

Fix

Each worker keeps its own repository in thread-local storage and reuses it across batches.

before

74 batches
→ up to ~74 repositories / clients

after

5 workers
→ up to 5 reusable worker repositories

Clients were also made lazy, so a worker only opens a connection when it actually needs one. Worker resources are explicitly closed when processing finishes.

Result

Resource creation became bounded by worker count rather than batch count.

Load testing around the same path also exposed retained futures on error paths; fixing that lifecycle reduced observed peak memory growth from roughly 47 MiB to 9 MiB.

Takeaway

Limiting concurrency does not automatically limit resource creation.

The connection problem was not really a ClickHouse problem. It was an object-lifetime problem.