PostgreSQL arxitekturasi va sozlamalari: shared_buffers, work_mem, WAL va pg_hba.conf

Qisqacha: Postgres — har ulanishga alohida jarayon (process) ochadigan server; shuning uchun yuzlab ulanish qimmat va hovuz (pool) kerak. Unumdorlikni bir necha sozlama belgilaydi: shared_buffers (umumiy kesh, sukut 128 MB), work_mem (har saralash/hash uchun, 4 MB), effective_cache_size (planner'ga "OS keshi qancha" maslahati), WAL va checkpoint sozlamalari. Sozlamalar postgresql.conf yoki ALTER SYSTEM bilan o'zgaradi; ba'zilari reload'da, ba'zilari faqat restart'da kuchga kiradi. Kim ulana olishini pg_hba.conf hal qiladi.

Bu darsda

• Postgres jarayonlarini (postmaster, backend, checkpointer, walwriter …) va "har ulanish — jarayon" modelini tushuntirasiz.
• shared_buffers, work_mem, effective_cache_size, maintenance_work_mem nima qilishini va qanday hisoblanishini bilasiz.
• WAL va checkpoint nima ekanini, synchronous_commit narxini o'lchov bilan bilasiz.
• Sozlamani ALTER SYSTEM bilan o'zgartirib, context (reload yoki restart) ni tekshirasiz.
• pg_hba.conf qoidalarini o'qiysiz.

Oldin bilishingiz kerak: Connection pooling, MVCC: Postgres ichida versiyalar, Rollar va huquqlar.

1. Nega bu kerak?

Shu paytgacha Postgres'ni "qora quti" sifatida ishlatdik: so'rov yubordik — javob oldik. Lekin prod'da birinchi kunlardanoq savollar chiqadi:

• "Nega 300 ta ulanishda server qotib qoldi?"
• "Nega bu so'rov diskka yozyapti?"
• "Serverda 16 GB xotira bor — Postgres nega 128 MB ishlatyapti?"
• "Nega bazaga tashqaridan ulanib bo'lmayapti?"

O'xshatish: oshxona. Postmaster — eshik oldidagi administrator: har yangi mijozga alohida ofitsiant (jarayon) chaqiradi. Shared buffers — umumiy muzlatkich: hamma oshpaz undan oladi. work_mem — har oshpazning stol ustidagi joyi: kichik bo'lsa, ish polga (diskka) tushadi. WAL — buyurtmalar daftari: avval daftarga yoziladi, keyin pishiriladi — chiroq o'chsa ham daftardan tiklanadi.

Bu darsda ikki manba bor: kanon PostgreSQL 18.6 (Docker, Linux konteyner; kanon o'lchovi, IlmHamroh, 2026-10) va bizning PostgreSQL 17.6 (Windows, o'z klasterimiz). Ular farq qiladigan joylar — o'zi saboq. Sozlamalarni bazaga emas, serverga o'zgartirganimiz uchun ba'zi bloklar faqat o'z klasteringizda takrorlanadi.

2. Jarayonlar: har ulanish — alohida process

2.1 Kim ishlayapti

Kanon konteyner ichidagi jarayonlar ro'yxati (ps). RSS (jarayon egallagan xotira, kB) raqamlariga emas, ikki ustunga qarang: PPID (kim ochgan) va COMMAND (jarayon nomi):

  PID  PPID   RSS COMMAND
    1     0 25088 postgres -c shared_preload_libraries=pg_stat_statements -c track_io_timing=on
   75     1 125728 postgres: io worker 0
   76     1 82080 postgres: io worker 1
   77     1 63648 postgres: io worker 2
   78     1 138268 postgres: checkpointer
   79     1 138144 postgres: background writer
   81     1  9888 postgres: walwriter
   82     1  9504 postgres: autovacuum launcher
   83     1  7584 postgres: logical replication launcher

PID 1 — postmaster (bosh jarayon); qolganlari uning bolalari (PPID 1):

Xuddi shu ma'lumot SQL'dan — pg_stat_activity ning backend_type ustuni. Biz o'z klasterimizda (PostgreSQL 17.6, Windows):

SELECT backend_type, count(*) FROM pg_stat_activity
WHERE backend_type NOT IN ('autovacuum worker', 'parallel worker')
GROUP BY 1 ORDER BY 1;

         backend_type         | count
------------------------------+-------
 autovacuum launcher          |     1
 background writer            |     1
 checkpointer                 |     1
 client backend               |     1
 logical replication launcher |     1
 walwriter                    |     1
(6 rows)

Kanonning PG 18 chiqishida yana io worker | 3 bor — bizda (17) yo'q. client backend — bizning ulanishimiz.

2.2 Har ulanish — yangi jarayon

Kanon 5 ta parallel ulanish ochdi va qayta sanadi: client backend — 6 (5 + o'lchayotgan ulanish). Har ulanish — alohida OS jarayoni (Windows'da ham), o'z xotirasi bilan (bir necha MB dan o'nlab MB gacha). Shuning uchun:

• max_connections sukut bo'yicha 100 — bu chegara bejiz emas.
• 1 000 ta ulanish = 1 000 ta jarayon: xotira va kontekst almashinuvi narxi.
• Yechim — hovuz (pool): ilova 10–20 ulanishni qayta ishlatadi (Connection pooling), ko'p xizmat bo'lsa — PgBouncer (PgBouncer va tashqi pooler'lar).

Butun tuzilma bitta rasmda — postmaster jarayonlarni boshqaradi, backend'lar umumiy xotira (shared buffers, WAL buferi) orqali ishlaydi:

flowchart TD
  K["Mijozlar<br/>(API, psql)"] --> PM["postmaster"]
  PM --> B1["backend 1<br/>work_mem"]
  PM --> B2["backend 2<br/>work_mem"]
  B1 --> SB["shared_buffers<br/>umumiy kesh"]
  B2 --> SB
  B1 --> WB["WAL buferi"]
  WB --> WW["walwriter"] --> WAL[("pg_wal/")]
  SB --> CP["checkpointer,<br/>bgwriter"] --> D[("ma'lumot fayllari")]

Tekshirib ko'ring: API'da hovuz hajmi 20, API'ning 4 ta nusxasi ishlaydi, yana hisobot xizmati 5 ulanish oladi. Postgres'da nechta client backend jarayoni bo'lishi mumkin? max_connections = 100 yetadimi?

3. Asosiy sozlamalar

3.1 Sozlamalar katalogi

Hamma sozlama — pg_settings da: qiymat, birlik va context (qachon o'zgartirsa bo'ladi):

SELECT name, setting, unit, context FROM pg_settings
WHERE name IN ('max_connections', 'shared_buffers', 'work_mem',
  'maintenance_work_mem', 'effective_cache_size', 'max_wal_size',
  'synchronous_commit', 'random_page_cost')
ORDER BY name;

         name         | setting | unit |  context
----------------------+---------+------+------------
 effective_cache_size | 524288  | 8kB  | user
 maintenance_work_mem | 65536   | kB   | user
 max_connections      | 100     |      | postmaster
 max_wal_size         | 1024    | MB   | sighup
 random_page_cost     | 4       |      | user
 shared_buffers       | 16384   | 8kB  | postmaster
 synchronous_commit   | on      |      | user
 work_mem             | 4096    | kB   | user
(8 rows)

Bu qiymatlar sukut (biz initdb dan keyin faqat vaqt zonasini o'zgartirganmiz); kanondagisi aynan shunday. Ikkita narsaga qarang:

• Birlik. shared_buffers = 16384 — 16 384 ta 8 kB lik sahifa = 128 MB. work_mem = 4096 kB = 4 MB. setting ustunini birliksiz o'qisangiz, adashasiz.
• Context: postmaster — faqat server qayta ishga tushganda; sighup — konfiguratsiyani qayta o'qish (reload) yetadi; user — har sessiya o'zi ham o'zgartira oladi (SET work_mem = '64MB').

Birlik tuzog'ini bitta xato bilan ko'ramiz:

SELECT pg_size_pretty(current_setting('shared_buffers')::bigint);
SHOW shared_buffers;

ERROR:  invalid input syntax for type bigint: "128MB"
 shared_buffers
----------------
 128MB
(1 row)

current_setting va SHOW birlikli matn qaytaradi (128MB), pg_settings.setting — birliksiz son. Hisob uchun pg_settings dan setting va unit ni birga oling.

3.2 shared_buffers — umumiy kesh

Postgres o'qigan har sahifa (8 kB) avval shared_buffers ga tushadi; keyingi so'rov uni diskdan emas, xotiradan oladi. EXPLAIN'dagi Buffers: shared hit=… (EXPLAIN ANALYZE'ni o'qish) — aynan shu keshdan topilgan sahifalar.

Sukut 128 MB — har qanday kompyuterda ishga tushishi uchun ataylab kichik. Keng tarqalgan boshlang'ich qoida (hujjatdagi tavsiya): ajratilgan serverda RAM ning ~25 %. 40 % dan ko'pi kamdan-kam foyda beradi: Postgres operatsion tizimning fayl keshiga ham tayanadi (bir sahifa ikki marta keshlanadi). Context — postmaster: o'zgartirish restart talab qiladi.

3.3 work_mem — har amal uchun

work_mem — bitta saralash yoki hash amali uchun xotira. Yetmasa, amal diskka tushadi: EXPLAIN'da Sort Method: external merge Disk: …. Muhim nozik joy: bu chegara so'rovga emas, amalga — bitta murakkab so'rovda 3 ta saralash va 2 ta hash bo'lsa, 5 × work_mem ishlatilishi mumkin; parallel ishchilar bilan yana ko'payadi. Ustiga har ulanish.

Shuning uchun work_mem = 1GB "tezroq bo'lsin" deb qo'yilgan serverda 100 ulanish bir vaqtda og'ir so'rov yuborsa — xotira tugaydi va OS jarayonni o'ldiradi. To'g'ri yo'l: global qiymat mo'tadil (4–64 MB), og'ir hisobot uchun esa faqat o'sha sessiyada:

SET work_mem = '64MB';
SHOW work_mem;

SET
 work_mem
----------
 64MB
(1 row)

Yoki rolga: ALTER ROLE vazifalar_hisobot SET work_mem = '64MB' (faqat login'da qo'llanadi — Rollar va huquqlar darsidagi tuzoq).

3.4 effective_cache_size, maintenance_work_mem, random_page_cost

• effective_cache_size (sukut 4 GB) — xotira ajratmaydi. Bu planner'ga maslahat: "OS keshi + shared_buffers taxminan shuncha". Katta bo'lsa planner indeksni ko'proq tanlaydi. Odatda RAM ning 50–75 %.
• maintenance_work_mem (64 MB) — CREATE INDEX, VACUUM uchun. Bir vaqtda kam ishlaydi, shuning uchun kattaroq (256 MB – 1 GB) bo'lishi mumkin.
• random_page_cost (4) — tasodifiy o'qish ketma-ket o'qishdan necha barobar qimmat. Eski aylanuvchi disklar uchun hisoblangan; SSD'da ko'pincha 1.1–2 qo'yiladi — planner indeksni "arzonroq" ko'radi.

PGTune kabi onlayn kalkulyatorlar RAM, CPU va yuklama turidan boshlang'ich qiymatlarni chiqarib beradi. Bu kursda sinalmagan — foydali boshlanish nuqtasi, lekin oxirgi so'z o'lchov (Sekin so'rovlarni topish).

4. WAL va checkpoint

4.1 Avval jurnalga, keyin joyiga

WAL (Write-Ahead Log — oldindan yoziladigan jurnal) — har o'zgarish avval ketma-ket jurnalga yoziladi (pg_wal/ papkasi), va faqat jurnal diskka yozilgandan keyin COMMIT "muvaffaqiyatli" deb javob beradi. Jadval sahifalari esa xotirada o'zgaradi va diskka keyinroq yoziladi. Server to'satdan o'chsa — qayta yonganda WAL'dan o'zgarishlar takrorlanadi. Bu ACID dagi D (durability — saqlanish) ning texnik asosi.

Bitta UPDATE ning yo'li:

sequenceDiagram
  participant I as Ilova
  participant B as backend
  participant W as WAL (pg_wal)
  participant J as Jadval fayli
  I->>B: UPDATE + COMMIT
  B->>W: yozuv, diskka majburan
  B-->>I: COMMIT OK
  Note over J: sahifa hali xotirada
  B->>J: keyinroq checkpoint yozadi

Diqqat qiling: ilova "OK" ni jadval fayli o'zgarishidan oldin oladi. Xavfsizlikni WAL beradi.

Nega bu tezroq? Jurnalga yozish — faylning oxiriga ketma-ket qo'shish; sahifalarni joyiga yozish — diskning turli joylariga tasodifiy yozish. Ketma-ket yozish ancha arzon.

WAL hajmi — ma'lumotdan kam emas. 10 000 qator (har biri ~100 bayt) qo'shganda qancha WAL yozildi:

CREATE TABLE m12 (id int, matn text);
SELECT pg_current_wal_lsn() AS boshi \gset
INSERT INTO m12
SELECT g, repeat('x', 100) FROM generate_series(1, 10000) g;
SELECT pg_size_pretty(
         pg_wal_lsn_diff(pg_current_wal_lsn(), :'boshi')) AS wal,
       pg_size_pretty(pg_relation_size('m12')) AS jadval;
DROP TABLE m12;

Biz ishga tushirdik (PostgreSQL 17.6; WAL hajmi checkpoint vaqtiga qarab biroz o'zgarishi mumkin):

   wal   | jadval
---------+---------
 1567 kB | 1384 kB
(1 row)

Kanonda (PG 18) xuddi shu tajriba: WAL 1 582 kB, jadval 1 416 kB. WAL jadvaldan katta: har qator + sarlavhalar + tranzaksiya yozuvlari. pg_current_wal_lsn() — WAL'dagi hozirgi joy (LSN — Log Sequence Number), \gset — psql'da natijani o'zgaruvchiga saqlash. Bu WAL keyin zaxira va replikatsiya uchun ham ishlatiladi (Backup va restore darsidagi PITR).

4.2 Checkpoint

WAL cheksiz o'smasligi uchun checkpoint (nazorat nuqtasi) bor: checkpointer xotiradagi barcha o'zgargan sahifalarni diskka yozadi. Shundan keyin eski WAL fayllari kerak emas (zaxira arxivlanmasa — qayta ishlatiladi). Ikki sozlama:

• checkpoint_timeout (sukut 5 daqiqa) — eng ko'pi bilan qancha vaqtda bir;
• max_wal_size (sukut 1 GB) — WAL shuncha o'sganda — muddatidan oldin.

Tez-tez checkpoint — ko'p disk yozuvi (va har checkpoint'dan keyingi birinchi o'zgarishda butun sahifa WAL'ga yoziladi). Kam checkpoint — WAL katta, avariyadan keyin tiklanish uzoqroq. Katta yozish yuklamasida max_wal_size ni oshirish — ko'p uchraydigan sozlash.

Kanon topilmasi (PG 17+): checkpoint statistikasi ko'chgan.

SELECT checkpoints_timed FROM pg_stat_bgwriter;
SELECT num_timed >= 0 AS bor FROM pg_stat_checkpointer;

ERROR:  column "checkpoints_timed" does not exist
LINE 1: SELECT checkpoints_timed FROM pg_stat_bgwriter;
               ^
 bor
-----
 t
(1 row)

PostgreSQL 17 dan pg_stat_bgwriter.checkpoints_timed o'rniga pg_stat_checkpointer.num_timed (va num_requested). Eski maqolalardagi monitoring so'rovlari yangi versiyada shunday yiqiladi. Kanonda num_requested = 13, num_timed = 0 edi — ya'ni checkpoint'larni vaqt emas, WAL hajmi va buyruqlar chaqirgan (tajribalarda ko'p yozildi).

4.3 synchronous_commit — tezlik va xavfsizlik

synchronous_commit = off — COMMIT WAL diskka yozilishini kutmaydi. Kanon buni pgbench (Postgres bilan birga keladigan yuklama vositasi; TPC-B o'xshash bank tranzaksiyalari, scale 10 = 1 mln hisob, 8 mijoz, 15 soniya) bilan o'lchagan:

off — 4.5 barobar ko'p tranzaksiya. Narxi: server to'satdan o'chsa (elektr, OS qulashi), oxirgi bir necha yuz millisekunddagi "muvaffaqiyatli" deb javob berilgan tranzaksiyalar yo'qolishi mumkin. Baza buzilmaydi (WAL izchil qoladi) — faqat oxirgi o'zgarishlar yo'q. Shuning uchun:

• Pul, buyurtma, foydalanuvchi ma'lumoti — on (sukut).
• Log, statistika, sessiya kabi yo'qolsa ham bo'ladigan narsalar — faqat o'sha tranzaksiya uchun SET LOCAL synchronous_commit = off.

Bu raqamlar Docker Desktop ichida, band mashinada, bitta o'lchov; Linux serverda boshqacha bo'ladi — nisbat muhim.

Tekshirib ko'ring: fsync = off sozlamasi ham bor va u yanada tezroq. Nega uni prod'da hech qachon o'chirmaslik kerak, synchronous_commit = off esa ba'zan mumkin?

5. Sozlamalarni o'zgartirish

5.1 Uch joy

1. postgresql.conf — asosiy fayl (SHOW config_file). Kanonda /var/lib/postgresql/18/docker/postgresql.conf.
2. postgresql.auto.conf — ALTER SYSTEM yozadigan fayl; postgresql.conf dan keyin o'qiladi va ustun turadi. Qo'lda tahrirlanmaydi.
3. Rol/baza/sessiya — ALTER ROLE … SET, ALTER DATABASE … SET, SET.

5.2 ALTER SYSTEM va reload/restart

Kanon tajribasi: bitta sighup va bitta postmaster sozlamani o'zgartirib, konfiguratsiyani qayta o'qitdi (pg_reload_conf()):

ALTER SYSTEM SET work_mem = '16MB';
ALTER SYSTEM SET shared_buffers = '256MB';
SELECT pg_reload_conf();
SELECT name, setting, unit, pending_restart FROM pg_settings
WHERE name IN ('work_mem', 'shared_buffers');
SELECT name, setting, applied, error FROM pg_file_settings
WHERE name IN ('work_mem', 'shared_buffers');

Kanon chiqishi (PostgreSQL 18.6):

      name      | setting | unit | pending_restart
----------------+---------+------+-----------------
 shared_buffers | 16384   | 8kB  | t
 work_mem       | 16384   | kB   | f
(2 rows)

      name      | setting | applied |            error
----------------+---------+---------+------------------------------
 shared_buffers | 128MB   | f       |
 work_mem       | 16MB    | t       |
 shared_buffers | 256MB   | f       | setting could not be applied
(3 rows)

• work_mem (sighup) — reload'dan keyin darhol 16 MB (16 384 kB).
• shared_buffers (postmaster) — qiymat o'zgarmadi (16 384 × 8 kB = 128 MB), pending_restart = t: "restart kutilmoqda". pg_file_settings aniqroq: faylda 256 MB bor, lekin setting could not be applied.

postgresql.auto.conf mazmuni shu paytda:

# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
work_mem = '16MB'
shared_buffers = '256MB'

Qaytarish — ALTER SYSTEM RESET nom; + pg_reload_conf().

Biz o'z klasterimizda xuddi shu ketma-ketlikni takrorladik va yana bir narsani ko'rdik. Reload'dan darhol keyingi so'rov hali eski qiymatlarni ko'rsatdi (work_mem 4 096, pending_restart f). Keyingi RESET va yana reload'dan keyingi so'rov esa, aksincha, oldingi qadamning holatini ko'rsatdi (work_mem 16 384, pending_restart t) — har safar bir qadam orqada. Sababi: pg_reload_conf() faqat signal yuboradi va kutmaydi — natijani tekshirishdan oldin bir lahza kuting yoki yangi ulanishda tekshiring. (Shu sababli bu bo'lim bloklari bizning tekshiruvimizda emas, kanon chiqishi bilan.)

Docker'da: docker run postgres -c shared_buffers=512MB — buyruq qatoridagi sozlama ham bor (kanon shared_preload_libraries ni shunday bergan, PID 1 ning buyrug'iga qarang).

6. pg_hba.conf — kim, qayerdan, qanday

listen_addresses — server qaysi tarmoq interfeysida tinglaydi (localhost — faqat shu kompyuter, * — hamma). Kim ulana olishini esa pg_hba.conf (host-based authentication) hal qiladi: har qator — qoida, yuqoridan pastga birinchi mosi ishlaydi. Faylni SQL'dan ham o'qish mumkin. Bizning initdb --auth=trust bilan yaratilgan klasterimiz:

SELECT line_number, type, database, user_name, address, auth_method
FROM pg_hba_file_rules ORDER BY line_number;

 line_number | type  |   database    | user_name |  address  | auth_method
-------------+-------+---------------+-----------+-----------+-------------
         117 | local | {all}         | {all}     |           | trust
         119 | host  | {all}         | {all}     | 127.0.0.1 | trust
         121 | host  | {all}         | {all}     | ::1       | trust
         124 | local | {replication} | {all}     |           | trust
         125 | host  | {replication} | {all}     | 127.0.0.1 | trust
         126 | host  | {replication} | {all}     | ::1       | trust
(6 rows)

Ustunlar — fayldagi qator maydonlari: ulanish turi (local — Unix socket, host — TCP), qaysi baza, qaysi rol, qaysi manzildan, qanday tekshiruv. trust — parolsiz: faqat shu kompyuterdan (127.0.0.1, ::1). Kanon konteynerida yana 130-qator bor: host all all all trust — har qanday manzildan parolsiz. Bu Docker image'ning POSTGRES_HOST_AUTH_METHOD=trust sozlamasi; kanon portni faqat 127.0.0.1 ga ochgani uchun xavfsiz. Serverda bunday qator — ochiq eshik.

Prod uchun naqsh (hujjat bo'yicha; bu kursda parolli rejim sinalmagan):

# TYPE  DATABASE   USER                  ADDRESS        METHOD
local   all        postgres                             peer
host    vazifalar  vazifalar_api         10.0.0.0/24    scram-sha-256
host    vazifalar  vazifalar_hisobot     10.0.0.15/32   scram-sha-256
host    all        all                   0.0.0.0/0      reject

peer — OS foydalanuvchisi nomi bilan (faqat Unix socket); scram-sha-256 — parol, xavfsiz algoritm bilan; reject — qolganlarni rad etish. Shunday qoida bilan Rollar va huquqlar darsidagi topilma (API roli boshqa bazalarga ham ulandi) yopiladi: API faqat vazifalar ga, faqat ichki tarmoqdan. pg_hba.conf o'zgarsa — reload yetadi.

7. PostgreSQL 18: asinxron I/O

Kanondagi io worker jarayonlari — PostgreSQL 18 yangiligi. io_method = worker (sukut) bo'lsa, ma'lumot o'qishni backend o'zi kutmaydi — ishchilarga beradi va oldindan o'qib turadi (ketma-ket skanlash, VACUUM uchun foydali). Linux'da io_method = io_uring ham bor. Bizning PG 17 da bu sozlama yo'q. Kanon sozlamalari:

           name           | setting | unit |  context   |       source
--------------------------+---------+------+------------+--------------------
 effective_io_concurrency | 16      |      | user       | default
 io_method                | worker  |      | postmaster | default
 io_workers               | 3       |      | sighup     | default

Tezlik foydasi bu kursda alohida o'lchanmagan; tafsilot — PostgreSQL 18 reliz eslatmalarida.

8. Ko'p uchraydigan xatolar

8.1 max_connections ni ko'tarib "muammoni hal qilish"

Har ulanish — jarayon va xotira. Hovuz (pool) va PgBouncer; max_connections — mo'tadil.

8.2 work_mem ni global katta qilish

Bir vaqtda ko'p so'rov — xotira tugaydi. Global mo'tadil, og'ir ishlar uchun sessiya/rol darajasida.

8.3 shared_buffers ni o'zgartirib, restart qilmaslik

pending_restart = t — hech narsa o'zgarmagan. context ni tekshiring; postmaster — rejalashtirilgan restart.

8.4 fsync = off yoki full_page_writes = off "tezlik uchun"

Avariyada buzilgan baza. Hech qachon prod'da; tezlik kerak bo'lsa — tranzaksiya darajasida synchronous_commit.

8.5 host all all 0.0.0.0/0 trust

Internetdan parolsiz kirish. Aniq baza, rol, tarmoq va scram-sha-256; oxirida reject.

9. Mashqlar

1-mashq (oson): Birliklarni o'qing

pg_settings dan shared_buffers, work_mem, effective_cache_size uchun setting, unit ni oling va megabaytdagi qiymatini hisoblang. Ishora: unit 8kB bo'lsa — setting × 8 / 1024, kB bo'lsa — setting / 1024; CASE (Ifodalar va o'rnatilgan funksiyalar).

SELECT name || ': ' || (setting::bigint * CASE unit
         WHEN '8kB' THEN 8 WHEN 'kB' THEN 1 END / 1024) || ' MB'
FROM pg_settings
WHERE name IN ('shared_buffers', 'work_mem', 'effective_cache_size')
ORDER BY name;

effective_cache_size: 4096 MB
shared_buffers: 128 MB
work_mem: 4 MB

2-mashq (o'rta): Eng yomon holat xotirasi

max_connections × work_mem — barcha ulanishlar bir vaqtda bittadan saralash qilsa, qancha xotira kerak (MB)? Unga shared_buffers ni qo'shing. Natija: ulanishlar, bitta_amal_mb, jami_mb. Keyin work_mem ni sessiyada 256MB qilib, xuddi shu hisobni takrorlang.

SELECT current_setting('max_connections') || ' ulanish, '
  || (SELECT setting::bigint / 1024 FROM pg_settings
      WHERE name = 'work_mem') || ' MB, jami '
  || (SELECT setting::bigint FROM pg_settings
      WHERE name = 'max_connections')
     * (SELECT setting::bigint / 1024 FROM pg_settings
        WHERE name = 'work_mem')
     + (SELECT setting::bigint * 8 / 1024 FROM pg_settings
        WHERE name = 'shared_buffers') || ' MB';
SET work_mem = '256MB';
SELECT 'work_mem 256 MB bilan jami: '
  || (SELECT setting::bigint FROM pg_settings
      WHERE name = 'max_connections')
     * (SELECT setting::bigint / 1024 FROM pg_settings
        WHERE name = 'work_mem')
     + (SELECT setting::bigint * 8 / 1024 FROM pg_settings
        WHERE name = 'shared_buffers') || ' MB';

100 ulanish, 4 MB, jami 528 MB
work_mem 256 MB bilan jami: 25728 MB

3-mashq (qiyin): WAL va indekslar

m12_a (id int, matn text) va m12_b (xuddi shunday, lekin matn ustida B-tree indeks bilan) jadvallariga 10 000 tadan qator (repeat('x', 100)) qo'shing va har biri uchun yozilgan WAL'ni o'lchang (pg_current_wal_lsn, \gset, pg_wal_lsn_diff). Natijani raqam emas, mantiqiy javob sifatida chiqaring: indeksli ko'p WAL yozdi: t/f.

CREATE TABLE m12_a (id int, matn text);
CREATE TABLE m12_b (id int, matn text);
CREATE INDEX m12_b_matn_idx ON m12_b (matn);
SELECT pg_current_wal_lsn() AS b1 \gset
INSERT INTO m12_a SELECT g, repeat('x', 100)
FROM generate_series(1, 10000) g;
SELECT pg_current_wal_lsn() AS b2 \gset
INSERT INTO m12_b SELECT g, repeat('x', 100)
FROM generate_series(1, 10000) g;
SELECT pg_current_wal_lsn() AS b3 \gset
SELECT 'indeksli ko''p WAL yozdi: '
  || (pg_wal_lsn_diff(:'b3', :'b2') > pg_wal_lsn_diff(:'b2', :'b1'));
DROP TABLE m12_a, m12_b;

indeksli ko'p WAL yozdi: true

10. Loyiha qadami: kanon tajribasi, loyihaga kirmaydi

vazifalar sxemasiga o'zgarish yo'q — sozlamalar bazaga emas, serverga tegishli. Kanon nima qildi va nega loyihaga kirmadi:

• Kanon konteyneri sukut sozlamalar bilan (+ pg_stat_statements va track_io_timing) ishlaydi. O'quv loyihasining yuklamasi kichik — sozlash o'lchab bo'lmaydigan foyda beradi.
• ALTER SYSTEM tajribasi oxirida RESET bilan qaytarildi.
• Prod sozlamalari (shared_buffers, pg_hba.conf, parollar) — server va Docker Compose ishi (33-qism, deploy). Bu darsdagi bilim o'sha paytda kerak bo'ladi.

11. Real ishda

• Yangi server sozlash — birinchi soatdagi ish: shared_buffers, effective_cache_size, work_mem, max_wal_size, random_page_cost (SSD), pg_hba.conf. Managed bazada bularning ko'pi provayder tomonidan qo'yilgan (Managed va serverless Postgres).
• "Server xotirasi tugadi, Postgres o'ldi" (Linux'da OOM killer) — eng ko'p sabab: katta work_mem × ko'p ulanish.
• Monitoring — pg_stat_checkpointer, pg_stat_activity, pg_stat_statements (Monitoring va favqulodda vaziyatlar).
• Intervyu: "Postgres qanday arxitekturada ishlaydi?", "shared_buffers va work_mem farqi?", "WAL nima uchun kerak?".

Xulosa

• Postgres — postmaster + har ulanishga alohida backend jarayon + fon jarayonlari; ko'p ulanish — hovuz (pool) bilan.
• shared_buffers — umumiy kesh (sukut 128 MB, odatda RAM ~25 %); work_mem — har amal uchun (× amallar × ulanishlar).
• effective_cache_size — planner maslahati, xotira ajratmaydi.
• WAL — avval jurnal, keyin joyi; checkpoint — sahifalarni diskka yozish; synchronous_commit = off — kanonda 4.5× tez, oxirgi tranzaksiyalar tavakkali.
• context: postmaster — restart, sighup — reload, user — sessiya; ALTER SYSTEM → postgresql.auto.conf.
• pg_hba.conf — kim, qaysi bazaga, qayerdan, qanday; trust faqat lokal.

Keyingi dars: VACUUM, autovacuum va bloat — o'lik versiyalar, autovacuum chegaralari, VACUUM FULL qulfi va transaction ID wraparound.

Manbalar

• PostgreSQL 18 hujjati: Server Configuration — Resource Consumption (shared_buffers, work_mem, io_method) — 2026-10-25 da ko'rildi.
• PostgreSQL 18 hujjati: Reliability and the Write-Ahead Log, WAL Configuration.
• PostgreSQL 18 hujjati: The pg_hba.conf File, ALTER SYSTEM.
• PostgreSQL 18 hujjati: pgbench, pg_stat_checkpointer.
• PGTune: pgtune.leopard.in.ua — sinalmagan, boshlang'ich qiymatlar uchun.