Przechowywanie i sprawdzanie ruchomych danych w PostgreSQL

Mam dużą liczbę danych modelu pogody umieszczanych w bazie danych PostgreSQL. Maszyna ma 8 rdzeni i 16 GB pamięci RAM. Korzystam z PostgreSQL 9.3 z PostGIS 2.1. Każda tabela będzie zawierała różne dane pogodowe (temperatura, punkt rosy, wiatr itp.). Każda tabela będzie miała 6-7 kolumn: szerokość, długość, geometria punktu, wysokość, data i czas, dla których model ma znaczenie, oraz 1-2 wartości danych, które mogą być interesujące. Dane będą przede wszystkim wyszukiwane dla ramki ograniczającej według czasu i wysokości. Będzie około 145 753 370 wierszy na tabelę (dane starsze niż teraz nie są już istotne, zostaną usunięte). Z grubsza szacuję wielkość tabel na około 10 GB bez indeksów. (To 52 bajty danych plus 23 bajty narzutu na wiersz). Dane będą regularnie aktualizowane / wstawiane, gdy dostępne będą nowe dane modelu. Uwaga:

Więc patrzę na te dwa plany:

Po prostu indeksuj i grupuj według (data / godzina, rzędna) z dodatkowym indeksem dla geometrii punktu. Uruchom zwykłe zadanie cron, które usuwa stare wiersze, uruchamia próżnię / analizę i ponownie klastruje.
Podział według daty i godziny, a następnie klaster i indeks według wysokości na tabelę z indeksem geometrii. Uruchom zwykłe zadanie crona, aby dodawać nowe tabele i upuszczać stare tabele.

Dalej,

Wiem więc, że upuszczenie stołu jest znacznie bardziej wydajne oraz usuwanie i odkurzanie. Ale czy w przeciwnym razie widziałbym wzrost wydajności?
Czy partycje są odpowiednie, gdy wszystkie tabele będą równomiernie aktualizowane i wybierane do momentu usunięcia jako nieistotne (dokumentacja wskazuje, że partycje działały najlepiej, gdy wybrano tylko kilka z nich)?

Czy podczas dostarczania danych selekcje będą szybsze niż indeks klastrowany? Czy odpowiedź zmienia się, jeśli jednocześnie składa się wiele wniosków?

Dziękuję Ci. Mam nadzieję, że podałem wszystkie potrzebne dane. Jeśli nie, daj mi znać, a dodam to.

— bshender
źródło

Ojej, w tych wąskich wierszach duże nagłówki wierszy PostgreSQL zaczynają naprawdę boleć. Szkoda, że tak naprawdę niewiele można usunąć; to nie jest tak możemy stracić xminlub xmax, itd. Jest to funkcja, która może uczynić go do 9.4, które prawdopodobnie będą podniecać się, zwane indeksy minimaksowe, że uczyni wszystko tak wiele wygodniejsze.

— Craig Ringer

Czy następująca kombinacja jest powtarzalna: „szerokość, długość, geometria punktu, wysokość”. Jeśli tak, normalizacja go do innej tabeli może zaoszczędzić trochę miejsca.

— AK

Tylko nieznacznie. Geometria PostGIS jest tablicą binarną i nie jest czytelna dla człowieka. Mógłbym wyprowadzić te wartości na wyjściu, ale potem nie mogłem ich skupić. Mógłbym użyć GeoHash do klastra, ale nie jest to już czytelne, niż byłoby w przypadku lat lon. Ale przestrzeń w obu kierunkach nie jest problemem. Oferowali tyle terrabajtów, ile tylko mogę. Problem polega na tym, że nie mogę szybko przesyłać zapytań do terrabytes. Sama baza danych będzie w dużej mierze nietransakcyjna. Tylko dwa skrypty będą miały w ogóle dostęp do zapisu. Cała reszta jest przeznaczona tylko do odczytu.

— bshender

Craig: Wydają się intrygujące. Nie mogę się doczekać eksperymentowania z nimi, kiedy wyjdą. Masz jakieś przemyślenia na temat mojej konfiguracji w 9.3?

— bshender

Czy możesz podać dwie informacje: 1) Co jest dla Ciebie najważniejsze, szybkość wstawiania lub szybkość zapytania? 2) Jakie zapytania są najczęściej?

— Thomas Kejser

Biorąc wszystko pod uwagę, wybrałbym opcję 2. Daty będą równomiernie wybrane, ale zgaduję, że dla danego zapytania będzie dotyczyć tylko jedna lub dwie partycje daty. Szkoda, że nie można skupić się na geolokalizacji i partycjonowaniu na bieżąco, co byłoby idealne. Wysokość i tak ma tendencję do korelacji z geolokalizacją, jeśli obwiednie są wystarczająco małe.

Biorąc pod uwagę dostępne opcje, czystsze operacje danych i unikanie codziennej próżni jest dobrą rzeczą.

Dostarczanie zaznaczeń może być szybsze z opcją 1, ale podejrzewam, że prawdopodobnie będzie to pranie. W przypadku opcji 1 rekordy o tej samej dacie i wysokości są umieszczane blisko siebie w jednym dużym indeksie klastrowym. W opcji 2 rekordy z tą samą datą i rzędną są umieszczane blisko siebie w wielu mniejszych indeksach klastrowych.

— Carlos Bueno
źródło