# Storage Optimization

> Compression codecs, TTL policies, tiered storage, part management, and disk space optimization.

- Skill: `terrylica/storage-optimization` (Agent Skill)
- Install (CLI): `npx skillmds@latest add terrylica/storage-optimization`
- Raw SKILL.md: https://api.skillmd.com/api/skills/terrylica/storage-optimization/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: terrylica (https://skillmd.com/u/terrylica)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/terrylica/storage-optimization

---


# Storage Optimization

## Compression Codecs
- Default: LZ4 — fast, moderate compression
- `ZSTD(level)` — better compression, slower. Level 1-22 (3 is good default)
- `Delta` + ZSTD — excellent for time-series monotonic data
- `DoubleDelta` — best for timestamps and counters
- `Gorilla` — optimized for floating-point gauge metrics
- `T64` — for integer columns with small range
- Per-column: `ALTER TABLE t MODIFY COLUMN col TYPE UInt32 CODEC(Delta, ZSTD(3))`
- In-place codec change: `ALTER TABLE t MODIFY COLUMN col CODEC(Delta, ZSTD(3))`

## TTL Policies
- Table-level: `ALTER TABLE t MODIFY TTL event_time + INTERVAL 90 DAY`
- Column-level: `ALTER TABLE t MODIFY COLUMN old_col TTL event_time + INTERVAL 30 DAY`
- Move to volume: `TTL event_time + INTERVAL 7 DAY TO VOLUME 'cold'`
- Delete: `TTL event_time + INTERVAL 365 DAY DELETE`
- Monitor TTL merges: `system.merges WHERE is_ttl_merge = 1`

## Tiered Storage
- Define storage policies in config: hot (SSD) → warm (HDD) → cold (S3)
- `<storage_configuration>` in config.xml
- Move rules: `TTL event_time + INTERVAL 7 DAY TO DISK 'hdd'`
- Check disk usage: `system.disks`
- Check volume usage: `system.storage_policies`

## Part Management
- Monitor part count: `SELECT count() FROM system.parts WHERE active AND database = 'db' AND table = 't'`
- Too many parts (>300 per partition) = degraded performance
- Detached parts: check `system.detached_parts`, clean with `DROP DETACHED PART`
- Part size target: 1-10GB per part for optimal performance

## Disk Space Recovery
- `DROP PARTITION` for bulk deletion by time
- `TRUNCATE TABLE` for full table cleanup
- Check for orphaned data: detached parts, temporary files
- System tables can grow large — set TTL on query_log, trace_log, etc.
- Load the `troubleshooting` skill for disk-full recovery procedures.