PostgreSQL VPS serveryje: kaip parinkti atmintį ir diską

Pradėti nuo naudingos apimties

Duomenų bazės serverio atmintis pirmiausia tarnauja kaip podėlis: iš atminties perskaitytas puslapis pateikiamas daug greičiau nei perskaitytas iš disko, net ir NVMe. Todėl klausimas yra ne tik „kiek sveria duomenų bazė?“, bet ir „kokia jos dalis skaitoma dažnai?“. 50 GB duomenų bazė, kurioje naudojami tik paskutinių dvylikos mėnesių duomenys, galbūt dirba su 8 GB.

Esamoje duomenų bazėje dvi užklausos parodo visą apimtį ir didžiausias lenteles, įskaitant indeksus:

SELECT pg_size_pretty(pg_database_size(current_database()));

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

Būsimai duomenų bazei vertinkite lentelę po lentelės: laukiamas eilučių skaičius po metų × vidutinis eilutės dydis, tada pridėkite indeksus, kurie paprastai sudaro nuo 30 iki 100 % duomenų. Pavyzdys: 2 milijonai eilučių per mėnesį, po 500 baitų kiekviena, per metus sudaro 12 GB duomenų, o su indeksais nuo 18 iki 24 GB.

Atmintis: pradiniai nustatymai

PostgreSQL perskaitytus puslapius laiko dviem lygiais: savo podėlyje (shared_buffers) ir sistemos failų podėlyje. Jo dokumentacija rekomenduoja serveryje, skirtame tik duomenų bazei, shared_buffers pradėti nuo 25 % atminties ir pastebi, kad virš 40 % nauda tampa reta: likusi dalis tenka sistemos podėliui ir rikiavimui.

Šios reikšmės numato, kad serveryje veikia tik duomenų bazė. Jei šalia veikia programa, pirmiausia atimkite jos atmintį (procesus, podėlį, kompiliavimą) ir procentus taikykite likučiui: 8 GB serveris, kuriame veikia 3 GB programa, nustatomas kaip 5 GB duomenų bazė.

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

shared_buffers įsigalioja tik perkrovus PostgreSQL; kiti parametrai įkeliami iš naujo be pertrūkio, komanda SELECT pg_reload_conf();.

Norėdami sužinoti, ar atminties pakanka, žiūrėkite skaitymų iš podėlio dalį. Verslo valdymo programoje ar svetainėje ji viršija 99 %:

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

Jungtys: kiekvienai po procesą

PostgreSQL kiekvienai jungčiai atidaro procesą ir pagal numatytuosius nustatymus priima 100 jungčių (max_connections). Kiekvienas procesas ramybės būsenoje užima kelis megabaitus, daugiau, kai rikiuoja ar maišo duomenis (work_mem, kartais kelis kartus toje pačioje užklausoje). Padidinti max_connections iki 500, kad atlaikytumėte daug jungčių atidarančią programą, reiškia rizikuoti, kad serveriui pritrūks atminties per pirmąjį piką.

Teisingas atsakymas yra jungčių telkimo įrankis: PgBouncer transaction režimu leidžia šimtams programos jungčių dalytis keliomis dešimtimis tikrų jungčių. Karkasai taip pat turi savo telkinį (SQLAlchemy, Prisma, HikariCP): jo dydį derinkite prie duomenų bazės serverio vCPU skaičiaus, o ne prie vartotojų skaičiaus.

Diskas: apimtis, atsarga ir žurnalas

PostgreSQL serverio diske saugoma daugiau nei lentelės:

• Duomenys ir indeksai bei jų augimas per metus.
• Transakcijų žurnalas (WAL): pagal numatytuosius nustatymus 1 GB tarp dviejų kontrolinių taškų (max_wal_size), daugiau masinio importo metu ir dar daugiau, jei žurnalo archyvavimas vėluoja.
• Laikinieji failai, kuriuos kuria work_mem viršijančios rikiavimo operacijos.
• Vieta pertvarkymui: VACUUM FULL ir indekso perkūrimas įrašo visą kopiją prieš atlaisvindami senąją.
• Vietiniai eksportai, jei atsargines kopijas laikote serveryje prieš išsiųsdami jas kitur.

Taisyklė: apimtis po metų, plius mažiausiai 30 % atsarga. Tai ne patogumo klausimas: kai prisipildo diskas, kuriame yra transakcijų žurnalas, PostgreSQL sustoja, kad apsaugotų savo duomenis. Stebėkite užimtumą ir didinkite diską, kol jis nepasiekė 80 %.

Našumo požiūriu kiekvienas transakcijos patvirtinimas laukia, kol žurnalas bus įrašytas į diską: saugyklos delsa lemia rašymo spartą. Būtent čia NVMe SSD daro skirtumą, daug labiau nei skaitymui, kurį jau aptarnauja atmintis.

Procesorius: viena užklausa, vienas branduolys

PostgreSQL užklausa vykdoma viename branduolyje, išskyrus didelius skaitymus, kuriuos variklis lygiagretina. Todėl vCPU skaičius lemia, kiek sunkių užklausų apdorojama vienu metu: 2 vCPU pakanka verslo valdymo programai paleidimo metu, nuo 4 iki 8 tinka labai apkrautoms duomenų bazėms ir sunkioms ataskaitoms.

Prieš pridėdami vCPU, suraskite užklausas, kurios juos naudoja. Plėtinys pg_stat_statements jas surikiuoja pagal bendrą laiką:

shared_preload_libraries = 'pg_stat_statements'

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

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

Tas pats serveris kaip programa ar atskiras serveris?

Paleidimo metu duomenų bazė tame pačiame serveryje kaip programa yra teisingas pasirinkimas: jokios tinklo delsos tarp jų, jungtis per Unix lizdą (socket), vienas serveris, kurį reikia kopijuoti ir stebėti. Atskiriama tada, kai jos konkuruoja dėl išteklių: kompiliavimas ar srauto pikas, išvalantis duomenų bazės podėlį, duomenų bazė, kuriai duomenims reikia visos atminties, ar skirtingi atsarginių kopijų reikalavimai.

Saugumas ir atsarginės kopijos

• Vietinis klausymasis: listen_addresses = 'localhost', kol jungiasi tik serverio programa. Jei prieiga reikalinga kitam serveriui, atverkite 5432 prievadą tik jo adresui, ugniasienėje ir pg_hba.conf faile.
• Autentifikavimas: scram-sha-256 metodas pg_hba.conf faile, po vieną vaidmenį kiekvienai programai, be supervartotojo teisių.
• Loginė atsarginė kopija kasdien su pg_dump, nukopijuota už serverio ribų.
• Atkūrimas į konkretų momentą: nuolatinis žurnalo archyvavimas (pgBackRest, WAL-G) leidžia grįžti į būseną minutės tikslumu prieš klaidą, o kasdienis eksportas grąžina į vakarykštę būseną.
• Išbandytas atkūrimas: atsarginė kopija įrodoma ją atkuriant testinėje duomenų bazėje, reguliariais intervalais.

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