Temat: Problem - usuwanie starych danych

Witam!
Mam nawet problem ze zdefiniowaniem dobrego tematu...

Mam problem tego typu - ma tabelę, w której mam:
IDKlienta
Data
StanKonta

W tabeli tej codziennie zapisywany jest aktualny stan konta klienta. Całość sprawowała się dobrze, dopóki tabela dość sporo nie urosła przez zwiększenie się ilości klientów.
Od czasu do czasu muszę wyczyścić tabelę. Do tej pory robiłem to w ten sposób, że po prostu starą historię usuwałem. Nie chciałbym jednak jej definitywnie tracić, bo może się kiedyś przydać... A ponieważ nie wszystkim się codziennie zmienia stan konta, więc wymyśliłem sobie, aby pozostawić tylko te daty, w których jest zmiana stanu.
Np. mam takie dane:
A 2012-01-01 10
B 2012-01-01 12
C 2012-01-01 22
A 2012-01-02 10
B 2012-01-02 10
C 2012-01-02 22
A 2012-01-03 11
B 2012-01-03 10
C 2012-01-03 23
A 2012-01-04 12
B 2012-01-04 12
C 2012-01-04 23

(rekordy podkreślone powinny pozostać w tabeli)

Jakieś propozycje jak to rozwiązać? Wolałbym rozwiązanie pure-sql z racji wielkiego rozmiaru tabeli.
A może jakieś inne propozycje?
Baza to postgreSQL.

Z góry dzięki.

pozdrawiaMM

Temat: Problem - usuwanie starych danych

Ten "wielki rozmiar tabeli" to ile wierszy i jaka wielkość tabeli w MB?

Zawsze zamiast usuwać może przenieść dane do innej tabeli, wtedy tam będzie dużo danych, czy to w czymś przeszkadza? Czy w ogóle chcesz się tych danych z bazy pozbyć?

Temat: Problem - usuwanie starych danych

Wymyśliłem takie zapytanie:
select m1.* from tabela m1 left join tabela m2 on m1.IDKlienta=m2.IDKlienta and m1.Data=m2.Data+1
where m1.StanKonta!=m2.StanKonta or m2.StanKonta is null
order by m1.Data, m1.IDKlienta
Zapytanie zwraca (teoretycznie) to co powinno. Sprawdzałem na próbce danych.
Problemem jest wielkość tabeli....

Może coś szybszego?

Temat: Problem - usuwanie starych danych

I takie pytanie przy okazji: co jeśli ktoś kilka razy dziennie zmienił stan konta? W sumie nie wiem co to za system, ale coś mi się tu nie podoba.

Czemu nie zrobić tak, że masz tabelę z zapisem operacji. Wrzucasz tam dane i ich nie zmieniasz. Osobno masz tabelę, gdzie masz jeden wiersz dla każdego klienta z aktualnym stanem konta, wyliczanym na podstawie listy operacji z tabeli pierwszej.

Temat: Problem - usuwanie starych danych

Szymon G.:
Ten "wielki rozmiar tabeli" to ile wierszy i jaka wielkość tabeli w MB?

Zawsze zamiast usuwać może przenieść dane do innej tabeli, wtedy tam będzie dużo danych, czy to w czymś przeszkadza? Czy w ogóle chcesz się tych danych z bazy pozbyć?

Tak. Chcę się pozbyć, bo rozmiar hostingu jest ograniczony - mieszczą się już tylko dwa backupy.
Rozmiar tabeli to ok 110 mln rekordów. W MB dopiszę za chwilę - zapytanie się wykonuje i pgAdmin "stoi".

[EDIT] Tabela ma 18GB !!
SELECT pg_size_pretty(pg_total_relation_size('tabela'));Marcin Miga edytował(a) ten post dnia 27.06.12 o godzinie 11:44

Temat: Problem - usuwanie starych danych

Marcin Miga:
Wymyśliłem takie zapytanie:
select m1.* from tabela m1 left join tabela m2 on m1.IDKlienta=m2.IDKlienta and m1.Data=m2.Data+1
where m1.StanKonta!=m2.StanKonta or m2.StanKonta is null
order by m1.Data, m1.IDKlienta
Zapytanie zwraca (teoretycznie) to co powinno. Sprawdzałem na próbce danych.
Problemem jest wielkość tabeli....

Może coś szybszego?

A czemu uważasz, że to jest wolne? Wolne czyli ile to trwa, jaki jest plan zapytania na tej oryginalnej tabeli?

Temat: Problem - usuwanie starych danych

Szymon G.:
I takie pytanie przy okazji: co jeśli ktoś kilka razy dziennie zmienił stan konta?

To mnie nie interesuje - system pobiera stan konta raz dziennie.
Tabelę z zapisem operacji mam, ale że są to dane zbierane z kilku różnych systemów, więc czasem mogą dane się pojawić po kilku dniach (!!) a stany są zawsze aktualne..

Sprawa dość skomplikowana, ale szczegółów (oprócz bazy danych) nie mogę podać.

Temat: Problem - usuwanie starych danych

Marcin Miga:

Szymon G.:
I takie pytanie przy okazji: co jeśli ktoś kilka razy dziennie zmienił stan konta?

To mnie nie interesuje - system pobiera stan konta raz dziennie.
Tabelę z zapisem operacji mam, ale że są to dane zbierane z kilku różnych systemów, więc czasem mogą dane się pojawić po kilku dniach (!!) a stany są zawsze aktualne..

Sprawa dość skomplikowana, ale szczegółów (oprócz bazy danych) nie mogę podać.

OK, po prostu zastanawiałem się czy jakieś obejście można zrobić...

Ja bym zrobił po prostu takie zapytanie jak zrobiłeś... przy czym:

1) Możesz wrzucić dane do osobnej tabeli i je usunąć z głównej tak:

begin;
insert into archiwum(...) select <tutaj to zapytanie Twoje>
delete from tabela where id in (<tutaj to zapytanie Twoje)>
end;

2) Możesz po prostu wrzucać te importowane dane do innej tabeli i tylko aktualizować główną tabelę jak coś się zmieniło.

Temat: Problem - usuwanie starych danych

Problemem jest zły schemat. Powinieneś mieć tabelę "bieżącą" i tabelę "historyczną" i np trigger wpisujący do tabeli historycznej dane za każdym razem kiedy się coś zmieni, albo coś... Ciężko powiedzieć, za skromny opis wymagań.
Jeżeli po prostu będziesz usuwał, to potem będziesz miał problemy z zapytaniami typu "jaki stan konta miał klient tego dnia?" - do tego już musisz użyć zapytania z magicznym joinem, maxem itd...

Twoje zapytanie pozbędzie się tylko wierszy które się nie różniły następnego dnia. Jeżeli jakiś wiersz się nie zmieniał przez tydzień to Twoje zapytanie nie usunie 6 dni tylko jeden. Jak już się upierasz przy usuwaniu to idea powinna być taka (pewnie nie zadziała, nie mam pg pod ręką, zresztą pewnie rekurencja jakaś by była lepsza)

select m1.* from tabela m1 left join tabela m2 on m1.IDKlienta=m2.IDKlienta and m1.Data<m2.Data
and (select m3.data from tabela m3 where m3.Data > m1.Data and m3.Data < m2.Data and m3.IDKlienta = m1.IDKlienta and m3.StanKonta <> m1.StanKonta) is null
where m1.StanKonta!=m2.StanKonta or m2.StanKonta is null
order by m1.Data, m1.IDKlienta

Temat: Problem - usuwanie starych danych

Adam O.:
Problemem jest zły schemat. Powinieneś mieć tabelę "bieżącą" i tabelę "historyczną" i np trigger wpisujący do tabeli historycznej dane za każdym razem kiedy się coś zmieni, albo coś... Ciężko powiedzieć, za skromny opis wymagań.
Jeżeli po prostu będziesz usuwał, to potem będziesz miał problemy z zapytaniami typu "jaki stan konta miał klient tego dnia?" - do tego już musisz użyć zapytania z magicznym joinem, maxem itd...

Schemat nie jest zły. Interesuje mnie tylko stan konta na dziś o godzinie 00:00. Reszta jest nieważna - dlatego te dane mogę spokojnie usunąć. Historię chcę trzymać tylko i wyłącznie dla siebie. Fakt, że nie spodziewałem się takiej ilości klientów...

Twoje zapytanie pozbędzie się tylko wierszy które się nie różniły następnego dnia. Jeżeli jakiś wiersz się nie zmieniał przez tydzień to Twoje zapytanie nie usunie 6 dni tylko jeden.

Sprawdzałeś? Zapytanie jest na tyle uniwersalne, że na każdej bazie zadziała... Zadziała dobrze

Jak już się upierasz przy usuwaniu to idea powinna być taka (pewnie nie zadziała, nie mam pg pod ręką, zresztą pewnie rekurencja jakaś by była lepsza)

select m1.* from tabela m1 left join tabela m2 on m1.IDKlienta=m2.IDKlienta and m1.Data<m2.Data

> and (select m3.data from tabela m3 where m3.Data > m1.Data and

m3.Data < m2.Data and m3.IDKlienta = m1.IDKlienta and

> m3.StanKonta <> m1.StanKonta) is null

where m1.StanKonta!=m2.StanKonta or m2.StanKonta is null
order by m1.Data, m1.IDKlienta

BŁĄD: ponad jeden wiersz zwrócony przez podzapytanie użyte jako wyrażenie

Temat: Problem - usuwanie starych danych

Marcin Miga:

Adam O.:
Problemem jest zły schemat. Powinieneś mieć tabelę "bieżącą" i tabelę "historyczną" i np trigger wpisujący do tabeli historycznej dane za każdym razem kiedy się coś zmieni, albo coś... Ciężko powiedzieć, za skromny opis wymagań.
Jeżeli po prostu będziesz usuwał, to potem będziesz miał problemy z zapytaniami typu "jaki stan konta miał klient tego dnia?" - do tego już musisz użyć zapytania z magicznym joinem, maxem itd...

Schemat nie jest zły. Interesuje mnie tylko stan konta na dziś o godzinie 00:00. Reszta jest nieważna - dlatego te dane mogę spokojnie usunąć. Historię chcę trzymać tylko i wyłącznie dla siebie. Fakt, że nie spodziewałem się takiej ilości klientów...

Więc źle ładujesz dane, skoro schemat nie jest zły.

Niepotrzebnie mieszasz dane historyczne z aktualnymi, przez to wyciąganie aktualnego stanu konta jest zbyt kosztowne.

Lepszy schemat byłby taki jak pisze Adam. Masz tabelę ze stanem konta i tabelę historyczną. Tabelę historyczną ładujesz zawsze przy imporcie oraz uaktualniasz tablę ze stanem konta zgodnie z importem.

Poza tym w 90% przypadków gdy słyszę o "dużej ilości danych" albo "wolnym zapytaniu" problemem jest schemat albo złe zapytanie, ewentualnie brak indeksów lub zła konfiguracja bazy.

Nie wiem ile masz tych danych, ale takie zapytanie na tabeli nawet niewielkiej może być wolne, bo wyciągasz dużo danych. Wtedy zapytanie jest szybkie, przesyłanie danych z bazy woooolne jak cholera. Hint: nie wyciągaj danych z bazy, zrób całą operację w bazie.

Temat: Problem - usuwanie starych danych

Szymon G.:

Niepotrzebnie mieszasz dane historyczne z aktualnymi, przez to wyciąganie aktualnego stanu konta jest zbyt kosztowne.

Nigdzie nie napisałem, że z tej tabeli wyciągam aktualny stan konta. Stan konta mam w innej tabeli. Ta jest historyczną. I to właśnie tabelę historyczną chcę odchudzić.

Lepszy schemat byłby taki jak pisze Adam. Masz tabelę ze stanem konta i tabelę historyczną. Tabelę historyczną ładujesz zawsze przy imporcie oraz uaktualniasz tablę ze stanem konta zgodnie z importem.

Jw.

Poza tym w 90% przypadków gdy słyszę o "dużej ilości danych" albo "wolnym zapytaniu" problemem jest schemat albo złe zapytanie, ewentualnie brak indeksów lub zła konfiguracja bazy.

Nie wiem ile masz tych danych, ale takie zapytanie na tabeli nawet niewielkiej może być wolne, bo wyciągasz dużo danych. Wtedy zapytanie jest szybkie, przesyłanie danych z bazy woooolne jak cholera. Hint: nie wyciągaj danych z bazy, zrób całą operację w bazie.

Tabela ma ponad 110 milionów rekordów. Ponad 18 GB danych. O tym, że takich danych się nie wyciąga, to ja doskonale wiem.
Zapytanie SELECT Count(*) FROM <tamto zapytanie> po dwóch godzinach wykonywania zostało zresetowane przez serwer (bez zwrócenia wyników).

Temat: Problem - usuwanie starych danych

Marcin Miga:

Szymon G.:

Niepotrzebnie mieszasz dane historyczne z aktualnymi, przez to wyciąganie aktualnego stanu konta jest zbyt kosztowne.

Nigdzie nie napisałem, że z tej tabeli wyciągam aktualny stan konta. Stan konta mam w innej tabeli. Ta jest historyczną. I to właśnie tabelę historyczną chcę odchudzić.

Aha... teraz to wszystko jest jaśniejsze nieco :)

Poza tym w 90% przypadków gdy słyszę o "dużej ilości danych" albo "wolnym zapytaniu" problemem jest schemat albo złe zapytanie, ewentualnie brak indeksów lub zła konfiguracja bazy.

Nie wiem ile masz tych danych, ale takie zapytanie na tabeli nawet niewielkiej może być wolne, bo wyciągasz dużo danych. Wtedy zapytanie jest szybkie, przesyłanie danych z bazy woooolne jak cholera. Hint: nie wyciągaj danych z bazy, zrób całą operację w bazie.

Tabela ma ponad 110 milionów rekordów. Ponad 18 GB danych. O tym, że takich danych się nie wyciąga, to ja doskonale wiem.
Zapytanie SELECT Count(*) FROM <tamto zapytanie> po dwóch godzinach wykonywania zostało zresetowane przez serwer (bez zwrócenia wyników).

Jak to zostało zresetowane przez serwer? Samo tak się zresetowało? Dziwne... poza tym się nie dziwię, bo count(*) wcale nie musi korzystać z indeksów, a nawet jak korzysta to i tak musi przemielić dane.

Tak się zastanawiam czy jest się nad czym zastanawiać... po prostu usuń te dane i już. To jest jednorazowa operacja, co z tego, że nieco trwa? Potrwa i przestanie.

Możesz też zrobić inaczej. Wybierz dane, które chcesz zostawić, wrzuć do innej tabeli. Usuń tę dużą tabelę, zmień nazwę tej nowej i już. Możliwe, że będzie szybciej.

Temat: Problem - usuwanie starych danych

Szymon G.:
Jak to zostało zresetowane przez serwer? Samo tak się zresetowało? Dziwne... poza tym się nie dziwię, bo count(*) wcale nie musi korzystać z indeksów, a nawet jak korzysta to i tak musi przemielić dane.

Nie korzysta z indeksów, robi sobie skana sekwencyjnego, sprawdzając widoczność (MVCC ;] )

@Marcin Miga
Skoro nie potrzebujesz wszystkich danych historycznych, jakie dane potrzebujesz?
10 ostatnich zmiana na danym koncie, czy jakoś inaczej to opisz słownie.

Temat: Problem - usuwanie starych danych

Krzysztof P.:

Szymon G.:
Jak to zostało zresetowane przez serwer? Samo tak się zresetowało? Dziwne... poza tym się nie dziwię, bo count(*) wcale nie musi korzystać z indeksów, a nawet jak korzysta to i tak musi przemielić dane.

Nie korzysta z indeksów, robi sobie skana sekwencyjnego, sprawdzając widoczność (MVCC ;] )

Jest całkiem inaczej. Może korzystać z indeksów.

Może korzystać jeśli tam jest jakiś bardziej zaawansowany warunek w zapytaniu. Potem i tak musi sprawdzić wszystkie wersje wierszy wybranych z indeksu w pliku z danymi, bo indeks przechowuje tylko numer strony z wersjami wierszy, bez numerów wersji.

Przykład:

create table t( i integer primary key, t text);



test=# insert into t(i,t) select generate_series(1,1000000), (random() * 100)::integer::text;

INSERT 0 1000000

Tutaj nie używa indeksów:

test=# explain select count(*) from t;

                          QUERY PLAN                           

---------------------------------------------------------------

 Aggregate  (cost=11228.44..11228.45 rows=1 width=0)

   ->  Seq Scan on t  (cost=0.00..9867.75 rows=544275 width=0)

(2 rows)

Tutaj też nie, bo nie ma odpowiedniego indeksu:

test=# explain select count(*) from t where t = '10';

                          QUERY PLAN                          

--------------------------------------------------------------

 Aggregate  (cost=11235.24..11235.25 rows=1 width=0)

   ->  Seq Scan on t  (cost=0.00..11228.44 rows=2721 width=0)

         Filter: (t = '10'::text)

(3 rows)

A tutaj używa, po czym sprawdza widoczność wierszy. To jest o tyle szybsze, że trzeba wczytać tylko niektóre strony z pliku z danymi zamiast seqscanu po całości

test=# create index tt on t(t);

CREATE INDEX

test=# explain select count(*) from t where t = '10';

                                QUERY PLAN                                 

---------------------------------------------------------------------------

 Aggregate  (cost=4805.49..4805.50 rows=1 width=0)

   ->  Bitmap Heap Scan on t  (cost=95.11..4792.99 rows=5000 width=0)

         Recheck Cond: (t = '10'::text)

         ->  Bitmap Index Scan on tt  (cost=0.00..93.86 rows=5000 width=0)

               Index Cond: (t = '10'::text)

(5 rows)

Temat: Problem - usuwanie starych danych

A co z partycjonowaniem - zdaje się ze jest to idealna sytuacja żeby je wprowadzić - magiczne słówko INHERITS bardzo tu pomoże.
Można by utworzyć kilka tabel z podziałem na miesiące, kwartały albo lata lub odwrotnie - z podziałem wg id klientów.
W przypadku podziału czasowego można będzie dropować całe tabele gdy już będą konkretnie przestarzałe.
W przypadku podziału według id klientów - zyskamy po prostu czas czytania z takich tabel, ale problem usuwania starych danych pozostanie.

Temat: Problem - usuwanie starych danych

Problemem jest (z tego co zdążyłem się zorientować) część zapytania na pole nieindeksowane. W tym przypadku StanKonta.

select m1.* from tabela m1 left join tabela m2 on m1.IDKlienta=m2.IDKlienta and m1.Data=m2.Data+1 where m1.StanKonta!=m2.StanKonta or m2.StanKonta is null

to zapytanie nie korzysta z indeksu.
ale jeśli zrobię:

SELECT * FROM (select m1.*, m2.StanKonta StanKonta2 from tabela m1 left join tabela m2 on m1.IDKlienta=m2.IDKlienta and m1.Data=m2.Data+1) X WHERE StanKonta!=StanKonta2 or StanKonta2 is null

To już korzysta :)
I to mi wystarczy - już wiem, co dalej...

Temat: Problem - usuwanie starych danych

Koledzy się uprali na select count(*) które nie użyje index'u jeżeli jakikolwiek atrybut nie posiada założonego index'u. A'propos ostatniego przykładu... Zliczamy ilość wierszy. Dlaczego zatem planer ma patrzeć na wszystkie atrybuty? Wystarczy że popatrzy na jeden. W tym wypadku najlepiej zrobić count z klucza głównego: select count(i)... Nie będzie sekwencyjnego skanu bo klucz główny z założenia dostaje index.

Pierwszą rzeczą jaką bym zrobił to partycjonowanie tej tabeli na mniejszych x tabel. Nie wiem jak stare tam są dane ale 1 tabela = 1 rok lub 1 tabela = 1 miesiąc spowodowało by ze zapytania wykonują się szybciej i możesz proces usuwania danych rozłożyć w czasie.

Proponuje dla tabeli:

create table klienci (

id varchar(1),

dzien date,

stan int)

z wpisanymi tymi przykładowymi danymi zrobić zapytanie

select distinct on (stan) * from klienci where id = 'A';

To zapytanie zminimalizowane do jednego klienta zwraca te wartości których potrzebujesz (przynajmniej w moim PostgreSQL 9.1.2 tak jest).
Proponuje napisać funkcje z parametrem przyjmującym id klienta i zrobic

select nazwa_funkcji(id) from ( select distinct id from klienci);

w samej funkcji natomiast zrobic

insert into klienci_new select distinct on (stan) * from klienci where id = '$1';

Przepisuje potrzebne mi wiersze dla konkretnego klienta. Po zakończeniu starą tabelę można usunąć.
Pod żadnym pozorem nie korzystaj z identyfikatorów z tej tabeli jeżeli masz słownik klientów (po co skanować tak dużą tabelę nawet z wykorzystaniem index'u jeżeli masz o wiele krótszą).
Co do przenoszenia do tabeli historycznej proponuje napisać jakiś wyzwalacz który zapisze w historii daną tylko i wyłącznie jeżeli nastąpi zmiana stanu na inny. Jest to trochę śliskie ponieważ, jeżeli w obrębie jednego dnia nastąpi kilka zmian stanu i na końcu dnia stan będzie identyczny jak na początku to system tego nie zarejestruje.

Temat: Problem - usuwanie starych danych

Szymon G.:
...

Czyli ostatecznie nie korzysta do zliczenia, może zbyt duży skrót myślowy dałem, bo pominąłem że sobie ogranicza wynik (jeśli może) przed "chodzeniem sobie" po tabeli. Ale ostatecznie to robi, co przy dużym zbiorze wynikowym powoduje masakrę.

Bo w przykładzie dodałeś koszt 93.86 aby zredukować z 11228.44 do 4792.99 koszt przeglądania.

Ale chyba starty tej dygresji.

Temat: Problem - usuwanie starych danych

Użyj funkcji analitycznych lead/lag do znalezienia punktów, w których wartości się zmieniają - będzie znacznie szybciej niż join.