Skip to content

Optimization: Fix Gateway N+1 Queries via Eager Loading#2204

Merged
crivetimihai merged 1 commit intomainfrom
1994_perf_n1_queries_gateways
Jan 20, 2026
Merged

Optimization: Fix Gateway N+1 Queries via Eager Loading#2204
crivetimihai merged 1 commit intomainfrom
1994_perf_n1_queries_gateways

Conversation

@kevalmahajan
Copy link
Copy Markdown
Member

@kevalmahajan kevalmahajan commented Jan 20, 2026

✨ Performance Enhancement PR

🔗 Epic / Issue

Closes #1994

🚀 Summary (1-2 sentences)

This PR resolves the N+1 query performance issue in the Gateway service by implementing eager loading for the team relationship. It introduces the email_team relationship and team property to the Gateway model and updates service methods to fetch all necessary data in a single database query, mirroring the optimization recently applied to the Server model.

🛠️ Fix Description

This PR addresses the N+1 query issue where retrieving a Gateway entity resulted in an additional database query to fetch the associated team name.

1. Database Model Updates (mcpgateway/db.py)

  • Added an email_team relationship to the Gateway model. This relationship is configured to lazily load by default but can be eagerly loaded using joinedload.
  • Added a team property to the Gateway model. This property serves as a proxy to retrieve the team name from the email_team relationship, ensuring consistent access without explicit query calls in the application logic.

2. Service Layer Optimization (mcpgateway/services/gateway_service.py)

  • Eager Loading: Updated get_gateway, update_gateway, set_gateway_state, create_gateway, delete_gateway, and _sync_gateway_with_oauth method to use db.execute() with joinedload(DbGateway.email_team). This instructs SQLAlchemy to fetch the Gateway and its associated EmailTeam in a single SQL query using a LEFT JOIN.
  • Removed Helper: Deleted the _get_team_name helper method, which was previously responsible for the secondary query effectively eliminating the N+1 problem.

3. Test Updates (tests/unit/mcpgateway/services/test_gateway_service.py)

  • Refactored unit tests to mock db.execute instead of db.get, reflecting the change in the service layer from simple primary key lookups to complex select statements with options.
  • Updated mock_gateway fixtures to include the email_team relationship, ensuring tests accurately simulate the new data structure.

⚡ Impact Assessment

The following table demonstrates the reduction in database queries for key Gateway operations:

Operation Previous Queries New Queries Improvement
get_gateway() 2+ (One for gateway + one per team lookup) 1 (Gateway + Team via JOIN) 50% Reduction
update_gateway() 2+ 1 50% Reduction
set_gateway_state() 2+ 1 50% Reduction
delete_gateway() Lazy loaded (N queries) 1 (Eager loaded) Optimized
_sync_gateway_with_oauth() Lazy loaded (N queries) 1 (Eager loaded) Optimized

🧪 Checks

  • make lint passes
  • make test passes
  • CHANGELOG updated (if user-facing)

📓 Notes (optional)

This implementation follows the pattern established in PR #1962 and #1975 for the Server model. The _get_team_name helper method, which was responsible for the extra N+1 query, has been removed entirely.

@kevalmahajan kevalmahajan force-pushed the 1994_perf_n1_queries_gateways branch 3 times, most recently from b896fb8 to 6861616 Compare January 20, 2026 11:11
@crivetimihai crivetimihai force-pushed the 1994_perf_n1_queries_gateways branch from 6861616 to dda02c3 Compare January 20, 2026 22:40
@crivetimihai
fix(perf): resolve Gateway N+1 queries via eager loading
e545808 
Add email_team relationship and team property to Gateway model, mirroring
the optimization applied to Server model in PR #1962 and #1975.

Changes:
- Add email_team relationship to Gateway model (db.py)
- Add team property for convenient access to team name
- Update gateway_service.py to use joinedload(DbGateway.email_team)
- Remove _get_team_name helper method (no longer needed)
- Update tests to mock db.execute instead of db.get

This reduces database queries by ~50% for gateway operations that need
team information, eliminating the N+1 query pattern.

Closes #1994

Signed-off-by: Keval Mahajan <mahajankeval23@gmail.com>
Signed-off-by: Mihai Criveti <crivetimihai@gmail.com>
@crivetimihai crivetimihai force-pushed the 1994_perf_n1_queries_gateways branch from dda02c3 to e545808 Compare January 20, 2026 23:05
@crivetimihai
Copy link
Copy Markdown
Member

crivetimihai commented Jan 20, 2026

Review & Testing Summary

Changes Made During Review

  1. Fixed list_gateways_for_user (lines 1503-1567): Added joinedload(DbGateway.email_team) and removed manual g.team = ... assignment that would have caused AttributeError (team is now a read-only @property)

  2. Fixed update_gateway (line 1617): Changed joinedload to selectinload for email_team inside get_for_update() to avoid unnecessary PostgreSQL FOR UPDATE locks on the email_teams table

  3. Fixed doctest (line 2596): Updated mock from db.get.return_value to db.execute.return_value.scalar_one_or_none.return_value to match actual implementation

  4. Cleanup: Removed unused EmailTeam import and PropertyMock import, fixed extra blank lines

Testing Performed

SQL Query Verification (with SQLALCHEMY_ECHO=true):

  • Confirmed list_gateways uses single LEFT OUTER JOIN instead of N+1 queries
  • Confirmed get_gateway uses single LEFT OUTER JOIN
  • Confirmed list_gateways_for_user uses joinedload pattern
  • Confirmed update_gateway uses selectinload (separate query, no FOR UPDATE lock on email_teams)

API Response Verification:

GET /gateways → Found 1 gateways
  - fast_time: team=Platform Administrator's Team

Unit Tests: All 5,262 tests pass (3 pre-existing failures unrelated to this PR)

Consistency Check

Implementation follows the same pattern as Server model (PR #1962, #1975) and Tool model.

Commits squashed into single clean commit with original author attribution preserved.

@crivetimihai crivetimihai merged commit ff40116 into main Jan 20, 2026
51 checks passed
@crivetimihai crivetimihai deleted the 1994_perf_n1_queries_gateways branch January 20, 2026 23:17
@crivetimihai crivetimihai mentioned this pull request Jan 23, 2026
Closed
kcostell06 pushed a commit to kcostell06/mcp-context-forge that referenced this pull request Feb 24, 2026
@kevalmahajan @crivetimihai
fix(perf): resolve Gateway N+1 queries via eager loading (IBM#2204)
b81e0fd 
Add email_team relationship and team property to Gateway model, mirroring
the optimization applied to Server model in PR IBM#1962 and IBM#1975.

Changes:
- Add email_team relationship to Gateway model (db.py)
- Add team property for convenient access to team name
- Update gateway_service.py to use joinedload(DbGateway.email_team)
- Remove _get_team_name helper method (no longer needed)
- Update tests to mock db.execute instead of db.get

This reduces database queries by ~50% for gateway operations that need
team information, eliminating the N+1 query pattern.

Closes IBM#1994

Signed-off-by: Keval Mahajan <mahajankeval23@gmail.com>
Signed-off-by: Mihai Criveti <crivetimihai@gmail.com>
Co-authored-by: Mihai Criveti <crivetimihai@gmail.com>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

@crivetimihai crivetimihai crivetimihai approved these changes

@madhav165 madhav165 Awaiting requested review from madhav165 madhav165 is a code owner

Assignees

No one assigned

Labels

None yet

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

[PERFORMANCE]: Fix N+1 queries in Gateway single-entity retrieval functions

2 participants

@kevalmahajan @crivetimihai