Postgres hat seit der Version 10.x die Partitionierung auf eine neue Stufe gehoben. Datensätze werden jetzt ohne den zusätzlichem Einsatz von Triggern auf die richtigen Tabellen-Partitionen verteilt.
Schauen wir uns ein Beispiel an:
CREATE TABLE verkauf (
verkaufs_datum date,
tier varchar(200),
total int)
partition by range (verkaufs_datum);
CREATE TABLE verkauf_y2014 PARTITION OF verkauf FOR VALUES FROM (MINVALUE) TO ('2015-01-01');
CREATE TABLE verkauf_y2015 PARTITION OF verkauf FOR VALUES FROM ('2015-01-01') TO ('2016-01-01');
CREATE TABLE verkauf_y2016 PARTITION OF verkauf FOR VALUES FROM ('2016-01-01') TO ('2017-01-01');
CREATE TABLE verkauf_y2017 PARTITION OF verkauf FOR VALUES FROM ('2017-01-01') TO ('2018-01-01');
CREATE TABLE verkauf_y2018 PARTITION OF verkauf FOR VALUES FROM ('2018-01-01') to (MAXVALUE);
Hinweis die Endgrenzen sind in der Range NICHT enthalten (also <'2015-01-01' !!!)
insert into verkauf values ('2014-09-13','Hund',1);
insert into verkauf values ('2014-12-30','Pinguin',1);
insert into verkauf values ('2015-02-21','Katze',2);
insert into verkauf values ('2016-05-23','Maus',3);
insert into verkauf values ('2017-09-13','Hirsch',4);
insert into verkauf values ('2018-01-11','Reh',5);
insert into verkauf values ('2014-05-29','Kuh',6);
\d+ verkauf_y2014
Wir tragen mal 1000 Datensätze ein ...
INSERT INTO verkauf
SELECT generate_series(0,1000) + date '1/1/2015', 'Tier',(cast(trunc(random() * 1000) as int));
insert into verkauf values ('2019-05-09','Robot',7); -- Bei Maxvalue OK
ERROR: no partition of relation "sales" found for row
DETAIL: Partition key of the failing row contains (year) = (2019-05-09).
insert into verkauf values ('2011-05-09','Dino',8); -- bei Minvalue OK
ERROR: no partition of relation "sales" found for row
DETAIL: Partition key of the failing row contains (year) = (2011-05-09).
Lesen (komplette Tabelle):
SELECT * FROM verkauf;
Wir lesen nur eine Partition:
SELECT * FROM verkauf_y2014;
Nun wollen wir auf einer Partition einen Index erstellen: Defaultname ist <table_name_col_name>_idx
CREATE INDEX ON verkauf_y2014 (verkaufs_datum);
CREATE INDEX ON verkauf_y2015 (verkaufs_datum);
CREATE INDEX ON verkauf_y2016 (verkaufs_datum);
CREATE INDEX ON verkauf_y2017 (verkaufs_datum);
CREATE INDEX ON verkauf_y2018 (verkaufs_datum);
Nun können wir eine Partition löschen:
DROP TABLE verkauf_2014;
Oder eine Partition aushängen:
ALTER TABLE verkauf DETACH PARTITION verkauf_y2015;
Partition wieder einhängen (mit gleicher Zeitspanne):
ALTER TABLE verkauf ATTACH PARTITION verkauf_y2015
for values from ('2015-01-01') TO ('2016-01-01');
Partition wieder einhängen (mit anderer Zeitspanne): Das würde einem SPLIT PARTITION entsprechen, den es derzeit bei Postgres nicht gibt!
Partition aushängen:
ALTER TABLE verkauf DETACH PARTITION verkauf_y2015;
Daten aus dem zweiten Halbjahr in eine Tabelle verschieben:
CREATE TABLE verkauf_y2015_2
(LIKE verkauf INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
ALTER TABLE verkauf_y2015_2 ADD CONSTRAINT co_y2015_2
CHECK ( year >= DATE '2015-07-01' AND year <= DATE '2016-01-01' );
INSERT INTO verkauf_y2015_2
SELECT * FROM verkauf_y2015
WHERE year>=DATE '2015-07-01';
DELETE FROM verkauf_y2015
WHERE year>=DATE '2015-07-01';
Die beiden (neu/alt) Partitionen wieder einhängen:
ALTER TABLE verkauf ATTACH PARTITION verkauf_y2015
FOR VALUES FROM ('2015-01-01') TO ('2015-07-01');
ALTER TABLE verkauf ATTACH PARTITION verkauf_y2015_2
FOR VALUES FROM ('2015-07-01') TO ('2016-01-01');
Wir testen das Ganze:
insert into verkauf values ('2015-07-29','Kalb',6);
SELECT * FROM verkauf_y2015_2 WHERE product='Kalb';
SELECT table_name,
substring(substring(range from 1 for (select position('TO' in range)-3)) from 18)
as start_range,
substring(range from (select position('TO' in range)+5) for 128) as end_range
FROM ( SELECT inhparent::pg_catalog.regclass as table_name,
pg_catalog.pg_get_expr(c.relpartbound, inhrelid) as range
FROM pg_catalog.pg_class c JOIN pg_catalog.pg_inherits i
ON c.oid = inhrelid
WHERE c.oid = ( SELECT c.oid
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ='sales_y2018' --~ '^(sales_y2016)$'
AND pg_catalog.pg_table_is_visible(c.oid) )
AND c.relispartition) t;
Na, das Ganze können wir über eine Funktion auch vereinfachen:
CREATE OR REPLACE FUNCTION split_partition
( partition_name text,
new_partition1 text,
new_partition2 text,
split_date date)
returns text as $$
DECLARE
v_table_name TEXT;
str TEXT;
str_ges TEXT;
start_date DATE;
end_date DATE;
hk char(1):=chr(39);
BEGIN
SELECT tab_name,
substring(substring(range from 1 for (select position('TO' in range)-3)) from 18)
as start_range,
substring(range from ( select position('TO' in range)+4) for 128) as end_range
INTO v_table_name, start_date, end_date
FROM ( SELECT inhparent::pg_catalog.regclass as tab_name,
pg_catalog.pg_get_expr(c.relpartbound, inhrelid) as range
FROM pg_catalog.pg_class c JOIN pg_catalog.pg_inherits i
ON c.oid = inhrelid
WHERE c.oid = ( SELECT c.oid
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname = partition_name
AND pg_catalog.pg_table_is_visible(c.oid))
AND c.relispartition) t;
str:='ALTER TABLE '||v_table_name||' DETACH PARTITION '||partition_name;
-- EXECUTE str;
str_ges:=str;
str:='CREATE TABLE '||new_partition2||'
(LIKE '||v_table_name||' INCLUDING DEFAULTS INCLUDING CONSTRAINTS)';
-- EXECUTE str;
str_ges:=str_ges||chr(10)||str;
str:='ALTER TABLE '||new_partition2||' ADD CONSTRAINT co_'||new_partition2||'
CHECK ( year >= DATE '||hk||split_date||hk||' AND year <= DATE '||hk||end_date||hk||' )';
-- EXECUTE str;
str_ges:=str_ges||chr(10)||str;
str:='INSERT INTO '||new_partition2||'
SELECT * FROM '||partition_name||'
WHERE year>=DATE '||hk||split_date||hk;
-- EXECUTE str;
str_ges:=str_ges||chr(10)||str;
str:='DELETE FROM '||partition_name||'
WHERE year>=DATE '||hk||split_date||hk;
-- EXECUTE str;
str_ges:=str_ges||chr(10)||str;
str:='ALTER TABLE '||partition_name||' RENAME TO '||new_partition1;
-- EXECUTE str;
str_ges:=str_ges||chr(10)||str;
str:='ALTER TABLE '||v_table_name||' ATTACH PARTITION '||new_partition1||'
for values from ('||hk||start_date||hk||') to ('||hk||split_date||hk||')';
-- EXECUTE str;
str_ges:=str_ges||chr(10)||str;
str:='ALTER TABLE '||v_table_name||' ATTACH PARTITION '||new_partition2||'
FOR VALUES FROM ('||hk||split_date||hk||') TO ('||hk||end_date||hk||')';
-- EXECUTE str;
str_ges:=str_ges||chr(10)||str;
RETURN str_ges;
end;
$$ language plpgsql
;
SELECT split_partition('sales_y2015','sales_y2015_1','sales_y2015_2','2015-07-01');
Nachträgliches Hinzufügen einer normalen Tabelle als Partition:
CREATE TABLE verkauf_y2019
(LIKE verkauf INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
ALTER TABLE verkauf_2019 ADD CONSTRAINT co_y2019
CHECK ( year >= DATE '2019-01-01' AND year <= DATE '2019-12-31' );
\copy measurement_y2008m02 from 'measurement_y2008m02'
-- possibly some other data preparation work
ALTER TABLE_verkauf y2018 VALUES FROM ('2018-01-01') to ('2018-12-31');
ALTER TABLE verkauf ATTACH PARTITION_verkauf 2019
FOR VALUES FROM ('2019-01-01') TO ('2019-12-31' );
Derzeitige Beschränkungen (01.08.2017):
- Kein gemeinsamer Index über alle Partitionen
- Zeilen dürfen durch einem Update nicht die Partition wechseln
- Row Trigger müssen auf Partitionseben definiert werden