hussein-awala commented on code in PR #64611: URL: https://github.com/apache/airflow/pull/64611#discussion_r3493624897
########## airflow-core/src/airflow/migrations/versions/0124_3_4_0_add_gin_index_on_asset_event_extra.py: ########## @@ -0,0 +1,57 @@ +# +# Licensed to the Apache Software Foundation (ASF) under one +# or more contributor license agreements. See the NOTICE file +# distributed with this work for additional information +# regarding copyright ownership. The ASF licenses this file +# to you under the Apache License, Version 2.0 (the +# "License"); you may not use this file except in compliance +# with the License. You may obtain a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, +# software distributed under the License is distributed on an +# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +# KIND, either express or implied. See the License for the +# specific language governing permissions and limitations +# under the License. + +""" +Add GIN index on asset_event.extra for PostgreSQL. + +Revision ID: 5a5d3253e946 +Revises: 9ff64e1c35d3 +Create Date: 2026-04-01 23:00:00.000000 + +""" + +from __future__ import annotations + +from alembic import op +from sqlalchemy import text + +# revision identifiers, used by Alembic. +revision = "5a5d3253e946" +down_revision = "9ff64e1c35d3" +branch_labels = None +depends_on = None +airflow_version = "3.3.0" + + +def upgrade(): + """Add GIN index on asset_event.extra for PostgreSQL only.""" + conn = op.get_bind() + if conn.dialect.name == "postgresql": + op.execute( + text( + "CREATE INDEX IF NOT EXISTS idx_asset_event_extra_gin " + "ON asset_event USING GIN ((extra::jsonb) jsonb_ops)" + ) + ) Review Comment: Good question — I benchmarked it instead of guessing. **TL;DR:** it doesn't take forever. Even an extreme 20M-event table with a very rich `extra` builds the index in ~2.7 min; realistic/mid-size tables are seconds. ### Setup / resources - PostgreSQL **14** (matches our CI) in Docker, **default config** — notably `maintenance_work_mem = 64MB` (the PG default; real servers usually have more, which makes large builds *faster*, so these numbers are conservative). - Host: **Apple M4 Max, 64 GB RAM** (a laptop — a dedicated DB server would do at least as well). - Index built exactly as the migration does: `CREATE INDEX ... USING GIN ((extra::jsonb) jsonb_ops)`. ### Method / data Synthetic `asset_event` rows where each row's `extra` is a JSON object with **N distinct keys**, keys drawn from a pool of 1,000 and values from a pool of 10,000. GIN with `jsonb_ops` indexes every key **and** value, so build cost ≈ `events × keys/event` (total indexed tokens). I also confirmed separately that cardinality (key/value pool size, up to 100k keys / 2M values) barely affects build *time* — it mainly grows index *size*. ### Build time — events × keys/event | Events | 5 keys | 10 keys | 20 keys | |---|---|---|---| | 1M | 1.7 s | 3.0 s | 7.6 s | | 5M | 6.7 s | 16.7 s | 53.6 s | | 10M | 13.3 s | 35.8 s | 76.6 s | | 20M | 25.9 s | 57.1 s | **159.4 s** | ### Table / index size (context, 20M rows) | keys/event | table | index | |---|---|---| | 5 | 3.7 GB | 207 MB | | 10 | 6.2 GB | 420 MB | | 20 | 11 GB | 864 MB | Query latency with the index stays sub-2ms throughout. ### Conclusion Scaling is ~linear (~1.3s per million rows per 5 keys/event). The absolute worst case I tested (20M events, 20 keys each, 11 GB table) is **~2.7 min** with the tiny default `maintenance_work_mem` — faster on a tuned server. Realistic deployments (a handful of keys per event) are single-digit to tens of seconds even at 10M+ events. The plain `CREATE INDEX` takes a `SHARE` lock (blocks writes) only for that bounded build window, which happens during `airflow db migrate` (scheduler/workers stopped) — consistent with our other large-table index migrations (`task_instance`, `dag_run`). So I'd propose keeping the plain `CREATE INDEX` rather than introducing `CREATE INDEX CONCURRENTLY` + `autocommit_block` (no precedent in our migrations, and it leaves an INVALID index if interrupted). Happy to switch to the concurrent approach if you'd prefer. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
