Jak podzielić istniejącą tabelę na postgres?

Chciałbym podzielić tabelę z 1M + wierszy według zakresu dat. Jak to się zwykle robi, nie wymagając dużo przestojów lub ryzyka utraty danych? Oto strategie, które rozważam, ale są otwarte na sugestie:

1. Istniejący stół jest wzorcem, a dzieci dziedziczą po nim. Z biegiem czasu przenoszą dane z głównego na potomne, ale przez pewien czas niektóre dane znajdą się w głównej tabeli, a niektóre w potomnych.

2. Utwórz nowe tabele główne i podrzędne. Utwórz kopię danych w istniejącej tabeli w tabelach podrzędnych (więc dane będą znajdować się w dwóch miejscach). Gdy tabele potomne będą miały najnowsze dane, zmień wszystkie wstawki, aby wskazać nową tabelę główną, i usuń istniejącą tabelę.

Ponieważ nr 1 wymaga kopiowania danych z wzorca do dziecka, gdy jest ono w aktywnym środowisku produkcyjnym, osobiście poszedłem z rozdz. 2 (tworzenie nowego wzorca). Zapobiega to zakłóceniom oryginalnej tabeli, gdy jest ona aktywnie używana, a jeśli występują jakiekolwiek problemy, mogę łatwo usunąć nowy wzorzec bez problemów i kontynuować korzystanie z oryginalnej tabeli. Oto kroki, aby to zrobić:

1. Utwórz nowy stół główny.

   CREATE TABLE new_master (
       id          serial,
       counter     integer,
       dt_created  DATE DEFAULT CURRENT_DATE NOT NULL
   );

2. Twórz dzieci dziedziczące od mistrza.

   CREATE TABLE child_2014 (
       CONSTRAINT pk_2014 PRIMARY KEY (id),
       CONSTRAINT ck_2014 CHECK ( dt_created < DATE '2015-01-01' )
   ) INHERITS (new_master);
   CREATE INDEX idx_2014 ON child_2014 (dt_created);
   
   CREATE TABLE child_2015 (
       CONSTRAINT pk_2015 PRIMARY KEY (id),
       CONSTRAINT ck_2015 CHECK ( dt_created >= DATE '2015-01-01' AND dt_created < DATE '2016-01-01' )
   ) INHERITS (new_master);
   CREATE INDEX idx_2015 ON child_2015 (dt_created);
   
   ...

3. Skopiuj wszystkie dane historyczne do nowej tabeli głównej

   INSERT INTO child_2014 (id,counter,dt_created)
   SELECT id,counter,dt_created
   from old_master
   where dt_created < '01/01/2015'::date;

4. Tymczasowo wstrzymaj nowe wstawki / aktualizacje produkcyjnej bazy danych

5. Skopiuj najnowsze dane do nowej tabeli głównej

   INSERT INTO child_2015 (id,counter,dt_created)
   SELECT id,counter,dt_created
   from old_master
   where dt_created >= '01/01/2015'::date AND dt_created < '01/01/2016'::date;

6. Zmień nazwy tabel, aby new_master stał się produkcyjną bazą danych.

   ALTER TABLE old_master RENAME TO old_master_backup;
   ALTER TABLE new_master RENAME TO old_master;

7. Dodaj funkcję instrukcji INSERT do old_master, aby dane były przekazywane do właściwej partycji.

   CREATE OR REPLACE FUNCTION fn_insert() RETURNS TRIGGER AS $$
   BEGIN
       IF ( NEW.dt_created >= DATE '2015-01-01' AND
            NEW.dt_created < DATE '2016-01-01' ) THEN
           INSERT INTO child_2015 VALUES (NEW.*);
       ELSIF ( NEW.dt_created < DATE '2015-01-01' ) THEN
           INSERT INTO child_2014 VALUES (NEW.*);
       ELSE
           RAISE EXCEPTION 'Date out of range';
       END IF;
       RETURN NULL;
   END;
   $$
   LANGUAGE plpgsql;

8. Dodaj wyzwalacz, aby funkcja była wywoływana na WSTAWKI

   CREATE TRIGGER tr_insert BEFORE INSERT ON old_master
   FOR EACH ROW EXECUTE PROCEDURE fn_insert();

9. Ustaw wykluczenie ograniczenia na WŁ

   SET constraint_exclusion = on;

10. Ponownie włącz AKTUALIZACJE i WSTAWKI w produkcyjnej bazie danych

11. Skonfiguruj wyzwalacz lub cron, aby utworzyć nowe partycje i zaktualizować funkcję w celu przypisania nowych danych do poprawnej partycji. W tym artykule znajdują się przykłady kodu

12. Usuń old_master_backup

Istnieje nowe narzędzie o nazwie pg_pathman ( https://github.com/postgrespro/pg_pathman ), które zrobi to za Ciebie automatycznie.

Zrobiłoby to coś takiego.

SELECT create_range_partitions('master', 'dt_created', 
   '2015-01-01'::date, '1 day'::interval);