PostgreSQL VPS-palvelimella: muistin ja levyn mitoitus

Lähtökohtana käytössä oleva data

Tietokantapalvelimen muisti toimii ennen kaikkea välimuistina: muistista luettu sivu palvellaan paljon nopeammin kuin levyltä luettu, NVMe-levyltäkin. Kysymys ei siis ole vain ”paljonko tietokanta vie tilaa?”, vaan ”mikä osa siitä luetaan usein?”. 50 GB:n tietokanta, josta luetaan vain viimeiset kaksitoista kuukautta, työskentelee ehkä 8 gigatavun varassa.

Olemassa olevasta tietokannasta kaksi kyselyä kertoo kokonaiskoon ja suurimmat taulut indekseineen:

SELECT pg_size_pretty(pg_database_size(current_database()));

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

Tulevan tietokannan koko arvioidaan taulu kerrallaan: vuoden päästä odotettu rivimäärä × rivin keskikoko, ja siihen lisätään indeksit, jotka ovat yleisesti 30 ja 100 %:n välillä datan koosta. Esimerkki: 2 miljoonaa riviä kuukaudessa, kukin 500 tavua, tekee vuoden päästä 12 GB dataa ja indekseineen 18 ja 24 GB:n välillä.

Muisti: lähtöasetukset

PostgreSQL pitää luetut sivut kahdella tasolla: omassa välimuistissaan (shared_buffers) ja järjestelmän tiedostovälimuistissa. Sen dokumentaatio suosittelee lähtökohdaksi 25 % muistista shared_buffers-asetukselle tietokannalle omistetulla palvelimella ja toteaa, että yli 40 %:n osuudesta on harvoin lisähyötyä: loput muistista hyödyttää järjestelmän välimuistia ja lajitteluja.

Nämä arvot olettavat, että tietokanta on palvelimella yksin. Jos sovellus toimii samalla palvelimella, vähentäkää ensin sen muisti (prosessit, välimuisti, käännös) ja soveltakaa prosenttiosuuksia jäljelle jäävään: 8 GB:n palvelin, joka ajaa 3 GB:n sovellusta, säädetään kuin 5 GB:n tietokantapalvelin.

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

shared_buffers tulee voimaan vasta, kun PostgreSQL käynnistetään uudelleen; muut parametrit ladataan uudelleen ilman katkoa komennolla SELECT pg_reload_conf();.

Muistin riittävyyden kertoo välimuistista luettujen sivujen osuus. Liiketoimintasovelluksessa tai sivustossa se on yli 99 %:

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

Yhteydet: jokaiselle oma prosessi

PostgreSQL avaa yhden prosessin yhteyttä kohden ja hyväksyy oletuksena 100 yhteyttä (max_connections). Jokainen prosessi vie levossa muutaman megatavun, enemmän lajitellessaan tai hajauttaessaan dataa (work_mem, joskus useita kertoja saman kyselyn aikana). Jos max_connections nostetaan 500:aan puheliaan sovelluksen tarpeisiin, palvelimen muisti voi loppua ensimmäisessä piikissä.

Oikea ratkaisu on yhteyspooli: transaction-tilassa toimiva PgBouncer jakaa muutaman kymmenen todellisen yhteyden sovelluksen satojen yhteyksien kesken. Sovelluskehyksillä on myös oma poolinsa (SQLAlchemy, Prisma, HikariCP): säätäkää sen koko tietokantapalvelimen vCPU:iden määrän eikä käyttäjien määrän mukaan.

Levy: koko, marginaali ja transaktioloki

PostgreSQL-palvelimen levyllä on muutakin kuin taulut:

• Data ja indeksit ja niiden kasvu vuoden aikana.
• Transaktioloki (WAL): oletuksena 1 GB kahden tarkistuspisteen välillä (max_wal_size), enemmän massatuonnin aikana ja vielä enemmän, jos lokin arkistointi jää jälkeen.
• Väliaikaistiedostot lajitteluista, jotka ylittävät work_mem-arvon.
• Uudelleenjärjestelyn vaatima tila: VACUUM FULL ja indeksin uudelleenrakennus kirjoittavat täydellisen kopion ennen kuin vanha vapautetaan.
• Paikalliset viennit, jos säilytätte varmuuskopioita palvelimella ennen kuin lähetätte ne muualle.

Sääntö: koko vuoden päästä ja vähintään 30 %:n marginaali. Kyse on toimintavarmuudesta: kun transaktiolokia säilyttävä levy täyttyy, PostgreSQL pysähtyy suojellakseen dataansa. Seuratkaa käyttöastetta ja laajentakaa ennen kuin se ylittää 80 %.

Suorituskyvyn kannalta jokainen transaktion vahvistus odottaa lokin kirjoittamista levylle: tallennustilan viive määrää kirjoitusten läpäisyn. Juuri tässä NVMe-SSD tekee eron, paljon enemmän kuin luvuissa, jotka muisti jo palvelee.

Suoritin: yksi kysely, yksi ydin

PostgreSQL-kysely suoritetaan yhdellä ytimellä lukuun ottamatta suuria lukuja, jotka moottori rinnakkaistaa. vCPU:iden määrä ratkaisee siis, montako raskasta kyselyä käsitellään yhtä aikaa: 2 vCPU:ta riittää liiketoimintasovellukselle julkaisuvaiheessa, ja kovassa käytössä oleville tietokannoille ja raskaille raporteille sopii 4 tai 8 vCPU:ta.

Ennen kuin lisäätte vCPU:ita, etsikää kyselyt, jotka niitä kuluttavat. Laajennus pg_stat_statements järjestää ne kokonaisajan mukaan:

shared_preload_libraries = 'pg_stat_statements'

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

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

Sama palvelin kuin sovelluksella vai erillinen palvelin?

Julkaisuvaiheessa tietokanta samalla palvelimella sovelluksen kanssa on oikea valinta: niiden välillä ei ole verkkoviivettä, yhteys kulkee Unix-socketin kautta, ja varmuuskopioitavana ja valvottavana on yksi palvelin. Ne erotetaan, kun ne kilpailevat resursseista: käännös tai liikennepiikki tyhjentää tietokannan välimuistin, tietokanta tarvitsee koko muistin datalleen tai varmuuskopiointivaatimukset eroavat.

Tietoturva ja varmuuskopiot

• Paikallinen kuuntelu: listen_addresses = 'localhost' niin kauan kuin vain palvelimen oma sovellus ottaa yhteyttä. Jos toisen palvelimen on päästävä tietokantaan, avatkaa portti 5432 vain sen osoitteelle, palomuurissa ja pg_hba.conf-tiedostossa.
• Tunnistautuminen: menetelmä scram-sha-256 pg_hba.conf-tiedostossa, oma rooli jokaiselle sovellukselle, ilman pääkäyttäjän oikeuksia.
• Looginen varmuuskopio päivittäin pg_dump-komennolla, kopioituna palvelimen ulkopuolelle.
• Palautus tiettyyn hetkeen: lokin jatkuva arkistointi (pgBackRest, WAL-G) mahdollistaa paluun minuutilleen virhettä edeltävään hetkeen, kun päivittäinen vienti palauttaa edellisen päivän tilaan.
• Testattu palautus: varmuuskopio todistetaan palauttamalla se testitietokantaan säännöllisin väliajoin.

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