Building Agentic AI on Distributed SQL With a Semantic Foundation

Updated October 2026

Agentic AI needs two foundations that most data stacks keep apart. Distributed SQL gives agents correct, durable state through ACID transactions that survive node and region failures. A semantic foundation gives them shared meaning through governed definitions, an ontology, and resolved entities. Agents that have only the first act correctly on misunderstood data, and agents that have only the second reason well over stale facts.

What does an agentic AI workload need from a database?

An agentic workload needs transactions that hold up under many concurrent writers doing multi step work. Agents read state, decide, act through tools, and record results, often while other agents touch the same records. Request and response apps rarely stress a database this way.

Microsoft's AI agent orchestration guidance tells teams to persist shared state externally for long running work rather than keep it in memory. It also warns that sharing mutable state between concurrent agents can produce transactionally inconsistent data. Both points push agent state into a real database with real isolation.

Cockroach Labs makes the vendor case in its agentic AI architecture post from January 2026. It lists serializable transactions, high write concurrency, failure tolerance, horizontal scale, and data locality as requirements. The list is sound even though the post promotes its own product and offers no benchmarks.

Why choose distributed SQL for agent state?

Distributed SQL combines relational transactions with horizontal scale and multi region resilience, which is the mix agent fleets need. You keep SQL, joins, and constraints while adding nodes instead of sharding by hand. A single logical database can hold task queues, plans, tool outputs, and business records together.

That matters because agent state is business state. When a billing agent claims a failed payment and a retention agent offers a discount on the same account, both writes belong in one consistent system. Splitting them across a cache, a queue, and a separate database creates gaps that no prompt can repair.

The tradeoff is latency and retries. Strong consistency across regions costs round trips, and stricter isolation means more transactions abort under contention. Agents therefore need idempotent tools and retry loops, which are good habits anyway because model calls already fail and repeat.

How do the main distributed SQL databases compare for agents?

The leading options differ most in default isolation, PostgreSQL compatibility, and built in AI features. Every one of them supports ACID transactions at scale. Choose by the guarantees your agents need and the ecosystem you already run.

CockroachDB

CockroachDB docs state that transactions run at the strongest ANSI level by default, which is SERIALIZABLE, with READ COMMITTED as an option. Its vector indexes are compatible with pgvector syntax. The limit is that serializable transactions can fail with SQLSTATE 40001, and the application must retry them.

Google Spanner

Spanner offers external consistency, a guarantee stricter than serializability because the serial order matches real commit time. Spanner Graph adds ISO GQL, vector search, and full text search over the same tables. The limit is that Spanner Graph requires the Enterprise or Enterprise Plus edition and does not support the PostgreSQL interface.

YugabyteDB

YugabyteDB ships pgvector with HNSW indexes on its PostgreSQL compatible YSQL API. Its isolation docs explain that the YSQL default is effectively snapshot unless read committed is turned on. Teams that want serializable behavior for agents have to set it explicitly.

Amazon Aurora DSQL

AWS made Aurora DSQL generally available on May 27, 2025 with active active high availability and multi region strong consistency. It is compatible with PostgreSQL 16. It uses optimistic concurrency control with snapshot isolation, so conflicts return SQLSTATE 40001 at commit and AWS recommends idempotent retries.

TiDB

TiDB is MySQL compatible and implements snapshot isolation, which it advertises as repeatable read. It also supports read committed in pessimistic mode. Neither level is serializable, so agent logic that checks a condition and then writes needs explicit row locks.

Which isolation level should AI agents use?

Use serializable, or an equivalent stronger guarantee, for any agent that reads a condition and then writes based on it. Refund approvals, inventory holds, and task claims all follow that check then write shape. Weaker levels allow two agents to each see a valid state and together produce an invalid one.

Snapshot and read committed are reasonable for read heavy agents, reporting, and append only logs. If you use them for decisions, add explicit locks such as SELECT FOR UPDATE on the rows the decision depends on. The PostgreSQL docs note that SKIP LOCKED helps multiple consumers share a queue-like table without lock contention.

Retries are the price of strict isolation. Design every agent tool as idempotent, give each action a unique key, and let the runtime retry on 40001. That way a retried transaction never sends two emails or issues two refunds.

What can distributed SQL not tell an agent?

A database can tell an agent that a status column holds the value active. It cannot tell the agent what active means, which systems agree, or how that status relates to billing and support. Those definitions live in people's heads, dashboards, and old pipeline code.

Models do poorly without them. A 2023 data.world benchmark found GPT-4 answered 16 percent of enterprise questions correctly over raw SQL schemas and 54 percent over a knowledge graph. An April 2026 study found explicit business semantics added 17 to 23 points across Claude Opus 4.7, Claude Sonnet 4.6, and GPT-5.4.

Vendor results point the same way. A dbt Labs benchmark from April 2026 reported 98 to 100 percent accuracy through its semantic layer against 84 to 90 percent for text to SQL. It covered only 11 questions and comes from a vendor, so treat it as directional.

What is a semantic foundation for agentic AI?

A semantic foundation is the governed layer that defines what your data means, so every agent and person uses the same definitions. It usually combines four parts. Each one answers a different question an agent will ask before it acts.

Semantic layer

A semantic layer defines metrics, dimensions, and joins once and serves them to any tool. MetricFlow is Apache 2.0, and the Open Semantic Interchange effort became Apache Ossie in July 2026. Warehouse native options exist too, such as Snowflake Semantic View Autopilot, which reached general availability in February 2026.

Ontology and knowledge graph

An ontology names the entities in your business and the relationships between them, such as customer, subscription, invoice, and ticket. A knowledge graph holds instances of those entities, so an agent can follow dependencies before it acts. Our guide to knowledge graphs for agentic AI covers retrieval patterns in more depth.

Entity resolution

Entity resolution links records that describe the same real world object across systems. Without it, one agent refunds a CRM record while another upgrades its billing duplicate. See identity resolution and entity mastering for matching methods.

Provenance

Provenance records where each fact came from and which agent changed what. It turns an agent decision into something you can replay and audit. At minimum, store the source system, the acting agent, and the time with every change.

Who should own the semantic foundation?

You should own it. Definitions of customers, revenue, and risk are institutional knowledge, and they become more valuable as agents depend on them. Keep them in open formats, in version control, on infrastructure you control, so you can change models, agent frameworks, or databases without rebuilding meaning.

Galaxy works with teams to build this semantic foundation on the stack they already run, using internal tooling that speeds up semantic layers, ontologies, and entity resolution. It builds in the open, starting with Filament by Galaxy, its Apache 2.0 data movement engine, and plans to open source more of that tooling.

How do the transactional and semantic layers connect?

The two layers connect through a clean split between reads, writes, and change propagation. Agents read meaning from the semantic foundation, act through narrow transactional tools, and let change streams keep context fresh. Four patterns cover most production designs.

Read through governed definitions

Agents query metrics and entities through the semantic layer rather than writing raw SQL against base tables. The Model Context Protocol, now under the Linux Foundation's Agentic AI Foundation, is the common way to expose those definitions as tools. Permission checks should follow the user the agent acts for.

Write through typed transactional tools

Each write is a named tool that runs one known transaction, such as claim_task or issue_refund. The tool checks permissions, enforces business rules, and records the acting agent. This keeps free form SQL out of production and gives auditors a short list of operations to review.

Propagate changes with CDC

Agents write once to the database, and change data capture updates the graph and semantic stores. CockroachDB changefeeds emit row changes to Kafka, webhooks, or cloud storage, and Spanner change streams do the same in near real time. Read our CDC guide for the general method.

Coordinate through shared task tables

Multi agent work coordinates through task rows rather than chat history alone. Agents claim tasks with locking reads, write results in the same transaction, and emit events through CDC. This gives you the shared state Microsoft recommends while keeping every handoff in one auditable log.

How should a team sequence the build?

Start with one workflow where wrong actions are costly and the data is already in SQL. Get transactions and tools right first, then add meaning, then widen scope. Each step should prove value before the next one starts.

1. Model agent state as tables, including tasks, claims, plans, and tool results, with serializable isolation for decision paths.

2. Wrap every write in an idempotent typed tool and add retry handling for 40001 errors.

3. Define the dozen metrics and entities that workflow depends on in a semantic layer under version control.

4. Resolve the core entities across the systems the workflow touches.

5. Stream changes into the context layer and monitor its lag.

6. Add tracing and provenance before giving agents more tools or more data.

The cost of skipping steps is real. Gartner predicts over 40 percent of agentic AI projects will be canceled by the end of 2027, citing costs, unclear value, and weak risk controls. For the wider picture, see our agentic enterprise architecture guide and the comparison of RAG, knowledge graphs, and semantic layers.

Frequently asked questions

Why use distributed SQL for AI agents?

Agents run multi step work that reads, decides, and writes, often many agents at once. Distributed SQL gives them ACID transactions, strong consistency, and failure tolerance across zones or regions while scaling horizontally. That combination lets you keep task claims, plans, tool results, and business writes in one correct store instead of stitching together caches, queues, and a single node database.

Which isolation level should AI agents use?

Use serializable for any agent that checks a condition and then writes based on it, such as approving a refund or claiming a task. Snapshot and read committed are faster under contention but allow anomalies that look like reasonable decisions on stale facts. If you lower isolation, add explicit row locks for those check then write paths.

Is CockroachDB serializable by default?

Yes. CockroachDB runs transactions at SERIALIZABLE by default and also offers READ COMMITTED. Under serializable, the application must handle transaction retry errors with SQLSTATE 40001. Read committed transactions do not need client side retries, but they permit some anomalies, so reserve that level for work where an agent does not act on what it just read.

What is the difference between external consistency and serializability?

Serializability means results match some serial order of transactions. External consistency, which Google Spanner provides, adds that the serial order must match real commit time. If one transaction commits before another starts, nobody can see the second without the first. For agents in different regions acting on shared entities, that removes a class of confusing ordering bugs.

Can a distributed SQL database replace a semantic layer?

No. A database stores facts and enforces constraints, but it does not say what active customer or net revenue means. A semantic layer and ontology supply those definitions, metrics, and relationships. Some databases now add graph and vector features, which help retrieval, but definitions still need to be modeled, versioned, and governed as their own asset.

How much does a semantic layer improve agent accuracy?

Published results point the same way. A 2023 benchmark found GPT-4 answered 16 percent of enterprise questions correctly over raw SQL schemas and 54 percent with a knowledge graph. An April 2026 study measured 17 to 23 point gains from explicit business semantics across current models. A dbt vendor benchmark reported 98 to 100 percent with a semantic layer.

How do agents keep a knowledge graph in sync with the database?

Use change data capture rather than asking agents to write to two stores. CockroachDB changefeeds and Spanner change streams emit row changes in near real time, and a pipeline applies them to the graph or semantic store. Agents write once, inside a transaction, and the context layer catches up from the change log with clear lag you can monitor.

Why do AI agents need entity resolution?

The same customer, supplier, or product appears under different keys and spellings across CRM, billing, and support systems. Without a resolved entity, an agent may refund one record while another agent upgrades a duplicate. Entity resolution links those records to one identity with a stable key, so agents reason about the real world object rather than about whichever row they found first.

Should agents write directly to production tables?

Rarely. Expose writes as narrow, typed tools that run a known transaction, check permissions, and record who acted and why. That turns a free form SQL risk into a reviewed business operation. Read access can be broader, but it should still go through governed definitions and respect the permissions of the user the agent acts for.

Why do agentic AI projects get canceled?

Gartner predicts over 40 percent of agentic AI projects will be canceled by the end of 2027, citing escalating costs, unclear business value, and inadequate risk controls. Weak foundations feed all three. Agents that act on inconsistent state or ambiguous definitions make errors that are expensive to find, hard to explain to auditors, and difficult to tie to a business result.