[Bug] PostgreSQL session activity query runs for minutes and saturates CPU with large event_data tables

Describe the Bug

Summary

On 2026-09-09, we investigated a production PostgreSQL CPU incident and traced sustained CPU consumption to Umami's PostgreSQL getSessionActivity query. Opening a session profile can run this query for several minutes. Two concurrent executions consumed approximately two CPU cores in a controlled reproduction.

The problematic expression checks each event's hasData flag against a site/date-wide IN (SELECT website_event_id FROM event_data ...) subquery. With the default 4 MB work_mem and our data distribution, PostgreSQL chose a sequential scan plus Materialize rather than a hashed subplan. A correlated EXISTS using the already-existing event_data_website_event_id_idx avoided that plan and was dramatically faster.

There is also a surprising UI amplification: the Events page was set to Last 24 hours, but clicking a session avatar requested approximately 20 days of session activity, using the session's firstAt/lastAt. The list's short date range therefore did not protect this query from scanning a much larger range.

Environment and data scale

• Runtime-confirmed Umami version: 3.2.0, read from /app/package.json in the running container.
• Image: ghcr.io/umami-software/umami:latest, already running for approximately five weeks. The tag name was not used to infer the runtime version.
• Self-hosted Docker, administered through 1Panel; PostgreSQL backend, no ClickHouse/read replica in this deployment.
• PostgreSQL 16.14, pgvector/pgvector:pg16 image; Linux x86_64.
• Host: 4 vCPUs, approximately 15 GiB RAM. PostgreSQL's cgroup had no CPU quota configured.
• Initial settings: work_mem=4MB, hash_mem_multiplier=2, shared_buffers=128MB, max_connections=100; no role/database statement timeout override.
• Approximate row counts from PostgreSQL statistics: event_data 6.88 million, website_event 443,000. These are estimates, not exact COUNT(*) results.
• event_data total relation size including indexes: about 1.7 GiB.
• Relevant indexes already existed: event_data(website_event_id), event_data(website_id, created_at), and website_event(website_id, session_id, created_at).

Incident sequence and impact

1. We initially observed a dashboard CPU reading of 100% and high PostgreSQL resource usage.
2. Inspection of pg_stat_activity found two instances of the same Umami session-activity SQL, already executing for approximately 6m22s and 5m18s. They were active, with no reported wait event and no blocking PIDs. Most connections to the separate primary application database were idle.
3. During a quiet window, we reproduced the query with bounded, read-only tests: one execution and then two concurrent executions, with a 15-second statement timeout. All original-query executions reached that timeout.
4. Per-process CPU counter deltas showed approximately 100.8% of one core for the single execution and 98.4% / 98.5% for the two concurrent executions. CPU promptly fell after the queries stopped.
5. We traced the actual browser action and API call described below. Closing the session-profile dialog did not stop the already-running database query in our observed test; we canceled that specific test query separately.
6. We applied a database/role-scoped temporary memory/timeout mitigation, restarted only Umami to recycle its connections, and confirmed the previously stalled profile loaded successfully.

Scope of the CPU conclusion: the original 100% dashboard reading was the incident symptom, not proof that two queries exhausted the whole 4-core host. Our controlled two-query test averaged 58.1% whole-host CPU, with PostgreSQL accounting for about two cores. We did not reproduce 100% whole-host CPU with just two queries. We are reporting a reproducible CPU-heavy query, not claiming a measured primary-application outage or a PostgreSQL memory leak.

Exact UI/API trigger

Events → Activity log → click an avatar in the Session column → session profile → default Activity log tab.

The browser called:

GET /api/websites/{websiteId}/sessions/{sessionId}/activity?startAt=...&endAt=...

For the profile tested, firstAt to lastAt covered roughly 20 days, despite Last 24 hours being selected on the underlying Events page. This range was confirmed from the network request, not inferred from the dropdown.

In v3.2.0, the call chain is:

SessionProfile.tsx (startDate=data.firstAt, endDate=data.lastAt)
  → SessionActivity.tsx
  → useSessionActivityQuery.ts
  → api/websites/[websiteId]/sessions/[sessionId]/activity/route.ts
  → queries/sql/sessions/getSessionActivity.ts

Source references:

• v3.2.0 query
• v3.2.0 SessionProfile
• v3.2.0 request hook

Reproduction

Use a test copy with similar event-data volume/distribution. A small empty installation may not select the same plan.

1. Use PostgreSQL with work_mem=4MB and hash_mem_multiplier=2.
2. Have a site with several million event_data rows over a 20–30 day period, and a session with at least 500 events in that period.
3. Open the session profile through the UI above; inspect the activity request's actual startAt/endAt.
4. Observe pg_stat_activity and process CPU. Inspect the v3.2.0 SQL below with EXPLAIN; use EXPLAIN (ANALYZE, BUFFERS) only with an explicit statement timeout in a controlled environment.
5. Compare with the correlated EXISTS variant below, retaining the same parameters, date filters, output columns and LIMIT.

The following is the original query shape with parameter names normalized. Bind the placeholders to your test site/session and timestamps:

SELECT
  e.created_at AS "createdAt",
  e.url_path AS "urlPath",
  e.url_query AS "urlQuery",
  e.referrer_domain AS "referrerDomain",
  e.event_id AS "eventId",
  e.event_type AS "eventType",
  e.event_name AS "eventName",
  e.visit_id AS "visitId",
  e.hostname,
  e.event_id IN (
    SELECT d.website_event_id
    FROM event_data d
    WHERE d.website_id = :website_id
      AND d.created_at BETWEEN :start_at AND :end_at
  ) AS "hasData"
FROM website_event e
WHERE e.website_id = :website_id
  AND e.session_id = :session_id
  AND e.event_type <> 5
  AND e.created_at BETWEEN :start_at AND :end_at
ORDER BY e.created_at DESC
LIMIT 500;

Execution-plan evidence

In the controlled 30-day test, the original query's plan included:

Limit (cost=0.42..77527206.12 rows=500)
  -> Index Scan Backward using website_event_website_id_session_id_created_at_idx
       Filter: event_type <> 5
       SubPlan 1
         -> Materialize (cost=0.00..292940.97 rows=6865774 width=16)
              -> Seq Scan on event_data d
                   (cost=0.00..225087.10 rows=6865774 width=16)
                   Filter: website_id and created_at range

IDs and timestamps are omitted here. Plan costs/row estimates are not timings or measured comparison counts. The canceled original executions did not produce a completed EXPLAIN ANALYZE, so we do not claim an actual subplan loop count.

Our interpretation is that repeated membership checks against the materialized large subquery result are the expensive path. LIMIT 500 limits output events, not the amount of work needed to determine each hasData value. In the CPU tests, average whole-host I/O wait was only about 0.1–0.2%.

Controlled CPU comparison

The monitor read /proc/stat, per-backend /proc/{pid}/stat, and PostgreSQL's cgroup cpu.stat. Whole-host CPU and one-core-normalized container CPU are intentionally separate columns.

Load-phase averages cover about 16 seconds, including a short period after timeout. The 30-day tests used representative parameters; original incident bind values were unavailable. The later UI reproduction and mitigation test used the actual captured 20-day profile parameters. These are distinct samples.

Suggested SQL fix and measurements

Replace only the hasData expression with a correlated EXISTS, preserving the website/date predicates:

EXISTS (
  SELECT 1
  FROM event_data d
  WHERE d.website_event_id = e.event_id
    AND d.website_id = :website_id
    AND d.created_at BETWEEN :start_at AND :end_at
) AS "hasData"

With work_mem still at 4MB, the new plan used the existing event_data_website_event_id_idx with Index Cond: website_event_id = e.event_id. No new index was needed for this test.

For the same 30-day sample and 500-row projection, EXPLAIN ANALYZE execution times were 150.349ms, then 3.127ms and 3.169ms. The first run read 713 shared blocks; the later runs had 1,900 shared hits and no shared reads. The warm-cache numbers should not be presented as guaranteed cold-request latency.

For the actual 20-day UI sample, once the original IN query was made tractable with more work_mem, we executed both variants and compared all returned fields keyed by event ID: all 500 returned rows matched. This is sample validation, not a substitute for regression tests across all schemas/data shapes.

Expected: determining hasData for a limited event list should use event-specific lookups (or another bounded approach), without minute-long CPU consumption for ordinary profile navigation. The session-wide date behavior may be intentional, but it makes query efficiency especially important; the visible list date filter can otherwise be misleading.

Temporary production mitigation and verification

We tested the unmodified IN query with the actual 20-day browser parameters:

We then applied settings only to the Umami login in the Umami database (placeholder role/database below), and restarted only the Umami container to establish new connections:

ALTER ROLE umami_login IN DATABASE umami SET work_mem = '128MB';
ALTER ROLE umami_login IN DATABASE umami SET statement_timeout = '10s';

Afterward:

• Fresh Umami connections reported 128MB / 10s; connections to the separate application database retained 4MB / no timeout.
• The original SQL completed in 1,774.922ms, returning 500 rows.
• A low-CPU pg_sleep(11) test was canceled at 10.013s, confirming the server-side timeout actually applied.
• The real session activity endpoint returned HTTP 200, 500 rows, and completed in approximately 1.51s; the profile activity list rendered successfully.
• Recovery sampling showed approximately 3.62% whole-host CPU and 2.95% PostgreSQL CPU on the one-core scale.
• No data/index/schema changes or PostgreSQL restart were required.

This is a workaround, not a universal configuration recommendation: work_mem applies per operation and concurrent query, hash_mem_multiplier=2 increases hash budgets, larger ranges/data growth can change the plan again, and a 10s timeout also affects long reports/migrations. It does not impose a total CPU/concurrency limit. An efficient upstream SQL implementation is preferable to requiring large memory budgets for this UI action.

Version scope and follow-up

Runtime reproduction was on v3.2.0. We also inspected the v3.3.1 tag: getSessionActivity.ts still contains the site/date-wide PostgreSQL IN subquery, although its outer session predicate now supports multiple session IDs. We have not runtime-tested v3.3.1, so this is a source observation, not a claim that the exact same timings apply there.

Could the PostgreSQL implementation be changed to correlated EXISTS or another event-bounded approach, with regression coverage for large event_data tables? Request/database cancellation when a profile closes may also deserve review, independently of the SQL fix.

Production addresses, credentials, real website/session IDs, event names and user data are intentionally excluded from this public report.

Database

PostgreSQL

Relevant log output

Which Umami version are you using?

3.2.0 (runtime reproduced); v3.3.1 source inspected only

How are you deploying your application?

Official ghcr.io/umami-software/umami:latest Docker image, managed with 1Panel; PostgreSQL 16.14

Which browser are you using?

Google Chrome on macOS