Database Scaling for AI Travel: Relational Stores Under Load

a relational database is our primary database. It stores users, conversations, bookings, payments, trip data, travel reference data, and notification records. We chose it because relational data with ACID transactions is the right model for financial operations like travel bookings, and a relational database is the most capable open-source relational database available.

But running a relational database for an AI-driven product is different from running it for a traditional web app. The query patterns are different. The connection dynamics are different. The growth patterns are different. Here is what we have learned.

Query patterns from AI operations

A traditional form-based travel app generates predictable queries. User submits search form: one query for flights matching those parameters. User clicks a result: one query for flight details. User fills out traveler form: one insert for the booking.

Our AI agent generates unpredictable and compound queries. A single user message might trigger: load conversation history (1 query), load user preferences (1 query), load active booking state (1 query), execute tool calls that each require database lookups (2-5 queries), store the agent's response (1 insert), update session state (1 update), and log analytics events (1-3 inserts). That is 8-12 database operations for a single chat message.

Multiply this by concurrent users and the query volume is significantly higher per user than a traditional web app. The query types are also more varied. We do not have a small set of hot queries that account for 80% of traffic. The AI agent's non-deterministic behavior means the mix of queries changes based on what users ask and what the agent decides to do.

This complicates index optimization because we cannot easily identify a small number of queries to optimize for maximum impact. We analyze query patterns weekly and adjust indexes based on actual usage rather than predicted usage.

Conversation history at scale

Conversation threads are our fastest-growing data. Every user message, every agent response, every tool call result, and every booking event gets stored as a conversation record. A single active user generates 20-50 records per session. Power users doing complex trip planning generate hundreds.

The schema is straightforward: threads contain messages, messages have types and content. But the access patterns are challenging. When the agent needs conversation history, it needs the most recent N messages from a specific thread, ordered by timestamp, including any embedded tool results. This is a query that gets more expensive as threads grow longer.

We handle this with a combination of strategies. Conversation history queries use a composite index on thread ID and creation timestamp. We limit history retrieval to the most recent messages (typically 20-30) for the agent's context window, avoiding full thread scans. For users who want to scroll back through old conversations, we paginate with cursor-based pagination rather than offset, which performs consistently regardless of thread length.

Archiving is part of our long-term strategy. Conversations older than 90 days are candidates for archival to cold storage. The AI agent's memory system captures the important preferences and facts from old conversations, so the raw messages are mainly needed for audit purposes after that point.

Connection pooling for AI sessions

This is the scaling challenge that caught us most off guard. Traditional web requests hold a database connection for milliseconds. AI agent requests hold connections for seconds.

When the agent processes a user message, it makes multiple database queries over a period of 5-30 seconds (depending on conversation complexity and tool calls). Each of these queries uses the same database connection. The connection is checked out from the pool at the start and returned when the agent's response is complete.

With a default connection pool size of 20 connections and agent requests that hold connections for 10 seconds on average, our maximum concurrent AI sessions was effectively 20. That is not enough.

We solved this in two ways. First, we increased the connection pool size and configured a relational database to handle more connections. This buys headroom but has diminishing returns because each connection consumes server memory.

Second, and more importantly, we restructured our database access to release connections between queries rather than holding them for the entire agent turn. The agent checks out a connection, runs a query, releases the connection, does non-database work (AI inference, external API calls), then checks out a connection again for the next query. This dramatically improves connection utilization because the connection is not idle while the agent thinks or waits for external APIs.

The tradeoff is that transactions cannot span the entire agent turn. If we need transactional consistency (like during booking), we use explicit transactions for the critical section only, not for the entire request lifecycle.

Index optimization

We take a data-driven approach to indexing. Every week, we analyze the slow query log and the query plan statistics to identify queries that are scanning more rows than necessary.

For AI-driven query patterns, the most impactful indexes are:

Conversation history: composite index on (thread_id, created_at) for fast ordered retrieval of recent messages.

User lookups: index on external auth ID for fast authentication-to-user mapping.

Booking queries: composite index on (user_id, status, created_at) for retrieving active and recent bookings.

Trip queries: composite index on (user_id, status) for listing a user's trips.

Search analytics: indexes on (created_at) for time-range queries in our analytics pipeline.

We avoid over-indexing because every index slows down writes. Our conversation message table receives high write volume (every message is an insert), so we are selective about which indexes it carries.

Backup and recovery for financial data

Booking and payment records are financial data that requires a higher standard of protection than most application data. We maintain:

Continuous WAL (Write-Ahead Log) archiving for point-in-time recovery. If something goes wrong, we can restore to any second in the past 30 days.

Daily full backups stored in a separate region. These are our disaster recovery baseline.

Transaction-level audit logging for all booking and payment operations. Every state change is recorded with who, what, when, and why.

We test recovery regularly. Once a month, we restore a backup to a test environment and verify that the data is complete and consistent. We also test point-in-time recovery to random timestamps. This is not exciting work, but the day you need it and it does not work is very exciting in all the wrong ways.

AI-shaped data versus form-shaped data

Traditional OTA databases store form-shaped data. A flight search has a fixed schema: origin, destination, dates, passengers. A booking has a fixed set of fields. The data is rectangular and predictable.

Our data is conversation-shaped. A message can contain anything from a two-word question to a structured booking request with nested traveler profiles. Tool results vary in structure based on which tool was called. The AI agent's reasoning (logged for debugging) is unstructured text.

We handle this mix with a combination of relational columns for structured, queryable fields and JSON columns for variable-structure content. A message row has typed columns for thread ID, role, and timestamp (things we query on) and a JSON column for the message content (which varies by message type).

This hybrid approach gives us the query performance of relational data where we need it and the flexibility of document storage where structure varies. a relational database's JSON support is mature enough that this works well in practice.

Nowah is an AI travel agent that searches and books real flights and hotels through conversation — no filters, no thirty open tabs. Plan your next trip.