범위, 목록, 해시 파티셔닝

범위 파티셔닝

CREATE TABLE events (
  id BIGSERIAL,
  ts TIMESTAMPTZ NOT NULL,
  data JSONB
) PARTITION BY RANGE (ts);

CREATE TABLE events_2024_q1 PARTITION OF events
  FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');
CREATE TABLE events_2024_q2 PARTITION OF events
  FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

범위 경계는 반열린 구간입니다

목록 파티셔닝

CREATE TABLE customers (
  id BIGSERIAL,
  country TEXT NOT NULL,
  name TEXT
) PARTITION BY LIST (country);

CREATE TABLE customers_us PARTITION OF customers FOR VALUES IN ('US');
CREATE TABLE customers_eu PARTITION OF customers FOR VALUES IN ('DE','FR','IT','ES','NL');
CREATE TABLE customers_other PARTITION OF customers DEFAULT;

DEFAULT 파티션

해시 파티셔닝

CREATE TABLE sessions (
  id UUID,
  user_id BIGINT,
  data JSONB
) PARTITION BY HASH (user_id);

CREATE TABLE sessions_0 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE sessions_1 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE sessions_2 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE sessions_3 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 3);

전략 선택

하위 파티셔닝

CREATE TABLE events_2024 PARTITION OF events
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01')
  PARTITION BY HASH (user_id);
-- 2024 is a yearly range partition, internally hashed by user_id.

파티션 테이블의 인덱스

CREATE INDEX events_user_idx ON events (user_id);
-- Creates one index per partition automatically.

ALTER TABLE events ADD CONSTRAINT events_uniq UNIQUE (id, ts);
-- ts must be in the constraint because it's the partition key.

기본 키와 파티션

파티션 추가 및 삭제

CREATE TABLE events_2024_q3 PARTITION OF events
  FOR VALUES FROM ('2024-07-01') TO ('2024-10-01');

DROP TABLE events_2023_q1;
-- Instant; equivalent to deleting all old data.

파티션 자동 생성

외래 키

요약

빠른 확인