评估数据库表分区与在线迁移方案
依据查询模式和数据保留需求判断分区必要性,给出分区设计、约束处理及可回滚迁移步骤。
完整提示词
<context>
A DBA or backend engineer has a large, growing table on {{database}} and is considering partitioning. Partitioning is a maintenance and lifecycle tool more than a speed tool: it pays off when queries filter on the partition key so whole partitions are pruned, and when old data is removed by dropping partitions instead of mass deletes. It hurts when the key does not appear in most queries (every query visits every partition), when there are thousands of tiny partitions, when unique constraints must include the partition key and the application relies on uniqueness of another column, and when foreign keys or the engine's limits rule it out. Often a better index, a covering index or an archival job solves the actual problem.
</context>
<task>
<table_ddl>
{{table_ddl}}
</table_ddl>
<query_patterns>
{{query_patterns}}
</query_patterns>
1. Verdict first: partition, do not partition, or not yet (with the trigger). Base it on whether a partition key appears in the hot queries and the retention rule, and whether simpler fixes would solve the stated problem.
2. Evidence: for each query, whether it would prune with the proposed key, and what each stated problem (bloat, slow deletes, vacuum time, index size) gains.
3. Partition design: key and method (range for time and lifecycle, list for a small set of tenants or regions, hash to spread write hot spots only when pruning is not the goal), interval or count with the arithmetic (aim for partitions that stay manageable to maintain and index, and avoid thousands of partitions; state the target), default partition handling, and sub-partitioning only if justified.
4. Indexes and constraints: primary key and unique constraints must include the partition key in many engines (verify for this version); say how uniqueness of other columns will be enforced instead. Local indexes per partition; foreign keys to and from the table and what the engine supports (to verify).
5. Maintenance: creating future partitions ahead of time (a scheduled job or extension, with an alert if fewer than N future partitions exist), dropping or detaching old partitions per retention, statistics per partition, and monitoring partition count and size.
6. Migration path for the existing table online: create the partitioned table, dual-write or trigger-based copy or attach the existing table as an old partition where the engine allows, backfill in throttled batches, verify counts and checksums, switch reads and writes with a short lock, and the rollback. Name each step's lock.
</task>
<constraints>
- Every engine limit or feature (unique constraints, foreign keys, attach and detach behaviour, online options) is stated with "verify for this version" unless you are sure.
- Show size arithmetic; do not invent row counts.
- If DDL, queries or retention are missing, ask, and mark assumptions as [X].
- Every DDL step names its lock and a rollback.
{{> output/uncertainty}}
</constraints>
<output_format>
## Verdict
One line, then up to three reasons.
## Evidence
Table: query or problem | prunes or helps? | why.
## Partition design
Bullets and the DDL in one code block.
## Indexes and constraints
Bullets, including how lost uniqueness is enforced.
## Maintenance
Checklist and the job sketch.
## Migration path
Numbered steps with lock, duration estimate and rollback.
## Risks and open questions
Bullets.
</output_format>可替换内容
- {{database}}
- {{table_ddl}}
- {{query_patterns}}
- {{> output/uncertainty}}
- [X]
分类与标签
编程开发、数据库分区、查询优化、在线迁移、数据保留