PostgreSQL VPS-en: a memória és a lemez méretezése

Kiindulás a hasznos adatmennyiségből

Egy adatbázis-szerver memóriája elsősorban gyorsítótárként szolgál: a memóriából olvasott lap sokkal gyorsabban kiszolgálható, mint a lemezről olvasott, még NVMe esetén is. A kérdés tehát nem csak az, hogy „mekkora az adatbázis?”, hanem az, hogy „mekkora részét olvassák gyakran?”. Egy 50 GB-os adatbázis, amelynek csak az utolsó tizenkét hónapját kérdezik le, talán 8 GB-on dolgozik.

Egy meglévő adatbázison két lekérdezés adja meg a teljes méretet és a legnagyobb táblákat, indexekkel együtt:

SELECT pg_size_pretty(pg_database_size(current_database()));

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

Egy még elkészítendő adatbázisnál becsüljön táblánként: az egy év múlva várható sorok száma × egy sor átlagos mérete, majd adja hozzá az indexeket, amelyek gyakran az adatok 30 és 100%-a közötti helyet foglalnak. Példa: havi 2 millió, egyenként 500 bájtos sor egy év alatt 12 GB adatot jelent, az indexekkel pedig 18 és 24 GB között.

A memória: a kiinduló beállítások

A PostgreSQL két szinten tartja a beolvasott lapokat: a saját gyorsítótárában (shared_buffers) és a rendszer fájlgyorsítótárában. A dokumentációja azt javasolja, hogy egy dedikált szerveren a shared_buffers a memória 25%-áról induljon, és megjegyzi, hogy 40% felett ritkán van nyereség: a maradék a rendszer gyorsítótárának és a rendezéseknek jut.

Ezek az értékek azt feltételezik, hogy az adatbázis egyedül fut a szerveren. Ha mellette az alkalmazás is fut, először vonja le annak memóriáját (folyamatok, gyorsítótár, fordítás), és a százalékokat a maradékra alkalmazza: egy 8 GB-os szerver, amely egy 3 GB-os alkalmazást futtat, úgy hangolandó, mint egy 5 GB-os adatbázis-szerver.

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

A shared_buffers csak a PostgreSQL újraindításakor lép életbe; a többi paraméter megszakítás nélkül újratölthető a SELECT pg_reload_conf(); paranccsal.

Ahhoz, hogy megtudja, elég-e a memória, olvassa le a gyorsítótár-találati arányt. Egy ügyviteli alkalmazásnál vagy egy weboldalnál ez 99% felett van:

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

A kapcsolatok: mindegyik egy folyamat

A PostgreSQL kapcsolatonként egy folyamatot nyit, és alapértelmezés szerint 100 kapcsolatot fogad (max_connections). Minden folyamat nyugalmi állapotban néhány megabájtot foglal, többet, amikor adatokat rendez vagy hashel (work_mem, egyetlen lekérdezésen belül akár többször is). Ha egy bőbeszédű alkalmazás kedvéért 500-ra emeli a max_connections értékét, azzal kiteszi a szervert annak, hogy az első csúcsnál elfogyjon a memóriája.

A jó válasz egy kapcsolatkészlet-kezelő (connection pooler): a PgBouncer transaction módban néhány tucat valós kapcsolaton osztoztatja meg az alkalmazás több száz kapcsolatát. A keretrendszereknek is van saját kapcsolatkészletük (SQLAlchemy, Prisma, HikariCP): a méretét az adatbázis-szerver vCPU-inak számához igazítsa, ne a felhasználók számához.

A lemez: méret, tartalék és napló

Egy PostgreSQL-szerver lemeze többet tárol a tábláknál:

• Az adatok és az indexek, valamint egyéves növekedésük.
• A tranzakciós napló (WAL): alapértelmezés szerint 1 GB két ellenőrzőpont között (max_wal_size), több egy tömeges importálás alatt, és még több, ha a napló archiválása lemarad.
• Az ideiglenes fájlok azokból a rendezésekből, amelyek túllépik a work_mem értékét.
• Egy átszervezés helyigénye: a VACUUM FULL és egy index újraépítése teljes másolatot ír, mielőtt felszabadítaná a régit.
• A helyi exportok, ha a mentéseket a szerveren is tárolja, mielőtt máshová küldené őket.

A szabály: az egy év múlva várható méret, plusz legalább 30% tartalék. Ez nem kényelmi kérdés: amikor a tranzakciós naplót tároló lemez megtelik, a PostgreSQL leáll, hogy megvédje az adatait. Figyelje a foglaltságot, és bővítsen, mielőtt eléri a 80%-ot.

Teljesítmény szempontjából minden tranzakció véglegesítése megvárja, hogy a napló kiíródjon a lemezre: a tároló késleltetése határozza meg az írási teljesítményt. Itt jelent igazi különbséget az NVMe SSD, sokkal inkább, mint az olvasásoknál, amelyeket már a memória kiszolgál.

A processzor: egy lekérdezés, egy mag

Egy PostgreSQL-lekérdezés egyetlen magon fut, kivéve a nagy olvasásokat, amelyeket a motor párhuzamosít. A vCPU-k száma tehát meghatározza, hány nehéz lekérdezés fut egyszerre: induláskor 2 vCPU elég egy ügyviteli alkalmazásnak, a nagyon terhelt adatbázisokhoz és a nehéz jelentésekhez 4 és 8 közötti vCPU illik.

Mielőtt vCPU-t adna hozzá, keresse meg azokat a lekérdezéseket, amelyek fogyasztják őket. A pg_stat_statements bővítmény összesített idő szerint rangsorolja őket:

shared_preload_libraries = 'pg_stat_statements'

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

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

Ugyanaz a szerver, mint az alkalmazásé, vagy külön szerver?

Induláskor az a jó választás, ha az adatbázis az alkalmazással azonos szerveren fut: nincs hálózati késleltetés a kettő között, a kapcsolat Unix-socketen megy, és egyetlen szervert kell menteni és felügyelni. Akkor érdemes szétválasztani őket, amikor versengenek az erőforrásokért: egy fordítás vagy egy forgalmi csúcs kiüríti az adatbázis gyorsítótárát, az adatbázisnak az összes memóriára szüksége van az adataihoz, vagy eltérőek a mentési követelmények.

Biztonság és mentések

• Helyi figyelés: listen_addresses = 'localhost', amíg csak a szerveren futó alkalmazás kapcsolódik. Ha egy másik szervernek is hozzá kell férnie, az 5432-es portot csak annak címe számára nyissa meg, a tűzfalban és a pg_hba.conf fájlban.
• Hitelesítés: scram-sha-256 módszer a pg_hba.conf fájlban, alkalmazásonként egy szerepkör, szuperfelhasználói jogok nélkül.
• Napi logikai mentés pg_dump segítségével, a szerveren kívülre másolva.
• Visszaállítás egy adott időpontra: a napló folyamatos archiválása (pgBackRest, WAL-G) lehetővé teszi, hogy percre pontosan visszatérjen egy hiba előtti állapotra, míg egy napi export az előző napra visz vissza.
• Tesztelt visszaállítás: egy mentést a visszaállításával lehet bizonyítani, egy tesztadatbázisba, rendszeres időközönként.

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