PostgreSQL na VPS: kako dimenzionirati pomnilnik in disk

Izhodišče je uporabni obseg

Pomnilnik strežnika podatkovne zbirke služi najprej kot predpomnilnik: stran, prebrana iz pomnilnika, se postreže veliko hitreje kot stran, prebrana z diska, tudi NVMe. Vprašanje torej ni le „koliko tehta zbirka?“, temveč „kolikšen del se bere pogosto?“. Zbirka s 50 GB, pri kateri se bere le zadnjih dvanajst mesecev, morda dela z 8 GB.

Na obstoječi zbirki dve poizvedbi dasta skupni obseg in največje tabele, skupaj z indeksi:

SELECT pg_size_pretty(pg_database_size(current_database()));

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

Za prihodnjo zbirko ocenite tabelo za tabelo: pričakovano število vrstic čez eno leto × povprečna velikost vrstice, nato dodajte indekse, ki pogosto tehtajo od 30 do 100 % podatkov. Primer: 2 milijona vrstic na mesec, po 500 bajtov, pomeni 12 GB podatkov čez eno leto in od 18 do 24 GB z indeksi.

Pomnilnik: začetne nastavitve

PostgreSQL prebrane strani hrani na dveh ravneh: v lastnem predpomnilniku (shared_buffers) in v predpomnilniku datotek sistema. Njegova dokumentacija za shared_buffers na namenskem strežniku priporoča izhodišče 25 % pomnilnika in ugotavlja, da nad 40 % izboljšave postanejo redke: preostanek koristi predpomnilniku sistema in razvrščanju.

Te vrednosti predpostavljajo, da je zbirka na strežniku sama. Če poleg nje teče aplikacija, najprej odštejte njen pomnilnik (procesi, predpomnilnik, prevajanje) in odstotke uporabite na preostanku: strežnik z 8 GB, ki poganja aplikacijo s 3 GB, se nastavi kot zbirka s 5 GB.

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

shared_buffers začne veljati šele ob ponovnem zagonu PostgreSQL; drugi parametri se znova naložijo brez prekinitve, z SELECT pg_reload_conf();.

Ali pomnilnik zadošča, vam pove delež branj iz predpomnilnika. Pri poslovni aplikaciji ali spletni strani presega 99 %:

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

Povezave: vsaka je proces

PostgreSQL odpre po en proces na povezavo in jih privzeto sprejme 100 (max_connections). Vsak proces v mirovanju zasede nekaj megabajtov, več, ko razvršča ali zgoščuje podatke (work_mem, včasih večkrat v isti poizvedbi). Kdor max_connections dvigne na 500, da bi sledil zgovorni aplikaciji, strežnik izpostavi pomanjkanju pomnilnika ob prvi konici.

Pravi odgovor je združevalnik povezav: PgBouncer v načinu transaction omogoči, da si stotine povezav aplikacije delijo nekaj deset resničnih povezav. Ogrodja imajo tudi lasten bazen povezav (SQLAlchemy, Prisma, HikariCP): njegovo velikost nastavite glede na število vCPU strežnika podatkovne zbirke, ne glede na število uporabnikov.

Disk: obseg, rezerva in dnevnik

Disk strežnika PostgreSQL nosi več kot tabele:

• Podatke in indekse ter njihovo rast v enem letu.
• Dnevnik transakcij (WAL): privzeto 1 GB med dvema kontrolnima točkama (max_wal_size), več med obsežnim uvozom in še več, če arhiviranje dnevnika zaostaja.
• Začasne datoteke razvrščanj, ki presežejo work_mem.
• Prostor za reorganizacijo: VACUUM FULL in ponovna izgradnja indeksa zapišeta celotno kopijo, preden sprostita staro.
• Lokalne izvoze, če varnostne kopije hranite na strežniku, preden jih pošljete drugam.

Pravilo: obseg čez eno leto, plus vsaj 30 % rezerve. To ni udobje: ko se disk, ki nosi dnevnik transakcij, napolni, se PostgreSQL ustavi, da zaščiti svoje podatke. Spremljajte zasedenost in disk povečajte pred 80 %.

Kar zadeva zmogljivost, vsaka potrditev transakcije počaka na zapis dnevnika na disk: zakasnitev shrambe določi hitrost pisanja. Prav tu NVMe SSD naredi razliko, veliko bolj kot pri branju, ki ga že streže pomnilnik.

Procesor: ena poizvedba, eno jedro

Poizvedba PostgreSQL se izvaja na enem samem jedru, razen velikih branj, ki jih pogon paralelizira. Število vCPU torej določi število zahtevnih poizvedb, obdelanih hkrati: 2 vCPU zadoščata poslovni aplikaciji ob zagonu, od 4 do 8 pa ustreza zelo obremenjenim zbirkam in zahtevnim poročilom.

Preden dodate vCPU, poiščite poizvedbe, ki jih porabljajo. Razširitev pg_stat_statements jih razvrsti po skupnem času:

shared_preload_libraries = 'pg_stat_statements'

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

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

Isti strežnik kot aplikacija ali ločen strežnik?

Ob zagonu je zbirka na istem strežniku kot aplikacija prava izbira: med njima ni omrežne zakasnitve, povezava poteka prek vtičnice Unix, varnostno kopirati in nadzirati je treba en sam strežnik. Ločimo ju, ko si tekmujeta za vire: prevajanje ali konica prometa, ki izprazni predpomnilnik zbirke, zbirka, ki za svoje podatke potrebuje ves pomnilnik, ali različne zahteve glede varnostnih kopij.

Varnost in varnostne kopije

• Lokalno poslušanje: listen_addresses = 'localhost', dokler se povezuje samo aplikacija na strežniku. Če mora do zbirke dostopati drug strežnik, vrata 5432 odprite samo za njegov naslov, v požarnem zidu in v pg_hba.conf.
• Overjanje: metoda scram-sha-256 v pg_hba.conf, ena vloga na aplikacijo, brez pravic superuporabnika.
• Logična varnostna kopija vsak dan z pg_dump, kopirana zunaj strežnika.
• Obnova na natančen trenutek: neprekinjeno arhiviranje dnevnika (pgBackRest, WAL-G) omogoča vrnitev na minuto natančno pred napako, medtem ko dnevni izvoz vrne na prejšnji dan.
• Preizkušena obnova: varnostna kopija se dokaže z obnovo, na testni zbirki, v rednih presledkih.

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