1 Tabele historyczne w PostgreSQLRafał Piechocki Promet
2 Tabele historyczne wprowadzenie
3 Cele i korzyści
4 Cele i korzyści stosowania tabel historycznychArchiwum wersji Pomoc w sporze Śledzenie zmian Podstawowe PITR Backup danych Wyłapywanie błędów Dodatkowe
5 Realizacja tabel historycznych
6 Realizacja tabel historycznychAplikacja ORM Baza danych Serwer bazy danych
7 Czynniki, które należy brać pod uwagęWpływ na wydajność Przeźroczystość rozwiązania Łatwość dostępu do archiwum Łatwość implementacji
8 Przykładowe implementacje1. Osobna tabela – change logi
9 Tabela podstawowa + tabela change logówZDJECIE (tabela podstawowa) id pesel status komunikat pracownik 1 Promet 2 (update) -1 Nie kolorowe CHANGE_LOG # tabela id data operacja 3 zdjecie 1 insert 4 2 update CHANGE_LOG_VALUE # kolumna stara nowa 4 status -1 komunikat Nie kolorowe pracownik Promet
10 INSERT INTO dz_oceny (os_id,toc_kod,wart_oc_kolejnosc,prot_id,term_prot_nr) VALUES ('5094','ZAL-STD','1','136651','1'); INSERT INTO M_CHANGE_LOG (TABLE_NAME, MOD_TIME, MOD_TYPE) VALUES ('dz_oceny', now(), 'insert'); SELECT MAX(id) as max FROM M_CHANGE_LOG; INSERT INTO M_CHANGE_LOG_VALUES (CHANGE_ID, FIELD_NAME, VALUE) VALUES (8044, 'os_id', '5094'); INSERT INTO M_CHANGE_LOG_VALUES (CHANGE_ID, FIELD_NAME, VALUE) VALUES (8044, 'toc_kod', 'ZAL-STD'); INSERT INTO M_CHANGE_LOG_VALUES (CHANGE_ID, FIELD_NAME, VALUE) VALUES (8044, 'wart_oc_kolejnosc', '1'); INSERT INTO M_CHANGE_LOG_VALUES (CHANGE_ID, FIELD_NAME, VALUE) VALUES (8044, 'prot_id', '136651'); INSERT INTO M_CHANGE_LOG_VALUES (CHANGE_ID, FIELD_NAME, VALUE) VALUES (8044, 'term_prot_nr', '1');
11 Tabele typu change log - podsumowanieZalety: Łatwość wykrywania zmian Łatwość migracji danych Testowanie spójności danych Wady: Wydajność (liczba zapytań) Trudność implementacji Nieprzeźroczystość?
12 Przykładowe implementacje2. Tabela + widok
13 Tabela + widok Filtr: WHERE waznosc=infinityZDJECIE (tabela podstawowa) id waznosc pesel status komunikat pracownik 1 -1 Nie kolorowe Promet infinity Zdjęcie OK 2 Filtr: WHERE waznosc=infinity V_ZDJECIE (widok dla tabeli podstawowej) id waznosc pesel status komunikat pracownik 1 infinity Zdjęcie OK Promet 2
14 Tabela podstawowa ZDJECIE (tabela podstawowa) id waznosc pesel status komunikat pracownik 1 -1 Nie kolorowe Promet infinity Zdjęcie OK 2 Tabela zawiera zarówno bieżące dane jak i archiwalne. Klucz główny: (ID + waznosc), ograniczenia (pesel + waznosc) Triggery INSERT, UPDATE, DELETE: Oznaczają bieżący rekord jako wygasły Wstawiają nowy rekord oznaczony jako ważny Chronią archiwalne dane przed zmianą
15 Widok dla tabeli podstawowejV_ZDJECIE (widok dla tabeli podstawowej) id waznosc pesel status komunikat pracownik 1 infinity Zdjęcie OK Promet 2 Filtr wybiera z tabeli podstawowej wyłącznie te rekordy, które nie wygasły. Widok zawiera zatem wyłącznie bieżące dane. Możliwość wprowadzenia reguł dla widoku = możliwość wykonywania na widoku operacji: INSERT UPDATE DELETE
16 Demonstracja Tabela + widok
17 Tabela + widok - podsumowanieZalety: Łatwy dostęp do archiwum Bezpieczeństwo danych Wady: Klucze główne, ograniczenia Rozmiar tabeli podstawowej Nienaturalne operacje Problematyczna zmiana struktury
18 Przykładowe implementacje3. Tabela podstawowa + tabela archiwalna
19 Tabela podstawowa + tabela archiwalnaPUBLIC.ZDJECIE (tabela podstawowa) id timestamp pesel status komunikat pracownik 1 Zdjęcie OK Promet 2 ARCHIWUM.ZDJECIE (tabela archiwalna) id timestamp pesel status komunikat pracownik 1 -1 Nie kolorowe Promet Zdjęcie OK 2
20 Tabela podstawowa Zawiera wyłącznie aktualne danePUBLIC.ZDJECIE (tabela podstawowa) id timestamp pesel status komunikat pracownik 1 Zdjęcie OK Promet 2 Zawiera wyłącznie aktualne dane Triggery INSERT, DELETE, UPDATE kopiują dane do archiwum, nie zmieniając nic w tabeli podstawowej. Mogą dziedziczyć podstawową strukturę z szablonu
21 Tabela archiwalna Tabela znajduje się w osobnym schemacie DBARCHIWUM.ZDJECIE (tabela archiwalna) id timestamp pesel status komunikat pracownik 1 -1 Nie kolorowe Promet Zdjęcie OK 2 Tabela znajduje się w osobnym schemacie DB Zawiera dane archiwalne + dane bieżące Dane mogą być chronione przed zmianą/kasowaniem Schemat może być umieszczony na innym Storage-u
22 Tabela podstawowa + tabela archiwalnaWykorzystywane mechanizmy DBMS: Dziedziczenie struktury Triggery Procedury składowane Rozwiązane problemy: Spójność struktury tabeli podstawowej z archiwalną Łatwość tworzenia tabel archiwalnych i potrzebnych triggerów
23 Dziedziczenie tabel Wzorzec (dla wszystkich tabel, które mają mieć archiwum) Tabela podstawowa Tabela archiwalna wskazuje jakie tabele mają być archiwizowane (opcjonalne) zapewnienie spójności struktury dla tabel (konieczne)
24 Tabela podstawowa + tabela archiwalnaDemonstracja Tabela podstawowa + tabela archiwalna
25 Tabela podstawowa + tabela archiwalnaZalety: Łatwy dostęp do danych Dostosowanie się do zmian struktury Wydajność Przeźroczystość dla aplikacji Naturalne zapytania SQL Tabela archiwalna jako backup, PITR Wady: Implementacja w innych DBMS?
26 Kiedy stosować, a kiedy nie?
27 Kiedy (nie) stosować? Warto stosować: Nie warto stosować:Kluczowe dane Nie warto stosować: Często zmieniające się dane Dane statyczne Dane wtórne/redundantne
28 Pytania?