PostgreSQL op engem VPS: Aarbechtsspäicher an Disk dimensionéieren

Vum nëtzleche Volume ausgoen

Den Aarbechtsspäicher vun engem Datebankserver déngt als éischt als Cache: eng Säit, déi aus dem Späicher gelies gëtt, ass vill méi séier do wéi eng Säit vum Disk, och NVMe. D’Fro ass also net nëmmen „wéi grouss ass d’Datebank?“, mä „wéi en Deel gëtt dacks gelies?“. Eng Datebank vu 50 GB, vun där nëmmen déi lescht zwielef Méint ofgefrot ginn, schafft vläicht op 8 GB.

Op enger bestoender Datebank ginn zwou Ufroen de ganze Volume an déi gréisst Tabellen, Indexen abegraff:

SELECT pg_size_pretty(pg_database_size(current_database()));

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

Fir eng Datebank, déi nach kënnt, schätzt Dir Tabell fir Tabell: Zuel vun den erwaarte Zeilen no engem Joer × duerchschnëttlech Gréisst vun enger Zeil, an dann d’Indexen derbäi, déi üblecherweis 30 bis 100 % vun den Donnéeë weien. Beispill: 2 Milliounen Zeilen pro Mount, vu 500 Bytes all eenzel, maachen 12 GB Donnéeën no engem Joer, an 18 bis 24 GB mat den Indexen.

Den Aarbechtsspäicher: d’Startastellungen

PostgreSQL hält déi geliese Säiten op zwee Niveauen: a sengem eegene Cache (shared_buffers) an am Fichierscache vum System. Seng Dokumentatioun recommandéiert, op engem dedizéierte Server mat 25 % vum Aarbechtsspäicher fir shared_buffers unzefänken, a stellt fest, datt iwwer 40 % de Gewënn rar gëtt: de Rescht kënnt dem Cache vum System an de Sortéierungen zegutt.

Dës Wäerter ginn dovun aus, datt d’Datebank eleng um Server ass. Wann d’Applikatioun niewendrun leeft, zitt als éischt hire Späicher of (Prozesser, Cache, Kompilatioun) a rechent d’Prozentsätz mam Rescht: e Server vun 8 GB, op deem eng Applikatioun vun 3 GB leeft, gëtt wéi eng Datebank vu 5 GB agestallt.

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

shared_buffers gëllt eréischt nom Neistart vu PostgreSQL; déi aner Parameteren ginn ouni Ënnerbriechung nei gelueden, mat SELECT pg_reload_conf();.

Fir ze wëssen, ob den Aarbechtsspäicher duergeet, liest den Undeel vun de Liesungen aus dem Cache. Op enger Geschäftsapplikatioun oder enger Websäit läit en iwwer 99 %:

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

D’Verbindungen: ee Prozess fir all eenzel

PostgreSQL mécht ee Prozess pro Verbindung op, an akzeptéiert standardméisseg 100 dovun (max_connections). All Prozess hëlt am Rouzoustand e puer Megabyte, méi, wann e Donnéeë sortéiert oder hasht (work_mem, heiansdo e puer Mol an enger eenzeger Ufro). max_connections op 500 eropzesetzen, fir enger gespréicheger Applikatioun nozekommen, heescht, datt dem Server bei der éischter Spëtzt den Aarbechtsspäicher ka feelen.

Déi richteg Äntwert ass e Pooler fir Verbindungen: PgBouncer, am Modus transaction, léisst Honnerte vu Verbindunge vun der Applikatioun eng kleng Zuel vu reelle Verbindungen deelen. D’Frameworken hunn och hiren eegene Pool (SQLAlchemy, Prisma, HikariCP): stellt seng Gréisst no der Zuel vun de vCPU vum Datebankserver an, an net no der Zuel vun de Benotzer.

Den Disk: Volume, Marge a Journal

Den Disk vun engem PostgreSQL-Server dréit méi wéi d’Tabellen:

• D’Donnéeën an d’Indexen, an hire Wuesstem iwwer ee Joer.
• Den Transaktiounsjournal (WAL): 1 GB standardméisseg tëscht zwee Kontrollpunkten (max_wal_size), méi wärend engem massiven Import, an nach méi, wann d’Archivéierung vum Journal hannendrun ass.
• Déi temporär Fichiere vun de Sortéierungen, déi work_mem iwwerschreiden.
• D’Plaz fir eng Reorganisatioun: VACUUM FULL an den Neiopbau vun engem Index schreiwen eng komplett Kopie, ier déi al fräi gëtt.
• Déi lokal Exporter, wann Dir Backups um Server behaalt, ier Dir se soss anzwousch hinschéckt.

D’Reegel: de Volume no engem Joer, plus op mannst 30 % Marge. Dat ass kee Komfort: wann den Disk, deen den Transaktiounsjournal dréit, voll gëtt, stoppt PostgreSQL, fir seng Donnéeën ze schützen. Iwwerwaacht d’Belegung, a vergréissert, ier se 80 % erreecht.

Wat d’Performance ugeet, waart all Commit vun enger Transaktioun, bis de Journal op den Disk geschriwwen ass: d’Latenz vum Späicher leet de Schreifduerchsaz fest. Do mécht en NVMe-Disk den Ënnerscheed, vill méi wéi beim Liesen, dat den Aarbechtsspäicher schonn iwwerhëlt.

De Prozessor: eng Ufro, ee Kär

Eng PostgreSQL-Ufro leeft op engem eenzege Kär, ausser déi grouss Liesoperatiounen, déi de Moteur paralleliséiert. D’Zuel vun de vCPU leet also d’Zuel vu schwéieren Ufroe fest, déi zur selwechter Zäit veraarbecht ginn: 2 vCPU ginn duer fir eng Geschäftsapplikatioun beim Start, 4 bis 8 passe fir Datebanken, déi vill gefuerdert ginn, a fir schwéier Berichter.

Ier Dir vCPU derbäisetzt, sicht d’Ufroen, déi se verbrauchen. D’Extensioun pg_stat_statements klasséiert se no Gesamtzäit:

shared_preload_libraries = 'pg_stat_statements'

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

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

Um selwechte Server wéi d’Applikatioun, oder op engem separate Server?

Beim Start ass d’Datebank um selwechte Server wéi d’Applikatioun déi richteg Wiel: keng Netzwierklatenz tëscht deenen zwee, eng Verbindung iwwer en Unix-Socket, ee Server fir ze sécheren an z’iwwerwaachen. Getrennt gëtt, wann déi zwee sech ëm d’Ressource streiden: eng Kompilatioun oder eng Traffic-Spëtzt, déi de Cache vun der Datebank eidel mécht, eng Datebank, déi de ganzen Aarbechtsspäicher fir hir Donnéeë brauch, oder ënnerschiddlech Ufuerderungen un d’Backups.

Sécherheet a Backups

• Lokal lauschteren: listen_addresses = 'localhost', soulaang nëmmen d’Applikatioun vum Server sech verbënnt. Wann en anere Server drop zougräife muss, maacht de Port 5432 nëmme fir seng Adress op, an der Firewall an an pg_hba.conf.
• Authentifikatioun: Method scram-sha-256 an pg_hba.conf, eng Roll pro Applikatioun, ouni Superuser-Rechter.
• E logesche Backup all Dag mat pg_dump, ausserhalb vum Server kopéiert.
• Restauratioun op e präzise Moment: déi kontinuéierlech Archivéierung vum Journal (pgBackRest, WAL-G) erlaabt et, op d’Minutt genee op de Stand virun engem Feeler zeréckzegoen, wou en alldeegleche Export op de Stand vum Virdag zeréckbréngt.
• Eng getest Restauratioun: e Backup beweist sech, andeems en restauréiert gëtt, op enger Testdatebank, a reegelméissegen Ofstänn.

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