UAT: Performance - SessionMessageRepository.count_for_session() called on every append_message(), causing redundant COUNT query per message #4065

New issue

Open

opened 2026-04-06 09:51:08 +00:00 by freemo · 0 comments

freemo commented

2026-04-06 09:51:08 +00:00

Owner

Bug Report

What was tested: Database query efficiency of PersistentSessionService.append_message() in src/cleveragents/application/services/session_service.py (lines 153–220)

Expected behavior:
Appending a message to a session should require at most 2 database operations: one to verify the session exists, and one to insert the message. The sequence number should be derived efficiently without a separate COUNT query.

Actual behavior:
append_message() issues a separate COUNT(*) query via count_for_session() on every single message append to determine the next sequence number. This means every message append requires 3 database round-trips:

session_repo.get_by_id() — verify session exists
message_repo.count_for_session() — COUNT query to get sequence number
message_repo.append() — INSERT the message
session_repo.update() — UPDATE session timestamp

Code location: src/cleveragents/application/services/session_service.py, lines 184–190:

def append_message(self, session_id, role, content, metadata=None):
    session = self._session_repo.get_by_id(session_id)
    if session is None:
        raise SessionNotFoundError(...)
    
    # ... sanitize content ...
    
    # Separate COUNT query on every append — unnecessary!
    count = self._message_repo.count_for_session(session_id)
    
    message = SessionMessage(
        message_id=str(ULID()),
        role=role,
        content=content,
        sequence=count,  # Uses count as sequence number
        ...
    )
    self._message_repo.append(session_id, message)
    session.updated_at = datetime.now()
    self._session_repo.update(session)

Performance impact:

Every message append requires a COUNT query that scans the session_messages table filtered by session_id
In an active session with 1000 messages, the COUNT query must count 1000 rows on every new append
For a session with N messages, the total cost of appending all N messages is O(N²) in terms of rows scanned (1+2+3+...+N)
Sessions with long conversation histories (common in autonomous agent workflows) will experience quadratic slowdown in message appending
The count_for_session() query is not needed if the sequence number is tracked differently

Steps to reproduce:

Create a session and append 500+ messages
Enable SQLAlchemy query logging
Observe a COUNT query before every INSERT

Fix options:

Option 1 (Recommended): Use database-side sequence via MAX(sequence) + 1

# In SessionMessageRepository, replace count_for_session with:
def next_sequence(self, session_id: str) -> int:
    """Get next sequence number using MAX(sequence) + 1."""
    result = (
        db_session.query(sa_func.max(SessionMessageModel.sequence))
        .filter_by(session_id=session_id)
        .scalar()
    )
    return (result or -1) + 1

Option 2: Use database auto-increment for sequence
Add an auto-increment sequence column to session_messages table, removing the need for any sequence-counting query.

Option 3: Track sequence in the session model
Store message_count in the sessions table and increment it atomically during the INSERT transaction, avoiding the separate COUNT query.

Option 4: Use INSERT ... RETURNING with a subquery

INSERT INTO session_messages (session_id, sequence, ...)
SELECT :session_id, COALESCE(MAX(sequence), -1) + 1, ...
FROM session_messages WHERE session_id = :session_id
RETURNING sequence;

Additional concern: The count_for_session() method name is misleading — it's used to determine the next sequence number, not just to count messages. This makes the code harder to understand and maintain.

Severity: Medium — affects all active sessions. Long-running autonomous agent sessions that generate hundreds of messages will experience significant slowdown.

Automated by CleverAgents Bot
Supervisor: UAT Testing | Agent: ca-uat-tester

## Bug Report **What was tested:** Database query efficiency of `PersistentSessionService.append_message()` in `src/cleveragents/application/services/session_service.py` (lines 153–220) **Expected behavior:** Appending a message to a session should require at most 2 database operations: one to verify the session exists, and one to insert the message. The sequence number should be derived efficiently without a separate COUNT query. **Actual behavior:** `append_message()` issues a separate `COUNT(*)` query via `count_for_session()` on every single message append to determine the next sequence number. This means every message append requires 3 database round-trips: 1. `session_repo.get_by_id()` — verify session exists 2. `message_repo.count_for_session()` — COUNT query to get sequence number 3. `message_repo.append()` — INSERT the message 4. `session_repo.update()` — UPDATE session timestamp **Code location:** `src/cleveragents/application/services/session_service.py`, lines 184–190: ```python def append_message(self, session_id, role, content, metadata=None): session = self._session_repo.get_by_id(session_id) if session is None: raise SessionNotFoundError(...) # ... sanitize content ... # Separate COUNT query on every append — unnecessary! count = self._message_repo.count_for_session(session_id) message = SessionMessage( message_id=str(ULID()), role=role, content=content, sequence=count, # Uses count as sequence number ... ) self._message_repo.append(session_id, message) session.updated_at = datetime.now() self._session_repo.update(session) ``` **Performance impact:** - Every message append requires a COUNT query that scans the `session_messages` table filtered by `session_id` - In an active session with 1000 messages, the COUNT query must count 1000 rows on every new append - For a session with N messages, the total cost of appending all N messages is O(N²) in terms of rows scanned (1+2+3+...+N) - Sessions with long conversation histories (common in autonomous agent workflows) will experience quadratic slowdown in message appending - The `count_for_session()` query is not needed if the sequence number is tracked differently **Steps to reproduce:** 1. Create a session and append 500+ messages 2. Enable SQLAlchemy query logging 3. Observe a COUNT query before every INSERT **Fix options:** **Option 1 (Recommended): Use database-side sequence via MAX(sequence) + 1** ```python # In SessionMessageRepository, replace count_for_session with: def next_sequence(self, session_id: str) -> int: """Get next sequence number using MAX(sequence) + 1.""" result = ( db_session.query(sa_func.max(SessionMessageModel.sequence)) .filter_by(session_id=session_id) .scalar() ) return (result or -1) + 1 ``` **Option 2: Use database auto-increment for sequence** Add an auto-increment `sequence` column to `session_messages` table, removing the need for any sequence-counting query. **Option 3: Track sequence in the session model** Store `message_count` in the `sessions` table and increment it atomically during the INSERT transaction, avoiding the separate COUNT query. **Option 4: Use INSERT ... RETURNING with a subquery** ```sql INSERT INTO session_messages (session_id, sequence, ...) SELECT :session_id, COALESCE(MAX(sequence), -1) + 1, ... FROM session_messages WHERE session_id = :session_id RETURNING sequence; ``` **Additional concern:** The `count_for_session()` method name is misleading — it's used to determine the next sequence number, not just to count messages. This makes the code harder to understand and maintain. **Severity:** Medium — affects all active sessions. Long-running autonomous agent sessions that generate hundreds of messages will experience significant slowdown. --- **Automated by CleverAgents Bot** Supervisor: UAT Testing | Agent: ca-uat-tester

freemo referenced this issue

2026-04-06 09:52:06 +00:00

[Automated] CleverAgents Build Session - 2026-04-06 #3775

freemo referenced this issue

2026-04-06 09:56:47 +00:00

[Automated] CleverAgents Build Session - 2026-04-06 #3775

freemo referenced this issue

2026-04-06 10:15:25 +00:00

[Automated] CleverAgents Build Session - 2026-04-06 #3775

freemo referenced this issue

2026-04-06 10:35:53 +00:00

[Automated] CleverAgents Build Session - 2026-04-06 #3775