Mam zestaw danych szeregów czasowych (popraw mnie, jeśli się mylę), który ma wiele powiązanych wartości.

Przykładem może być modelowanie samochodu i śledzenie jego różnych atrybutów podczas podróży. Na przykład:

znacznik czasu | prędkość | przebyty dystans | temperatura | itp

Jaki byłby najlepszy sposób przechowywania tych danych, aby aplikacja internetowa mogła efektywnie wyszukiwać pola w celu znalezienia maksymalnego, minimalnego i wykreślanego każdego zestawu danych w czasie?

Rozpocząłem naiwne podejście do analizowania zrzutu danych i buforowania wyników, aby nigdy nie musiały być przechowywane. Jednak po odrobinie zabawy wydaje się, że to rozwiązanie nie skalowałoby się w dłuższej perspektywie z powodu ograniczeń pamięci i gdyby pamięć podręczna została wyczyszczona, wszystkie dane musiałyby zostać ponownie przeanalizowane i ponownie buforowane.

Ponadto, zakładając, że dane są śledzone co sekundę z rzadką możliwością ponad 10-godzinnych zestawów danych, czy ogólnie zaleca się obcinanie zestawu danych przez próbkowanie co N sekund?

Naprawdę nie ma jednego „najlepszego sposobu” na przechowywanie danych szeregów czasowych, i to szczerze zależy od wielu czynników. Jednak skupię się przede wszystkim na dwóch czynnikach, a mianowicie na:

(1) Jak poważny jest ten projekt, że zasługuje na Twój wysiłek w celu optymalizacji schematu?

(2) Jakie naprawdę będą wzorce dostępu do zapytań ?

Mając na uwadze te pytania, omówmy kilka opcji schematu.

Płaski stół

Opcja użycia płaskiego stołu ma o wiele więcej wspólnego z pytaniem (1) , w którym jeśli nie jest to projekt poważny lub na dużą skalę, łatwiej będzie ci nie myśleć zbyt dużo o schemacie, i wystarczy użyć płaskiego stołu, ponieważ:

CREATE flat_table(
  trip_id integer,
  tstamp timestamptz,
  speed float,
  distance float,
  temperature float,
  ,...);

Nie ma wielu przypadków, w których poleciłbym ten kurs, tylko jeśli jest to mały projekt, który nie gwarantuje dużej ilości czasu.

Wymiary i fakty

Jeśli więc udało Ci się usunąć przeszkodę wynikającą z pytania (1) i chcesz uzyskać schemat wydajności, jest to jedna z pierwszych opcji do rozważenia. Obejmuje ona podstawową normalizację, ale wyodrębnianie wielkości „wymiarowych” ze zmierzonych wielkości „faktycznych”.

Zasadniczo będziesz chciał, aby stolik zapisywał informacje o podróżach,

CREATE trips(
  trip_id integer,
  other_info text);

oraz tabelę do rejestrowania znaczników czasu,

CREATE tstamps(
  tstamp_id integer,
  tstamp timestamptz);

i na koniec wszystkie zmierzone fakty, z kluczami obcymi do tabel wymiarów (tj. meas_facts(trip_id)referencje trips(trip_id)i meas_facts(tstamp_id)referencje tstamps(tstamp_id))

CREATE meas_facts(
  trip_id integer,
  tstamp_id integer,
  speed float,
  distance float,
  temperature float,
  ,...);

To może nie wydawać się początkowo pomocne, ale jeśli masz na przykład tysiące równoczesnych podróży, wtedy wszyscy mogą wykonywać pomiary raz na sekundę, na sekundę. W takim przypadku trzeba będzie ponownie rejestrować znacznik czasu za każdym razem, zamiast tylko jednego wpisu w tstampstabeli.

Przypadek użycia: ten przypadek będzie dobry, jeśli istnieje wiele równoczesnych podróży, dla których rejestrowane są dane, i nie przeszkadza ci dostęp do wszystkich rodzajów pomiarów jednocześnie.

Ponieważ Postgres czyta wiersze, w dowolnym momencie, na przykład, speedpomiary w danym przedziale czasowym, musisz odczytać cały wiersz z meas_factstabeli, co zdecydowanie spowolni zapytanie, chociaż jeśli zestaw danych, z którym pracujesz, to nie jest zbyt duży, wtedy nawet nie zauważysz różnicy.

Podział mierzonych faktów

Aby jeszcze bardziej rozszerzyć ostatnią sekcję, możesz rozdzielić swoje pomiary na osobne tabele, gdzie na przykład pokażę tabele prędkości i odległości:

CREATE speed_facts(
  trip_id integer,
  tstamp_id integer,
  speed float);

i

CREATE distance_facts(
  trip_id integer,
  tstamp_id integer,
  distance float);

Oczywiście możesz zobaczyć, jak można to rozszerzyć na inne pomiary.

Przypadek użycia: nie da to ogromnego przyspieszenia zapytania, być może tylko liniowego wzrostu prędkości, gdy pytasz o jeden typ pomiaru. Dzieje się tak dlatego, że jeśli chcesz sprawdzić informacje o prędkości, musisz tylko odczytać wiersze z speed_factstabeli, a nie wszystkie dodatkowe niepotrzebne informacje, które byłyby obecne w rzędzie meas_factstabeli.

Musisz więc odczytywać ogromne ilości danych tylko o jednym rodzaju pomiaru, możesz uzyskać pewne korzyści. Przy proponowanym przypadku 10 godzin danych w jednosekundowych odstępach odczytujesz tylko 36 000 wierszy, więc tak naprawdę nigdy nie znajdziesz znaczącej korzyści. Jeśli jednak chcesz przeglądać dane pomiaru prędkości dla 5000 przejazdów, które trwały około 10 godzin, teraz możesz odczytać 180 milionów wierszy. Liniowy wzrost prędkości dla takiego zapytania może przynieść pewne korzyści, o ile wystarczy dostęp do jednego lub dwóch rodzajów pomiarów na raz.

Tablice / HStore / & TOAST

Prawdopodobnie nie musisz się martwić o tę część, ale znam przypadki, w których ma to znaczenie. Jeśli potrzebujesz dostępu do OGROMNYCH ilości danych szeregów czasowych i wiesz, że musisz uzyskać dostęp do wszystkich w jednym ogromnym bloku, możesz użyć struktury, która wykorzysta tabele TOAST , które zasadniczo przechowują twoje dane w większych, skompresowanych segmenty. Prowadzi to do szybszego dostępu do danych, o ile Twoim celem jest dostęp do wszystkich danych.

Jednym z przykładów może być implementacja

CREATE uber_table(
  trip_id integer,
  tstart timestamptz,
  speed float[],
  distance float[],
  temperature float[],
  ,...);

W tej tabeli tstartprzechowywany byłby znacznik czasu dla pierwszego wpisu w tablicy, a każdy kolejny wpis byłby wartością odczytu dla następnej sekundy. Wymaga to zarządzania odpowiednim znacznikiem czasu dla każdej wartości tablicy w oprogramowaniu aplikacyjnym.

Inną możliwością jest

CREATE uber_table(
  trip_id integer,
  speed hstore,
  distance hstore,
  temperature hstore,
  ,...);

gdzie dodajesz wartości pomiaru jako pary (klucz, wartość) (znacznik czasu, pomiar).

Przypadek użycia: Jest to implementacja, którą prawdopodobnie lepiej pozostawić osobie, która jest wygodniejsza w korzystaniu z PostgreSQL i tylko wtedy, gdy masz pewność, że wzorce dostępu muszą być wzorcami dostępu zbiorczego.

Wnioski

Wow, to trwało dłużej niż się spodziewałem, przepraszam. :)

Zasadniczo istnieje wiele opcji, ale prawdopodobnie uzyskasz największy huk za swoje pieniądze, używając drugiej lub trzeciej, ponieważ pasują one do bardziej ogólnego przypadku.

PS: Twoje początkowe pytanie sugerowało, że będziesz masowo ładować swoje dane po ich zebraniu. Jeśli przesyłasz strumieniowo dane do instancji PostgreSQL, będziesz musiał wykonać dalszą pracę, aby obsłużyć zarówno pobieranie danych, jak i obciążenie zapytaniami, ale zostawimy to na inny czas. ;)

Jego rok 2019 i to pytanie zasługuje na zaktualizowaną odpowiedź.

• To, czy takie podejście jest najlepsze, czy nie, jest czymś, co pozostawiam wam do przetestowania i przetestowania, ale oto podejście.
• Użyj rozszerzenia bazy danych o nazwie timescaledb
• Jest to rozszerzenie zainstalowane na standardowym PostgreSQL i obsługuje kilka napotkanych problemów podczas rozsądnego przechowywania szeregów czasowych

Biorąc przykład, najpierw stwórz prostą tabelę w PostgreSQL

Krok 1

CREATE TABLE IF NOT EXISTS trip (
    ts TIMESTAMPTZ NOT NULL PRIMARY KEY,
    speed REAL NOT NULL,
    distance REAL NOT NULL,
    temperature REAL NOT NULL
) 

Krok 2

• Zamień to w coś, co nazywa się hypertable w świecie skalowanym czasemb .
• Krótko mówiąc, jest to duży stół, który jest stale dzielony na mniejsze tabele o określonym przedziale czasu, powiedzmy dzień, w którym każdy mini stół jest określany jako porcja

• Ten mini-stół nie jest oczywisty podczas uruchamiania zapytań, chociaż można go włączyć lub wyłączyć w zapytaniach

  WYBIERZ create_hypertable („trip”, „ts”, chunk_time_interval => interwał „1 godzina”, if_not_exists => PRAWDA);

• To, co zrobiliśmy powyżej, to zabranie naszego stołu potrójnego, dzielenie go na mini porcje co godzinę na podstawie kolumny „ts”. Jeśli dodasz znacznik czasu od 10:00 do 10:59, zostaną one dodane do 1 porcji, ale godzina 11:00 zostanie wstawiona do nowej porcji i będzie to trwało nieskończenie długo.

• Jeśli nie chcesz przechowywać danych w nieskończoność, możesz również użyć fragmentów DROP starszych niż, powiedzmy, 3 miesiące korzystania

  SELECT drop_chunks (interwał „3 miesiące”, „trip”);

• Możesz również uzyskać listę wszystkich fragmentów utworzonych do daty za pomocą zapytania takiego jak

  SELECT chunk_table, table_bytes, index_bytes, total_bytes FROM chunk_relation_size ('trip');

• Spowoduje to wyświetlenie listy wszystkich mini-tabel utworzonych do daty i możesz uruchomić zapytanie na ostatnim mini-stole, jeśli chcesz z tej listy

• Możesz zoptymalizować swoje zapytania, aby uwzględnić, wykluczyć fragmenty lub operować tylko na ostatnich N fragmentach i tak dalej