A 311 assistant team is debating whether to store active conversation and session state in Firestore or in Cloud SQL. Given that this data is document-like, evolves per conversation, and doesn't need complex relational joins, which choice fits better?
Select an answer to reveal the explanation.
Short Explanation
Think of it like choosing a notebook with blank pages instead of a preprinted form with fixed rows and columns for something that changes shape every time you write in it. Session state that varies per conversation fits Firestore's flexible document model the way free-form notes fit a blank notebook, while Cloud SQL's relational structure is built for data that actually needs those fixed rows and columns, and the joins between them.
Full Explanation
Choosing between Firestore and Cloud SQL for session state comes down to matching the data's actual shape and access pattern to the database's model. Session state that's document-like, varies per conversation, and doesn't require relational joins across structured tables fits Firestore's flexible schema well, letting each session document evolve independently without a rigid predefined structure.
Defaulting to Cloud SQL as always the safer choice ignores that relational databases add overhead, a fixed schema and join logic, that this specific use case doesn't need, making it a mismatch rather than a conservative choice. Avoiding persistence entirely and relying only on in-memory context abandons durability altogether, which breaks the moment the process restarts or the conversation needs to resume later. Insisting on transactional guarantees for every category of data overgeneralizes a requirement that matters for some agentic workloads, like billing or inventory, but isn't what's driving the decision for loosely structured session state.
Scope note: this reasoning is specific to session-shaped data; other parts of the same agentic system, like structured records requiring relational integrity, may still belong in Cloud SQL. Operational check: review the session document's actual field structure across a sample of real conversations to confirm it genuinely varies enough to benefit from Firestore's flexibility rather than settling into a fixed shape that would fit a relational table just as well.