PostgreSQL op een VPS: geheugen en schijf dimensioneren

Vertrekken van het werkvolume

Het geheugen van een databaseserver dient in de eerste plaats als cache: een pagina die uit het geheugen wordt gelezen, wordt veel sneller geleverd dan een pagina van schijf, zelfs van NVMe. De vraag is dus niet alleen “hoe groot is de database?”, maar ook “welk deel wordt vaak gelezen?”. Een database van 50 GB waarvan alleen de laatste twaalf maanden worden geraadpleegd, werkt misschien op 8 GB.

Op een bestaande database geven twee query’s het totale volume en de grootste tabellen, indexen inbegrepen:

SELECT pg_size_pretty(pg_database_size(current_database()));

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

Voor een database die er nog moet komen, schat u tabel per tabel: het aantal verwachte rijen over een jaar × de gemiddelde grootte van een rij, en daarna telt u de indexen erbij, die gewoonlijk 30 tot 100% van de gegevens wegen. Voorbeeld: 2 miljoen rijen per maand, van elk 500 bytes, geven 12 GB aan gegevens over een jaar, en 18 tot 24 GB met de indexen.

Het geheugen: de startinstellingen

PostgreSQL bewaart gelezen pagina’s op twee niveaus: zijn eigen cache (shared_buffers) en de bestandscache van het systeem. De documentatie raadt aan op een dedicated databaseserver te beginnen met 25% van het geheugen voor shared_buffers, en merkt op dat boven 40% de winst zelden groter wordt: de rest komt ten goede aan de cache van het systeem en aan sorteerbewerkingen.

Deze waarden gaan uit van een database die alleen op de server staat. Draait de applicatie ernaast, trek dan eerst haar geheugen af (processen, cache, build) en pas de percentages toe op de rest: een server van 8 GB die een applicatie van 3 GB draait, stelt u af als een database van 5 GB.

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

shared_buffers wordt pas actief na een herstart van PostgreSQL; de andere parameters worden zonder onderbreking herladen, met SELECT pg_reload_conf();.

Om te weten of het geheugen volstaat, leest u de cache-hitratio. Bij een bedrijfsapplicatie of een website ligt die boven 99%:

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

De verbindingen: elk een eigen proces

PostgreSQL opent een proces per verbinding, en accepteert er standaard 100 (max_connections). Elk proces neemt in rust enkele megabytes in, meer wanneer het gegevens sorteert of hasht (work_mem, soms meermaals binnen dezelfde query). max_connections naar 500 optrekken om een spraakzame applicatie bij te houden, stelt de server bloot aan geheugentekort bij de eerste piek.

Het juiste antwoord is een connection pooler: PgBouncer, in de modus transaction, laat honderden verbindingen van de applicatie enkele tientallen echte verbindingen delen. Frameworks hebben ook hun eigen pool (SQLAlchemy, Prisma, HikariCP): stem de grootte ervan af op het aantal vCPU van de databaseserver, eerder dan op het aantal gebruikers.

De schijf: volume, marge en transactielogboek

De schijf van een PostgreSQL-server draagt meer dan de tabellen:

• De gegevens en de indexen, en hun groei over een jaar.
• Het transactielogboek (WAL): standaard 1 GB tussen twee checkpoints (max_wal_size), meer tijdens een grote import, en nog meer als de archivering van het logboek achterstand oploopt.
• De tijdelijke bestanden van sorteerbewerkingen die work_mem overschrijden.
• De ruimte voor een reorganisatie: VACUUM FULL en het herbouwen van een index schrijven een volledige kopie weg voordat de oude wordt vrijgegeven.
• De lokale exports, als u back-ups op de server bewaart voordat u ze elders naartoe stuurt.

De regel: het volume over een jaar, plus minstens 30% marge. Dat is geen luxe: als de schijf met het transactielogboek volloopt, stopt PostgreSQL om zijn gegevens te beschermen. Houd de bezetting in de gaten, en vergroot vóór 80%.

Wat de prestaties betreft, wacht elke commit van een transactie tot het logboek naar schijf is geschreven: de latentie van de opslag bepaalt de schrijfdoorvoer. Daar maakt een NVMe-SSD het verschil, veel meer dan bij het lezen, dat het geheugen al bedient.

De processor: één query, één core

Een PostgreSQL-query draait op één core, behalve de grote leesbewerkingen die de engine parallelliseert. Het aantal vCPU bepaalt dus hoeveel zware query’s tegelijk worden verwerkt: 2 vCPU volstaan voor een bedrijfsapplicatie bij de lancering, 4 tot 8 passen bij zwaar belaste databases en zware rapporten.

Zoek, voordat u vCPU toevoegt, de query’s op die ze verbruiken. De extensie pg_stat_statements rangschikt ze naar totale tijd:

shared_preload_libraries = 'pg_stat_statements'

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

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

Dezelfde server als de applicatie, of een aparte server?

Bij de lancering is de database op dezelfde server als de applicatie de juiste keuze: geen netwerklatentie tussen beide, een verbinding via een Unix-socket, één server om te back-uppen en te bewaken. U splitst ze wanneer ze om dezelfde resources concurreren: een build of een verkeerspiek die de cache van de database leegt, een database die al het geheugen nodig heeft voor haar gegevens, of verschillende eisen aan de back-ups.

Beveiliging en back-ups

• Lokaal luisteren: listen_addresses = 'localhost' zolang alleen de applicatie op de server verbinding maakt. Moet een andere server erbij kunnen, open dan poort 5432 alleen voor zijn adres, in de firewall en in pg_hba.conf.
• Authenticatie: de methode scram-sha-256 in pg_hba.conf, één rol per applicatie, zonder superuserrechten.
• Een logische back-up elke dag met pg_dump, gekopieerd naar een plek buiten de server.
• Herstel naar een precies tijdstip: continue archivering van het logboek (pgBackRest, WAL-G) laat u terugkeren tot op de minuut vóór een fout, waar een dagelijkse export u terugbrengt naar de dag ervoor.
• Getest herstel: een back-up bewijst zich door hem terug te zetten, in een testdatabase, op vaste tijdstippen.

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