postgresql 파티션 테스트

 

drop table measurement;

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);


CREATE TABLE measurement_2407 PARTITION OF measurement FOR VALUES FROM ('2024-07-01') TO ('2024-08-01');
CREATE TABLE measurement_2408 PARTITION OF measurement FOR VALUES FROM ('2024-08-01') TO ('2024-09-01');
CREATE TABLE measurement_2409 PARTITION OF measurement FOR VALUES FROM ('2024-09-01') TO ('2024-10-01');

--CREATE TABLE measurement_y2006m02 PARTITION OF measurement FOR VALUES FROM ('2006-02-01') TO ('2006-03-01') PARTITION BY RANGE (peaktemp);

INSERT INTO measurement (city_id, logdate, peaktemp, unitsales) values (1, now(), 1, 100);

select * from measurement;
select * from measurement_2407;

-- 특정 파티션 테이블 삭제  
drop table measurement_2407

-- drop 했으니 당연히 조회 안 됨
select * from measurement_2407;

-- 조회되나 2407 데이터는 조회 안 됨 
select * from measurement;

-- 파티션 테이블 새로 생성 
CREATE TABLE measurement_2407 PARTITION OF measurement FOR VALUES FROM ('2024-07-01') TO ('2024-08-01');

INSERT INTO measurement (city_id, logdate, peaktemp, unitsales) values (1, now(), 1, 100);

-- 조회 
select * from measurement_2407;
select * from measurement;

-- 파티션 테이블 분리 
ALTER TABLE measurement DETACH PARTITION measurement_2407;

-- 조회됨 
select * from measurement_2407;
-- 2407 데이터 조회 안 됨 
select * from measurement;

-- insert 안 됨 
INSERT INTO measurement (city_id, logdate, peaktemp, unitsales)
values (1, now(), 2, 200);

-- 08월 데이터는 insert 됨 
INSERT INTO measurement (city_id, logdate, peaktemp, unitsales) VALUES (1, '2024-08-01', 22, 345);

-- 조회됨 
select * from measurement_2408;
-- 2407 데이터 조회 안 됨 
select * from measurement;

-- 파티션 테이블 다시 붙임  
ALTER TABLE measurement ATTACH PARTITION measurement_2407 FOR VALUES FROM ('2024-07-01') TO ('2024-08-01');

-- 조회 됨
select * from measurement_2407;
-- 2407, 2408 데이터 조회 됨 
select * from measurement;

-- insert 됨 
INSERT INTO measurement (city_id, logdate, peaktemp, unitsales) values (1, now(), 2, 200);


select * from measurement_2407;
select * from measurement;

 

참고 문서