Partitioning

-- ===== Why partition =====
-- - Drop a partition (DROP PARTITION) instead of DELETE millions of rows
-- - Bound query scans to one partition via partition pruning
-- - Spread writes across files or disks

-- Caution: partitioning is NOT a silver bullet. Often correct indexes beat partitioning for read-heavy workloads.

-- ===== RANGE partitioning (most common) =====
CREATE TABLE events (
  id BIGINT UNSIGNED AUTO_INCREMENT,
  occurred_at DATETIME NOT NULL,
  data JSON,
  PRIMARY KEY (id, occurred_at)
)
PARTITION BY RANGE (TO_DAYS(occurred_at)) (
  PARTITION p2024_01 VALUES LESS THAN (TO_DAYS('2024-02-01')),
  PARTITION p2024_02 VALUES LESS THAN (TO_DAYS('2024-03-01')),
  PARTITION p2024_03 VALUES LESS THAN (TO_DAYS('2024-04-01')),
  PARTITION pmax     VALUES LESS THAN MAXVALUE
);

-- Partition pruning: WHERE occurred_at = '2024-02-15' touches only p2024_02.
-- Drop a month: ALTER TABLE events DROP PARTITION p2024_01;

-- ===== LIST partitioning =====
CREATE TABLE orders (
  id BIGINT, region VARCHAR(2), PRIMARY KEY (id, region)
)
PARTITION BY LIST COLUMNS (region) (
  PARTITION pAU VALUES IN ('AU','NZ'),
  PARTITION pEU VALUES IN ('UK','DE','FR'),
  PARTITION pUS VALUES IN ('US','CA')
);

-- ===== HASH partitioning (spread evenly by id) =====
CREATE TABLE users (
  id BIGINT, name VARCHAR(100), PRIMARY KEY (id)
)
PARTITION BY HASH (id) PARTITIONS 8;

-- ===== KEY partitioning (server picks the hash function) =====
PARTITION BY KEY (id) PARTITIONS 4;

-- ===== Sub-partitioning =====
PARTITION BY RANGE (YEAR(created_at))
SUBPARTITION BY HASH (id) SUBPARTITIONS 4 (
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION p2025 VALUES LESS THAN (2026)
);

-- ===== Adding / removing partitions =====
ALTER TABLE events ADD PARTITION (
  PARTITION p2024_04 VALUES LESS THAN (TO_DAYS('2024-05-01'))
);

ALTER TABLE events REORGANIZE PARTITION pmax INTO (
  PARTITION p2024_04 VALUES LESS THAN (TO_DAYS('2024-05-01')),
  PARTITION pmax VALUES LESS THAN MAXVALUE
);

-- ===== Inspecting =====
SELECT partition_name, table_rows
FROM information_schema.partitions
WHERE table_schema = 'app' AND table_name = 'events';

EXPLAIN PARTITIONS SELECT * FROM events WHERE occurred_at = '2024-02-15';

-- ===== Limitations / gotchas =====
-- - All UNIQUE / PRIMARY keys must include the partition column
-- - Cannot use FOREIGN KEYs on partitioned tables (in many engines/versions)
-- - InnoDB only (MyISAM partitions are deprecated)
-- - Some queries (without partition column in WHERE) scan ALL partitions
-- - Adding a partition requires explicit DDL (no automatic rolling)

-- ===== Operational pattern =====
-- - Weekly cron: ADD next month's partition; DROP oldest
-- - Pair with logical / physical backups so dropped partitions are recoverable

-- ===== When partitioning wins =====
-- - Time-series data with bulk delete by date
-- - VERY large tables where partition pruning matters
-- - Regulatory data retention requirements

-- ===== When it hurts =====
-- - Tables where queries do not include the partition column
-- - Small tables (overhead > benefit)
-- - Workloads needing FOREIGN KEYs

-- ===== Patterns to internalise =====
-- - RANGE BY date for time-series + drop-old workflow
-- - HASH BY id for evenly-distributed write spreading
-- - Cron to roll partitions forward (LESS THAN MAXVALUE catch-all)
-- - EXPLAIN PARTITIONS to verify pruning

-- ===== Pitfalls =====
-- - WHERE on column NOT in the partition key -> all partitions scanned
-- - Cross-partition JOINs (slow)
-- - Cannot add FK constraints across partitions
-- - Forgetting MAXVALUE catch-all -> inserts beyond defined ranges fail

CREATE TABLE events (
    id BIGINT, ts DATETIME, payload JSON
) PARTITION BY RANGE (YEAR(ts)) (
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p2026 VALUES LESS THAN (2027),
    PARTITION pmax  VALUES LESS THAN MAXVALUE
);