By Wuzhe
AI applications have completely changed shape over the past two years. A single session with a coding assistant like Qoder is packed with multi-turn conversations, code snippets, and tool-call results — dozens or hundreds of turns stacked together. General-purpose chat products like Qwen, ChatGPT, and Claude must remember multi-turn context spanning several days word for word; a single user can run hundreds of turns in a day, interleaved with images, long documents, and chain-of-thought. These scenarios share one common trait: a single message grows from KB to MB, and the total session volume swells from millions to hundreds of millions.
This trend is still accelerating. In the GPT-3 era the context window was 4K tokens; GPT-4 reached 128K; Claude Opus 4 is already at 1M. Store a snapshot of one complete session before compression and it is a few MB this year — perhaps more than ten MB next year. What lives in the database only gets bigger, and bigger faster.
The industry has more than one solution to this problem:
| Solution | Typical practice | Strengths | Costs |
|---|---|---|---|
| PostgreSQL + jsonb | LangGraph default checkpointer | Ecosystem-friendly; coding style unchanged | Data still lives entirely on the primary database's disk; high migration cost for MySQL teams |
| MongoDB | LlamaIndex's built-in MongoDBChatStore | Flexible; natively supports nested structures | Weak transactions and weak SQL; a separate operations stack |
| Redis + backing store | Redis's official agent-memory-server | Extremely fast reads and writes | Memory is expensive; persistence still relies on a backing store |
Notice that MySQL is missing from this table. PostgreSQL, MongoDB, and Redis all have industry-proven solutions for AI scenarios. However, the MySQL ecosystem still lacks a widely recognized standard path.
Currently, mainstream practice in the MySQL ecosystem comes down to two routes:
Route 1: Store data directly in InnoDB. A LONGTEXT content column, one INSERT statement, done. It is simple, fully transactional, and maps directly through an ORM. But large columns and ordinary fields share the same data path: binlog bloats, primary-secondary replication comes under pressure, the buffer pool gets squeezed, and the whole thing strains once scale grows.
Route 2: MySQL + OSS. The business table stores only metadata and OSS reference keys, while large-column content is written separately to OSS. Storage costs come down and binlog pressure disappears, but transaction consistency, lifecycle management, and the complexity of operating two systems are all handled by the application layer.
Each route has its strengths: the first wins on simplicity and transactions, the second on cost and tiering. The design goal of external columns is to combine them: InnoDB's simplicity and transaction semantics + OSS's low cost and storage tiering — but with the complexity borne by the engine, not the application layer.
What does it look like in use? Almost identical to creating an ordinary table, except you add an EXTERNALIZE keyword after the column you want externalized:
CREATE TABLE chat_messages (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
session_id BIGINT,
user_id BIGINT,
role ENUM('user','assistant','system'),
token_count INT,
created_at DATETIME,
content LONGTEXT EXTERNALIZE -- Only this keyword is added
) PARTITION BY KEY(session_id);The application's INSERT / SELECT / DELETE syntax stays completely unchanged. It is transparent to ORMs — MyBatis and JPA map as usual, with no special adaptation needed. It is also transparent to frameworks like LangChain and LlamaIndex: the memory module works as before, only the backend changes from Redis or a local file to a MySQL table.
Among the large columns annotated with EXTERNALIZE, a 1 KB conversation snippet and a 1 MB PDF are two different businesses in storage terms: sending KB-scale data straight to OSS is uneconomical, because the fixed ~20 ms PUT overhead cannot be amortized; keeping MB-scale data in InnoDB runs straight into the whole set of old problems — LOB, binlog, buffer pool. External columns split these two kinds of columns onto their respective suitable paths at the CN layer: a 1 KB large column borrows a local fast path, while a 100 KB large column goes straight to OSS. The next two sections take each apart in turn.
From the application's viewpoint, writing a record with an external column is no different from writing to an ordinary table. Where and how the data is stored is the engine's business. Inside the engine, however, a 1 KB large column and a 100 KB large column take entirely different storage paths.
PolarDB-X uses a Compute Node (CN) + Data Node (DN) architecture. External-column writes are split at the CN layer:
100 KB large columns go straight to OSS. The DN's binlog carries only the blob_addr, not the large-column content, so the pressure of primary-secondary replication and serialized disk flushing disappears outright. The fixed overhead of a single OSS PUT is almost invisible when spread over the total transfer time of an MB-scale payload, and the cost it saves is substantial: LOB off-page page splits, the buffer pool being swallowed by large blocks of data, and the binlog bursting the primary-secondary channel — these troubles all vanish at once.
Transaction safety is not compromised either. If the OSS write fails, the InnoDB transaction rolls back and leaves no half-finished data. If OSS succeeds but the InnoDB commit fails, an orphan OSS object is produced, to be cleaned up later by background garbage collection (GC).
In distributed scenarios this guarantee matters even more: a single tool call by an AI agent produces three records — the user message, the assistant reply, and the tool result — and the application wants either all of them saved or none. If only two are saved, the next time the context window is assembled the model sees a fragmented conversation history, and output quality suffers directly. External columns wrap all of this inside the transaction, so the application need not worry about it.
What about 1 KB large columns? That same OSS-direct-write path is unfriendly to them. Something a single local InnoDB write could finish instantly gets dragged out dozens of times slower by OSS. In AI session scenarios, writing 1 KB large columns is a hot path, and it cannot be made to pay an extra cost there.
So the engine routes automatically by size: 1 KB large columns take a local fallback fast path, and 100 KB large columns go straight to OSS. The user notices nothing — the syntax is exactly the same as the table in the previous section — while the engine decides behind the scenes which path each piece of data takes. The next section explains how that fast path for 1 KB large columns is built.
OSS is a good storage medium for 100 KB large columns and a burden for 1 KB ones. For a single external-column design to win at both ends, 1 KB large columns need their own path: at the moment of writing, the data first lands in a local InnoDB staging table; once it accumulates to a certain point, a background process moves it to OSS in batches. This way, the write latency of 1 KB large columns is determined locally, while OSS's storage cost and tiering are still captured.
The whole path is just three steps:
How reads adapt is covered in the next section, on the multi-level cache.
When an application INSERTs a 1 KB large column, committing the transaction only needs to wait for that one row in the staging table to be flushed to disk; OSS's RTT never enters the hot path, so write-side latency is exactly the same as writing directly to an InnoDB table.
The data's final home is still OSS. The staging table merely buffers it; once a flush moves it to OSS, the staging table is dropped. Most long term data in the storage is OSS objects, at a unit price an order of magnitude lower.
As for engineering details such as the state machine, protection window, concurrent flush, and crash recovery — the engine handles them all. The application layer sees only an external column that writes fast and stores cheaply.
On a read, the DN returns the blob_addr and the CN fetches the content by that address. But if every read went back to OSS for the origin, the latency would be unacceptable. External columns design a dedicated four-level cache for this read path: local memory (microseconds) → local SSD (milliseconds) → RPC to another node's cache (milliseconds) → OSS (final fallback).
On write, the blob is synchronously written to the local cache, so it is present locally the instant the write completes, and subsequent reads within the same transaction hit the cache directly. 100 KB large columns are later pushed to OSS asynchronously, and 1 KB large columns are later flushed to OSS — whichever path is taken, reads are never held up.
Cross-CN scenarios do not fetch from OSS either. Suppose a user's request is load-balanced to CN1, but the previous turn was written on CN0. CN1 has no local cache, but it first asks CN0 over RPC, "Do you have this blob?" If so, it retrieves it directly; only if not does it go to OSS. This way, even as requests are distributed among multiple CNs, hot data still hits the cache.
There is one more fallback for 1 KB large columns: the staging table from the previous section is itself a local InnoDB query, one or two orders of magnitude faster than an OSS fetch. Before the data is flushed to OSS, even if the local caches on all CNs unexpectedly expire, a read back to the staging table is still at local speed. Once the flush completes and the staging table exits, OSS takes over — and by then the data has already been hit in the multi-level cache countless times, so the odds of fetching from OSS were low to begin with.
In AI sessions, the vast majority of reads are "read right after write" (assembling the context window), and the data almost always hits Level 1 or Level 2 cache. In our tests, hot-read latency is about the same as an ordinary column query, and users cannot tell that the data actually lives on OSS.
In the MySQL + OSS approach, deletion is the most error-prone step. Delete a row — who deletes the object on OSS, and when? Delete too early and someone else's read gets a 404; do not delete and the orphan file keeps burning money. Most teams write a scheduled reconciliation script, but never quite feel safe.
The fundamental knot for an application-layer reconciliation script is that it does not know whether the database still has old transactions reading old versions. A long query that started 30 seconds ago may still be using the old blob of that row from 30 seconds ago — and you just deleted it on OSS.
External columns pull this entirely into the engine. The engine's GC follows InnoDB's purge mechanism: it takes the global minimum active transaction view, and only versions older than that view may be truly deleted. The lifecycle of OSS objects is strictly protected by MVCC — cleaner than any reconciliation script. The application layer writes no cleanup logic, needs no reconciliation script, and never has to worry about deleting "too early" or "too late."
Read latency is comparable to InnoDB. On a hot read, InnoDB fetches from the buffer pool and an external column fetches from the Level 1 memory cache; both are memory hits, so the difference is negligible. A large share of AI-session requests are on the hot path, and here external columns carry no performance premium.
But equal latency does not mean equal cost. When InnoDB reads a large column, the entire LOB page is pulled into the buffer pool; these large blocks, no longer hot once read, push out index pages and the data pages of ordinary fields, and other TP workloads in the same database suffer for it. External columns pull large columns off this path, so the main storage's buffer pool holds only indexes and ordinary fields, which is clean and simple.
On the write side, 1 KB and 100 KB large columns each have their own story.
1 KB large columns: write performance = InnoDB. Columns in this tier take the staging path, so a write is a local InnoDB INSERT on the DN. Put plainly, external columns use InnoDB underneath too — writing a 1 KB piece of data is just as fast as INSERTing directly into an InnoDB table. There is no performance premium, but the storage savings are real money: in AI session scenarios, KB-scale messages number in the hundreds of millions, and the unit price of main-storage disk versus OSS differs by an order of magnitude, so at scale the annual bill differs substantially. Equal performance means you pay nothing, and the saved cost is what you truly gain.
100 KB large columns: a clean win. Columns in this tier take the OSS-direct-write path. On the InnoDB side, large columns mainly face the whole drag chain of LOB off-page page splits, the buffer pool being swallowed at once, and serialized binlog write queuing; external columns strip all these costs away, with each blob taking its own OSS object so writes do not block one another. At scale, the write-latency gap is an order of magnitude, and storage cost is likewise billed at the OSS unit price.
Just how large can the gap get? Measured under identical table schemas:
| Column size | InnoDB Avg / P99 | External column Avg / P99 |
|---|---|---|
| 200 KB | 30 / 79 ms | 37 / 70 ms |
| 500 KB | 78 / 225 ms | 51 / 87 ms |
| 1 MB | 201 ms / 1.2 s | 80 / 162 ms |
| 2 MB | 406 ms / 1.7 s | 149 / 387 ms |
| Column size | InnoDB Avg / P99 | External column Avg / P99 |
|---|---|---|
| 200 KB | 152 ms / 1.2 s | 101 / 138 ms |
| 500 KB | 374 ms / 2.3 s | 137 / 425 ms |
| 1 MB | 929 ms / 4.8 s | 262 / 774 ms |
| 2 MB | 5.6 s / 48.7 s | 514 ms / 1.5 s |
At 64 concurrency, external columns pull ahead from 500 KB onward; by 1 MB, InnoDB's tail latency has climbed to the seconds range. At 256 concurrency the gap is even starker: at 1 MB, InnoDB's P99 approaches 5 seconds, and at 2 MB it hits 48 seconds, while external columns stay comfortably below a second.
The conclusion is direct: regardless of column size, external columns should be the default. A 1 KB large column writes as fast as writing InnoDB directly, and a 100 KB large column leaves native InnoDB an order of magnitude behind under high concurrency. No cost on the write side, and storage cost significantly decreases.
To be honest, external columns are not a cure-all; two boundaries should be stated up front:
Cold reads are a notch slower. On a cache miss, fetching a large column from OSS takes 20+ ms, slower than InnoDB reading a LOB from disk. In AI sessions almost everything is the "read right after write" hot path, so the odds of actually hitting a cold read are low, and the four-level cache above exists precisely as that backstop; if your scenario involves heavy cold-data lookups (for example, sessions from months ago are queried for analysis) and you have read-latency requirements, weigh it carefully.
You cannot build an index on an external column. Content on OSS cannot support secondary indexes, range scans, or LIKE fuzzy queries. Fortunately, external columns carry exactly the kind of data you "fetch whole by primary key" (message bodies, attachments, chain-of-thought), while the small fields that need to be searched (user_id, session_id, created_at) remain normally indexed on the main table. If you plan to externalize a column that needs full-text search, it is not a good fit today. That said, full-text indexing for external text columns is already on the roadmap and will be added in the future.
After all this theory, what does it look like in a real workload? Take an AI agent's session storage as an example. A typical session system needs to store the content of each conversation turn (KB to MB scale) while supporting query-by-session for history, query-by-user for recent sessions, and deletion of expired data. With external columns, one table is enough:
CREATE TABLE chat_messages (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
session_id BIGINT,
user_id BIGINT,
role ENUM('user','assistant','system'),
token_count INT,
created_at DATETIME,
content LONGTEXT EXTERNALIZE,
KEY idx_user_recent (user_id, created_at),
KEY idx_session (session_id, created_at)
) PARTITION BY KEY(session_id);The core read pattern of AI sessions is "fetch a stretch of history by session." Production systems usually add a session cache at the application layer to hold hot data, but session switches, multi-device sync, application restarts, cache evictions, and cross-node agent recovery all push requests through to the DB. Once they do, the path looks like this:
SELECT id, role, content, token_count
FROM chat_messages
WHERE session_id = ?
ORDER BY created_at DESC
LIMIT 20So ordinary it hardly looks like it has any pitfalls. But how many bytes one context assembly pulls depends on how many tokens the model can take: mainstream LLMs now push the context window to 1M tokens, corresponding to 2–3 MB of bytes. To give the model enough context, engineers stuff it to that limit every time. Every pass-through means pulling those 2–3 MB from storage again.
Looking at the read side alone, external columns actually carry no premium. When hot data hits from memory, InnoDB's buffer pool and an external column's Level 1 cache are almost equally fast.
But AI sessions are not read-only. At the same time, new turns keep streaming in — user messages, assistant replies, tool-call results. Under InnoDB, writes and reads share the same I/O resources: writing a large column runs the whole chain of data pages, redo log, and binlog, tying up disk bandwidth and the commit channel, so reads sharing that path queue up behind it. The 256-concurrency table earlier makes this congestion tangible: once message bodies exceed 1 MB, InnoDB's write tail latency runs into the seconds range or higher.
External columns divert large columns off this path entirely:
• Only blob_addr remains in the DN's binlog, so write pressure no longer floods the main path.
• Index pages and ordinary field pages face less competition from large columns in the buffer pool, so hits are more stable.
• Large-column write tail latency is also held down: under external columns, the P99 of MB-scale blobs is an order of magnitude lower than InnoDB's.
On the read side, pulling large columns out of main storage lets ordinary fields, indexes, and hot data live more comfortably in the buffer pool.
An AI message is no longer plain text. An assistant reply typically contains:
• content: the answer the user actually sees.
• attachments: images, PDFs, original documents.
• tool_calls: the JSON results of tool calls.
• reasoning: the chain-of-thought (open-source reasoning models like DeepSeek-R1 and Qwen QwQ emit the entire reasoning process in full).
These fields differ in semantics and in read frequency. content is read every time the context is assembled; reasoning is usually read only during audits, debugging, or tracing the cause of an error — ordinary model conversation does not need it at all.
External columns let the schema grow naturally; add as many columns as you like:
content LONGTEXT EXTERNALIZE,
attachments LONGBLOB EXTERNALIZE,
tool_calls LONGTEXT EXTERNALIZE,
reasoning LONGTEXT EXTERNALIZE,The key is that cache granularity follows the column: each external column maps to its own OSS object and its own cache entry.
The moment they are written, all columns go write-through into Level 1 — none are cold. But the subsequent access pattern sorts them out: content is selected over and over, so LRU keeps it in L1; reasoning goes untouched, so LRU gradually evicts it until it lives only in OSS. When an audit needs it, SELECT reasoning fetches it back from OSS in tens of milliseconds — perfectly acceptable for a debugging scenario.
Earlier this article promised that external columns are transparent to frameworks like LangChain and LlamaIndex. Here we make good on that promise in code.
The mainstream AI frameworks in the Java ecosystem are Spring AI and LangChain4j, and their chat memory interfaces differ in semantics: Spring AI is "append per message," LangChain4j is "full replacement." The two contracts map to different schemas, but external columns connect to both.
Spring AI (based on v1.0.x)
public interface ChatMemory {
void add(String conversationId, Message message);
void add(String conversationId, List<Message> messages);
List<Message> get(String conversationId, int lastN);
void clear(String conversationId);
}The "append per message" semantics naturally match the row-stored chat_messages table shown earlier:
public class PolarDBXChatMemory implements ChatMemory {
private final JdbcTemplate jdbc;
@Override
public void add(String conversationId, Message message) {
jdbc.update(
"INSERT INTO chat_messages(session_id, role, content, created_at) " +
"VALUES (?, ?, ?, NOW())",
conversationId, message.getMessageType().name(), message.getText());
}
@Override
public void add(String conversationId, List<Message> messages) {
messages.forEach(m -> add(conversationId, m));
}
@Override
public List<Message> get(String conversationId, int lastN) {
return jdbc.query(
"SELECT role, content FROM chat_messages WHERE session_id = ? " +
"ORDER BY created_at DESC LIMIT ?",
(rs, i) -> toMessage(rs.getString("role"), rs.getString("content")),
conversationId, lastN);
}
@Override
public void clear(String conversationId) {
jdbc.update("DELETE FROM chat_messages WHERE session_id = ?", conversationId);
}
}LangChain4j (based on v0.x)
public interface ChatMemoryStore {
List<ChatMessage> getMessages(Object memoryId);
void updateMessages(Object memoryId, List<ChatMessage> messages);
void deleteMessages(Object memoryId);
}The "full replacement" semantics suit a KV table better: the entire session's message sequence is serialized to JSON and stored in one external large column:
CREATE TABLE chat_memory_store (
session_id BIGINT PRIMARY KEY,
messages LONGTEXT EXTERNALIZE
);
public class PolarDBXChatMemoryStore implements ChatMemoryStore {
private final JdbcTemplate jdbc;
@Override
public List<ChatMessage> getMessages(Object sessionId) {
String json = jdbc.query(
"SELECT messages FROM chat_memory_store WHERE session_id = ?",
rs -> rs.next() ? rs.getString(1) : null,
sessionId);
return json == null ? List.of() : ChatMessageDeserializer.messagesFromJson(json);
}
@Override
public void updateMessages(Object sessionId, List<ChatMessage> messages) {
String json = ChatMessageSerializer.messagesToJson(messages);
jdbc.update(
"INSERT INTO chat_memory_store(session_id, messages) VALUES (?, ?) " +
"ON DUPLICATE KEY UPDATE messages = VALUES(messages)",
sessionId, json);
}
@Override
public void deleteMessages(Object sessionId) {
jdbc.update("DELETE FROM chat_memory_store WHERE session_id = ?", sessionId);
}
}Two contracts, two schemas, both pure SQL — no OSS calls, no compensation logic, no reconciliation scripts. Whether content or messages is a large column, whether the cache hits, how the blob is GC'd — all of this happens inside the engine, invisible to the application code and needing to stay that way.
Not just session storage. This applies to any scenario where "structured fields need to be queried + large columns need transaction consistency": RAG knowledge bases, audit logs, ticket attachments, raw audio and video files.
A good abstraction lets you forget how complex things are underneath. You write your SQL; the database worries about where the bytes go.
Get Started with Tair Semantic Caching in 4 Steps: Stop Burning Money on Repetitive AI Queries
What Kind of PostgreSQL Do We Need When 80% of Databases Are Automatically Created by AI?
ApsaraDB - January 16, 2026
ApsaraDB - August 13, 2024
ApsaraDB - June 24, 2026
ApsaraDB - November 1, 2022
ApsaraDB - October 17, 2024
ApsaraDB - July 22, 2026
PolarDB for MySQL
Alibaba Cloud PolarDB for MySQL is a cloud-native relational database service 100% compatible with MySQL.
Learn More
PolarDB for PostgreSQL
Alibaba Cloud PolarDB for PostgreSQL is an in-house relational database service 100% compatible with PostgreSQL and highly compatible with the Oracle syntax.
Learn More
PolarDB for Xscale
Alibaba Cloud PolarDB for Xscale (PolarDB-X) is a cloud-native high-performance distributed database service independently developed by Alibaba Cloud.
Learn More
AnalyticDB for MySQL
AnalyticDB for MySQL is a real-time data warehousing service that can process petabytes of data with high concurrency and low latency.
Learn MoreMore Posts by ApsaraDB
