Missing Index on events table

[TanishqPorwar]

🔴 Required Information

Describe the Bug:
When using DatabaseSessionService with PostgreSQL, the SELECT ... FROM events query used by get_session causes high CPU utilization on the database cluster. The root cause is a missing composite index -- PostgreSQL falls back to scanning the primary key index (events_pkey), which has id as its leading column and is ineffective for filtering by (app_name, user_id, session_id) with ORDER BY timestamp DESC.

Current query plan (no index):

Sort  (cost=75710.16..75710.17 rows=1 width=2722) (actual time=1735.258..1735.259 rows=4 loops=1)
  Sort Key: "timestamp" DESC
  Sort Method: quicksort  Memory: 30kB
  ->  Index Scan using events_pkey on events  (cost=0.55..75710.15 rows=1 width=2722) (actual time=254.580..1735.211 rows=4 loops=1)
        Index Cond: (((app_name)::text = '<>'::text) AND ((user_id)::text = '<>'::text) AND ((session_id)::text = '<>'::text))
Planning Time: 7.189 ms
Execution Time: 1735.363 ms

After adding the composite index:

CREATE INDEX idx_events_app_user_session_ts
  ON public.events USING btree (app_name, user_id, session_id, "timestamp" DESC);

Index Scan using idx_events_app_user_session_ts on events  (cost=0.55..2.78 rows=1 width=2722) (actual time=0.028..0.034 rows=4 loops=1)
  Index Cond: (((app_name)::text = '<>'::text) AND ((user_id)::text = '<>'::text) AND ((session_id)::text = '<>'::text))
Planning Time: 0.689 ms
Execution Time: 0.057 ms

Steps to Reproduce:
Please provide a numbered list of steps to reproduce the behavior: NA

Expected Behavior:
Queries against the events table filtered by (app_name, user_id, session_id) and ordered by timestamp DESC should use an efficient index scan.

Observed Behavior:
Without the index, PostgreSQL performs an expensive index scan on the primary key followed by an in-memory sort, causing high CPU load and ~1.7s query times on production workloads.

Root Cause: The primary key on the events table is (id, app_name, user_id, session_id) . Since id is the leading column, this index is not usable for queries that filter on (app_name, user_id, session_id) -- which is the access pattern used by DatabaseSessionService.get_session() and list_events() in database_session_service.py.

Proposed Fix: Add a SQLAlchemy Index to StorageEvent.table_args in src/google/adk/sessions/schemas/v1.py:

Index(
    "idx_events_app_user_session_ts",
    "app_name",
    "user_id",
    "session_id",
    desc("timestamp"),
),

Environment Details:

• ADK Library Version (pip show google-adk):

> uv pip show google-adk
Name: google-adk
Version: 1.21.0

• Database: PostgreSQL
• Desktop OS:** [e.g., macOS, Linux, Windows]: ghcr.io/astral-sh/uv:python3.12-bookworm-slim
• Python Version (python -V): ghcr.io/astral-sh/uv:python3.12-bookworm-slim

---

🟡 Optional Information

Regression:
Did this work in a previous version of ADK? If so, which one?: N/A -- the index has never existed in the schema.

Screenshots / Video:
If applicable, add screenshots or screen recordings to help explain
your problem.

Additional Context:
Add any other context about the problem here.

Minimal Reproduction Code:
NA

How often has this issue occurred?:

• Always (100%)