AI queries pick their database node at run time, not at registration

To keep AI queries off the primary node, you can register the connection with a standby's address. Then the node that runs those queries is fixed the moment you register it, and the connection keeps reading the same address after node roles change. When a failover turns that standby into the primary, AI queries run without warning on the same node as orders and payments. If the connection points at the old primary and that node is still up, queries succeed and answer with data frozen at the moment of failover. A standby whose replication lag has grown does the same: it answers with inventory from minutes ago and no sign of failure.

For databases connected through OwlDB, OwlAI does not decide the node. The execution request carries no node. OwlDB looks at node roles and replication lag at the moment the request arrives, picks the node, and states in the response which node ran the query and why. When there is no node to pick, OwlAI sends the same request again a little later. This post explains who picks the node and when, what happens when there is no node to pick, and why the statistics queries in schema analysis go to the primary.

#A node fixed by address doesn't know its role changed

An AI sends several SQL statements per question. Short queries that check a few code values, execution plan lookups and the main query all go to the source database. Schema analysis, which runs when a database is first connected (“DDL doesn't say what a column means, so we read its values first”), sends catalog queries and sample queries table after table. If these queries go to the primary, they share CPU and I/O with business transactions. So when a production database has a standby (a server that replicates the primary in real time and waits to take over), sending AI queries there is the natural move.

A direct connection (one where an administrator types in the connection details) can do this. You enter the standby's address when registering the connection (“Put AI on the production database, and security asks three questions”). It takes one setting and nothing extra on the OwlAI side. But all the connection knows is one address, not what role the node behind it has right now. The table shows what happens on a connection fixed to an address when node state changes.

The last two rows are the dangerous ones. A failed query gets noticed, but an answer built on stale data looks exactly like a correct one. Someone who asks for an item's stock in one warehouse gets a number and has no way to tell it is minutes old.

#If OwlAI watches node state on its own, two places make the same call

OwlAI could also hold a node list on the connection and check each node's role and replication lag periodically. That has the advantage of working without OwlDB. On PostgreSQL, pg_is_in_recovery() gives the role, and the timestamp of the last replayed transaction gives the lag.

But that judgment lags by the check interval. A failover finishes in seconds, and during those seconds the role OwlAI knows differs from the real one. The bigger problem is that two places now make the same call. The database management platform already watches node roles, lag and load, and it triggers the failover itself. If OwlAI keeps its own copy of that monitoring, at the moment of failover the two products believe different nodes are the primary. The side that learns node state first is the side that triggers the failover. So on an OwlDB connection, OwlDB picks the node.

#The request has no node; the response names the node that ran it

On an OwlDB connection, the execution request OwlAI sends carries the database identifier, the account identifier mapped to the asking user's role, the SQL and a row limit (“OwlDB connections hold a database ID instead of credentials”). OwlDB picks the node from node roles and replication lag at the moment the request arrives. Queries that read table data go to a readable standby. After a failover, OwlDB picks from the new roles starting with the next request, so nothing on OwlAI's connection needs fixing.

Along with the result, the response carries the node that actually ran the query and the reason that node was chosen. OwlAI stores both on that statement's row in the admin console's SQL execution history. Later you can check that row to see which node's data yesterday afternoon's answer came from. If many queries ran on the primary even though a standby existed, the reason is in the same record.

#OwlDB decides how stale is acceptable

Reading from a standby means reading data as old as the replication lag. "Revenue by region last month" does not change with a few seconds of lag, but a question checking an order that just came in does. OwlDB holds a replication lag threshold: it accepts lag within the threshold and does not pick a standby beyond it.

OwlDB could skip a lagging standby and read from the primary instead. Then every time lag grows, all AI queries pile onto the primary. Replication lag tends to grow when writes pile onto the primary, so AI queries would land on the primary at its busiest moment. So when the threshold is exceeded, OwlDB answers that there is no node to pick.

#With no node to pick, OwlAI asks again a little later

When there is no readable standby (NO_READABLE_REPLICA), standby replication lag exceeds the threshold (REPLICA_LAG_EXCEEDED), or the database is busy (DATABASE_BUSY), OwlDB does not run the SQL and returns a reason code. All three are transient: they clear with time. OwlAI resends the same request a few times with growing intervals, and the SQL stays unchanged throughout.

These reasons are not included in the tool result the model receives, because the model cannot resolve them by fixing its SQL (“Heavy SQL is judged by OwlDB, which knows the database's state, not by a cost threshold”). If every retry fails, the tool result says only that this database cannot be read right now. With no SQL to fix, the model has no reason to produce SQL that drops valid conditions or detours through other tables. A rejection for exceeding the execution cost threshold (QUERY_COST_EXCEEDED) is different. Fixing the SQL resolves it, so it goes to the model together with the plan nodes that pushed it over (“Heavy SQL is judged by OwlDB, which knows the database's state, not by a cost threshold”).

#Schema analysis reads statistics on the primary

Schema analysis reads, table by table, what the database already knows (“DDL doesn't say what a column means, so we read its values first”). Sort what it reads on PostgreSQL by where each piece is kept, and it becomes clear what can be read on a standby and what cannot.

Read the last two rows on a standby and the analysis reaches wrong conclusions. It treats a table with healthy statistics as one that was never analyzed, and it cannot find the SQL that shows how business screens join that table. What remains on a standby are the report queries attached to that node. SQL sent by the AI starts with a fixed comment, so the analysis excludes it from this list.

So OwlDB splits analysis queries by what they read too. Queries that read the catalog and sample rows go to the standby, like chat queries, and queries that read statistics kept separately on each node go to the primary. The queries sent to the primary are light, reading a few rows of statistics per table. Because the standby handles queries that read table data, you can run analysis during business hours when a standby exists.

#Design choices

Instead of fixing a node on the connection, OwlDB picks one at every execution. Fixing it at registration is simple and works without OwlDB, but it cannot see node state change. Having OwlAI watch nodes directly would see the state, but two products would make the same call separately. The cost is that OwlAI cannot specify a node per query, and if OwlDB does not respond, queries on OwlDB connections do not run.

When lag exceeds the threshold, OwlAI retries instead of falling back to the primary. Falling back would return answers faster but would add load to the primary at its busiest. The cost is that long lag means slow answers, and the answer can end up being that the database cannot be read. Questions that need just-arrived data follow the same threshold.

OwlAI, not the model, handles transient reasons. The information worth showing the model is information that lets it change the result by changing its SQL, and node state is not that kind of information. The cost is a slower answer while retries run.

Statistics kept separately on each node are read on the primary. Sending every analysis query to the standby would lighten the primary further, but the analysis would not see the statistics and SQL of the node where business actually runs. The primary carries the load of those queries.

Handing off the choice does not mean giving up the result. The execution node and reason are kept in the SQL execution history so administrators can check that node selection works as expected.

If your production database has a standby and OwlDB manages that replication, an OwlDB connection that leaves node selection to OwlDB is the better fit. If you have a separate analytics replica outside the failover pair, a direct connection registered with its address is enough. In that case, keep in mind that the executed SQL and statistics collection times schema analysis reads belong to that replica.