Как посчитать App Startup Time в SQL

Зачем App Startup Time

Startup time = время от tap iconки до interactive screen. < 2 sec — good. > 5 sec — кratoxодит users. Cold start (no cache) обычно 2-3x slower warm.

Cold vs Warm start

Базовый расчёт

SELECT
    start_type,
    COUNT(*) AS launches,
    AVG(startup_ms) AS avg_ms,
    PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY startup_ms) AS p50_ms,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY startup_ms) AS p95_ms,
    PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY startup_ms) AS p99_ms
FROM app_launches
WHERE launch_time >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY start_type
ORDER BY start_type;

По device class

SELECT
    device_tier,  -- low/mid/high-END
    device_os,
    COUNT(*) AS launches,
    PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY startup_ms) FILTER (WHERE start_type = 'cold') AS p50_cold,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY startup_ms) FILTER (WHERE start_type = 'cold') AS p95_cold,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY startup_ms) FILTER (WHERE start_type = 'warm') AS p95_warm
FROM app_launches
WHERE launch_time >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY device_tier, device_os
ORDER BY p95_cold DESC;

Low-tier Android — typically worst startup.

Trend по app version

SELECT
    app_version,
    COUNT(*) AS launches,
    PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY startup_ms) FILTER (WHERE start_type = 'cold') AS p50_cold,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY startup_ms) FILTER (WHERE start_type = 'cold') AS p95_cold
FROM app_launches
WHERE launch_time >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY app_version
HAVING COUNT(*) >= 1000
ORDER BY app_version DESC;

Watch regression на new releases.

SLO check

«95% of cold starts < 3 seconds»:

SELECT
    DATE_TRUNC('day', launch_time) AS day,
    COUNT(*) AS launches,
    COUNT(*) FILTER (WHERE startup_ms < 3000) AS fast_launches,
    COUNT(*) FILTER (WHERE startup_ms < 3000)::NUMERIC * 100 / COUNT(*) AS slo_pct
FROM app_launches
WHERE start_type = 'cold'
  AND launch_time >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY 1
ORDER BY 1;

Startup → retention

WITH first_launch AS (
    SELECT user_id, startup_ms
    FROM app_launches
    WHERE start_type = 'cold'
      AND launch_time >= CURRENT_DATE - INTERVAL '60 days'
    QUALIFY ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY launch_time) = 1
),
retained AS (
    SELECT DISTINCT user_id
    FROM app_launches
    WHERE launch_time::DATE >= CURRENT_DATE - INTERVAL '14 days'
)
SELECT
    CASE
        WHEN startup_ms < 2000 THEN 'fast (<2s)'
        WHEN startup_ms < 4000 THEN 'medium (2-4s)'
        ELSE 'slow (>4s)'
    END AS startup_bucket,
    COUNT(fl.user_id) AS first_launchers,
    COUNT(r.user_id) AS retained,
    COUNT(r.user_id)::NUMERIC * 100 / COUNT(fl.user_id) AS retention_pct
FROM first_launch fl
LEFT JOIN retained r USING (user_id)
GROUP BY startup_bucket
ORDER BY MIN(startup_ms);

Slow first startup → lower retention.

Частые ошибки

Ошибка 1. Cold / warm не distinguished. Aggregated startup time misleading. Always split.

Ошибка 2. Time-to-interactive vs main display. «App shows splash» vs «User can tap». Different metrics.

Ошибка 3. Device variance. P95 на iPhone 6 ≠ iPhone 15. Bucket by tier.

Ошибка 4. Background pre-launch. iOS / Android can pre-launch app — measured startup artificially low.

Ошибка 5. Aggressive caching hides issues. Cached → fast warm. But first install (no cache) — slow.

Связанные темы

• Как посчитать crash rate в SQL
• Как посчитать API latency в SQL
• Как посчитать app uninstall rate в SQL
• Как посчитать перцентили в SQL

FAQ

Какой startup time ok?

Cold < 2s — excellent. < 3s — good. > 5s — issue.

Cold vs Warm metric?

Cold более critical (first impression). Warm less significant.

TTI vs TTID?

TTID = Time to Initial Display. TTI = Time to Interactive (user can tap). TTI больше.

Slow startup на cheap devices?

Bucket by device tier. Different SLOs.

Improve startup как?

1. Lazy load. 2) Code splitting. 3) Optimize images. 4) Reduce dependencies. 5) Async init.