PostgreSQL na VPS: jak dobrać pamięć i dysk

Punkt wyjścia: ilość używanych danych

Pamięć serwera bazy danych służy przede wszystkim jako pamięć podręczna: strona odczytana z pamięci jest serwowana znacznie szybciej niż strona odczytana z dysku, nawet NVMe. Pytanie brzmi więc nie tylko „ile waży baza?”, ale także „jaka jej część jest często odczytywana?”. Baza o rozmiarze 50 GB, w której odpytywane jest tylko ostatnie dwanaście miesięcy, pracuje być może na 8 GB.

Na istniejącej bazie dwa zapytania podają rozmiar całkowity i największe tabele, łącznie z indeksami:

SELECT pg_size_pretty(pg_database_size(current_database()));

SELECT relname AS nom,
       pg_size_pretty(pg_total_relation_size(relid)) AS taille_avec_index
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;

Dla bazy, która dopiero powstanie, proszę szacować tabela po tabeli: liczba wierszy oczekiwana za rok × średni rozmiar wiersza, a następnie dodać indeksy, które ważą zwykle od 30 do 100 % danych. Przykład: 2 miliony wierszy miesięcznie, po 500 bajtów każdy, dają za rok 12 GB danych, a z indeksami od 18 do 24 GB.

Pamięć: ustawienia początkowe

PostgreSQL przechowuje odczytane strony na dwóch poziomach: we własnej pamięci podręcznej (shared_buffers) i w pamięci podręcznej plików systemu. Jego dokumentacja zaleca, aby na serwerze przeznaczonym wyłącznie dla bazy zacząć od 25 % pamięci dla shared_buffers, i zauważa, że powyżej 40 % zysk pojawia się rzadko: reszta służy pamięci podręcznej systemu i sortowaniu.

Te wartości zakładają, że baza działa na serwerze sama. Jeśli obok działa aplikacja, proszę najpierw odjąć jej pamięć (procesy, pamięć podręczna, kompilacja) i zastosować procenty do reszty: serwer 8 GB, na którym działa aplikacja zajmująca 3 GB, konfiguruje się jak bazę dysponującą 5 GB.

shared_buffers = 2GB
effective_cache_size = 6GB
work_mem = 16MB
maintenance_work_mem = 512MB
random_page_cost = 1.1

shared_buffers zaczyna obowiązywać dopiero po restarcie PostgreSQL; pozostałe parametry przeładowuje się bez przerwy w działaniu, przez SELECT pg_reload_conf();.

Aby sprawdzić, czy pamięci wystarcza, proszę odczytać współczynnik trafień w pamięci podręcznej. W aplikacji biznesowej lub na stronie internetowej przekracza on 99 %:

SELECT round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS taux_cache
FROM pg_stat_database
WHERE datname = current_database();

Połączenia: każde to osobny proces

PostgreSQL otwiera jeden proces na połączenie i domyślnie przyjmuje ich 100 (max_connections). Każdy proces zajmuje kilka megabajtów w spoczynku, więcej, gdy sortuje lub haszuje dane (work_mem, czasem kilka razy w jednym zapytaniu). Podniesienie max_connections do 500, aby nadążyć za aplikacją, która otwiera wiele połączeń, naraża serwer na brak pamięci przy pierwszym szczycie.

Właściwą odpowiedzią jest pula połączeń: PgBouncer w trybie transaction pozwala setkom połączeń aplikacji współdzielić kilkadziesiąt rzeczywistych połączeń. Frameworki mają też własną pulę (SQLAlchemy, Prisma, HikariCP): jej rozmiar proszę dopasować do liczby vCPU serwera bazy, a nie do liczby użytkowników.

Dysk: rozmiar, zapas i dziennik

Dysk serwera PostgreSQL przechowuje więcej niż tabele:

• Dane i indeksy oraz ich przyrost w ciągu roku.
• Dziennik transakcji (WAL): domyślnie 1 GB między dwoma punktami kontrolnymi (max_wal_size), więcej podczas masowego importu, a jeszcze więcej, jeśli archiwizacja dziennika się opóźnia.
• Pliki tymczasowe sortowań, które przekraczają work_mem.
• Miejsce na reorganizację: VACUUM FULL i przebudowa indeksu zapisują pełną kopię przed zwolnieniem starej.
• Lokalne eksporty, jeśli przechowują Państwo kopie zapasowe na serwerze przed wysłaniem ich gdzie indziej.

Zasada: rozmiar za rok plus co najmniej 30 % zapasu. To nie kwestia wygody: gdy dysk z dziennikiem transakcji się zapełni, PostgreSQL zatrzymuje się, aby chronić swoje dane. Proszę obserwować zajętość i powiększać dysk przed osiągnięciem 80 %.

Jeśli chodzi o wydajność, każde zatwierdzenie transakcji czeka na zapis dziennika na dysk: opóźnienie pamięci masowej wyznacza przepustowość zapisu. Tu SSD NVMe robi różnicę, znacznie większą niż przy odczytach, które obsługuje już pamięć.

Procesor: jedno zapytanie, jeden rdzeń

Zapytanie PostgreSQL wykonuje się na jednym rdzeniu, z wyjątkiem dużych odczytów, które silnik zrównolegla. Liczba vCPU wyznacza więc liczbę ciężkich zapytań przetwarzanych jednocześnie: 2 vCPU wystarczają aplikacji biznesowej na start, od 4 do 8 sprawdza się przy mocno obciążonych bazach i ciężkich raportach.

Zanim dodadzą Państwo vCPU, proszę znaleźć zapytania, które je zużywają. Rozszerzenie pg_stat_statements szereguje je według łącznego czasu:

shared_preload_libraries = 'pg_stat_statements'

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT calls, round(total_exec_time) AS ms_total,
       round(mean_exec_time::numeric, 1) AS ms_moyen, query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Ten sam serwer co aplikacja czy osobny?

Na start baza na tym samym serwerze co aplikacja to dobry wybór: brak opóźnień sieciowych między nimi, połączenie przez gniazdo Unix, jeden serwer do archiwizacji i nadzoru. Rozdziela się je, gdy rywalizują o zasoby: kompilacja lub szczyt ruchu, który opróżnia pamięć podręczną bazy, baza, która potrzebuje całej pamięci na swoje dane, albo różne wymagania dotyczące kopii zapasowych.

Bezpieczeństwo i kopie zapasowe

• Nasłuch lokalny: listen_addresses = 'localhost', dopóki łączy się tylko aplikacja z tego serwera. Jeśli dostęp potrzebny jest innemu serwerowi, proszę otworzyć port 5432 wyłącznie dla jego adresu, w zaporze sieciowej i w pg_hba.conf.
• Uwierzytelnianie: metoda scram-sha-256 w pg_hba.conf, jedna rola na aplikację, bez uprawnień superużytkownika.
• Codzienna kopia logiczna przez pg_dump, kopiowana poza serwer.
• Przywracanie do określonego momentu: ciągła archiwizacja dziennika (pgBackRest, WAL-G) pozwala wrócić do stanu sprzed błędu z dokładnością do minuty, podczas gdy codzienny eksport przywraca stan z poprzedniego dnia.
• Przetestowane przywracanie: kopię zapasową potwierdza się, przywracając ją w regularnych odstępach czasu do testowej bazy.

pg_dump -Fc -d moja_baza -f moja_baza-$(date +%F).dump
createdb moja_baza_test
pg_restore -d moja_baza_test moja_baza-2026-10-29.dump