테이블 파티셔닝

CREATE TABLE orders (
    id bigint, ordered_at date NOT NULL, region text, amount int
) PARTITION BY RANGE (ordered_at);                 -- 파티션 키 = ordered_at

CREATE TABLE orders_2024_01 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');   -- 상한은 포함하지 않음
CREATE TABLE orders_2024_02 PARTITION OF orders
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

EXPLAIN SELECT * FROM orders
WHERE ordered_at = '2024-02-15';                      -- 계획에 orders_2024_02만 나온다 (프루닝)

ALTER TABLE orders DETACH PARTITION orders_2024_01;       -- 오래된 파티션을 떼어 내고
DROP TABLE orders_2024_01;                              -- 통째로 삭제 (대량 DELETE와 다름)

-- List와 Hash도 같은 형태
-- PARTITION BY LIST (region)   ... FOR VALUES IN ('KR')
-- PARTITION BY HASH (id)       ... FOR VALUES WITH (MODULUS 4, REMAINDER 0)

from collections import defaultdict

rg = ('KR', 'KR', 'US', 'JP', 'KR', 'US')
rows = [{'id': i + 1, 'month': 1 if i < 6 else 2 if i < 11 else 3 if i < 18 else 4,
         'region': rg[i % 6]} for i in range(24)]

def route(row, scheme):
    if scheme == 'range': return f"p_2024_{row['month']:02d}"   # 값 범위
    if scheme == 'list':  return f"p_{row['region']}"           # 값 목록
    return f"p_{row['id'] % 4}"                                 # 해시 (id % 4로 단순화)

def partitions(scheme):
    parts = defaultdict(list)
    for r in rows:
        parts[route(r, scheme)].append(r)
    return parts

def scan(scheme, key, value):           # 키 조건이면 프루닝, 아니면 전부 읽음
    parts = partitions(scheme)
    names = [f'p_2024_{value:02d}'] if (scheme, key) == ('range', 'month') else list(parts)
    return names, sum(len(parts[n]) for n in names)

for s in ('range', 'list', 'hash'):
    print(s, {k: len(v) for k, v in sorted(partitions(s).items())})
print(scan('range', 'month', 3))
print(scan('range', 'region', 'KR'))
# range {'p_2024_01': 6, 'p_2024_02': 5, 'p_2024_03': 7, 'p_2024_04': 6}
# list {'p_JP': 4, 'p_KR': 12, 'p_US': 8}
# hash {'p_0': 6, 'p_1': 6, 'p_2': 6, 'p_3': 6}
# (['p_2024_03'], 7)
# (['p_2024_01', 'p_2024_02', 'p_2024_03', 'p_2024_04'], 24)

TreeMap<Integer, String> bounds = new TreeMap<>();       // 키 = 파티션이 시작되는 month
bounds.put(1, "p_2024_01");
bounds.put(2, "p_2024_02");
bounds.put(3, "p_2024_03");
bounds.put(4, "p_2024_04");

int[] months = new int[24];
for (int i = 0; i < 24; i++) months[i] = i < 6 ? 1 : i < 11 ? 2 : i < 18 ? 3 : 4;

Map<String, Integer> count = new TreeMap<>();
for (int m : months) count.merge(bounds.floorEntry(m).getValue(), 1, Integer::sum);
System.out.println(count);                          // {p_2024_01=6, p_2024_02=5, p_2024_03=7, p_2024_04=6}

int[] hash = new int[4];
for (int id = 1; id <= 24; id++) hash[id % 4]++;
System.out.println(Arrays.toString(hash));            // [6, 6, 6, 6]