SQL Table Partitioning: Range, List & Hash Partitioning

-- =============================================
-- RANGE PARTITIONING (PostgreSQL 10+)
-- =============================================

-- Buat tabel utama (PARTITIONED TABLE)
CREATE TABLE transactions (
    id              BIGSERIAL,
    customer_id     INT NOT NULL,
    amount          DECIMAL(12,2) NOT NULL,
    transaction_date DATE NOT NULL,
    status          VARCHAR(20) DEFAULT 'completed',
    description     TEXT
) PARTITION BY RANGE (transaction_date);


-- =============================================
-- Buat partisi per tahun
-- =============================================
CREATE TABLE transactions_2023
    PARTITION OF transactions
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE TABLE transactions_2024
    PARTITION OF transactions
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

CREATE TABLE transactions_2025
    PARTITION OF transactions
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

CREATE TABLE transactions_2026
    PARTITION OF transactions
    FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

-- Default partition untuk data di luar range
CREATE TABLE transactions_default
    PARTITION OF transactions DEFAULT;


-- =============================================
-- Sub-partisi per kuartal (lebih granular)
-- =============================================
CREATE TABLE transactions_q1_2026
    PARTITION OF transactions
    FOR VALUES FROM ('2026-01-01') TO ('2026-04-01');

CREATE TABLE transactions_q2_2026
    PARTITION OF transactions
    FOR VALUES FROM ('2026-04-01') TO ('2026-07-01');

CREATE TABLE transactions_q3_2026
    PARTITION OF transactions
    FOR VALUES FROM ('2026-07-01') TO ('2026-10-01');

CREATE TABLE transactions_q4_2026
    PARTITION OF transactions
    FOR VALUES FROM ('2026-10-01') TO ('2027-01-01');


-- =============================================
-- Query — partition pruning otomatis!
-- =============================================
-- Query ini HANYA memindai partisi 2026:
EXPLAIN SELECT * FROM transactions
WHERE transaction_date >= '2026-01-01'
  AND transaction_date < '2027-01-01';

-- Output: hanya partisi transactions_2026 yang di-scan


-- =============================================
-- Index pada partisi
-- =============================================
-- PostgreSQL otomatis membuat index pada semua partisi
-- ketika Anda membuat index pada tabel induk:
CREATE INDEX idx_transactions_customer
ON transactions(customer_id);

CREATE INDEX idx_transactions_date
ON transactions(transaction_date);

-- =============================================
-- RANGE PARTITIONING (MySQL 8+)
-- =============================================

CREATE TABLE transactions (
    id              BIGINT AUTO_INCREMENT,
    customer_id     INT NOT NULL,
    amount          DECIMAL(12,2) NOT NULL,
    transaction_date DATE NOT NULL,
    status          VARCHAR(20) DEFAULT 'completed',
    PRIMARY KEY (id, transaction_date),
    INDEX idx_customer (customer_id),
    INDEX idx_date (transaction_date)
)
PARTITION BY RANGE (YEAR(transaction_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p2026 VALUES LESS THAN (2027),
    PARTITION pmax  VALUES LESS THAN MAXVALUE
);


-- =============================================
-- RANGE COLUMNS (langsung pakai DATE)
-- =============================================
CREATE TABLE logs (
    id          BIGINT AUTO_INCREMENT,
    log_level   VARCHAR(10),
    message     TEXT,
    log_date    DATE NOT NULL,
    PRIMARY KEY (id, log_date)
)
PARTITION BY RANGE COLUMNS(log_date) (
    PARTITION p_jan VALUES LESS THAN ('2026-02-01'),
    PARTITION p_feb VALUES LESS THAN ('2026-03-01'),
    PARTITION p_mar VALUES LESS THAN ('2026-04-01'),
    PARTITION p_apr VALUES LESS THAN ('2026-05-01'),
    PARTITION p_may VALUES LESS THAN ('2026-06-01'),
    PARTITION p_jun VALUES LESS THAN ('2026-07-01'),
    PARTITION p_max VALUES LESS THAN MAXVALUE
);


-- =============================================
-- CATATAN PENTING MySQL:
-- =============================================
-- 1. PRIMARY KEY HARUS mencakup kolom partisi
--    PRIMARY KEY (id, transaction_date) ✅
--    PRIMARY KEY (id) ❌ (error!)
--
-- 2. UNIQUE INDEX juga harus mencakup kolom partisi
--
-- 3. Kolom partisi harus bagian dari semua index

-- =============================================
-- LIST PARTITIONING (PostgreSQL)
-- =============================================

CREATE TABLE customers (
    id          SERIAL,
    nama        VARCHAR(100) NOT NULL,
    email       VARCHAR(150),
    wilayah     VARCHAR(20) NOT NULL,
    kota        VARCHAR(50),
    created_at  TIMESTAMP DEFAULT NOW()
) PARTITION BY LIST (wilayah);

-- Partisi per wilayah Indonesia
CREATE TABLE customers_jawa
    PARTITION OF customers
    FOR VALUES IN ('DKI Jakarta', 'Jawa Barat', 'Jawa Tengah',
                   'Jawa Timur', 'Yogyakarta', 'Banten');

CREATE TABLE customers_sumatera
    PARTITION OF customers
    FOR VALUES IN ('Sumatera Utara', 'Sumatera Barat', 'Sumatera Selatan',
                   'Riau', 'Lampung', 'Aceh', 'Bengkulu', 'Jambi');

CREATE TABLE customers_kalimantan
    PARTITION OF customers
    FOR VALUES IN ('Kalimantan Barat', 'Kalimantan Tengah',
                   'Kalimantan Selatan', 'Kalimantan Timur',
                   'Kalimantan Utara');

CREATE TABLE customers_sulawesi
    PARTITION OF customers
    FOR VALUES IN ('Sulawesi Utara', 'Sulawesi Tengah',
                   'Sulawesi Selatan', 'Sulawesi Barat',
                   'Sulawesi Tenggara', 'Gorontalo');

CREATE TABLE customers_indonesia_timur
    PARTITION OF customers
    FOR VALUES IN ('Bali', 'NTB', 'NTT', 'Maluku', 'Maluku Utara',
                   'Papua', 'Papua Barat');

-- Default partisi untuk wilayah tidak dikenal
CREATE TABLE customers_lainnya
    PARTITION OF customers DEFAULT;


-- =============================================
-- Query dengan partition pruning
-- =============================================

-- Hanya scan partisi Jawa!
SELECT * FROM customers
WHERE wilayah IN ('DKI Jakarta', 'Jawa Barat');

-- Hanya scan partisi Sulawesi
SELECT nama, email FROM customers
WHERE wilayah = 'Sulawesi Selatan';


-- =============================================
-- LIST PARTITIONING (MySQL)
-- =============================================

CREATE TABLE orders_by_region (
    id          BIGINT AUTO_INCREMENT,
    customer_id INT NOT NULL,
    region      VARCHAR(20) NOT NULL,
    amount      DECIMAL(12,2),
    order_date  DATE,
    PRIMARY KEY (id, region),
    INDEX idx_customer (customer_id)
)
PARTITION BY LIST COLUMNS(region) (
    PARTITION p_jakarta   VALUES IN ('Jakarta'),
    PARTITION p_bandung   VALUES IN ('Bandung'),
    PARTITION p_surabaya  VALUES IN ('Surabaya'),
    PARTITION p_medan     VALUES IN ('Medan'),
    PARTITION p_makassar  VALUES IN ('Makassar'),
    PARTITION p_other     VALUES IN ('Denpasar', 'Semarang', 'Yogyakarta')
);

-- =============================================
-- HASH PARTITIONING (PostgreSQL)
-- =============================================

CREATE TABLE user_sessions (
    session_id  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id     INT NOT NULL,
    ip_address  INET,
    user_agent  TEXT,
    started_at  TIMESTAMP DEFAULT NOW(),
    ended_at    TIMESTAMP
) PARTITION BY HASH (user_id);

-- Buat 8 partisi (jumlah ideal: power of 2)
CREATE TABLE user_sessions_p0 PARTITION OF user_sessions FOR VALUES WITH (MODULUS 8, REMAINDER 0);
CREATE TABLE user_sessions_p1 PARTITION OF user_sessions FOR VALUES WITH (MODULUS 8, REMAINDER 1);
CREATE TABLE user_sessions_p2 PARTITION OF user_sessions FOR VALUES WITH (MODULUS 8, REMAINDER 2);
CREATE TABLE user_sessions_p3 PARTITION OF user_sessions FOR VALUES WITH (MODULUS 8, REMAINDER 3);
CREATE TABLE user_sessions_p4 PARTITION OF user_sessions FOR VALUES WITH (MODULUS 8, REMAINDER 4);
CREATE TABLE user_sessions_p5 PARTITION OF user_sessions FOR VALUES WITH (MODULUS 8, REMAINDER 5);
CREATE TABLE user_sessions_p6 PARTITION OF user_sessions FOR VALUES WITH (MODULUS 8, REMAINDER 6);
CREATE TABLE user_sessions_p7 PARTITION OF user_sessions FOR VALUES WITH (MODULUS 8, REMAINDER 7);


-- =============================================
-- HASH PARTITIONING (MySQL)
-- =============================================

CREATE TABLE user_sessions_mysql (
    session_id  VARCHAR(36) NOT NULL,
    user_id     INT NOT NULL,
    ip_address  VARCHAR(45),
    user_agent  TEXT,
    started_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ended_at    TIMESTAMP NULL,
    PRIMARY KEY (session_id, user_id)
)
PARTITION BY HASH (user_id)
PARTITIONS 8;

-- MySQL juga mendukung LINEAR HASH untuk distribusi lebih merata
CREATE TABLE user_events (
    event_id    BIGINT AUTO_INCREMENT,
    user_id     INT NOT NULL,
    event_type  VARCHAR(50),
    event_data  JSON,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (event_id, user_id)
)
PARTITION BY LINEAR HASH (user_id)
PARTITIONS 16;


-- =============================================
-- Partition pruning pada Hash
-- =============================================

-- Query ini bisa pruning karena hash(user_id) diketahui:
SELECT * FROM user_sessions WHERE user_id = 1234;
-- Hanya 1 partisi yang di-scan

-- Tapi range query TIDAK bisa pruning:
SELECT * FROM user_sessions WHERE user_id BETWEEN 100 AND 200;
-- Semua partisi tetap di-scan!

-- =============================================
-- SUB-PARTITIONING (PostgreSQL)
-- =============================================

-- Tabel induk: partition by RANGE
CREATE TABLE events (
    id          BIGSERIAL,
    user_id     INT NOT NULL,
    event_type  VARCHAR(50),
    payload     JSONB,
    event_date  DATE NOT NULL
) PARTITION BY RANGE (event_date);

-- Partisi tahunan: partition by HASH
CREATE TABLE events_2026
    PARTITION OF events
    FOR VALUES FROM ('2026-01-01') TO ('2027-01-01')
    PARTITION BY HASH (user_id);

-- Sub-partisi per hash
CREATE TABLE events_2026_p0
    PARTITION OF events_2026
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE events_2026_p1
    PARTITION OF events_2026
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE events_2026_p2
    PARTITION OF events_2026
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE events_2026_p3
    PARTITION OF events_2026
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

-- Hasil: events → events_2026 → events_2026_p0..p3
-- Query by date → pruning di level pertama
-- Query by user_id → pruning di level kedua


-- =============================================
-- MySQL: PARTITION + SUBPARTITION
-- =============================================
CREATE TABLE large_events (
    id          BIGINT AUTO_INCREMENT,
    user_id     INT NOT NULL,
    event_type  VARCHAR(50),
    event_date  DATE NOT NULL,
    PRIMARY KEY (id, event_date, user_id)
)
PARTITION BY RANGE (YEAR(event_date))
SUBPARTITION BY HASH (user_id)
SUBPARTITIONS 4 (
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p2026 VALUES LESS THAN (2027),
    PARTITION pmax  VALUES LESS THAN MAXVALUE
);
-- Total: 4 partitions × 4 subpartitions = 16 physical tables

-- =============================================
-- MENAMBAH PARTISI BARU
-- =============================================

-- PostgreSQL:
CREATE TABLE transactions_2027
    PARTITION OF transactions
    FOR VALUES FROM ('2027-01-01') TO ('2028-01-01');

-- MySQL: ALTER TABLE
ALTER TABLE transactions
ADD PARTITION (
    PARTITION p2027 VALUES LESS THAN (2028)
);


-- =============================================
-- MENGHAPUS PARTISI (SANGAT CEPAT!)
-- =============================================

-- PostgreSQL:
DROP TABLE transactions_2023;
-- atau detach dulu:
ALTER TABLE transactions DETACH PARTITION transactions_2023;
DROP TABLE transactions_2023;

-- MySQL:
ALTER TABLE transactions DROP PARTITION p2022;
-- Data di p2022 langsung hilang, TANPA scan!

-- ⚡ DROP PARTITION vs DELETE:
-- DROP PARTITION: milidetik (hapus file fisik)
-- DELETE jutaan baris: menit sampai jam (scan + lock)


-- =============================================
-- MEMINDAHKAN PARTISI (Detach/Attach)
-- =============================================

-- PostgreSQL: Lepas partisi untuk archive
ALTER TABLE transactions DETACH PARTITION transactions_2023;

-- Simpan di archive table
CREATE TABLE transactions_archive_2023 (LIKE transactions_2023);
INSERT INTO transactions_archive_2023 SELECT * FROM transactions_2023;

-- Atau attach kembali
ALTER TABLE transactions ATTACH PARTITION transactions_2023
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');


-- =============================================
-- MERGE PARTISI (gabungkan partisi kecil)
-- =============================================

-- PostgreSQL: buat tabel baru, migrate data, swap
CREATE TABLE transactions_2024_h1 (
    LIKE transactions INCLUDING DEFAULTS INCLUDING CONSTRAINTS
);
INSERT INTO transactions_2024_h1
SELECT * FROM transactions_2024
WHERE transaction_date < '2024-07-01';

-- =============================================
-- CEK PARTISI YANG ADA
-- =============================================

-- PostgreSQL:
SELECT
    schemaname, tablename,
    pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE tablename LIKE 'transactions%'
ORDER BY tablename;

-- MySQL:
SHOW CREATE TABLE transactions;
SELECT TABLE_NAME, TABLE_ROWS, DATA_LENGTH
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'transactions'
  AND TABLE_SCHEMA = 'database_name';

-- =============================================
-- PARTITION PRUNING — PostgreSQL
-- =============================================

-- Set enable_partition_pruning = on; (default)

-- Query dengan filter tanggal — pruning aktif!
EXPLAIN SELECT * FROM transactions
WHERE transaction_date = '2026-06-15';
-- Output: hanya "transactions_2026" yang di-scan ✅

-- Query TANPA filter — scan semua partisi
EXPLAIN SELECT * FROM transactions;
-- Output: Append → scan semua partisi ❌

-- Query dengan parameter — optimizer cek runtime
PREPARE q AS SELECT * FROM transactions
WHERE transaction_date = $1;
EXECUTE q('2026-03-15');
-- PostgreSQL 11+ bisa prune partisi dari prepared statement ✅


-- =============================================
-- PARTITION PRUNING — MySQL
-- =============================================

-- Pruning dengan YEAR()
EXPLAIN SELECT * FROM transactions
WHERE YEAR(transaction_date) = 2026;
-- Optimizer bisa pruning ke p2026 ✅

-- Pruning dengan range
EXPLAIN SELECT * FROM transactions
WHERE transaction_date >= '2026-01-01'
  AND transaction_date < '2026-07-01';
-- Pruning ke p2026 ✅


-- =============================================
-- CEK partisi mana yang di-scan
-- =============================================

-- PostgreSQL: EXPLAIN VERBOSE
EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
SELECT * FROM transactions
WHERE transaction_date >= '2026-06-01'
  AND transaction_date < '2026-07-01'
  AND customer_id = 1234;

-- Output akan menunjukkan:
-- -> Index Scan using transactions_2026_customer_id_idx on transactions_2026
--    Index Cond: (customer_id = 1234)
--    Filter: (transaction_date >= ... AND transaction_date < ...)


-- =============================================
-- TIPS: Pastikan query bisa pruning
-- =============================================

-- ✅ Kolom partisi ada di WHERE
SELECT * FROM transactions WHERE transaction_date = '2026-01-15';

-- ✅ Kolom partisi dengan range
SELECT * FROM transactions
WHERE transaction_date BETWEEN '2026-01-01' AND '2026-03-31';

-- ❌ Function pada kolom partisi — pruning GAGAL di PG
-- (MySQL kadang masih bisa karena optimizer lebih agresif)
SELECT * FROM transactions
WHERE EXTRACT(MONTH FROM transaction_date) = 6;

-- ✅ Solusi: gunakan range langsung
SELECT * FROM transactions
WHERE transaction_date >= '2026-06-01'
  AND transaction_date < '2026-07-01';

-- =============================================
-- AUTO-CREATE PARTITIONS dengan pg_partman
-- =============================================

-- Install extension (PostgreSQL)
CREATE EXTENSION pg_partman;

-- Setup auto-partitioning per bulan
SELECT partman.create_parent(
    p_parent_table := 'public.transactions',
    p_control := 'transaction_date',
    p_type := 'range',
    p_interval := '1 month',
    p_premake := 3  -- Buat 3 bulan ke depan otomatis
);

-- Jalankan maintenance untuk membuat partisi baru
SELECT partman.run_maintenance();

-- Atau schedule via pg_cron:
-- SELECT cron.schedule('partman-maintenance', '0 0 1 * *',
--     'SELECT partman.run_maintenance()');


-- =============================================
-- MySQL: Auto-create dengan Event Scheduler
-- =============================================

DELIMITER //
CREATE EVENT create_monthly_partition
ON SCHEDULE EVERY 1 MONTH
STARTS '2026-01-01 00:00:00'
DO
BEGIN
    SET @next_month = DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 1 MONTH), '%Y%m');
    SET @sql = CONCAT(
        'ALTER TABLE transactions ADD PARTITION (',
        'PARTITION p_', @next_month,
        ' VALUES LESS THAN (', YEAR(DATE_ADD(NOW(), INTERVAL 2 MONTH)), '))'
    );
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;