Skip to Main Content
 
Titelbild Muniqsoft Training

Search Results

Oracle Tipp Text

Partitionierte Tabellen von Postgres (ab Version 10)

Bereich:Postgres, Version: ab PG 11, Letzte Überarbeitung: 19.03.2020

Keywords:Partitionierung, partitionierte Tabelle

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


Weitere Interessente Artikel zum Thema:


Empfohlene Schulungen zum Thema:
2026.02.18