ΠΡΠ΅Π΄ΠΈΡΠ»ΠΎΠ²ΠΈΠ΅ ΠΈΠ»ΠΈ ΠΊΠ°ΠΊ Π²ΠΎΠ·Π½ΠΈΠΊΠ»Π° ΠΈΠ΄Π΅Ρ ΡΠ΅ΠΊΡΠΈΠΎΠ½ΠΈΡΠΎΠ²Π°Π½ΠΈΡ
ΠΠ°ΡΠ°Π»ΠΎ ΠΈΡΡΠΎΡΠΈΠΈ Π·Π΄Π΅ΡΡ:
ΠΠΈΡΠΈΡΠ΅ΡΠΊΠΎΠ΅ ΠΎΡΡΡΡΠΏΠ»Π΅Π½ΠΈΠ΅:
ΠΠΌΠ΅Π½Π½ΠΎ ‘Π½Π° ΡΠΎΡ ΠΌΠΎΠΌΠ΅Π½Ρ’, ΠΏΠΎΡΠΎΠΌΡ, ΡΡΠΎ
ΠΡΠ°ΠΊ, ΠΊΠ°ΠΊ Π΅ΡΠ΅ ΠΌΠΎΠΆΠ½ΠΎ ΡΠ΄Π΅Π»Π°ΡΡ Π·Π°ΠΊΠ°Π·ΡΠΈΠΊΠ° ΠΊΠ°ΠΊ Π±Ρ ΡΡΠ°ΡΡΠ»ΠΈΠ²ΡΠΌ, Π·Π°ΠΎΠ΄Π½ΠΎ ΠΈ ΠΏΡΠΎΠΊΠ°ΡΠ°ΡΡ ΡΠΎΠ±ΡΡΠ²Π΅Π½Π½ΡΠ΅ ΡΠΊΠΈΠ»Ρ?
ΠΡΠ»ΠΈ ΠΏΡΠ΅Π΄Π΅Π»ΡΠ½ΠΎ Π²ΡΡ ΡΠΏΡΠΎΡΡΠΈΡΡ, ΡΠΎ, ΠΏΡΡΠ΅ΠΉ ΠΊΠ°ΡΠ΄ΠΈΠ½Π°Π»ΡΠ½ΠΎ, ΡΡΠΎ-ΡΠΎ ΡΠ»ΡΡΡΠΈΡΡ Π² Π±ΡΡΡΡΠΎΠ΄Π΅ΠΉΡΡΠ²ΠΈΠΈ Π±Π°Π·Ρ Π΄Π°Π½Π½ΡΡ
, Π²ΡΠ΅Π³ΠΎ Π΄Π²Π°:
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' ;
Π Π΅Π·ΡΠ»ΡΡΠ°ΡΡ Π²ΡΠΏΠΎΠ»Π½Π΅Π½ΠΈΡ Π½Π° ΡΠ΅ΡΡΠΎΠ²ΠΎΠΉ Π±Π°Π·Π΅ Π΄Π°Π½Π½ΡΡ
:
Cost : 502 997.55
Execution time: 505 seconds.
Π§ΡΠΎ ΠΌΡ Π²ΠΈΠ΄ΠΈΠΌ? ΠΠ±ΡΡΠ½ΡΠΉ Π·Π°ΠΏΡΠΎΡ, ΠΏΠΎ Π²ΡΠ΅ΠΌΠ΅Π½Π½ΠΎΠΌΡ ΡΡΠ΅Π·Ρ.
ΠΠ΅Π»Π°Π΅ΠΌ ΠΏΡΠΎΡΡΠ΅ΠΉΡΠ΅Π΅ Π»ΠΎΠ³ΠΈΡΠ΅ΡΠΊΠΎΠ΅ ΠΏΡΠ΅Π΄ΠΏΠΎΠ»ΠΎΠΆΠ΅Π½ΠΈΠ΅: Π΅ΡΠ»ΠΈ Π΅ΡΡΡ Π²ΡΠ±ΠΎΡΠΊΠ° Π²ΡΠ΅ΠΌΠ΅Π½Π½ΠΎΠ³ΠΎ ΡΡΠ΅Π·Π°, ΡΠΎ Π½Π°ΠΌ ΠΏΠΎΠΌΠΎΠΆΠ΅Ρ? ΠΡΠ°Π²ΠΈΠ»ΡΠ½ΠΎ β ΡΠ΅ΠΊΡΠΈΠΎΠ½ΠΈΡΠΎΠ²Π°Π½ΠΈΠ΅.
Π§ΡΠΎ ΡΠ΅ΠΊΡΠΈΠΎΠ½ΠΈΡΠΎΠ²Π°ΡΡ?
ΠΠ° ΠΏΠ΅ΡΠ²ΡΠΉ Π²Π·Π³Π»ΡΠ΄, Π²ΡΠ±ΠΎΡ ΠΎΡΠ΅Π²ΠΈΠ΄Π΅Π½ β Π΄Π΅ΠΊΠ»Π°ΡΠ°ΡΠΈΠ²Π½ΠΎΠ΅ ΡΠ΅ΠΊΡΠΈΠΎΠ½ΠΈΡΠΎΠ²Π°Π½ΠΈΠ΅ ΡΠ°Π±Π»ΠΈΡΡ Β«shipmentΒ» ΠΏΠΎ ΠΊΠ»ΡΡΡ Β«SHIPMENT_DATEΒ» (Π·Π°Π±Π΅Π³Π°Ρ ΡΠΈΠ»ΡΠ½ΠΎ Π²ΠΏΠ΅ΡΠ΅Π΄ β Π² ΠΈΡΠΎΠ³Π΅ Π½Π° ΠΏΡΠΎΠ΄Π°ΠΊΡΠ½ ΠΏΠΎΠ»ΡΡΠΈΠ»ΠΎΡΡ Π½Π΅ΠΌΠ½ΠΎΠ³ΠΎ Π½Π΅ ΡΠ°ΠΊ).
ΠΠ°ΠΊ ΡΠ΅ΠΊΡΠΈΠΎΠ½ΠΈΡΠΎΠ²Π°ΡΡ?
ΠΡΠΎΡ Π²ΠΎΠΏΡΠΎΡ ΡΠΎΠΆΠ΅ Π½Π΅ ΡΠ»ΠΈΡΠΊΠΎΠΌ ΡΠ»ΠΎΠΆΠ½ΡΠΉ. ΠΠ»Π°Π³ΠΎ, Π² PostgreSQL 10, ΡΠ΅ΠΏΠ΅ΡΡ ΡΠ΅Π»ΠΎΠ²Π΅ΡΠ΅ΡΠΊΠΈΠΉ ΠΌΠ΅Ρ
Π°Π½ΠΈΠ·ΠΌ ΡΠ΅ΠΊΡΠΈΠΎΠ½ΠΈΡΠΎΠ²Π°Π½ΠΈΡ.
ΠΡΠ°ΠΊ:
- Π‘ΠΎΡ ΡΠ°Π½ΡΠ΅ΠΌ Π΄Π°ΠΌΠΏ ΠΈΡΡ ΠΎΠ΄Π½ΠΎΠΉ ΡΠ°Π±Π»ΠΈΡΡ β pg_dump source_table
- Π£Π΄Π°Π»ΡΠ΅ΠΌ ΠΈΡΡ ΠΎΠ΄Π½ΡΡ ΡΠ°Π±Π»ΠΈΡΡ β drop table source_table
- Π‘ΠΎΠ·Π΄Π°Π΅ΠΌ ΡΠΎΠ΄ΠΈΡΠ΅Π»ΡΡΠΊΡΡ ΡΠ°Π±Π»ΠΈΡΡ Ρ ΡΠ΅ΠΊΡΠΈΠΎΠ½ΠΈΡΠΎΠ²Π°Π½ΠΈΠ΅ΠΌ ΠΏΠΎ Π΄ΠΈΠ°ΠΏΠ°Π·ΠΎΠ½Ρ β create table source_table
- Π‘ΠΎΠ·Π΄Π°Π΅ΠΌ ΡΠ΅ΠΊΡΠΈΠΈ β create table source_table, create index
- ΠΠΌΠΏΠΎΡΡΠΈΡΡΠ΅ΠΌ Π΄Π°ΠΌΠΏ, ΡΠΎΠ·Π΄Π°Π½Π½ΡΠΉ Π½Π° ΡΠ°Π³Π΅ 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
ΠΡΠΎΠ²Π΅ΡΡΠ΅ΠΌ ΡΠ΅Π·ΡΠ»ΡΡΠ°ΡΡ ΡΠ΅ΠΊΡΠΈΠΎΠ½ΠΈΡΠΎΠ²Π°Π½ΠΈΡ
Π§ΡΠΎ ΠΆΠ΅ ΠΌΡ ΠΈΠΌΠ΅Π΅ΠΌ Π² ΡΠ΅Π·ΡΠ»ΡΡΠ°ΡΠ΅? ΠΠΎΠ»Π½ΡΠΉ ΡΠ΅ΠΊΡΡ ΠΏΠ»Π°Π½Π° Π²ΡΠΏΠΎΠ»Π½Π΅Π½ΠΈΡ Π±ΠΎΠ»ΡΡΠΎΠΉ ΠΈ ΡΠΊΡΡΠ½ΡΠΉ, ΠΏΠΎΡΡΠΎΠΌΡ Π²ΠΏΠΎΠ»Π½Π΅ ΠΌΠΎΠΆΠ½ΠΎ ΠΎΠ³ΡΠ°Π½ΠΈΡΠΈΡΡΡΡ ΠΈΡΠΎΠ³ΠΎΠ²ΡΠΌΠΈ ΡΠΈΡΡΠ°ΠΌΠΈ.
ΠΡΠ»ΠΎ
Cost: 502 997.55
Execution time: 505 seconds.
Π‘ΡΠ°Π»ΠΎ
Cost: 77 872.36
Execution time: 79 seconds.
ΠΠΏΠΎΠ»Π½Π΅ Ρ ΠΎΡΠΎΡΠΈΠΉ ΡΠ΅Π·ΡΠ»ΡΡΠ°Ρ. Π£ΠΌΠ΅Π½ΡΡΠΈΠ»ΠΈ ΡΡΠΎΠΈΠΌΠΎΡΡΡ ΠΈ Π²ΡΠ΅ΠΌΡ Π²ΡΠΏΠΎΠ»Π½Π΅Π½ΠΈΡ. Π’Π°ΠΊΠΈΠΌ ΠΎΠ±ΡΠ°Π·ΠΎΠΌ ΠΈΡΠΏΠΎΠ»ΡΠ·ΠΎΠ²Π°Π½ΠΈΠ΅ ΡΠ΅ΠΊΡΠΈΠΎΠ½ΠΈΡΠΎΠ²Π°Π½ΠΈΡ Π΄Π°Π΅Ρ ΠΎΠΆΠΈΠ΄Π°Π΅ΠΌΡΠΉ ΡΡΡΠ΅ΠΊΡ ΠΈ Π² ΡΠ΅Π»ΠΎΠΌ β Π±Π΅Π· Π½Π΅ΠΎΠΆΠΈΠ΄Π°Π½Π½ΠΎΡΡΠ΅ΠΉ.
ΠΠ±ΡΠ°Π΄ΠΎΠ²Π°ΡΡ Π·Π°ΠΊΠ°Π·ΡΠΈΠΊΠ°
Π Π΅Π·ΡΠ»ΡΡΠ°ΡΡ ΡΠ΅ΡΡΠΈΡΠΎΠ²Π°Π½ΠΈΡ Π±ΡΠ»ΠΈ ΠΏΡΠ΅Π΄ΡΡΠ°Π²Π»Π΅Π½Ρ Π·Π°ΠΊΠ°Π·ΡΠΈΠΊΡ Π½Π° ΡΠ°ΡΡΠΌΠΎΡΡΠ΅Π½ΠΈΠ΅. Π ΠΏΠΎΡΠ»Π΅ ΠΎΠ·Π½Π°ΠΊΠΎΠΌΠ»Π΅Π½ΠΈΡ ΠΈΠΌ Π±ΡΠ» Π²ΡΠ΄Π°Π½ Π½Π΅ΡΠΊΠΎΠ»ΡΠΊΠΎ Π½Π΅ΠΎΠΆΠΈΠ΄Π°Π½Π½ΡΠΉ Π²Π΅ΡΠ΄ΠΈΠΊΡ: Β«ΠΡΠ»ΠΈΡΠ½ΠΎ, ΡΠ΅ΠΊΡΠΈΠΎΠ½ΠΈΡΡΠΉΡΠ΅ ΡΠ°Π±Π»ΠΈΡΡ Β«data»».
ΠΠ°, Π½ΠΎ ΠΌΡ Π²Π΅Π΄Ρ ΠΈΡΡΠ»Π΅Π΄ΠΎΠ²Π°Π»ΠΈ ΡΠΎΠ²ΡΠ΅ΠΌ Π΄ΡΡΠ³ΡΡ ΡΠ°Π±Π»ΠΈΡΡ Β«shipmentΒ», ΡΠ°Π±Π»ΠΈΡΠ° Β«dataΒ» Π½Π΅ ΠΈΠΌΠ΅Π΅Ρ ΠΏΠΎΠ»Ρ Β«SHIPMENT_DATEΒ».
ΠΠ΅ ΠΏΡΠΎΠ±Π»Π΅ΠΌΠ°, Π΄ΠΎΠ±Π°Π²Π»ΡΠΉΡΠ΅, ΠΌΠ΅Π½ΡΠΉΡΠ΅. ΠΠ»Π°Π²Π½ΠΎΠ΅, ΡΡΠΎΠ±Ρ Π·Π°ΠΊΠ°Π·ΡΠΈΠΊΠ° ΡΡΡΡΠ°ΠΈΠ²Π°Π»ΠΎ, ΡΡΠΎ ΠΏΠΎΠ»ΡΡΠΈΡΡΡ Π² ΡΠ΅Π·ΡΠ»ΡΡΠ°ΡΠ΅, ΠΏΠΎΠ΄ΡΠΎΠ±Π½ΠΎΡΡΠΈ ΡΠ΅Π°Π»ΠΈΠ·Π°ΡΠΈΠΈ Π½Π΅ ΠΎΡΠΎΠ±ΠΎ ΡΠΎ ΠΈ Π²Π°ΠΆΠ½Ρ.
Π‘Π΅ΠΊΡΠΈΠΎΠ½ΠΈΡΡΠ΅ΠΌ ΠΎΡΠ½ΠΎΠ²Π½ΡΡ ΡΠ°Π±Π»ΠΈΡΡ Β«dataΒ»
Π ΠΎΠ±ΡΠ΅ΠΌ-ΡΠΎ Π½ΠΈΠΊΠ°ΠΊΠΈΡ ΠΎΡΠΎΠ±ΡΡ ΡΠ»ΠΎΠΆΠ½ΠΎΡΡΠ΅ΠΉ Π½Π΅ Π²ΠΎΠ·Π½ΠΈΠΊΠ»ΠΎ. Π₯ΠΎΡΡ, Π°Π»Π³ΠΎΡΠΈΡΠΌ ΡΠ΅ΠΊΡΠΈΠΎΠ½ΠΈΡΠΎΠ²Π°Π½ΠΈΡ, ΠΊΠΎΠ½Π΅ΡΠ½ΠΎ, Π½Π΅ΡΠΊΠΎΠ»ΡΠΊΠΎ ΠΏΠΎΠΌΠ΅Π½ΡΠ»ΡΡ.
ΠΠΎΠ±Π°Π²Π»ΡΠ΅ΠΌ ΡΡΠΎΠ»Π±Π΅Ρ Β«SHIPMENT_DATAΒ» Π² ΡΠ°Π±Π»ΠΈΡΡ Β«dataΒ»
psql -h Ρ
ΠΎΡΡ -U Π±Π°Π·Π° -d ΡΠ·Π΅Ρ
=> ALTER TABLE data ADD COLUMN "SHIPMENT_DATE" timestamp without time zone ;
ΠΠ°ΠΏΠΎΠ»Π½ΡΠ΅ΠΌ Π·Π½Π°ΡΠ΅Π½ΠΈΡ ΡΡΠΎΠ»Π±ΡΠ° Β«SHIPMENT_DATAΒ» Π² ΡΠ°Π±Π»ΠΈΡΠ΅ Β«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
$$;
Π‘ΠΎΡ ΡΠ°Π½ΡΠ΅ΠΌ Π΄Π°ΠΌΠΏ ΡΠ°Π±Π»ΠΈΡΡ Β«dataΒ»
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
CΠΎΠ·Π΄Π°Π΅ΠΌ ΠΎΡΠ΄Π΅Π»ΡΠ½ΡΡ ΡΠ΅ΠΊΡΠΈΡ Π΄Π»Ρ ΡΡΠ°ΡΡΡ Π΄Π°Π½Π½ΡΡ
---------------------------------------------------
--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
$$;
ΠΡΠΎΠ³ΠΎΠ²ΡΠ΅ ΡΠ΅Π·ΡΠ»ΡΡΠ°ΡΡ:
ΠΡΠ»ΠΎ
Cost: 502 997.55
Execution time: 505 seconds.
Π‘ΡΠ°Π»ΠΎ
Cost: 68 533.70
Execution time: 69 seconds
ΠΠΎΡΡΠΎΠΉΠ½ΠΎ, Π²ΠΏΠΎΠ»Π½Π΅ Π΄ΠΎΡΡΠΎΠΉΠ½ΠΎ. Π ΡΡΠΈΡΡΠ²Π°Ρ, ΡΡΠΎ ΠΏΠΎ ΠΏΡΡΠΈ ΡΠ΄Π°Π»ΠΎΡΡ Π±ΠΎΠ»Π΅Π΅-ΠΌΠ΅Π½Π΅Π΅ ΠΎΡΠ²ΠΎΠΈΡΡ ΠΌΠ΅Ρ Π°Π½ΠΈΠ·ΠΌ ΡΠ΅ΠΊΡΠΈΠΎΠ½ΠΈΡΠΎΠ²Π°Π½ΠΈΡ Π² PostgreSQL 10 β ΠΡΠ»ΠΈΡΠ½ΡΠΉ ΡΠ΅Π·ΡΠ»ΡΡΠ°Ρ.
ΠΠΈΡΠΈΡΠ΅ΡΠΊΠΎΠ΅ ΠΎΡΡΡΡΠΏΠ»Π΅Π½ΠΈΠ΅
Π ΠΌΠΎΠΆΠ½ΠΎ, ΡΠ΄Π΅Π»Π°ΡΡ Π΅ΡΠ΅ Π»ΡΡΡΠ΅ — ΠΠ, ΠΠΠΠΠ!ΠΠ»Ρ ΡΡΠΎΠ³ΠΎ Π½ΡΠΆΠ½ΠΎ ΠΈΡΠΏΠΎΠ»ΡΠ·ΠΎΠ²Π°ΡΡ MATERIALIZED VIEW.
CREATE 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 ;
Π ΠΎΡΠ΅ΡΠ΅Π΄Π½ΠΎΠΉ ΡΠ°Π· ΠΏΠ΅ΡΠ΅ΠΏΠΈΡΡΠ²Π°Π΅ΠΌ Π·Π°ΠΏΡΠΎΡ:
ΠΠ°ΠΏΡΠΎΡ Ρ ΠΈΡΠΏΠΎΠ»ΡΠ·ΠΎΠ²Π°Π½ΠΈΠ΅ΠΌ materialized 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';
Π ΠΏΠΎΠ»ΡΡΠ°Π΅ΠΌ Π΅ΡΠ΅ ΠΎΠ΄ΠΈΠ½ ΠΈΡΠΎΠ³:
ΠΡΠ»ΠΎ
Cost: 502 997.55
Execution time: 505 seconds
Π‘ΡΠ°Π»ΠΎ
Cost: 42 481.16
Execution time: 43 seconds.
Π₯ΠΎΡΡ ΠΊΠΎΠ½Π΅ΡΠ½ΠΎ, ΡΡΠΎΠ»Ρ ΠΌΠ½ΠΎΠ³ΠΎΠΎΠ±Π΅ΡΠ°ΡΡΠΈΠΉ ΡΠ΅Π·ΡΠ»ΡΡΠ°Ρ ΠΎΠ½ ΠΎΠ±ΠΌΠ°Π½ΡΠΈΠ², ΠΏΡΠ΅Π΄ΡΡΠ°Π²Π»Π΅Π½ΠΈΡ-ΡΠΎ Π½Π°Π΄ΠΎ ΡΠ΅ΡΡΠ΅ΡΠΈΡΡ. Π’Π°ΠΊ ΡΡΠΎ ΠΈΡΠΎΠ³ΠΎΠ²ΠΎΠ΅ Π²ΡΠ΅ΠΌΡ ΠΏΠΎΠ»ΡΡΠ΅Π½ΠΈΡ Π΄Π°Π½Π½ΡΡ Π½Π΅ ΡΠΈΠ»ΡΠ½ΠΎ-ΡΠΎ ΠΈ ΠΏΠΎΠΌΠΎΠΆΠ΅Ρ. ΠΠΎ Π² ΠΊΠ°ΡΠ΅ΡΡΠ²Π΅ ΡΠΊΡΠΏΠ΅ΡΠΈΠΌΠ΅Π½ΡΠ° Π²ΠΏΠΎΠ»Π½Π΅ ΠΈΠ½ΡΠ΅ΡΠ΅ΡΠ½ΠΎ.
ΠΠ° ΡΠ°ΠΌΠΎΠΌ Π΄Π΅Π»Π΅, ΠΊΠ°ΠΊ Π²ΡΡΡΠ½ΠΈΠ»ΠΎΡΡ, Π΅ΡΡ ΡΠ°Π· ΡΠΏΠ°ΡΠΈΠ±ΠΎ
ΠΠΎΡΠ»Π΅ΡΠ»ΠΎΠ²ΠΈΠ΅
ΠΡΠ°ΠΊ, Π·Π°ΠΊΠ°Π·ΡΠΈΠΊ ΡΠ΄ΠΎΠ²Π»Π΅ΡΠ²ΠΎΡΠ΅Π½. Π Π½ΡΠΆΠ½ΠΎ ΠΏΠΎΠ»ΡΠ·ΠΎΠ²Π°ΡΡΡΡ ΡΠΈΡΡΠ°ΡΠΈΠ΅ΠΉ.
ΠΠΎΠ²Π°Ρ Π·Π°Π΄Π°ΡΠ°: Π§ΡΠΎ ΠΌΠΎΠΆΠ½ΠΎ ΡΠ°ΠΊΠΎΠ³ΠΎ ΠΏΡΠΈΠ΄ΡΠΌΠ°ΡΡ, ΡΡΠΎΠ±Ρ ΡΠ³Π»ΡΠ±ΠΈΡΡ ΠΈ ΡΠ°ΡΡΠΈΡΠΈΡΡ?
Π ΡΡΡ Π²ΡΠΏΠΎΠΌΠΈΠ½Π°Π΅ΡΡΡ β ΡΠ΅Π±ΡΡΠ°, Π° Π²Π΅Π΄Ρ Ρ Π½Π°Ρ Π½Π΅Ρ ΠΌΠΎΠ½ΠΈΡΠΎΡΠΈΠ½Π³Π° Π½Π°ΡΠΈΡ Π±Π°Π· Π΄Π°Π½Π½ΡΡ PostgreSQL.
ΠΠΎΠ»ΠΎΠΆΠ° ΡΡΠΊΡ Π½Π° ΡΠ΅ΡΠ΄ΡΠ΅, Π½Π΅ΠΊΠΈΠΉ ΠΌΠΎΠ½ΠΈΡΠΎΡΠΈΠ½Π³ Π² Π²ΠΈΠ΄Π΅ Cloud Watch Π½Π° AWS Π²ΡΠ΅-ΡΠ°ΠΊΠΈ Π΅ΡΡΡ. ΠΠΎ ΠΊΠ°ΠΊΠ°Ρ ΠΏΠΎΠ»ΡΠ·Π° ΠΎΡ ΡΡΠΎΠ³ΠΎ ΠΌΠΎΠ½ΠΈΡΠΎΡΠΈΠ½Π³Π° Π΄Π»Ρ DBA? Π ΠΎΠ±ΡΠ΅ΠΌ-ΡΠΎ ΠΏΡΠ°ΠΊΡΠΈΡΠ΅ΡΠΊΠΈ Π½ΠΈΠΊΠ°ΠΊΠΎΠΉ.
ΠΡΠ»ΠΈ Π²ΡΠΏΠ°Π» ΡΠ°Π½Ρ ΡΠ΄Π΅Π»Π°ΡΡ ΠΏΠΎΠ»Π΅Π·Π½ΠΎΠ΅ ΠΈ ΠΈΠ½ΡΠ΅ΡΠ΅ΡΠ½ΠΎΠ΅ ΠΈ Π΄Π»Ρ ΡΠ΅Π±Ρ, Π½Π΅ Π²ΠΎΡΠΏΠΎΠ»ΡΠ·ΠΎΠ²Π°ΡΡΡΡ ΡΠ°ΠΊΠΈΠΌ ΡΠ°Π½ΡΠΎΠΌ Π½Π΅Π»ΡΠ·Ρ β¦
ΠΠΠ
ΠΠΎΡ ΡΠ°ΠΊ ΠΌΡ ΠΈ ΠΏΠΎΠ΄ΠΎΡΠ»ΠΈ ΠΊ ΡΠ°ΠΌΠΎΠΌΡ ΠΈΠ½ΡΠ΅ΡΠ΅ΡΠ½ΠΎΠΌΡ:
3 ΠΠ΅ΠΊΠ°Π±ΡΡ 2018 Π³ΠΎΠ΄Π°.
ΠΡΠΈΠ½ΡΡΠΈΠ΅ ΡΠ΅ΡΠ΅Π½ΠΈΡ ΠΎ Π½Π°ΡΠ°Π»Π΅ ΡΠ°Π±ΠΎΡ ΠΏΠΎ ΠΈΡΡΠ»Π΅Π΄ΠΎΠ²Π°Π½ΠΈΡ ΠΈΠΌΠ΅ΡΡΠΈΡ ΡΡ Π²ΠΎΠ·ΠΌΠΎΠΆΠ½ΠΎΡΡΠ΅ΠΉ ΠΌΠΎΠ½ΠΈΡΠΎΡΠΈΠ½Π³Π° ΠΏΡΠΎΠΈΠ·Π²ΠΎΠ΄ΠΈΡΠ΅Π»ΡΠ½ΠΎΡΡΠΈ Π·Π°ΠΏΡΠΎΡΠΎΠ² PostgreSQL.
ΠΠΎ ΡΡΠΎ ΡΠΆΠ΅ ΡΠΎΠ²ΡΠ΅ΠΌ, Π΄ΡΡΠ³Π°Ρ ΠΈΡΡΠΎΡΠΈΡ.
ΠΡΠΎΠ΄ΠΎΠ»ΠΆΠ΅Π½ΠΈΠ΅, ΡΠ»Π΅Π΄ΡΠ΅Ρ…
ΠΡΡΠΎΡΠ½ΠΈΠΊ: habr.com