fix(tradein/snapshot): починить зависающий запрос снапшотов и бюджет времени (#2607) #2618

Merged

lekss361 merged 1 commit from fix/tradein-snapshot-query-perf into main

2026-08-02 09:19:21 +00:00

lekss361 commented

2026-08-02 08:57:37 +00:00

Owner

Summary

Issue #2607 — listing_source_snapshot (ежедневный per-source price-history snapshot) висел ночь за ночью минимум с 19 июля: scrape_runs каждый раз добирал до статуса zombie ровно через 6h (порог zombie-детектора), а backend в Postgres продолжал реально выполнять запрос ещё сутками (46h/22h/31h/7h — снимал руками pg_terminate_backend, но появлялись новые). Данные не писались (max(snapshot_date) застрял на 2026-07-29), а осиротевшие транзакции держали backend_xmin, блокируя autovacuum на listings/listing_sources (14-16% мёртвых кортежей).

Root cause (EXPLAIN на проде, planner-only, БЕЗ ANALYZE)

Event-diff CTE (app/tasks/listing_source_snapshot.py) джойнил today (снимок за CURRENT_DATE) с prior — DISTINCT ON (listing_source_id) ... FROM listing_source_snapshots WHERE snapshot_date < CURRENT_DATE по всей таблице (~2.6-2.8M строк, 81 387 distinct listing_source_id) — обычным JOIN.

Планировщик оценивает snapshot_date = CURRENT_DATE в 1 строку (свежевставленные в ЭТОЙ ЖЕ транзакции строки статистика ANALYZE ещё не видела — CURRENT_DATE всегда за пределами гистограммы) → выбирает Nested Loop без Materialize на внутренней стороне:

Insert on listing_source_events  (cost=0.86..299785.58 rows=0 width=0)
  ->  Nested Loop  (cost=0.86..299785.58 rows=1 width=136)
        ->  Index Scan using idx_lss_snapshot_date  (cost=0.43..4.56 rows=1 width=16)
              Index Cond: (snapshot_date = CURRENT_DATE)
        ->  Subquery Scan on p  (cost=0.43..298627.09 rows=76927 width=16)
              ->  Unique  (cost=0.43..297655.81 rows=77702 width=20)
                    ->  Index Scan using idx_lss_source_date  (cost=0.43..290980.55 rows=2670104 width=20)
                          Index Cond: (snapshot_date < CURRENT_DATE)

Реально today — это ~80-140k строк (весь listing_sources), а не 1. Каждая строка today заново пересчитывает DISTINCT ON по всей таблице (Index Scan + Unique над 2.67M строк, cost≈298 627) — экспоненциальный разгон, который никогда не завершался ни за 6h, ни за сутки.

listing_source_snapshots: 370 MB (table only) + ~265 MB индексов, 2 784 049 строк / 81 387 distinct sources, work_mem=4MB (не трогал — вне scope).

Fix

app/tasks/listing_source_snapshot.py — prior-CTE переписан на JOIN LATERAL (... WHERE s.listing_source_id = t.listing_source_id AND s.snapshot_date < CURRENT_DATE ORDER BY s.snapshot_date DESC LIMIT 1) ON true. Форсирует per-row индексный point-lookup через idx_lss_source_date (listing_source_id, snapshot_date DESC) вместо полного скана таблицы. Семантика идентична (PK (listing_source_id, snapshot_date) исключает дубликаты — "последний снимок до сегодня" тот же самый). EXPLAIN на проде после фикса:

Insert on listing_source_events  (cost=0.86..9.00 rows=0 width=0)
  ->  Nested Loop  (cost=0.86..9.00 rows=1 width=136)
        ->  Index Scan using idx_lss_snapshot_date  (cost=0.43..4.56 rows=1 width=16)
        ->  Subquery Scan on p  (cost=0.43..4.41 rows=1 width=8)
              ->  Limit  (cost=0.43..4.39 rows=1 width=12)
                    ->  Index Scan using idx_lss_source_date  (cost=0.43..135.07 rows=34 width=12)
                          Index Cond: (listing_source_id = ... AND snapshot_date < CURRENT_DATE)

Cost внутреннего подзапроса упал с ~298 627 до ~4.4 за строку today — при ~80-140k строк это несколько сотен тысяч cost-юнитов суммарно на чистых индексных lookup'ах (не полных сканах), т.е. ожидаемое время выполнения — низкие единицы-десятки секунд вместо суток. Не гонял ANALYZE на проде (запрещено правилами задачи — реальный запрос может повесить базу), оценка построена по planner-cost и характеру плана (индексные point-lookup вместо full-table Unique-скана).

budget_sec → SET LOCAL statement_timeout (defense-in-depth, по образцу run_geocode_missing_listings/budget_sec). Задача не батчится Python-циклом (два set-based statement'а), поэтому единственный надёжный способ прервать зависший statement — Postgres-нативный statement_timeout, выставленный SET LOCAL (per-transaction scope, не трогает server/role-level — issue #2607 п.2 остаётся отдельным решением). SET не принимает bind-параметр ($1/:name даёт syntax error — проверено вживую), поэтому значение подставляется как provalidated/clamp'нутый ([30, 3600] сек, default 900) int, источник — scrape_schedules.default_params, не user input.

data/sql/202_listing_source_snapshot_budget_sec.sql — UPDATE scrape_schedules SET default_params = default_params || '{"budget_sec": 900}'::jsonb WHERE source = 'listing_source_snapshot'. Идемпотентно, dry-run синтаксис проверен на проде внутри BEGIN; ... ROLLBACK; (без побочных эффектов).
app/services/product_handlers.py — _job_listing_source_snapshot теперь прокидывает params в snapshot_listing_sources (раньше игнорировались).

Зомби-детектор (issue #2607 п.3-4) — ОТЛОЖЕНО, не в этом PR

reap_zombies (scraper_kit/orchestration/scheduler.py) только помечает scrape_runs.status='zombie' — не убивает backend в Postgres. Корень симптомов 3-4 (осиротевшие backend'ы живут сутками, держат backend_xmin, блокируют autovacuum). Добавление pg_terminate_backend потребовало бы хранить pid/application_name прогона — этого нет в схеме scrape_runs (проверено: \d scrape_runs на проде, нет соответствующих колонок). Это кросс-катный (затрагивает _dispatch/_claim_run/create_run/reap_zombies — общий код ВСЕХ job-хендлеров, не только этого), требует pid-reuse-safe дизайна (сверка backend_start timestamp, иначе можно убить не тот backend) — отдельный follow-up PR, не раздуваю эту задачу. Теоретически можно обойтись без ALTER TABLE (pid можно писать в уже существующую counters/params jsonb), но сам объём изменений — за рамками этого фикса.

С этим PR риск снижается сам по себе: запрос теперь укладывается в секунды, а budget_sec гарантирует честный mark_failed вместо зависания, даже если что-то ещё разрегрессирует план.

Test plan

pytest tradein-mvp/backend — 3059 passed, 9 skipped, 1 pre-existing fail (test_search_api.py::test_search_cache_hit, 401 RBAC — известен, не в scope)
ruff check на изменённые файлы — чисто
EXPLAIN (planner-only, без ANALYZE) до/после на проде — см. выше
Migration dry-run на проде внутри BEGIN;...ROLLBACK; — синтаксис и результат проверены, без побочных эффектов
Post-deploy: проверить scrape_runs следующей ночью (окно 01:00-02:00 UTC) — status='done', не zombie; listing_source_snapshots.max(snapshot_date) продвинулся

Refs #2607

## Summary Issue #2607 — `listing_source_snapshot` (ежедневный per-source price-history snapshot) висел ночь за ночью минимум с 19 июля: `scrape_runs` каждый раз добирал до статуса `zombie` ровно через 6h (порог zombie-детектора), а backend в Postgres продолжал реально выполнять запрос ещё **сутками** (46h/22h/31h/7h — снимал руками `pg_terminate_backend`, но появлялись новые). Данные не писались (`max(snapshot_date)` застрял на 2026-07-29), а осиротевшие транзакции держали `backend_xmin`, блокируя autovacuum на `listings`/`listing_sources` (14-16% мёртвых кортежей). ## Root cause (EXPLAIN на проде, planner-only, БЕЗ ANALYZE) Event-diff CTE (`app/tasks/listing_source_snapshot.py`) джойнил `today` (снимок за `CURRENT_DATE`) с `prior` — `DISTINCT ON (listing_source_id) ... FROM listing_source_snapshots WHERE snapshot_date < CURRENT_DATE` по всей таблице (~2.6-2.8M строк, 81 387 distinct `listing_source_id`) — обычным `JOIN`. Планировщик оценивает `snapshot_date = CURRENT_DATE` в **1 строку** (свежевставленные в ЭТОЙ ЖЕ транзакции строки статистика ANALYZE ещё не видела — `CURRENT_DATE` всегда за пределами гистограммы) → выбирает `Nested Loop` **без `Materialize`** на внутренней стороне: ``` Insert on listing_source_events (cost=0.86..299785.58 rows=0 width=0) -> Nested Loop (cost=0.86..299785.58 rows=1 width=136) -> Index Scan using idx_lss_snapshot_date (cost=0.43..4.56 rows=1 width=16) Index Cond: (snapshot_date = CURRENT_DATE) -> Subquery Scan on p (cost=0.43..298627.09 rows=76927 width=16) -> Unique (cost=0.43..297655.81 rows=77702 width=20) -> Index Scan using idx_lss_source_date (cost=0.43..290980.55 rows=2670104 width=20) Index Cond: (snapshot_date < CURRENT_DATE) ``` Реально `today` — это ~80-140k строк (весь `listing_sources`), а не 1. Каждая строка `today` заново пересчитывает `DISTINCT ON` по всей таблице (Index Scan + Unique над 2.67M строк, cost≈298 627) — экспоненциальный разгон, который никогда не завершался ни за 6h, ни за сутки. `listing_source_snapshots`: 370 MB (table only) + ~265 MB индексов, 2 784 049 строк / 81 387 distinct sources, `work_mem=4MB` (не трогал — вне scope). ## Fix 1. **`app/tasks/listing_source_snapshot.py`** — `prior`-CTE переписан на `JOIN LATERAL (... WHERE s.listing_source_id = t.listing_source_id AND s.snapshot_date < CURRENT_DATE ORDER BY s.snapshot_date DESC LIMIT 1) ON true`. Форсирует per-row индексный point-lookup через `idx_lss_source_date (listing_source_id, snapshot_date DESC)` вместо полного скана таблицы. Семантика идентична (PK `(listing_source_id, snapshot_date)` исключает дубликаты — "последний снимок до сегодня" тот же самый). EXPLAIN на проде после фикса: ``` Insert on listing_source_events (cost=0.86..9.00 rows=0 width=0) -> Nested Loop (cost=0.86..9.00 rows=1 width=136) -> Index Scan using idx_lss_snapshot_date (cost=0.43..4.56 rows=1 width=16) -> Subquery Scan on p (cost=0.43..4.41 rows=1 width=8) -> Limit (cost=0.43..4.39 rows=1 width=12) -> Index Scan using idx_lss_source_date (cost=0.43..135.07 rows=34 width=12) Index Cond: (listing_source_id = ... AND snapshot_date < CURRENT_DATE) ``` Cost внутреннего подзапроса упал с ~298 627 до ~4.4 **за строку** today — при ~80-140k строк это несколько сотен тысяч cost-юнитов суммарно на чистых индексных lookup'ах (не полных сканах), т.е. ожидаемое время выполнения — низкие единицы-десятки секунд вместо суток. Не гонял ANALYZE на проде (запрещено правилами задачи — реальный запрос может повесить базу), оценка построена по planner-cost и характеру плана (индексные point-lookup вместо full-table Unique-скана). 2. **budget_sec → `SET LOCAL statement_timeout`** (defense-in-depth, по образцу `run_geocode_missing_listings`/`budget_sec`). Задача не батчится Python-циклом (два set-based statement'а), поэтому единственный надёжный способ прервать зависший statement — Postgres-нативный `statement_timeout`, выставленный `SET LOCAL` (per-transaction scope, **не трогает** server/role-level — issue #2607 п.2 остаётся отдельным решением). `SET` не принимает bind-параметр (`$1`/`:name` даёт `syntax error` — проверено вживую), поэтому значение подставляется как provalidated/clamp'нутый (`[30, 3600]` сек, default 900) int, источник — `scrape_schedules.default_params`, не user input. `data/sql/202_listing_source_snapshot_budget_sec.sql` — `UPDATE scrape_schedules SET default_params = default_params || '{"budget_sec": 900}'::jsonb WHERE source = 'listing_source_snapshot'`. Идемпотентно, dry-run синтаксис проверен на проде внутри `BEGIN; ... ROLLBACK;` (без побочных эффектов). 3. **`app/services/product_handlers.py`** — `_job_listing_source_snapshot` теперь прокидывает `params` в `snapshot_listing_sources` (раньше игнорировались). ## Зомби-детектор (issue #2607 п.3-4) — ОТЛОЖЕНО, не в этом PR `reap_zombies` (`scraper_kit/orchestration/scheduler.py`) только помечает `scrape_runs.status='zombie'` — не убивает backend в Postgres. Корень симптомов 3-4 (осиротевшие backend'ы живут сутками, держат `backend_xmin`, блокируют autovacuum). Добавление `pg_terminate_backend` потребовало бы хранить `pid`/`application_name` прогона — **этого нет в схеме `scrape_runs`** (проверено: `\d scrape_runs` на проде, нет соответствующих колонок). Это кросс-катный (затрагивает `_dispatch`/`_claim_run`/`create_run`/`reap_zombies` — общий код ВСЕХ job-хендлеров, не только этого), требует pid-reuse-safe дизайна (сверка `backend_start` timestamp, иначе можно убить не тот backend) — отдельный follow-up PR, не раздуваю эту задачу. Теоретически можно обойтись без ALTER TABLE (pid можно писать в уже существующую `counters`/`params` jsonb), но сам объём изменений — за рамками этого фикса. С этим PR риск снижается сам по себе: запрос теперь укладывается в секунды, а budget_sec гарантирует честный `mark_failed` вместо зависания, даже если что-то ещё разрегрессирует план. ## Test plan - [x] `pytest tradein-mvp/backend` — 3059 passed, 9 skipped, 1 pre-existing fail (`test_search_api.py::test_search_cache_hit`, 401 RBAC — известен, не в scope) - [x] `ruff check` на изменённые файлы — чисто - [x] EXPLAIN (planner-only, без ANALYZE) до/после на проде — см. выше - [x] Migration dry-run на проде внутри `BEGIN;...ROLLBACK;` — синтаксис и результат проверены, без побочных эффектов - [ ] Post-deploy: проверить `scrape_runs` следующей ночью (окно 01:00-02:00 UTC) — `status='done'`, не `zombie`; `listing_source_snapshots.max(snapshot_date)` продвинулся Refs #2607

lekss361 added 1 commit 2026-08-02 08:57:37 +00:00

fix(tradein/snapshot): починить зависающий запрос снапшотов и добавить бюджет времени (#2607 )

CI Trade-In / changes (pull_request) Successful in 8s

Details

CI / changes (pull_request) Successful in 8s

Details

CI Trade-In / frontend-checks (pull_request) Has been skipped

Details

CI / backend-tests (pull_request) Has been skipped

Details

CI / frontend-tests (pull_request) Has been skipped

Details

CI / openapi-codegen-check (pull_request) Has been skipped

Details

CI Trade-In / backend-tests (pull_request) Successful in 2m37s

Details

b586b5ff68

Root cause: event-diff CTE джойнил "today" (снимок за CURRENT_DATE) с "prior"
(DISTINCT ON по всей listing_source_snapshots, ~2.6-2.8M строк) обычным JOIN.
Планировщик оценивал today в 1 строку (свежевставленные в той же транзакции
строки ANALYZE ещё не видел) → Nested Loop без Materialize пересчитывал
DISTINCT ON по всей таблице заново на каждую из ~80-140k реальных строк today
(EXPLAIN на проде: cost≈300k на этом шаге) — прогон не укладывался ни в 6h
zombie-порог, ни в сутки, каждую ночь минимум с 19 июля.

Переписано на JOIN LATERAL (per-row indexed point-lookup через idx_lss_source_date,
cost упал до ~4.4/строку). Плюс budget_sec → SET LOCAL statement_timeout как
defense-in-depth (по образцу geocode_missing_listings) — задача теперь честно
падает в mark_failed вместо того чтобы висеть сутками, если план когда-нибудь
разрегрессирует снова.

Зомби-детектор (reap_zombies) не тронут — он только помечает scrape_runs.status,
не убивает backend (нет pid/application_name в схеме run'а); pg_terminate_backend
для этого — отдельный follow-up, не в этом PR.