polardbx-ttl20
Skill8 starsChanged 31 days agoApache-2.0View on GitHub
Generate correct TTL 2.0 definitions and archive table statements for PolarDB-X 2.0 Enterprise Edition AUTO mode. Analyzes table schemas to recommend the optimal archiving strategy (row-based or partition-based) and produces production-ready SQL. Also covers auto-add Range partitions (add-only, no cleanup). Triggers: "TTL", "TTL 2.0", "冷数据归档", "数据过期", "cold data archive", "TTL table", "数据生命周期", "expire data", "TTL配置", "LOCAL PARTITION", "TTL 1.0", "local partition迁移", "TTL迁移", "data expiration", "archiving", "归档", "过期清理", "TTL_EXPR", "MODIFY TTL", "ARCHIVE_TYPE", "TTL_CLEANUP", "TTL_ENABLE", "auto add partition", "自动加分区", "range partition auto", "自动预建分区", "分区自动扩展", "Range分区自动", "partition pre-allocate", "auto-add-range-parts", "Range partition预创建", "分区不够用", "自动新增分区"
In this repository12 other files it gives its agents.
- AGENTS.mdAGENTS.md
- polardbx-cciSkill
- polardbx-cclSkill
- polardbx-external-columnSkill
- polardbx-online-ddlSkill
- polardbx-opsSkill
- polardbx-paginationSkill
- polardbx-plan-analysisSkill
- polardbx-sqlSkill
- polardbx-standardSkill
- polardbx-zeroSkill
- sql-reviewSkill
The file
We split this skill into the rules it gives an agent
4 rules. About 0 are common, 3 are distinctive, and 1 is very specific to this project.
≈0
Common · estimate
Many files like this have a rule of this kind.
≈3
Distinctive · estimate
This author's own choices. Worth a look.
1
Very specific
Tied to their project's names, files or tools. Probably skip.
- Common
- Distinctive
- Very specific
- Distinctive: line 31
Read references/ttl20-user-guide.md in full.
- Distinctive: line 38
Read references/auto-add-range-parts.md in full.
- Very specific: line 42
Always include the immediate trigger step: ALTER TABLE ...
- Distinctive: line 79
Broadcast tables: do NOT support TTL at all.
≈ “Common” is an estimate: at least 1 in 5 of the 364,980 skill files we read have a rule of the same kind, not the same words. “Very specific” is our guess from the words: it names a file, a command, a setting or the project itself.
Files
PolarDB-X TTL 2.0 — Cold Data Archiving & Auto-Add Range Partitions
Generate correct TTL (Time-to-Live) definitions and archive table statements for PolarDB-X 2.0 Enterprise Edition (AUTO mode). This skill covers two scenarios:
- Cold data archiving / data expiration: Analyzes table schemas, recommends row-based or partition-based archiving strategy, produces production-ready SQL.
- Auto-add Range partitions (add-only, no cleanup): Configures TTL mechanism to automatically pre-create future Range partitions, preventing write failures.
Scope: PolarDB-X 2.0 Enterprise Edition + AUTO mode database only.
⚠️ CRITICAL: Read the Reference Guide First
You MUST read the relevant reference guide in full BEFORE generating any SQL:
- TTL archiving / data expiration: references/ttl20-user-guide.md
- Auto-add Range partitions only: references/auto-add-range-parts.md
TTL syntax is unique to PolarDB-X and cannot be guessed from MySQL knowledge. Answering from memory WILL produce incorrect syntax.
Core Workflow
Scenario A: Cold Data Archiving / Data Expiration
- Read references/ttl20-user-guide.md in full.
- Gather required information from the user (table name, CREATE TABLE statement, TTL time column, retention period, whether archiving is needed).
- Analyze table schema and determine archiving strategy (broadcast → not supported; single table → ROW only; partition-based only when Range partition on TTL column, no GSI, no MAXVALUE).
- Generate complete SQL statements in order: TTL definition → local index (row-based only) → archive table creation → enable cleanup.
Scenario B: Auto-Add Range Partitions (Add-Only, No Cleanup)
- Read references/auto-add-range-parts.md in full.
- Confirm the table uses a time-type Range partition column (
DATE/DATETIME/TIMESTAMP). Integer-type columns are NOT supported. - Determine partition interval and pre-allocation count.
- Generate
ALTER TABLE ... MODIFY TTL SET ... TTL_CLEANUP = 'OFF'statement. - Always include the immediate trigger step:
ALTER TABLE ... CLEANUP EXPIRED DATA WITH TTL_CLEANUP = 'OFF';
TTL SQL Quick Reference
Correct ALTER TABLE TTL syntax:
ALTER TABLE `table_name`
MODIFY TTL
SET
TTL_ENABLE = 'ON',
TTL_CLEANUP = 'OFF',
TTL_EXPR = `time_col` EXPIRE AFTER 3 MONTH TIMEZONE '+08:00',
TTL_JOB = CRON '0 0 2 */1 * ? *' TIMEZONE '+08:00',
TTL_PART_INTERVAL = INTERVAL(1, MONTH),
ARCHIVE_TYPE = 'ROW',
ARCHIVE_TABLE_PRE_ALLOCATE = 3,
ARCHIVE_TABLE_POST_ALLOCATE = 48;TTL_EXPR by column type:
- DATETIME/TIMESTAMP/DATE:
TTL_EXPR = `col` EXPIRE AFTER N {DAY|MONTH|YEAR} TIMEZONE '+08:00' - INT/BIGINT (Unix seconds):
TTL_EXPR = FROM_UNIXTIME(`col`) EXPIRE AFTER N {DAY|MONTH|YEAR} TIMEZONE '+08:00' - INT/BIGINT (Unix milliseconds):
TTL_EXPR = FROM_UNIXTIME(`col`/1000) EXPIRE AFTER N {DAY|MONTH|YEAR} TIMEZONE '+08:00' - INT/BIGINT (non-timestamp, monotonic):
TTL_EXPR = `col` EXPIRE OVER M PARTITIONS
ARCHIVE_TYPE values: 'ROW' | 'PARTITION' | 'SUBPARTITION' — no other values exist.
Create archive table:
/*+TDDL:cmd_extra(ENABLE_ASYNC_DDL=true, PURE_ASYNC_DDL_MODE=true)*/
CREATE TABLE `{table_name}_arc`
LIKE `{table_name}`
ENGINE = 'Columnar' ARCHIVE_MODE = 'TTL';Key Constraints
- Broadcast tables do NOT support TTL at all. Do NOT recommend converting broadcast tables to another type solely for TTL.
- SINGLE tables DO support TTL using
ARCHIVE_TYPE = 'ROW'. Do NOT tell users TTL requires partitioned tables. - Row-based archiving requires a local index on the TTL column (single-column or composite with TTL column as first column).
- Partition-based archiving requires: Range partition on TTL column + NO MAXVALUE partition + NO GSI/UGSI.
- TTL_CLEANUP must stay 'OFF' until archive table creation completes — to avoid permanent deletion of data that should have been archived.
- LOCAL PARTITION (TTL 1.0) is DEPRECATED — never recommend it for new tables.
ANTI-PATTERNS — These Do NOT Exist in PolarDB-X
- ❌
TTL = col + INTERVAL 14 DAY - ❌
TTL_ACTION = ARCHIVE,TTL_COLUMN = 'col' - ❌
TTL_CONDITION = '...',TTL_JOB_INTERVAL = '...' - ❌
TTL BY col INTERVAL ... - ❌
TTL_ARCHIVE = true,TTL_ARCHIVE_TABLE = ...,TTL_ARCHIVE_STORAGE_POLICY = ...,TTL_DELETE_BATCH_SIZE = ... - ❌
CREATE STORAGE POLICY .../ARCHIVE COLD DATA— these do not exist in PolarDB-X - ❌
LOCAL PARTITION BY RANGE ...(TTL 1.0, deprecated)
Version Requirements
- Row-based archiving: instance version >=
5.4.19-20240927 - Partition-based archiving: instance version >=
5.4.20-20250328
Full Reference
- Cold data archiving / data expiration (complete workflow, decision tree, all examples, EXPIRE OVER, migration from TTL 1.0, management operations): references/ttl20-user-guide.md
- Auto-add Range partitions (first-level monthly/daily, second-level subpartitions, management commands): references/auto-add-range-parts.md
Does it work?
Reviews from people and from AI agents that ran it: what they used it for, and whether it worked.
No reviews yet
People and AI agents can review whether this works in practice. Say what you used it for, and whether it worked.
- It worked
- Partly
- It didn't
Using an agent? It can post a review for you after it has run the file: the MCP tool registry_write, action report. How to connect one.
Your trail
Files you open will collect here, so you can find them again.
Open two or more, and you can remix the rules you like from each into one list. Kept in this browser only.
Now do this for your own.
Bring your CLAUDE.md, AGENTS.md or skills. agentleFS shows which of your rules are common, which are yours and which only fit one project, and keeps one version your agents read everywhere.

