Snapshot Backfill Performance Analysis¶

Goal¶

Reduce full 2007-10 → 2026-03 backfill time from ~5h 50m to 1–2 hours.

Baseline (Run: 2026-03-15)¶

Full run: time python run_snapshot_backfill.py → 5h 49m 38s (20,978 seconds)

Aggregated timing from stats.job_log (all 269 monthly runs)¶

Per-month detail: May 2016 (dense month, used as benchmark)¶

Root Cause Analysis¶

#1 — RBAR (Row-By-Agonizing-Row) in fn_snapshot_waypoints and fn_snapshot_cache_visits¶

71% of total runtime comes from these two functions.

Both functions use a FOR loop iterating over each touched waypoint one at a time:

-- fn_snapshot_waypoints (simplified)
FOR v_waypoint_code IN SELECT tw.waypoint_code FROM tmp ORDER BY waypoint_code LOOP
  -- Window function query per waypoint → INSERT into stats.waypoints
  WITH ranked_waypoint AS (SELECT ... FROM gk_moves WHERE UPPER(BTRIM(waypoint)) = v_waypoint_code)
  INSERT INTO stats.waypoints ... ON CONFLICT ... DO UPDATE;
END LOOP;

-- fn_snapshot_cache_visits (simplified)
FOR v_waypoint IN SELECT tw.id, tw.waypoint_code FROM tmp ORDER BY waypoint_code LOOP
  INSERT INTO stats.gk_cache_visits ... FROM gk_moves WHERE UPPER(BTRIM(waypoint)) = v_waypoint.code;
  INSERT INTO stats.user_cache_visits ... FROM gk_moves WHERE UPPER(BTRIM(waypoint)) = v_waypoint.code;
END LOOP;

For May 2016: 7,325 waypoints are touched → 7,325 iterations of each loop:

• fn_snapshot_cache_visits: 7,325 × 2 INSERTs = 14,650 individual queries → 91s → 6.2ms/query

• fn_snapshot_waypoints: 7,325 iterations → 73s → 10ms/iteration

Per-iteration overhead breakdown:

• PL/pgSQL executor overhead: ~0.5–1ms per iteration
• Index scan (using idx_gk_moves_waypoint_code_hist): ~0.3ms per iteration
• Individual INSERT into indexed table: ~5ms per iteration (buffer flush, index maintenance)

Note: The functional index idx_gk_moves_waypoint_code_hist on UPPER(BTRIM(waypoint)) already exists, so index scans are already efficient. The bottleneck is per-iteration overhead + individual INSERT overhead.

#2 — Serial execution of independent monthly phases¶

The 8 monthly phases run sequentially but only fn_backfill_heavy_previous_move_id_all is truly dependent (writes previous_move_id). All other 7 phases write to distinct tables and can run in parallel.

For May 2016 with estimated optimized times:

• Serial: ~30s
• Parallel (critical path = fn_snapshot_relationship_tables): ~11.5s

#3 — fn_normalize_country_code is VOLATILE (prevents parallelism)¶

geokrety.fn_normalize_country_code is declared VOLATILE despite being a pure computation. This prevents:

• PostgreSQL from executing aggregates in parallel
• Creation of functional indexes on the country code

Optimization Plan¶

OPT-1: Eliminate RBAR in fn_snapshot_waypoints¶

Strategy: Replace the FOR loop with a single set-based SQL using period-only moves.

Key insight: The original function queries ALL history for each touched waypoint to find the "best" representative (lat/lon/country). But because: 1. The ON CONFLICT clause already uses COALESCE to preserve existing good data 2. The backfill processes months in chronological order 3. Earlier periods have already updated stats.waypoints with historical data

→ We only need to process the current period's moves. The COALESCE in ON CONFLICT will preserve any better data from previous runs.

New approach:

WITH ranked AS (
  SELECT tw.waypoint_code, lat, lon, country, moved_on_datetime AS first_seen_at,
    ROW_NUMBER() OVER (PARTITION BY tw.waypoint_code ORDER BY ...) AS rn
  FROM geokrety.gk_moves m
  JOIN tmp_snapshot_touched_waypoints tw ON tw.waypoint_code = UPPER(BTRIM(m.waypoint))
  WHERE m.moved_on_datetime >= v_period_start AND m.moved_on_datetime < v_period_end
    AND m.waypoint IS NOT NULL AND BTRIM(m.waypoint) <> '' AND m.move_type <> 2
)
INSERT INTO stats.waypoints (...) SELECT ... FROM ranked WHERE rn = 1
ON CONFLICT (waypoint_code) DO UPDATE SET ...;

Expected speedup: From 73s to < 2s (period has ~10K–30K rows vs full 6.9M; hash joins trivial)

OPT-2: Eliminate RBAR in fn_snapshot_cache_visits¶

Strategy: Replace the FOR loop with two CROSS JOIN LATERAL queries.

LATERAL forces a nested-loop join which reuses the existing functional index per waypoint, but eliminates: 1. PL/pgSQL per-iteration overhead 2. Individual INSERT overhead (single bulk INSERT instead)

-- Replace loop with LATERAL-based bulk INSERT:
INSERT INTO stats.gk_cache_visits (gk_id, waypoint_id, visit_count, first_visited_at, last_visited_at)
SELECT ld.geokret, tw.id, ld.visit_count, ld.first_visited_at, ld.last_visited_at
FROM tmp_snapshot_touched_waypoints tw
CROSS JOIN LATERAL (
  SELECT m.geokret, COUNT(*)::BIGINT, MIN(m.moved_on_datetime), MAX(m.moved_on_datetime)
  FROM geokrety.gk_moves m
  WHERE m.waypoint IS NOT NULL AND BTRIM(m.waypoint) <> '' AND m.move_type <> 2
    AND UPPER(BTRIM(m.waypoint)) = tw.waypoint_code
  GROUP BY m.geokret
) ld;

Expected speedup: From 91s to ~10s (LATERAL uses same functional index, eliminates 14,650 individual inserts)

OPT-3: Mark fn_normalize_country_code as IMMUTABLE¶

Change from VOLATILE to IMMUTABLE STRICT PARALLEL SAFE. This function is a pure computation (BTRIM + UPPER + length check) with no side effects.

Enables:

• Parallel aggregate queries in fn_snapshot_daily_country_stats, fn_snapshot_user_country_stats, fn_snapshot_gk_country_stats
• Functional indexes on fn_normalize_country_code(country)

Expected speedup: ~30–40% reduction for the three country stats functions (parallelism enabled)

OPT-4: Parallel execution of monthly phases in Python script¶

Run all 7 independent monthly phases concurrently (they write to disjoint tables).

Dependency constraint: fn_backfill_heavy_previous_move_id_all must complete first (writes previous_move_id), then all others can run in parallel.

Expected speedup: Per-month time drops from ~30s (optimized-serial) to ~11.5s (parallel, critical path = fn_snapshot_relationship_tables).

Benchmark Results¶

Baseline (2026-03-15, before any optimization)¶

After OPT-1 + OPT-2 — Migration 20260316110000_optimize_snapshot_rbar_functions.php¶

Per-function comparison: May 2016¶

Average per-function times (59 month runs across multiple years)¶

Full run timing by year (serial, after OPT-1+2)¶

Projected full run serial (2007–2026): ~7,000–8,500s ≈ 2–2.5h

After OPT-1 + OPT-2 + OPT-4 — Parallel Python phases¶

Full run timing by year (parallel)¶

Projected full run parallel (2007–2026): ~3,000–3,500s ≈ 50–58 min ✅

Overall speedup summary (vs original baseline)¶

Goal achieved: Full run projected at under 1 hour (target was 1–2 hours).

Optimizations Applied¶

OPT-3 (fn_normalize_country_code IMMUTABLE): Not needed — DBA review confirmed the function is already IMMUTABLE STRICT. The remaining 3 country-stats functions are already set-based and PostgreSQL-level parallel-safe.

Final full backfill — Verified run (2026-03-16)¶

The final full backfill was executed and completed successfully. Key facts from the run and verification:

• Completed steps: 1777

• Wall time (real): 00:46:42.538 (46m42s)

• Throughput: 2282.8 steps/hour (measured)

Recent stats.job_log entries (last 2 hours) grouped by job_name and status:

No error status entries were observed in the recent stats.job_log window — the run completed without logged failures.

Log file (runtime events): docs/database-refactor/run_snapshot_backfill.log — contains per-phase start/done lines with wall time and job_log timing for each phase.

Resume and continuation timing (2026-03-16)¶

The runner now persists one completion marker per exact phase/slice step in stats.job_log under job_name = 'run_snapshot_backfill_step'. The run_key includes the resolved request window, current source bounds, batch size, parallel mode, replica-role mode, and --skip-entity-counters, so the same command with unchanged source bounds can continue or short-circuit safely.

Validation notes:

• The exact serial rerun printed Plan items: 0 and exited with Nothing to execute. in 0.14s.
• The exact parallel rerun printed Plan items: 0 and exited with Nothing to execute. in 0.12s.
• The parallel one-month run wrote 12 runner-owned markers and 12 distinct step_key values for the resolved run_key, proving one marker per completed step with no duplicates.
• A global duplicate audit returned 0 duplicate step_key rows across all run_snapshot_backfill_step markers.

Operational consequence: the historical backfill can now be stopped and restarted with the same parameters without replaying already-completed steps, which removes the previous restart penalty for long recovery runs.