Cost-effective context storage with external columns

Background

Agent and Copilot applications are becoming the fastest-growing and most resource-intensive workloads for OLTP databases. Consider a typical chat_messages table. Structured metadata like session_id / user_id / role / ts / token occupies only a few hundred bytes per row and handles nearly all filtering and aggregation. In contrast, large JSON fields like content / tool_calls / context_snapshot / attachments can range from kilobytes to megabytes. A single row containing context from a RAG query, a long JSON response from a tool, or a base64-encoded screenshot can easily exceed 100 KB. This payload often accounts for over 95% of the total data volume, leading to high storage costs, Buffer Pool contention, and bloated backup sizes.

The external column feature in PolarDB-X addresses this challenge by offloading large fields, such as AI conversation text, chain-of-thought data, images, or PDF attachments, from the primary table to OSS. When a large field is offloaded, the primary table stores only a pointer to its location, while the actual data resides in OSS. Using an external column in PolarDB-X requires no changes to your application logic. Simply add the EXTERNALIZE keyword to the column definition when you create the table. Your INSERT / UPDATE / DELETE / SELECT statements work exactly as they would with a regular table.

Benefits

• Lower costs: Storing large fields in OSS reduces storage costs from SSD prices to object storage prices. Additionally, backups based on OSS benefit from techniques like deduplication, further lowering backup costs.

• Greater stability: The Binlog size on the data node (DN) drops from megabytes to kilobytes. Commits from other services in the same database are no longer affected by large-field writes, resulting in more stable tail latency.

• Better memory efficiency: Offloading large text fields prevents them from consuming limited Buffer Pool memory. The Buffer Pool can then be dedicated to caching frequently accessed small metadata, leading to better cache efficiency for the same amount of memory.

Typical scenarios

Storing chat histories for AI Agents is a typical scenario for external columns. A single session can contain multi-turn conversations, code snippets, and tool call results, with individual messages ranging from kilobytes to megabytes. As the number of sessions grows from millions to billions, storing this data directly in InnoDB can bloat the Binlog and exhaust the Buffer Pool. Managing a dual-system architecture with both MySQL and OSS requires you to handle transactional consistency and garbage collection (GC). External columns combine the simplicity and transactional semantics of InnoDB with the low cost of OSS, and let the database engine handle the complexity.

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_session (session_id, created_at)
) PARTITION BY KEY(session_id);

-- Write data
INSERT INTO chat_messages(session_id, user_id, role, content, token_count, created_at)
VALUES (10001, 42, 'user', '{...conversation content...}', 1200, NOW());

-- Assemble context
SELECT role, content FROM chat_messages
WHERE session_id = 10001 ORDER BY created_at DESC LIMIT 20;

-- Update data
UPDATE chat_messages SET content = '{...new content...}' WHERE id = 1;

-- Delete data
DELETE FROM chat_messages WHERE session_id = 10001;

This feature is completely transparent to ORMs like MyBatis/JPA and AI frameworks like LangChain/LlamaIndex. You can integrate session data seamlessly with a single MySQL table, requiring zero code changes on the application side.

Prerequisites

• This feature is available only for Enterprise Edition instances.

• Your instance version must be 2.6.0_5.4.21-20260619 or later.

  Note

  • For information about the instance version naming rules, see Release notes.

  • For information about how to view the version of an instance, see View and update the engine version of a PolarDB for Xscale instance.

Syntax

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_session (session_id, created_at)
) PARTITION BY KEY(session_id);

ALTER TABLE chat_messages ADD COLUMN reasoning LONGTEXT EXTERNALIZE;

Application transparency

After the table is created, your application's SQL queries remain exactly the same.

INSERT INTO chat_messages(session_id, user_id, role, content, token_count, created_at)
VALUES (10001, 42, 'assistant', '{...response content...}', 800, NOW());

UPDATE chat_messages SET content = '{...new content...}' WHERE id = 1;

SELECT content FROM chat_messages WHERE session_id = 10001;

The database automatically handles uploading large fields to and fetching them from OSS. This process is invisible to the application. If a SQL query does not involve an external column (for example, querying only the ROLE column), it follows a path involving only the data node (DN), delivering performance identical to a regular table.

How it works

• The engine automatically routes data based on column size

  • Over 100 KB: Data is uploaded directly to OSS during a write operation. The primary table stores only a pointer. The transaction waits for confirmation from OSS before committing, ensuring full transactional semantics.

  • Kilobyte-level: Data is first written to a local staging table (InnoDB), and the operation returns immediately. The data is then flushed to OSS asynchronously in the background. This provides write latency equivalent to writing directly to InnoDB, while still benefiting from the long-term storage cost of OSS.

• Reads use a four-level cache

  • Data is fetched from local memory, then local SSD, then via an RPC call to another compute node (CN), and finally from OSS as a last resort. Most hot reads are served by the first two levels, avoiding access to OSS.

• Deletions are managed automatically by the engine

  • After you DELETE a row, the corresponding OSS object is cleaned up by GC after all active transactions end, so the application layer does not need to write reconciliation scripts or manually delete it.

Usage examples

CREATE TABLE chat_messages_v2 (
    id          BIGINT PRIMARY KEY AUTO_INCREMENT,
    session_id  BIGINT,
    role        ENUM('user','assistant','system'),
    token_count INT,
    created_at  DATETIME,
    content     LONGTEXT EXTERNALIZE,
    attachments LONGBLOB EXTERNALIZE,
    tool_calls  LONGTEXT EXTERNALIZE,
    reasoning   LONGTEXT EXTERNALIZE,
    KEY idx_session (session_id, created_at)
) PARTITION BY KEY(session_id);

-- Write a multimodal message
INSERT INTO chat_messages_v2(session_id, role, content, reasoning, token_count, created_at)
VALUES (10001, 'assistant', 'Main answer text...', 'Chain-of-thought content...', 3500, NOW());

-- Daily operations read only the content column
SELECT content FROM chat_messages_v2 WHERE session_id = 10001 ORDER BY created_at;

-- The reasoning column is read only for auditing
SELECT reasoning FROM chat_messages_v2 WHERE id = 12345;

-- Reuse the previous chat_messages table
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_session (session_id, created_at)
) PARTITION BY KEY(session_id);

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 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);
    }
}

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);
    }
}

Usage limits

Performance reference

The following data is from a public cloud instance with four Compute Nodes (CNs) and four Data Nodes (DNs). The instance uses a simple schema with a primary key and one MEDIUMBLOB column.

Use cases

• Suitable use cases: Use external columns for large fields written and read by a primary key or session ID. Examples are message bodies, JSON snapshots, and thought chains. External columns are also good for binary data, such as images, PDFs, and video files. This simplifies your system. You can store data directly in an external column. This removes the need to manage a separate system with Object Storage Service (OSS) and a metadata database. The engine handles transactions and manages the data lifecycle. This method is perfect for access patterns that write and read whole objects.

• Unsuitable use cases:

  • Frequent cold reads: After a cache miss, fetching data from the origin OSS adds 20+ ms of latency. If your use case involves frequent lookups of historical cold data and is sensitive to latency, consider the trade-offs.

  • Searching large fields: External columns do not support secondary indexes, full-text search, or LIKE fuzzy queries. Fields that require searching should be kept in regular columns of the main table.