이 실습에서는 PostgreSQL 에서 테이블 파티셔닝 (table partitioning) 을 구현하는 방법을 배웁니다. 목표는 대형 테이블을 더 작고 관리하기 쉬운 조각으로 나누어 쿼리 성능을 크게 향상시키고 백업 또는 아카이빙과 같은 데이터 관리 작업을 단순화하는 것입니다.
먼저 파티셔닝을 위해 설계된 메인 "부모" 테이블을 생성합니다. 그런 다음 특정 날짜 범위를 위한 데이터를 각각 보유하는 여러 "자식" 테이블, 즉 파티션을 정의합니다. 마지막으로 부모 테이블에 데이터를 삽입하고 PostgreSQL 이 올바른 파티션으로 자동으로 라우팅하는 것을 관찰합니다. 또한 파티셔닝된 테이블을 쿼리하는 방법을 배우고 PostgreSQL 이 관련 파티션에만 액세스하여 이러한 쿼리를 최적화하는 것을 볼 수 있습니다.
이 단계에서는 파티션의 메인 sales 테이블을 생성합니다. 이 테이블은 모든 파티션의 구조를 정의하지만 자체적으로는 데이터를 저장하지 않습니다.
먼저 PostgreSQL 데이터베이스에 연결해야 합니다. 터미널을 열고 다음 명령을 사용하여 postgres 사용자로 psql 대화형 쉘을 시작합니다.
sudo -u postgres psql이제 postgres=#와 같이 보이는 PostgreSQL 프롬프트가 표시됩니다. 이 실습의 후속 SQL 명령은 모두 이 프롬프트에서 실행됩니다.
다음으로 sales 테이블을 생성합니다. 이 테이블은 sale_date 열을 기준으로 범위 (range) 별로 파티셔닝됩니다.
CREATE TABLE sales (
sale_id SERIAL,
sale_date DATE NOT NULL,
product_id INTEGER,
sale_amount DECIMAL(10, 2),
PRIMARY KEY (sale_id, sale_date)
) PARTITION BY RANGE (sale_date);이 명령을 자세히 살펴보겠습니다.
CREATE TABLE sales (...): 판매 데이터의 열을 정의합니다.PRIMARY KEY (sale_id, sale_date): 파티셔닝된 테이블에서는 기본 키 (primary key) 에 파티셔닝 열 (sale_date) 이 포함되어야 합니다.PARTITION BY RANGE (sale_date): 이것이 핵심 부분입니다. sale_date 열에 대해 RANGE 메서드를 사용하여 이 테이블이 파티셔닝됨을 선언합니다.명령을 실행한 후 CREATE TABLE 확인 메시지가 표시됩니다.
테이블이 생성되었는지 확인하려면 psql에서 \d 명령을 사용하여 테이블 구조를 설명할 수 있습니다.
\d sales출력에는 테이블의 열이 표시되며, 하단에는 "Partitioned table"임을 확인하고 "Partition key"를 나열합니다.
Table "public.sales"
Column | Type | Collation | Nullable | Default
-------------+---------------+-----------+----------+------------------------------------------------
sale_id | integer | | not null | nextval('sales_sale_id_seq'::regclass)
sale_date | date | | not null |
product_id | integer | | |
sale_amount | numeric(10,2) | | |
Partition key: RANGE (sale_date)
Indexes:
"sales_pkey" PRIMARY KEY, btree (sale_id, sale_date)
Number of partitions: 0"Number of partitions"가 0 인 것을 확인합니다. 실제 파티션은 다음 단계에서 생성합니다.
이제 부모 sales 테이블이 준비되었으므로 실제 데이터가 저장될 파티션을 생성해야 합니다. 각 파티션은 특정 날짜 범위의 데이터를 보유합니다. 이 단계에서는 2023 년과 2024 년의 분기별 파티션을 생성합니다.
여전히 psql 대화형 터미널에 있어야 합니다.
먼저 2023 년의 네 가지 파티션을 생성합니다. 각 명령은 sales의 파티션으로 새 테이블을 정의하고 해당 테이블이 포함할 날짜 범위를 지정합니다.
CREATE TABLE sales_2023_q1 PARTITION OF sales
FOR VALUES FROM ('2023-01-01') TO ('2023-04-01');
CREATE TABLE sales_2023_q2 PARTITION OF sales
FOR VALUES FROM ('2023-04-01') TO ('2023-07-01');
CREATE TABLE sales_2023_q3 PARTITION OF sales
FOR VALUES FROM ('2023-07-01') TO ('2023-10-01');
CREATE TABLE sales_2023_q4 PARTITION OF sales
FOR VALUES FROM ('2023-10-01') TO ('2024-01-01');FOR VALUES FROM ... TO ... 절은 각 파티션의 범위를 정의합니다. 하한값은 포함되고 상한값은 제외됩니다. 예를 들어, sales_2023_q1은 sale_date가 2023-01-01부터 2023-04-01 직전까지인 레코드를 저장합니다.
다음으로 동일한 분기별 체계를 사용하여 2024 년의 파티션을 생성합니다.
CREATE TABLE sales_2024_q1 PARTITION OF sales
FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');
CREATE TABLE sales_2024_q2 PARTITION OF sales
FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');
CREATE TABLE sales_2024_q3 PARTITION OF sales
FOR VALUES FROM ('2024-07-01') TO ('2024-10-01');
CREATE TABLE sales_2024_q4 PARTITION OF sales
FOR VALUES FROM ('2024-10-01') TO ('2025-01-01');각 CREATE TABLE 명령을 실행한 후 확인 메시지가 표시됩니다.
모든 파티션이 생성되었는지 확인하려면 데이터베이스의 테이블을 다시 나열할 수 있습니다.
\dt이제 출력에서 부모 sales 테이블과 방금 생성한 여덟 개의 파티션 (sales_2023_q1, sales_2023_q2 등) 을 볼 수 있습니다.
이 단계에서는 샘플 데이터를 삽입합니다. 파티셔닝의 주요 특징은 데이터를 부모 테이블 (sales) 에 직접 삽입하면 PostgreSQL 이 파티션 키 (sale_date) 값을 기준으로 각 행을 올바른 파티션으로 자동 라우팅한다는 것입니다.
여전히 psql 대화형 터미널에 있어야 합니다.
다음 INSERT 문을 실행하여 2023 년과 2024 년에 걸친 16 개의 샘플 판매 기록을 추가합니다.
INSERT INTO sales (sale_date, product_id, sale_amount) VALUES
('2023-01-15', 101, 50.00),
('2023-02-20', 102, 75.50),
('2023-04-10', 103, 100.00),
('2023-05-25', 104, 60.25),
('2023-07-01', 105, 120.00),
('2023-08-12', 106, 80.75),
('2023-10-05', 107, 90.00),
('2023-11-18', 108, 110.50),
('2024-01-22', 109, 55.00),
('2024-03-01', 110, 70.00),
('2024-04-15', 111, 95.50),
('2024-06-10', 112, 65.00),
('2024-07-08', 113, 125.00),
('2024-09-20', 114, 85.25),
('2024-10-12', 115, 95.00),
('2024-12-01', 116, 115.75);명령이 완료된 후 INSERT 0 16 출력이 표시되며, 이는 16 개의 행이 성공적으로 삽입되었음을 나타냅니다.
데이터가 올바르게 라우팅되었는지 확인하려면 개별 파티션을 쿼리할 수 있습니다. 예를 들어, 2023 년 첫 분기의 레코드 수를 확인해 보겠습니다.
SELECT COUNT(*) FROM sales_2023_q1;출력은 다음과 같아야 합니다.
count
-------
2
(1 row)이제 2024 년 네 번째 분기의 수를 확인합니다.
SELECT COUNT(*) FROM sales_2024_q4;출력도 2여야 합니다. 이는 PostgreSQL 이 데이터를 올바른 하위 파티션 테이블에 배치했음을 확인합니다.
마지막 단계에서는 파티셔닝된 sales 테이블을 쿼리합니다. 파티셔닝의 주요 이점은 "파티션 프루닝 (partition pruning)"으로 알려져 있으며, PostgreSQL 의 쿼리 플래너는 전체 데이터셋을 스캔하는 대신 필요한 파티션만 스캔할 만큼 충분히 똑똑합니다.
여전히 psql 대화형 터미널에 있어야 합니다.
먼저 2023 년 첫 분기의 모든 판매를 검색하는 쿼리를 실행합니다.
SELECT * FROM sales WHERE sale_date >= '2023-01-01' AND sale_date < '2023-04-01';이 날짜 범위에 해당하는 두 개의 레코드가 표시됩니다. PostgreSQL 이 이를 어떻게 최적화하는지 보려면 쿼리 실행 계획을 보여주는 EXPLAIN 명령을 사용할 수 있습니다.
EXPLAIN SELECT * FROM sales WHERE sale_date >= '2023-01-01' AND sale_date < '2023-04-01';출력은 다음과 유사하게 표시됩니다.
QUERY PLAN
------------------------------------------------------------------------------------
Seq Scan on sales_2023_q1 sales (cost=0.00..31.75 rows=7 width=28)
Filter: ((sale_date >= '2023-01-01'::date) AND (sale_date < '2023-04-01'::date))
(2 rows)Seq Scan on sales_2023_q1 줄에 주목하십시오. 이는 PostgreSQL 이 sales_2023_q1 파티션만 스캔하고 나머지 일곱 개는 무시했음을 증명하며, 대규모 데이터셋에서 쿼리를 훨씬 빠르게 만듭니다.
이제 2024 년의 각 제품별 총 판매 금액을 찾기 위해 더 복잡한 쿼리를 실행합니다.
SELECT product_id, SUM(sale_amount) AS total_sales
FROM sales
WHERE sale_date >= '2024-01-01' AND sale_date < '2025-01-01'
GROUP BY product_id
ORDER BY product_id;이 쿼리는 결과를 계산하기 위해 2024 년의 네 가지 파티션만 효율적으로 스캔합니다. 출력은 109 부터 116 까지 각 제품의 총 판매액을 보여줍니다.
마지막으로 다음을 입력하여 PostgreSQL 대화형 터미널을 종료할 수 있습니다.
\q일반 셸 프롬프트로 돌아갑니다.
이 실습에서는 PostgreSQL 의 테이블 파티셔닝 기본 사항을 배웠습니다. 날짜 범위별로 파티셔닝된 부모 테이블을 성공적으로 생성하고, 다른 기간에 대한 특정 파티션을 정의했으며, 올바른 파티션으로 자동 라우팅된 데이터를 삽입했습니다. 가장 중요한 것은 EXPLAIN 명령을 사용하여 파티션 프루닝이 작동하는 것을 확인했으며, 이를 통해 파티셔닝이 데이터의 일부만 스캔하도록 하여 쿼리 성능을 크게 향상시킬 수 있음을 입증했습니다. 이는 대규모 데이터셋을 효율적으로 관리하기 위한 강력한 기술입니다.
