Jak duże jest za duże dla tabeli PostgreSQL?

127

Pracuję nad projektem RoR dla mojej firmy, a nasz zespół programistów rozpoczął już krótką debatę na temat projektu, w szczególności bazy danych.

Mamy model Message, który należy utrwalić. To bardzo, bardzo mały model z tylko trzema kolumnami db innymi niż id, jednak prawdopodobnie będzie ich DUŻO, kiedy przejdziemy do produkcji. Obserwujemy aż 1 000 000 wstawień dziennie. Modele będą zawsze przeszukiwane tylko za pomocą dwóch kluczy obcych, które mogą być indeksowane. Poza tym modele nigdy nie muszą być usuwane, ale nie musimy też ich przechowywać, gdy mają około trzech miesięcy.

Zastanawiamy się więc, czy implementacja tej tabeli w Postgres spowoduje znaczący problem z wydajnością? Czy ktoś ma doświadczenie z bardzo dużymi bazami danych SQL, aby powiedzieć nam, czy będzie to problem? A jeśli tak, jaką alternatywę powinniśmy wybrać?

— Dylan Karr
źródło

z dobrą warstwą pamięci podręcznej i niewielką konfiguracją w PG powinno być dobrze. Powinieneś rozwiązywać problemy z wydajnością w każdym przypadku i unikać wstępnej optymalizacji. To powiedziawszy, partycjonowanie i replikowanie to zawsze świetne opcje, z których możesz skorzystać po napotkaniu wąskich gardeł.

— Sam,

Powiązane pytanie tutaj i tutaj .

— Erwin Brandstetter

Przetwarzamy około 30 milionów wiadomości dziennie w jednej bazie danych PostgreSQL 5+ TB, działa dobrze.

— Frank Heikens,

zobacz także stackoverflow.com/questions/3132444/…

— rogerdpack

FYI, akurat czytałem dzisiaj postgresql.org/about i zauważyłem, że jest tam napisane, że (w zasadzie) liczba wierszy w tabeli jest nieograniczona.

— Al Chou,

Odpowiedzi:

115

Wiersze na tabelę same w sobie nie będą stanowić problemu.

Zatem z grubsza 1 milion wierszy dziennie przez 90 dni to 90 milionów wierszy. Nie widzę powodu, dla którego Postgres nie mógłby sobie z tym poradzić, nie znając wszystkich szczegółów tego, co robisz.

W zależności od dystrybucji danych możesz użyć kombinacji indeksów, indeksów filtrowanych i jakiegoś rodzaju partycjonowania tabel, aby przyspieszyć działanie, gdy zobaczysz, jakie problemy z wydajnością możesz mieć lub nie. Twój problem będzie taki sam na każdym innym RDMS, który znam. Jeśli potrzebujesz tylko 3 miesięcy projektowania danych w procesie wycinania danych, nie potrzebujesz już więcej. W ten sposób uzyskasz spójną ilość danych w tabeli. Twoje szczęście, że wiesz, ile danych będzie istnieć, przetestuj je pod kątem swojej objętości i zobacz, co otrzymasz. Testowanie jednej tabeli z 90 milionami wierszy może być tak proste, jak:

select x,1 as c2,2 as c3
from generate_series(1,90000000) x;

https://wiki.postgresql.org/wiki/FAQ

Limit   Value
Maximum Database Size       Unlimited
Maximum Table Size          32 TB
Maximum Row Size            1.6 TB
Maximum Field Size          1 GB
Maximum Rows per Table      Unlimited
Maximum Columns per Table   250 - 1600 depending on column types
Maximum Indexes per Table   Unlimited

— Kuberchaun
źródło

Zgadzam się, że 90 milionów wierszy nie będzie problemem dla PostgreSQL. Ale może to być problem dla ORM z PostgreSQL. (Właściwie ORM z dowolnymi dbms.)

— Mike Sherrill

@ MikeSherrill'Catcall 'Słuszna uwaga, skupiłem się tylko na pytaniu „Jak duże jest za duże dla tabeli PostgreSQL?”

— Kuberchaun

@yeyo: Ponieważ ORM zwykle używają wielu zapytań, aby uzyskać dane, które mogą zostać zwrócone tylko z jednym lub dwoma. OP używa Ruby on Rails.

— Mike Sherrill „Cat Recall”

To trochę za późno, ale myślę, że w wielu przypadkach (szczególnie w przypadku szyn / rekordów aktywnych) często całkowicie usuwa się ORM z równania i zapisuje nieprzetworzony ciąg sql w celu zapytania o wydajność. Nie pozwól, aby Twój ORM podejmował decyzje dotyczące danych za Ciebie! To akcesorium, a nie niezbędne.

— Stefan Theard

Adres URL „about” cytowany w adresie URL nie pokazuje obecnie tych ograniczeń - czy ktoś wie, dokąd został przeniesiony?

— Shorn

Innym sposobem na znaczne przyspieszenie zapytań w tabeli zawierającej> 100 milionów wierszy jest grupowanie tabeli w indeksie, który jest najczęściej używany w zapytaniach w godzinach poza godzinami pracy. Mamy tabelę z> 218 milionami wierszy i znaleźliśmy 30-krotne ulepszenia.

Ponadto w przypadku bardzo dużej tabeli dobrym pomysłem jest utworzenie indeksu dla kluczy obcych.

— James Doherty
źródło

> poza godzinami pracy grupuj tabelę w indeksie, która jest najczęściej używana w twoich zapytaniach ... czy możesz wyjaśnić, jak to się robi?

— szpieg

Tak, tutaj krok po kroku PRZYKŁAD: 1) Tabela, do której się odwołuję, w tym przykładzie nazywa się inwestycją. 2) Indeks najczęściej używany w zapytaniach to (bankid, record_date). Oto krok po kroku: 1) psql -c "drop index Investment_bankid_rec_dt_idx;" nazwa_db 2) psql -c "utwórz indeks Investment_bankid_rec_dt_idx dla inwestycji (bankid, record_date);" 3) psql -c "inwestycje klastra bankid_rec_dt_idx przy inwestycji;" 4) vacuumdb -d ccbank -z -v -t inwestycja Więc w kroku pierwszym i drugim usuwamy indeks i tworzymy go ponownie.

— James Doherty

Krok 3 tworzymy klaster, to w zasadzie umieszcza tabelę DB w fizycznej kolejności indeksu, więc kiedy postgresql wykonuje zapytanie, buforuje najbardziej prawdopodobne następne wiersze. Krok 4 odkurzamy bazę danych, aby zresetować statystyki narzędzia do planowania zapytań

— James Doherty.