PostgreSQL VPS-is: kui palju mälu ja kettaruumi on vaja

Lähtuge kasulikust mahust

Andmebaasiserveri mälu toimib eelkõige vahemäluna: mälust loetud leht serveeritakse palju kiiremini kui kettalt loetud leht, isegi NVMe puhul. Küsimus ei ole seega ainult «kui palju andmebaas kaalub?», vaid «milline osa sellest loetakse sageli?». 50 GB andmebaas, millest päritakse ainult viimast kaheteistkümmet kuud, töötab võib-olla 8 GB peal.

Olemasolevas andmebaasis annavad kaks päringut kogumahu ja suurimad tabelid koos indeksitega:

SELECT pg_size_pretty(pg_database_size(current_database()));

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

Tulevase andmebaasi puhul hinnake tabelite kaupa: aasta pärast oodatav ridade arv × rea keskmine suurus, seejärel lisage indeksid, mis kaaluvad tavaliselt 30 kuni 100 % andmetest. Näide: 2 miljonit rida kuus, igaüks 500 baiti, teeb aasta pärast 12 GB andmeid ja koos indeksitega 18 kuni 24 GB.

Mälu: lähteseadistused

PostgreSQL hoiab loetud lehti kahel tasandil: oma vahemälus (shared_buffers) ja süsteemi failivahemälus. Selle dokumentatsioon soovitab andmebaasile pühendatud serveris alustada shared_buffers väärtusest 25 % mälust ja märgib, et üle 40 % muutub kasu haruldaseks: ülejäänu tuleb kasuks süsteemi vahemälule ja sortimistele.

Need väärtused eeldavad, et andmebaas on serveris üksi. Kui rakendus töötab samas serveris, lahutage kõigepealt selle mälu (protsessid, vahemälu, kompileerimine) ja rakendage protsente ülejäänule: 8 GB server, mis käitab 3 GB rakendust, seadistatakse nagu 5 GB andmebaas.

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

shared_buffers jõustub alles PostgreSQL-i taaskäivitamisel; teised parameetrid laaditakse uuesti ilma katkestuseta käsuga SELECT pg_reload_conf();.

Et teada, kas mälust piisab, lugege vahemälust lugemise määra. Äritarkvara või saidi puhul ületab see 99 %:

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

Ühendused: igaüks eraldi protsess

PostgreSQL avab iga ühenduse jaoks protsessi ja lubab vaikimisi 100 ühendust (max_connections). Iga protsess hõivab jõudeolekus mõne megabaidi, rohkem siis, kui ta sorteerib või räsib andmeid (work_mem, mõnikord mitu korda ühe päringu jooksul). max_connections tõstmine 500-ni, et jutuka rakendusega sammu pidada, seab serveri esimese tipu ajal mälupuuduse ohtu.

Õige vastus on ühenduste koondaja: PgBouncer transaction-režiimis laseb mõnel kümnel tegelikul ühendusel teenindada sadu rakenduse ühendusi. Raamistikel on ka oma ühenduste kogum (SQLAlchemy, Prisma, HikariCP): seadistage selle suurus andmebaasiserveri vCPU-de arvu, mitte kasutajate arvu järgi.

Ketas: maht, varu ja logi

PostgreSQL-i serveri ketas kannab enamat kui tabeleid:

• Andmed ja indeksid ning nende kasv aasta jooksul.
• Tehingulogi (WAL): vaikimisi 1 GB kahe kontrollpunkti vahel (max_wal_size), rohkem massimpordi ajal ja veelgi rohkem, kui logi arhiveerimine jääb maha.
• Ajutised failid sortimistest, mis ületavad work_mem piiri.
• Ruum ümberkorraldamiseks: VACUUM FULL ja indeksi ülesehitamine kirjutavad täieliku koopia, enne kui vana vabastatakse.
• Kohalikud ekspordid, kui hoiate varukoopiaid serveris enne nende mujale saatmist.

Reegel: maht aasta pärast, pluss vähemalt 30 % varu. See ei ole mugavuse küsimus: kui tehingulogi kandev ketas täitub, peatub PostgreSQL oma andmete kaitseks. Jälgige täituvust ja suurendage ketast enne 80 %.

Jõudluse poolest ootab iga tehingu kinnitamine logi kettale kirjutamist: salvestuse latentsus määrab kirjutamise läbilaskevõime. Just siin teeb NVMe SSD vahet, palju rohkem kui lugemisel, mida teenindab juba mälu.

Protsessor: üks päring, üks tuum

PostgreSQL-i päring täidetakse ühel tuumal, välja arvatud suured lugemised, mida mootor paralleelistab. vCPU-de arv määrab seega samaaegselt töödeldavate raskete päringute arvu: käivitamisel piisab äritarkvarale 2 vCPU-st, 4 kuni 8 sobivad väga koormatud andmebaasidele ja rasketele aruannetele.

Enne vCPU-de lisamist otsige üles päringud, mis neid tarbivad. Laiendus pg_stat_statements järjestab need koguaja järgi:

shared_preload_libraries = 'pg_stat_statements'

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

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

Rakendusega samas serveris või eraldi serveris?

Käivitamisel on andmebaas rakendusega samas serveris õige valik: nende vahel puudub võrgulatentsus, ühendus käib Unixi sokli kaudu, varundada ja jälgida tuleb ainult ühte serverit. Eraldatakse siis, kui need kaks hakkavad ressursside pärast võistlema: kompileerimine või liiklustipp, mis tühjendab andmebaasi vahemälu, andmebaas, mis vajab kogu mälu oma andmete jaoks, või erinevad varundusnõuded.

Turvalisus ja varukoopiad

• Kohalik kuulamine: listen_addresses = 'localhost', seni kuni ühendub ainult samas serveris olev rakendus. Kui ligipääsu vajab mõni teine server, avage port 5432 ainult tema aadressile, nii tulemüüris kui ka failis pg_hba.conf.
• Autentimine: meetod scram-sha-256 failis pg_hba.conf, üks roll rakenduse kohta, ilma superkasutaja õigusteta.
• Loogiline varukoopia iga päev pg_dump-iga, kopeeritud väljaspool serverit.
• Taastamine kindlale ajahetkele: logi pidev arhiveerimine (pgBackRest, WAL-G) võimaldab naasta minutise täpsusega vea eelsesse hetke, samas kui igapäevane eksport viib tagasi eelmisse päeva.
• Testitud taastamine: varukoopia tõestatakse selle taastamisega testandmebaasi, regulaarsete vahedega.

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