dmosk / pgtune

PostgreSQL cher choHwI'

postgresql.conf Hap yIchenmoH: patlh, jaw nIn, Qu', RAM, CPU, yo', mem. Hoch mI' ghuH ghaj; lIjwI'Daq Hoch mI'.

jan chuq Qu' je

pgtune qatlh lo'

  • jan chuq cher. RAM, CPU, mem, Qu' mIw yIghItlh; shared_buffers, work_mem, WAL, parallel cher yIlaj.
  • PostgreSQL 14–19. patlh Daq mI'mey choH; io_method (18+), wal_compression, vacuum je.
  • ghuH ghItlh je. Hoch mI' ghaj ghuH; OOM, huge pages, LZ4, io_uring, DoH poQ cher je.
  • vagh mIw. web, OLTP, De' vem, DaH Qu', ngeH; SSD, HDD, SAN, NVMe.
  • lIjwI'Daq neH. server API pagh — mI'mey mej 'e' ghaHbe'. Huch pagh, 23 Hol, yo' ghaj.

PostgreSQL chu' cher

  1. PostgreSQL patlh, jaw nIn, Qu' mIw yIwIv.
  2. RAM Hoch 'ay' yIghItlh; CPU tIq yo' Hoch je (ngu'be').
  3. mem Segh yIwIv; De' RAM puS, rap pagh law 'e' yIlaj.
  4. chenmoH yIpol, ghuH yIlaj, 'ej Hap postgresql.conf yIqaw.

cher Qap Dun

Qapla' 'oH wa'DIch neH: qaw, WAL, planner, parallel mIw Daq. Qu' vo' yIlaj 'ej yIQoy.

QeS:

  • DoH poQ cher: ngoq cha' nagh.
  • work_mem mIw Hoch, yo' Hoch be' — wa' query law lo'laH.
  • yo' law: PgBouncer yIlo', max_connections yIlaw'be'.
  • Linux Daq shared_buffers 2 GB pagh law: huge pages yIchu'.
  • choH retlh: EXPLAIN (ANALYZE, BUFFERS) pg_stat_statements je yIlaj; cher wa' ghom, 'ej qa' cher yIlon.

pgtune Qu'

  • PostgreSQL server chu' wa'DIch cher;
  • RAM CPU je DuHIvDI' pagh patlh choHDI' qa' mI';
  • jan rap mIw yIghItlh (web, OLTP, ghuS);
  • mI'mey wIv 'e' yIchaq.

Qaw nuqneH

pgtune mI'mey server Daq ngeH'a'?

ghobe'. lIjwI'Daq neH mI', API pagh.

PostgreSQL patlh qaq?

14 19 je. mI'mey patlh Daq.

Qapla' production Daq yIqaw'a'?

wa'DIch neH. ghuH yIlaj, test Daq yIlo', Qu' vo'Daq yIQoy.

max_connections qatlh puS?

yo' Hoch qaw lo'; work_mem yo' Daq beq. PgBouncer QaQ law.

CPU yo' je vIQoybe'?

chIm yIlaj: mIw RAM je Daq mI' wIv; yo' mI' Qapla' Daq legh.