Skip to content

Performance: Fix N+1 query pattern in arrivals and departures handler #313

@3rabiii

Description

@3rabiii

Description

The arrivalsAndDeparturesForStopHandler in internal/restapi/arrivals_and_departure_for_stop.go currently suffers from a significant N+1 query problem.

Inside the main loop iterating over stopTimes, the code executes individual database queries for GetRoute and GetTrip for every single arrival.

Impact

For a busy stop with 50 upcoming arrivals, this results in 100+ separate database queries per single HTTP request. This causes high latency and unnecessary load on the database.

Goal

Refactor the handler to use Batch Fetching:

  1. Collect all unique Route IDs and Trip IDs before the loop.
  2. Fetch all required data in just two queries (GetRoutesByIDs and GetTripsByIDs).
  3. Use in-memory maps for O(1) lookups inside the loop.

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions