Auswahl  

 

Postgres
SQL
10.x
Partitionierung, partitionierte Tabelle
07.11.17
MP
15.06.18
MM

Body

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 einen Update nicht die Partition wechseln
  • Row Trigger müssen auf Partitionseben definiert werden