์๋ฌธ ๋๋ ๋จ๋ฉดํ ์์ด๋์ด๊ฐ ์ด๋ป๊ฒ ํ์ํ๋์ง
์ด์ผ๊ธฐ๋ ์ฌ๊ธฐ์ ์์๋ฉ๋๋ค:
์์ ์ ์ธ ํ์ :
๋ฐ๋ก '๊ทธ ์๊ฐ'์ด๋๊น์.
๊ทธ๋ ๋ค๋ฉด ๊ณ ๊ฐ์ ํ๋ณตํ๊ฒ ๋ง๋๋ ๋์์ ์์ ์ ๊ธฐ์ ๋ ํฅ์์ํฌ ์ ์๋ ๋ฐฉ๋ฒ์ ๋ฌด์์ผ๊น์?
๋ชจ๋ ๊ฒ์ ์ต๋ํ ๋จ์ํํ๊ธฐ ์ํด, ๋ฐ์ดํฐ๋ฒ ์ด์ค ์ฑ๋ฅ์ ๊ทผ๋ณธ์ ์ผ๋ก ํฅ์์ํค๋ ๋ฐฉ๋ฒ์ ๋ ๊ฐ์ง๋ฟ์
๋๋ค.
1) ํ์ฅ ๊ฒฝ๋ก - ๋ฆฌ์์ค๋ฅผ ๋๋ฆฌ๊ณ ๊ตฌ์ฑ์ ๋ณ๊ฒฝํฉ๋๋ค.
2) ์ง์ค ๊ฒฝ๋ก - ์ฟผ๋ฆฌ ์ต์ ํ
๋ฐ๋ณตํฉ๋๋ค. ๊ทธ ๋น์ ์๋ ํฅ์ ์์ฒญ์์ ๋ฌด์์ ๋ณ๊ฒฝํด์ผํ ์ง ๋ ์ด์ ๋ช ํํ์ง ์์๊ธฐ ๋๋ฌธ์ ๊ฒฝ๋ก๊ฐ ์ ํ๋์์ต๋๋ค. ํ ์ด๋ธ ๋์์ธ ๋ณ๊ฒฝ.
๋ฐ๋ผ์ ์ฃผ์ ์ง๋ฌธ์ด ์๊น๋๋ค. ์ฐ๋ฆฌ๋ ๋ฌด์์, ์ด๋ป๊ฒ ๋ณํ์ํฌ ๊ฒ์ธ๊ฐ?
์ด๊ธฐ ์กฐ๊ฑด
์ฒซ์งธ, ๋ค์๊ณผ ๊ฐ์ ERD๊ฐ ์์ต๋๋ค(์กฐ๊ฑด์ ์ผ๋ก ๋จ์ํ๋ ๋ฐฉ์์ผ๋ก ํ์๋จ).
์ฃผ์ ๊ธฐ๋ฅ :
- ๋ค๋๋ค ๊ด๊ณ
- ํ ์ด๋ธ์ ์ด๋ฏธ ์ ์ฌ์ ์ธ ํํฐ์ ํค๊ฐ ์์ต๋๋ค.
์๋ ์์ฒญ:
SELECT
p."PARAMETER_ID" as parameter_id,
pc."PC_NAME" AS pc_name,
pc."CUSTOMER_PARTNUMBER" AS customer_partnumber,
w."LASERMARK" AS lasermark,
w."LOTID" AS lotid,
w."REPORTED_VALUE" AS reported_value,
w."LOWER_SPEC_LIMIT" AS lower_spec_limit,
w."UPPER_SPEC_LIMIT" AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SHIPMENT_NAME" AS shipment_name,
s."SHIPMENT_DATE" AS shipment_date,
extract(year from s."SHIPMENT_DATE") AS year,
extract(month from s."SHIPMENT_DATE") as month,
s."REPORT_NAME" AS report_name,
p."SPARAM_NAME" AS SPARAM_name,
p."CUSTOMERPARAM_NAME" AS customerparam_name
FROM data w INNER JOIN shipment s ON s."SHIPMENT_ID" = w."SHIPMENT_ID"
INNER JOIN parameters p ON p."PARAMETER_ID" = w."PARAMETER_ID"
INNER JOIN shipment_pc sp ON s."SHIPMENT_ID" = sp."SHIPMENT_ID"
INNER JOIN pc pc ON pc."PC_ID" = sp."PC_ID"
INNER JOIN ( SELECT w2."LASERMARK" , MAX(s2."SHIPMENT_DATE") AS "SHIPMENT_DATE"
FROM shipment s2 INNER JOIN data w2 ON s2."SHIPMENT_ID" = w2."SHIPMENT_ID"
GROUP BY w2."LASERMARK"
) md ON md."SHIPMENT_DATE" = s."SHIPMENT_DATE" AND md."LASERMARK" = w."LASERMARK"
WHERE
s."SHIPMENT_DATE" >= '2018-07-01' AND s."SHIPMENT_DATE" <= '2018-09-30' ;
ํ
์คํธ ๋ฐ์ดํฐ๋ฒ ์ด์ค ์คํ ๊ฒฐ๊ณผ:
๋น์ฉ : 502 997.55
์คํ ์๊ฐ: 505 ์ด.
์ฐ๋ฆฌ๋ ๋ฌด์์ ๋ด
๋๊น? ์๊ฐ ์กฐ๊ฐ์ ๊ธฐ๋ฐ์ผ๋ก ํ๋ ์ผ๋ฐ ์์ฒญ์
๋๋ค.
๊ฐ์ฅ ๊ฐ๋จํ ๋
ผ๋ฆฌ์ ๊ฐ์ ์ ๋ง๋ค์ด ๋ด
์๋ค: ์๊ฐ ์กฐ๊ฐ์ ์ํ์ด ์๋ค๋ฉด ๊ทธ๊ฒ์ด ์ฐ๋ฆฌ์๊ฒ ๋์์ด ๋ ๊น์? ๋ง์ต๋๋ค - ํํฐ์
๋.
๋ฌด์์ ์น์ ์ผ๋ก ํ ๊น์?
์ธ๋ป ๋ณด๊ธฐ์ ์ ํ์ ๋ถ๋ช ํฉ๋๋ค. "SHIPMENT_DATE" ํค๋ฅผ ์ฌ์ฉํ์ฌ "๋ฐฐ์ก" ํ ์ด๋ธ์ ์ ์ธ์ ์ผ๋ก ๋ถํ ํ๋ ๊ฒ์ ๋๋ค(๋๋ฌด ์์ ๋๊ฐ ๊ฒ - ๊ฒฐ๊ตญ ์์ฐ์์ ์ฝ๊ฐ ์๋ชป๋ ๊ฒ์ผ๋ก ํ๋ช ๋์์ต๋๋ค.).
๋ถํ ํ๋ ๋ฐฉ๋ฒ?
์ด ์ง๋ฌธ๋ ๋ณ๋ก ์ด๋ ต์ง ์์ต๋๋ค. ๋คํํ PostgreSQL 10์๋ ์ด์ ํด๋จผ ํํฐ์
๋ ๋ฉ์ปค๋์ฆ์ด ์์ต๋๋ค.
๊ทธ๋์ :
- ์์ค ํ ์ด๋ธ์ ๋คํ ์ ์ฅ - pg_dump ์์ค_ํ ์ด๋ธ
- ์๋ณธ ํ ์ด๋ธ ์ญ์ - ๋๋กญ ํ ์ด๋ธ source_table
- ๋ฒ์ ํํฐ์ ๋์ ์ฌ์ฉํ์ฌ ์์ ํ ์ด๋ธ ์์ฑ - ํ ์ด๋ธ source_table ์์ฑ
- ์น์ ๋ง๋ค๊ธฐ - ํ ์ด๋ธ source_table ์์ฑ, ์ธ๋ฑ์ค ์์ฑ
- 1๋จ๊ณ์์ ์์ฑ๋ ๋คํ๋ฅผ ๊ฐ์ ธ์ต๋๋ค. pg_restore
ํํฐ์ ๋์ ์ํ ์คํฌ๋ฆฝํธ
๋จ์์ฑ๊ณผ ํธ์์ฑ์ ์ํด 2,3,4๋จ๊ณ๋ฅผ ํ๋์ ์คํฌ๋ฆฝํธ๋ก ๊ฒฐํฉํ์ต๋๋ค.
๊ทธ๋์ :
์์ค ํ
์ด๋ธ์ ๋คํ ์ ์ฅ
pg_dump postgres --file=/dump/shipment.dmp --format=c --table=shipment --verbose > /dump/shipment.log 2>&1
์์ค ํ ์ด๋ธ ์ญ์ + ๋ฒ์ ํํฐ์ ๋์ ์ฌ์ฉํ์ฌ ์์ ํ ์ด๋ธ ์์ฑ + ํํฐ์ ์์ฑ
--create_partition_shipment.sql
do language plpgsql $$
declare
rec_shipment_date RECORD ;
partition_name varchar;
index_name varchar;
current_year varchar ;
current_month varchar ;
begin_year varchar ;
begin_month varchar ;
next_year varchar ;
next_month varchar ;
first_flag boolean ;
i integer ;
begin
RAISE NOTICE 'CREATE TEMPORARY TABLE FOR SHIPMENT_DATE';
CREATE TEMP TABLE tmp_shipment_date as select distinct "SHIPMENT_DATE" from shipment order by "SHIPMENT_DATE" ;
RAISE NOTICE 'DROP TABLE shipment';
drop table shipment cascade ;
CREATE TABLE public.shipment
(
"SHIPMENT_ID" integer NOT NULL DEFAULT nextval('shipment_shipment_id_seq'::regclass),
"SHIPMENT_NAME" character varying(30) COLLATE pg_catalog."default",
"SHIPMENT_DATE" timestamp without time zone,
"REPORT_NAME" character varying(40) COLLATE pg_catalog."default"
)
PARTITION BY RANGE ("SHIPMENT_DATE")
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
RAISE NOTICE 'CREATE PARTITIONS FOR TABLE shipment';
current_year:='0';
current_month:='0';
begin_year := '0' ;
begin_month := '0' ;
next_year := '0' ;
next_month := '0' ;
FOR rec_shipment_date IN SELECT * FROM tmp_shipment_date LOOP
RAISE NOTICE 'SHIPMENT_DATE=%',rec_shipment_date."SHIPMENT_DATE";
current_year := date_part('year' ,rec_shipment_date."SHIPMENT_DATE");
current_month := date_part('month' ,rec_shipment_date."SHIPMENT_DATE") ;
IF to_number(current_month,'99') < 10 THEN
current_month := '0'||current_month ;
END IF ;
--Init borders
IF begin_year = '0' THEN
first_flag := true ; --first time flag
begin_year := current_year ;
begin_month := current_month ;
IF current_month = '12' THEN
next_year := date_part('year' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 year') ;
ELSE
next_year := current_year ;
END IF;
next_month := date_part('month' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 month') ;
END IF;
-- Check current date into borders NOT for First time
IF to_date( current_year||'.'||current_month, 'YYYY.MM') >= to_date( begin_year||'.'||begin_month, 'YYYY.MM') AND
to_date( current_year||'.'||current_month, 'YYYY.MM') < to_date( next_year||'.'||next_month, 'YYYY.MM') AND
NOT first_flag
THEN
CONTINUE ;
ELSE
--NEW borders only for second and after time
begin_year := current_year ;
begin_month := current_month ;
IF current_month = '12' THEN
next_year := date_part('year' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 year') ;
ELSE
next_year := current_year ;
END IF;
next_month := date_part('month' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 month') ;
END IF;
partition_name := 'shipment_shipment_date_'||begin_year||'-'||begin_month||'-01-'|| next_year||'-'||next_month||'-01' ;
EXECUTE format('CREATE TABLE ' || quote_ident(partition_name) || ' PARTITION OF shipment FOR VALUES FROM ( %L ) TO ( %L ) ' , current_year||'-'||current_month||'-01' , next_year||'-'||next_month||'-01' ) ;
index_name := partition_name||'_shipment_id_idx';
RAISE NOTICE 'INDEX NAME =%',index_name;
EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("SHIPMENT_ID") TABLESPACE pg_default ' ) ;
--Drop first time flag
first_flag := false ;
END LOOP;
end
$$;
๋คํ ๊ฐ์ ธ์ค๊ธฐ
pg_restore -d postgres --data-only --format=c --table=shipment --verbose shipment.dmp > /tmp/data_dump/shipment_restore.log 2>&1
ํํฐ์ ๋ ๊ฒฐ๊ณผ ํ์ธ
๊ทธ ๊ฒฐ๊ณผ ์ฐ๋ฆฌ๋ ๋ฌด์์ ๊ฐ๊ฒ ๋์์ต๋๊น? ์คํ ๊ณํ์ ์ ์ฒด ํ ์คํธ๋ ๋ฐฉ๋ํ๊ณ ์ง๋ฃจํ๋ฏ๋ก ์ต์ข ์ซ์๋ก ์ ํํ๋ ๊ฒ์ด ๊ฐ๋ฅํฉ๋๋ค.
ํ๋ค
๋น์ฉ : 502 997.55
์คํ ์๊ฐ: 505 ์ด.
๊ทธ๊ฒ์๋์๋ค
๋น์ฉ : 77 872.36
์คํ ์๊ฐ: 79 ์ด.
๊ฝค ์ข์ ๊ฒฐ๊ณผ์ ๋๋ค. ๋น์ฉ ๋ฐ ์คํ ์๊ฐ์ด ๋จ์ถ๋ฉ๋๋ค. ๋ฐ๋ผ์ ํํฐ์ ๋์ ์ฌ์ฉํ๋ฉด ์์ํ ํจ๊ณผ๋ฅผ ์ป์ ์ ์์ผ๋ฉฐ ์ผ๋ฐ์ ์ผ๋ก ๋๋ ์ผ์ด ์๋๋๋ค.
๊ณ ๊ฐ์ ํ๋ณตํ๊ฒ ํ๋ผ
ํ ์คํธ ๊ฒฐ๊ณผ๋ ๊ฒํ ๋ฅผ ์ํด ๊ณ ๊ฐ์๊ฒ ์ ๊ณต๋์์ต๋๋ค. ๊ทธ๋ฆฌ๊ณ ์ด๋ฅผ ๊ฒํ ํ ํ ๋ค์ ์์์น ๋ชปํ ๊ฒฐ๋ก ์ ๋ด๋ ธ์ต๋๋ค. โ์ข์ต๋๋ค. โ๋ฐ์ดํฐโ ํ ์ด๋ธ์ ๋ถํ ํ์ธ์.โ
์, ํ์ง๋ง ์์ ํ ๋ค๋ฅธ "๋ฐฐ์ก" ํ ์ด๋ธ์ ์กฐ์ฌํ์ต๋๋ค. "๋ฐ์ดํฐ" ํ ์ด๋ธ์๋ "SHIPMENT_DATE" ํ๋๊ฐ ์์ต๋๋ค.
๋ฌธ์ ์์ต๋๋ค. ์ถ๊ฐํ๊ณ ๋ณ๊ฒฝํ์ธ์. ๊ฐ์ฅ ์ค์ํ ๊ฒ์ ๊ณ ๊ฐ์ด ๊ฒฐ๊ณผ์ ๋ง์กฑํ๋ค๋ ๊ฒ์ด๋ฉฐ ๊ตฌํ ์ธ๋ถ ์ฌํญ์ ํน๋ณํ ์ค์ํ์ง ์์ต๋๋ค.
๋ฉ์ธ ํ ์ด๋ธ "๋ฐ์ดํฐ" ํํฐ์ ๋
์ผ๋ฐ์ ์ผ๋ก ํน๋ณํ ์ด๋ ค์์ ๋ฐ์ํ์ง ์์์ต๋๋ค. ๋ฌผ๋ก ํํฐ์ ๋ ์๊ณ ๋ฆฌ์ฆ์ ๋ค์ ๋ณ๊ฒฝ๋์์ต๋๋ค.
'๋ฐ์ดํฐ' ํ ์ด๋ธ์ 'SHIPMENT_DATA' ์ด ์ถ๊ฐ
psql -h ั
ะพัั -U ะฑะฐะทะฐ -d ัะทะตั
=> ALTER TABLE data ADD COLUMN "SHIPMENT_DATE" timestamp without time zone ;
"data" ํ ์ด๋ธ์ "SHIPMENT_DATA" ์ด์ ๊ฐ์ "shipment" ํ ์ด๋ธ์ ๋์ผํ ์ด๋ฆ์ ์ด ๊ฐ์ผ๋ก ์ฑ์๋๋ค.
-----------------------------
--update_data.sql
--updating for altered table "data" to values of "shipment_data" from the table "shipment"
--version 1.0
do language plpgsql $$
declare
rec_shipment_data RECORD ;
shipment_date timestamp without time zone ;
row_count integer ;
total_rows integer ;
begin
select count(*) into total_rows from shipment ;
RAISE NOTICE 'Total %',total_rows;
row_count:= 0 ;
FOR rec_shipment_data IN SELECT * FROM shipment LOOP
update data set "SHIPMENT_DATE" = rec_shipment_data."SHIPMENT_DATE" where "SHIPMENT_ID" = rec_shipment_data."SHIPMENT_ID";
row_count:= row_count +1 ;
RAISE NOTICE 'row count = % , from %',row_count,total_rows;
END LOOP;
end
$$;
"๋ฐ์ดํฐ" ํ ์ด๋ธ ๋คํ ์ ์ฅ
pg_dump postgres --file=/dump/data.dmp --format=c --table=data --verbose > /dump/data.log 2>&1</source
๋ถํ ๋ ํ ์ด๋ธ 'data'๋ฅผ ๋ค์ ์์ฑํฉ๋๋ค.
--create_partition_data.sql
--create partitions for the table "wafer data" by range column "shipment_data" with one month duration
--version 1.0
do language plpgsql $$
declare
rec_shipment_date RECORD ;
partition_name varchar;
index_name varchar;
current_year varchar ;
current_month varchar ;
begin_year varchar ;
begin_month varchar ;
next_year varchar ;
next_month varchar ;
first_flag boolean ;
i integer ;
begin
RAISE NOTICE 'CREATE TEMPORARY TABLE FOR SHIPMENT_DATE';
CREATE TEMP TABLE tmp_shipment_date as select distinct "SHIPMENT_DATE" from shipment order by "SHIPMENT_DATE" ;
RAISE NOTICE 'DROP TABLE data';
drop table data cascade ;
RAISE NOTICE 'CREATE PARTITIONED TABLE data';
CREATE TABLE public.data
(
"RUN_ID" integer,
"LASERMARK" character varying(20) COLLATE pg_catalog."default" NOT NULL,
"LOTID" character varying(80) COLLATE pg_catalog."default",
"SHIPMENT_ID" integer NOT NULL,
"PARAMETER_ID" integer NOT NULL,
"INTERNAL_VALUE" character varying(75) COLLATE pg_catalog."default",
"REPORTED_VALUE" character varying(75) COLLATE pg_catalog."default",
"LOWER_SPEC_LIMIT" numeric,
"UPPER_SPEC_LIMIT" numeric ,
"SHIPMENT_DATE" timestamp without time zone
)
PARTITION BY RANGE ("SHIPMENT_DATE")
WITH (
OIDS = FALSE
)
TABLESPACE pg_default ;
RAISE NOTICE 'CREATE PARTITIONS FOR TABLE data';
current_year:='0';
current_month:='0';
begin_year := '0' ;
begin_month := '0' ;
next_year := '0' ;
next_month := '0' ;
i := 1;
FOR rec_shipment_date IN SELECT * FROM tmp_shipment_date LOOP
RAISE NOTICE 'SHIPMENT_DATE=%',rec_shipment_date."SHIPMENT_DATE";
current_year := date_part('year' ,rec_shipment_date."SHIPMENT_DATE");
current_month := date_part('month' ,rec_shipment_date."SHIPMENT_DATE") ;
--Init borders
IF begin_year = '0' THEN
RAISE NOTICE '***Init borders';
first_flag := true ; --first time flag
begin_year := current_year ;
begin_month := current_month ;
IF current_month = '12' THEN
next_year := date_part('year' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 year') ;
ELSE
next_year := current_year ;
END IF;
next_month := date_part('month' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 month') ;
END IF;
-- RAISE NOTICE 'current_year=% , current_month=% ',current_year,current_month;
-- RAISE NOTICE 'begin_year=% , begin_month=% ',begin_year,begin_month;
-- RAISE NOTICE 'next_year=% , next_month=% ',next_year,next_month;
-- Check current date into borders NOT for First time
RAISE NOTICE 'Current data = %',to_char( to_date( current_year||'.'||current_month, 'YYYY.MM'), 'YYYY.MM');
RAISE NOTICE 'Begin data = %',to_char( to_date( begin_year||'.'||begin_month, 'YYYY.MM'), 'YYYY.MM');
RAISE NOTICE 'Next data = %',to_char( to_date( next_year||'.'||next_month, 'YYYY.MM'), 'YYYY.MM');
IF to_date( current_year||'.'||current_month, 'YYYY.MM') >= to_date( begin_year||'.'||begin_month, 'YYYY.MM') AND
to_date( current_year||'.'||current_month, 'YYYY.MM') < to_date( next_year||'.'||next_month, 'YYYY.MM') AND
NOT first_flag
THEN
RAISE NOTICE '***CONTINUE';
CONTINUE ;
ELSE
--NEW borders only for second and after time
RAISE NOTICE '***NEW BORDERS';
begin_year := current_year ;
begin_month := current_month ;
IF current_month = '12' THEN
next_year := date_part('year' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 year') ;
ELSE
next_year := current_year ;
END IF;
next_month := date_part('month' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 month') ;
END IF;
IF to_number(current_month,'99') < 10 THEN
current_month := '0'||current_month ;
END IF ;
IF to_number(begin_month,'99') < 10 THEN
begin_month := '0'||begin_month ;
END IF ;
IF to_number(next_month,'99') < 10 THEN
next_month := '0'||next_month ;
END IF ;
RAISE NOTICE 'current_year=% , current_month=% ',current_year,current_month;
RAISE NOTICE 'begin_year=% , begin_month=% ',begin_year,begin_month;
RAISE NOTICE 'next_year=% , next_month=% ',next_year,next_month;
partition_name := 'data_'||begin_year||begin_month||'01_'||next_year||next_month||'01' ;
RAISE NOTICE 'PARTITION NUMBER % , TABLE NAME =%',i , partition_name;
EXECUTE format('CREATE TABLE ' || quote_ident(partition_name) || ' PARTITION OF data FOR VALUES FROM ( %L ) TO ( %L ) ' , begin_year||'-'||begin_month||'-01' , next_year||'-'||next_month||'-01' ) ;
index_name := partition_name||'_shipment_id_parameter_id_idx';
RAISE NOTICE 'INDEX NAME =%',index_name;
EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("SHIPMENT_ID", "PARAMETER_ID") TABLESPACE pg_default ' ) ;
index_name := partition_name||'_lasermark_idx';
RAISE NOTICE 'INDEX NAME =%',index_name;
EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("LASERMARK" COLLATE pg_catalog."default") TABLESPACE pg_default ' ) ;
index_name := partition_name||'_shipment_id_idx';
RAISE NOTICE 'INDEX NAME =%',index_name;
EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("SHIPMENT_ID") TABLESPACE pg_default ' ) ;
index_name := partition_name||'_parameter_id_idx';
RAISE NOTICE 'INDEX NAME =%',index_name;
EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("PARAMETER_ID") TABLESPACE pg_default ' ) ;
index_name := partition_name||'_shipment_date_idx';
RAISE NOTICE 'INDEX NAME =%',index_name;
EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("SHIPMENT_DATE") TABLESPACE pg_default ' ) ;
--Drop first time flag
first_flag := false ;
END LOOP;
end
$$;
3๋จ๊ณ์์ ์์ฑ๋ ๋คํ๋ฅผ ๋ก๋ํฉ๋๋ค.
pg_restore -h ั
ะพัั -ัะทะตั -d ะฑะฐะทะฐ --data-only --format=c --table=data --verbose data.dmp > data_restore.log 2>&1
์ค๋๋ ๋ฐ์ดํฐ์ ๋ํด ๋ณ๋์ ์น์ ๋ง๋ค๊ธฐ
---------------------------------------------------
--create_partition_for_old_dates.sql
--create partitions for keeping old dates
--version 1.0
do language plpgsql $$
declare
rec_shipment_date RECORD ;
partition_name varchar;
index_name varchar;
begin
SELECT min("SHIPMENT_DATE") AS min_date INTO rec_shipment_date from data ;
RAISE NOTICE 'Old date is %',rec_shipment_date.min_date ;
partition_name := 'data_old_dates' ;
RAISE NOTICE 'PARTITION NAME IS %',partition_name;
EXECUTE format('CREATE TABLE ' || quote_ident(partition_name) || ' PARTITION OF data FOR VALUES FROM ( %L ) TO ( %L ) ' , '1900-01-01' ,
to_char( rec_shipment_date.min_date,'YYYY')||'-'||to_char(rec_shipment_date.min_date,'MM')||'-01' ) ;
index_name := partition_name||'_shipment_id_parameter_id_idx';
EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("SHIPMENT_ID", "PARAMETER_ID") TABLESPACE pg_default ' ) ;
index_name := partition_name||'_lasermark_idx';
EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("LASERMARK" COLLATE pg_catalog."default") TABLESPACE pg_default ' ) ;
index_name := partition_name||'_shipment_id_idx';
EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("SHIPMENT_ID") TABLESPACE pg_default ' ) ;
index_name := partition_name||'_parameter_id_idx';
EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("PARAMETER_ID") TABLESPACE pg_default ' ) ;
index_name := partition_name||'_shipment_date_idx';
EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("SHIPMENT_DATE") TABLESPACE pg_default ' ) ;
end
$$;
์ต์ข ๊ฒฐ๊ณผ:
ํ๋ค
๋น์ฉ : 502 997.55
์คํ ์๊ฐ: 505 ์ด.
๊ทธ๊ฒ์๋์๋ค
๋น์ฉ : 68 533.70
์คํ ์๊ฐ: 69 ์ด
๊ฐ์น ์๊ณ , ๊ฝค ๊ฐ์น๊ฐ ์์ต๋๋ค. ๊ทธ๋ฆฌ๊ณ ๊ทธ ๊ณผ์ ์์ ์ฐ๋ฆฌ๋ PostgreSQL 10์ ํํฐ์ ๋ ๋ฉ์ปค๋์ฆ์ ์ด๋ ์ ๋ ๋ง์คํฐํ ์ ์์์ต๋๋ค. ์ด๋ ํ๋ฅญํ ๊ฒฐ๊ณผ์ ๋๋ค.
์์ ์ ์นจ๋ต
๋ ์ํ ์ ์๋์? ์, ๊ฐ๋ฅํฉ๋๋ค!์ด๋ฅผ ์ํด์๋ MATERIALIZED VIEW๋ฅผ ์ฌ์ฉํด์ผ ํฉ๋๋ค.
๊ตฌ์ฒดํ๋ ๋ทฐ ์์ฑ LASERMARK_VIEW
CREATE MATERIALIZED VIEW LASERMARK_VIEW
AS
SELECT w."LASERMARK" , MAX(s."SHIPMENT_DATE") AS "SHIPMENT_DATE"
FROM shipment s INNER JOIN data w ON s."SHIPMENT_ID" = w."SHIPMENT_ID"
GROUP BY w."LASERMARK" ;
CREATE INDEX lasermark_vw_shipment_date_ind on lasermark_view USING btree ("SHIPMENT_DATE") TABLESPACE pg_default;
analyze lasermark_view ;
๋ค์ ํ ๋ฒ ์์ฒญ์ ๋ค์ ์์ฑํฉ๋๋ค.
๊ตฌ์ฒดํ๋ ๋ทฐ๋ฅผ ์ฌ์ฉํ ์ฟผ๋ฆฌ
SELECT
p."PARAMETER_ID" as parameter_id,
pc."PC_NAME" AS pc_name,
pc."CUSTOMER_PARTNUMBER" AS customer_partnumber,
w."LASERMARK" AS lasermark,
w."LOTID" AS lotid,
w."REPORTED_VALUE" AS reported_value,
w."LOWER_SPEC_LIMIT" AS lower_spec_limit,
w."UPPER_SPEC_LIMIT" AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SHIPMENT_NAME" AS shipment_name,
s."SHIPMENT_DATE" AS shipment_date,
extract(year from s."SHIPMENT_DATE") AS year,
extract(month from s."SHIPMENT_DATE") as month,
s."REPORT_NAME" AS report_name,
p."STC_NAME" AS STC_name,
p."CUSTOMERPARAM_NAME" AS customerparam_name
FROM data w INNER JOIN shipment s ON s."SHIPMENT_ID" = w."SHIPMENT_ID"
INNER JOIN parameters p ON p."PARAMETER_ID" = w."PARAMETER_ID"
INNER JOIN shipment_pc sp ON s."SHIPMENT_ID" = sp."SHIPMENT_ID"
INNER JOIN pc pc ON pc."PC_ID" = sp."PC_ID"
INNER JOIN LASERMARK_VIEW md ON md."SHIPMENT_DATE" = s."SHIPMENT_DATE" AND md."LASERMARK" = w."LASERMARK"
WHERE
s."SHIPMENT_DATE" >= '2018-07-01' AND s."SHIPMENT_DATE" <= '2018-09-30';
๊ทธ๋ฆฌ๊ณ ์ฐ๋ฆฌ๋ ๋ ๋ค๋ฅธ ๊ฒฐ๊ณผ๋ฅผ ์ป์ต๋๋ค:
ํ๋ค
๋น์ฉ : 502 997.55
์คํ ์๊ฐ: 505 ์ด
๊ทธ๊ฒ์๋์๋ค
๋น์ฉ : 42 481.16
์คํ ์๊ฐ: 43 ์ด.
๋ฌผ๋ก ๊ทธ๋ฌํ ์ ๋งํ ๊ฒฐ๊ณผ๋ ๊ธฐ๋ง์ ์ด์ง๋ง ์์ด๋์ด๋ ์๋ก์์ง ํ์๊ฐ ์์ต๋๋ค. ๋ฐ๋ผ์ ๋ฐ์ดํฐ๋ฅผ ์์ ํ๋ ๋ฐ ๊ฑธ๋ฆฌ๋ ์ด ์๊ฐ์ ๋ณ๋ก ๋์์ด ๋์ง ์์ต๋๋ค. ๊ทธ๋ฌ๋ ์คํ์ ์ผ๋ก๋ ๋งค์ฐ ํฅ๋ฏธ๋กญ์ต๋๋ค.
์ค์ ๋ก ๊ฒฐ๊ณผ์ ์ผ๋ก ๋ค์ ํ ๋ฒ ๊ฐ์ฌ๋๋ฆฝ๋๋ค.
์ฌํ
๊ทธ๋์ ๊ณ ๊ฐ์ ๋ง์กฑํฉ๋๋ค. ๊ทธ๋ฆฌ๊ณ ํ์ํ ์ํฉ์ ํ์ฉํ์ธ์.
์ ์์ : ์ฌํํ๊ณ ํ์ฅํ๊ธฐ ์ํด ๋ฌด์์ ์๊ฐํด ๋ผ ์ ์์ต๋๊น?
๊ทธ๋ฆฌ๊ณ ๊ธฐ์ต๋๋ ๊ฒ์, ์ฐ๋ฆฌ๋ PostgreSQL ๋ฐ์ดํฐ๋ฒ ์ด์ค๋ฅผ ๋ชจ๋ํฐ๋งํ์ง ์๋๋ค๋ ๊ฒ์ ๋๋ค.
์ค์ ๋ก AWS์๋ Cloud Watch ํํ์ ๋ชจ๋ํฐ๋ง์ด ์ฌ์ ํ ์์ต๋๋ค. ํ์ง๋ง DBA์๊ฒ ์ด๋ฌํ ๋ชจ๋ํฐ๋ง์ ์ด์ ์ ๋ฌด์์ ๋๊น? ์ผ๋ฐ์ ์ผ๋ก ๊ฑฐ์ ์์ต๋๋ค.
์์ ์๊ฒ ์ ์ฉํ๊ณ ํฅ๋ฏธ๋ก์ด ์ผ์ ํ ์ ์๋ ๊ธฐํ๊ฐ ์๋ค๋ฉด, ์ด ๊ธฐํ๋ฅผ ํ์ฉํ์ง ์์ ์ ์์ต๋๋ค...
Mbo
์ด๊ฒ์ด ์ฐ๋ฆฌ๊ฐ ๊ฐ์ฅ ํฅ๋ฏธ๋ก์ด ๋ถ๋ถ์ ๋๋ฌํ๋ ๋ฐฉ๋ฒ์
๋๋ค:
3๋ 2018์ XNUMX์ผ.
PostgreSQL ์ฟผ๋ฆฌ ์ฑ๋ฅ์ ๋ชจ๋ํฐ๋งํ๊ธฐ ์ํด ์ฌ์ฉ ๊ฐ๋ฅํ ๊ธฐ๋ฅ์ ๋ํ ์ฐ๊ตฌ๋ฅผ ์์ํ๊ธฐ๋ก ๊ฒฐ์ ํฉ๋๋ค.
๊ทธ๋ฌ๋ ๊ทธ๊ฒ์ ์์ ํ ๋ค๋ฅธ ์ด์ผ๊ธฐ์ ๋๋ค.
๊ณ์โฆ
์ถ์ฒ : habr.com