Denormalizatsiya va hisoblangan maydonlar: counter cache, snapshot va trigger

Qisqacha: Denormalizatsiya — tezlik yoki qulaylik uchun ma'lumotni ataylab ikkinchi joyda saqlash: har vazifa yonida tayyor izohlar_soni, buyurtmadagi narx nusxasi, oldindan hisoblangan statistika. Uning narxi — sinxronlash: asl ma'lumot o'zgarsa, nusxa ham o'zgarishi kerak. Postgres'da buni trigger, generated ustun yoki REFRESH MATERIALIZED VIEW bajaradi — va har birining o'z tuzog'i bor.

Bu darsda

• Denormalizatsiya qachon arziydi va qachon zarar ekanini ajrata olasiz.
• Trigger nima ekanini tushunasiz va counter cache'ni trigger bilan yozasiz.
• Counter buzilganda uni topadigan va tuzatadigan so'rovni yozasiz.
• Generated ustun, snapshot (narx nusxasi) va materialized view'ni farqlaysiz.
• vazifalar bazasiga izohlar_soni va teglar sinxronini qo'shamiz (holat 36).

Oldin bilishingiz kerak: Normalizatsiya, UPSERT va MERGE, UPDATE va DELETE xavfsiz, Bog'lanishlar.

1. Nega bu kerak?

O'tgan darsda qoida o'rnatdik: har fakt — bir joyda. Endi uni ataylab buzamiz. Nega?

Tasavvur qiling: vazifalar ro'yxatida har vazifa yonida izoh belgisi va son turibdi — "izohlar: 3". Ro'yxatda 50 ta vazifa. Normal sxemada har biri uchun izohlarni sanash kerak: 50 ta count(*) yoki bitta katta GROUP BY. Bitta foydalanuvchi uchun bu sezilmaydi. Lekin ro'yxat sahifasi kuniga yuz minglab marta ochilsa, har safar bir xil sonni qayta sanash — behuda ish.

Yechim: sonni bir marta hisoblab, vazifa qatorining o'zida saqlash. Izoh qo'shilganda — +1, o'chirilganda — −1. O'qish bepul bo'ladi, yozish esa biroz qimmatlashadi.

O'xshatish: do'kon kassasidagi kunlik tushum. Har safar "bugun qancha sotdik?" deb so'ralganda hamma cheklarni qaytadan qo'shib chiqmaysiz. Kassa har sotuvda summani yangilab boradi. Lekin agar kimdir chekni kassadan o'tkazmasdan sotsa — summa noto'g'ri bo'ladi. Denormalizatsiyaning butun mohiyati va xavfi shu misolda.

2. Denormalizatsiya — ongli takrorlash

Denormalizatsiya (denormalization) — normal formadan ongli chekinish: ma'lumotni hisoblangan yoki nusxalangan holda ikkinchi joyda saqlash. "Ongli" so'zi muhim: bilmasdan qilingan takrorlash — normalizatsiya xatosi; bilib, sinxronlash rejasi bilan qilingani — denormalizatsiya.

Asosiy turlar:

Har birini ko'rib chiqamiz. Nimaga qarang: yozish yo'lida qo'shimcha qadam paydo bo'ladi, o'qish yo'lida esa hisob yo'qoladi.

flowchart LR
  I["INSERT izoh"] --> T["Trigger: izohlar_soni + 1"]
  T --> V["vazifalar qatori yangilandi"]
  R["Ro'yxat so'rovi"] --> V
  R -. "count(*) kerak emas" .-> X["izohlar jadvali"]

3. Counter cache: izohlar_soni

3.1 Ustun va boshlang'ich qiymat

Counter cache — bog'liq yozuvlar sonini ota-ona qatorida saqlash. Kanon sql/009-hisoblangan.sql boshi. Bu va keyingi kanon parchalarida kod o'zgarmagan — faqat uzun qatorlarni telefon ekraniga sig'ishi uchun bo'ldik va izohlarni qisqartirdik:

ALTER TABLE vazifalar
  ADD COLUMN izohlar_soni integer NOT NULL DEFAULT 0,
  ADD CONSTRAINT vazifalar_izohlar_soni_check
    CHECK (izohlar_soni >= 0);

UPDATE vazifalar v SET izohlar_soni = s.soni
FROM (SELECT vazifa_id, count(*) AS soni
      FROM izohlar GROUP BY vazifa_id) s
WHERE s.vazifa_id = v.id;

Uch qadam:

1. Ustun DEFAULT 0 bilan — mavjud qatorlarga 0 yoziladi (ALTER TABLE darsida ko'rganimizdek, bu faqat metadata o'zgarishi, tez).
2. CHECK (izohlar_soni >= 0) — counter hech qachon manfiy bo'lmasin. Bu himoya: trigger xatosi tufayli −1 chiqsa, baza darhol xato beradi.
3. Backfill — mavjud izohlarni sanab, bir marta yozish: UPDATE … FROM (UPDATE va DELETE).

3.2 Trigger nima

Endi har yangi izohda sonni yangilash kerak. Buni ilova kodida qilish mumkin: "izoh qo'shgach, UPDATE vazifalar …". Lekin unda har kod yo'li (API, admin panel, import skripti) buni eslab qolishi kerak. Bitta joyda unutildi — son noto'g'ri.

Trigger (trigger) — jadvalda biror hodisa (INSERT, UPDATE, DELETE) bo'lganda bazaning o'zi avtomatik chaqiradigan funksiya. O'xshatish: eshikdagi qo'ng'iroq. Kim eshikni ochmasin — mehmonmi, kuryermi — qo'ng'iroq o'zi chalinadi; har kirgan odamga "qo'ng'iroqni chal" deb aytish shart emas.

Trigger ikki qismdan iborat: funksiya (nima qilish) va trigger e'loni (qachon chaqirish). Funksiya PL/pgSQL tilida yoziladi — Postgres'ning ichki dasturlash tili: SQL + IF, o'zgaruvchilar, sikllar. Uni chuqur o'rganmaymiz, faqat trigger uchun kerakli qismini.

3.3 Counter trigger'i

Kanon 009 davomi:

CREATE FUNCTION izohlar_sonini_yangila() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    UPDATE vazifalar SET izohlar_soni = izohlar_soni + 1
    WHERE id = NEW.vazifa_id;
  ELSIF TG_OP = 'DELETE' THEN
    UPDATE vazifalar SET izohlar_soni = izohlar_soni - 1
    WHERE id = OLD.vazifa_id;
  ELSIF NEW.vazifa_id <> OLD.vazifa_id THEN
    -- izoh boshqa vazifaga ko'chirildi
    UPDATE vazifalar SET izohlar_soni = izohlar_soni - 1
    WHERE id = OLD.vazifa_id;
    UPDATE vazifalar SET izohlar_soni = izohlar_soni + 1
    WHERE id = NEW.vazifa_id;
  END IF;
  RETURN NULL;  -- AFTER trigger: qaytgan qiymat e'tiborsiz
END $$;

CREATE TRIGGER izohlar_soni_trg
AFTER INSERT OR DELETE OR UPDATE OF vazifa_id ON izohlar
FOR EACH ROW EXECUTE FUNCTION izohlar_sonini_yangila();

Qatorma-qator:

Uchinchi tarmoq (ELSIF) ko'pincha unutiladi: izoh boshqa vazifaga ko'chirilsa, birinchisidan −1, ikkinchisiga +1. Trigger va asosiy amal bitta tranzaksiyada: izoh yozilmasa, son ham o'zgarmaydi.

3.4 Amalda

Kanon holat 36 da tekshiruvdan boshlaymiz: counter haqiqiy son bilan mos kelmaydigan vazifalar bormi? Bu va keyingi so'rovlar kanon sorovlar/36-hisoblangan.sql dan — uzun qatorlarni bo'lib yozdik, o'z PostgreSQL 17.6 klasterimizda qayta ishga tushirdik, chiqish bir xil:

SELECT count(*) AS mos_kelmaydi
FROM vazifalar v
WHERE v.izohlar_soni <> (SELECT count(*) FROM izohlar i
                         WHERE i.vazifa_id = v.id);

 mos_kelmaydi
--------------
            0
(1 row)

Backfill to'g'ri ishlagan. Endi ro'yxat so'rovi — hech qanday JOIN yoki count siz:

SELECT id, matn, izohlar_soni
FROM vazifalar
WHERE izohlar_soni > 0
ORDER BY izohlar_soni DESC, id
LIMIT 4;

 id |                       matn                       | izohlar_soni
----+--------------------------------------------------+--------------
  1 | JavaScript kursining 26-qismini boshlash #o'qish |            3
  6 | Server zaxirasini tekshirish #ish #server        |            2
 10 | Mashinani texnik ko'rikka olib borish #mashina   |            2
 23 | Oshxonadagi jo'mrakni almashtirish #ta'mir #uy   |            2
(4 rows)

Trigger'ni sinaymiz: 1-vazifaga izoh qo'shamiz, 23-vazifaning ikkala izohini o'chiramiz:

INSERT INTO izohlar (vazifa_id, muallif_id, matn)
VALUES (1, 1, 'Yangi izoh');
DELETE FROM izohlar WHERE vazifa_id = 23 RETURNING id;
SELECT id, izohlar_soni FROM vazifalar WHERE id IN (1, 23);

INSERT 0 1
 id
----
 11
 12
(2 rows)

DELETE 2
 id | izohlar_soni
----+--------------
  1 |            4
 23 |            0
(2 rows)

3 → 4 va 2 → 0. Biz vazifalar ga tegmadik — trigger o'zi yangiladi.

Tekshirib ko'ring: Nega trigger BEFORE emas, AFTER bilan e'lon qilingan?

3.5 Counter qachon buziladi

Trigger kafolati bitta shartga bog'liq: trigger ishlashi kerak. Uni chetlab o'tishning qonuniy yo'li bor — session_replication_role = replica. Bu rejimda oddiy trigger'lar o'chadi; uni replikatsiya va ommaviy import vositalari ishlatadi (kanon katta to'plamni yuklashda aynan shunday qilgan). Kanon tajribasi:

SET session_replication_role = replica;
INSERT INTO izohlar (vazifa_id, muallif_id, matn)
VALUES (1, 1, 'Triggersiz izoh');
RESET session_replication_role;
SELECT id, izohlar_soni,
       (SELECT count(*) FROM izohlar WHERE vazifa_id = 1) AS haqiqiy
FROM vazifalar
WHERE id = 1;

SET
INSERT 0 1
RESET
 id | izohlar_soni | haqiqiy
----+--------------+---------
  1 |            4 |       5
(1 row)

Counter 4, haqiqatda 5. Hech qanday xato chiqmadi — xuddi kassadan o'tmagan chek. Diqqat: tashqi kalit tekshiruvi ham Postgres ichida trigger bilan ishlaydi — replica rejimida u ham o'chadi. Bu rejim faqat ma'lumot ishonchli bo'lgan import uchun.

Shuning uchun counter cache bilan doim qayta hisoblash so'rovi yoniga yoziladi:

UPDATE vazifalar v
SET izohlar_soni = (SELECT count(*) FROM izohlar i
                    WHERE i.vazifa_id = v.id)
WHERE id = 1
RETURNING id, izohlar_soni;

 id | izohlar_soni
----+--------------
  1 |            5
(1 row)

UPDATE 1

Real loyihalarda bunday so'rov rejalashtirilgan vazifa (cron) sifatida kechasi ishlaydi yoki importdan keyin qo'lda chaqiriladi. Yana bir xavf — ilova kodida UPDATE vazifalar SET izohlar_soni = 0 kabi qo'lda yozish; buni review'da to'xtatish kerak.

4. Teglar sinxroni: matndan jadvalga

4.1 Ikki joydagi bitta fakt

vazifalar dagi ikkinchi ongli takrorlash — teglar. Ular vazifa matnida (#uy) ham, vazifa_teglari jadvalida ham. Frontend matnni ko'rsatadi, teg bo'yicha qidiruv va sanash esa jadvaldan. UPSERT darsida teglarni bir marta to'ldirgan edik — lekin yangi vazifa qo'shilganda ular o'zi yangilanmasdi. 009 shu teshikni yopadi (kanon):

CREATE FUNCTION vazifa_teglarini_yangila() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO teglar (egasi_id, nom)
  SELECT NEW.egasi_id, t.nom FROM teglarni_ajrat(NEW.matn) AS t(nom)
  ON CONFLICT (egasi_id, nom) DO NOTHING;

  DELETE FROM vazifa_teglari vt
  WHERE vt.vazifa_id = NEW.id
    AND vt.teg_id NOT IN (
      SELECT tg.id
      FROM teglar tg JOIN teglarni_ajrat(NEW.matn) AS t(nom)
        ON tg.egasi_id = NEW.egasi_id AND tg.nom = t.nom);

  INSERT INTO vazifa_teglari (vazifa_id, teg_id)
  SELECT NEW.id, tg.id
  FROM teglar tg JOIN teglarni_ajrat(NEW.matn) AS t(nom)
    ON tg.egasi_id = NEW.egasi_id AND tg.nom = t.nom
  ON CONFLICT DO NOTHING;
  RETURN NULL;
END $$;

-- UPDATE OF matn: faqat SET ro'yxatida matn bo'lsa ishlaydi
CREATE TRIGGER vazifa_teglari_trg
AFTER INSERT OR UPDATE OF matn ON vazifalar
FOR EACH ROW EXECUTE FUNCTION vazifa_teglarini_yangila();

Funksiya uch qadamdan iborat:

1. Matndagi yangi teglarni teglar ga qo'shadi (bori — DO NOTHING).
2. Matnda endi yo'q teglarni ko'prikdan o'chiradi.
3. Yetishmayotgan bog'lanishlarni qo'shadi.

teglarni_ajrat — holat 23 dagi funksiya, u frontend'dagi teg regex'ining Postgres nusxasi va teglarni kichik harfga o'giradi.

4.2 Amalda

23-vazifaning teglari, so'ng matn o'zgarishi:

SELECT t.nom
FROM vazifa_teglari vt JOIN teglar t ON t.id = vt.teg_id
WHERE vt.vazifa_id = 23
ORDER BY 1;
UPDATE vazifalar
SET matn = replace(matn, '#uy', '#shoshilinch')
WHERE id = 23;
SELECT t.nom
FROM vazifa_teglari vt JOIN teglar t ON t.id = vt.teg_id
WHERE vt.vazifa_id = 23
ORDER BY 1;

   nom
---------
 #ta'mir
 #uy
(2 rows)

UPDATE 1
     nom
--------------
 #shoshilinch
 #ta'mir
(2 rows)

#uy ketdi, #shoshilinch keldi — faqat matnni yangiladik. Yangi vazifa ham shunday, katta harfli teg esa kichigiga aylanadi:

INSERT INTO vazifalar (egasi_id, matn)
VALUES (8, 'Kitob o''qish #O''qish #kitob')
RETURNING id;
SELECT t.egasi_id, t.nom
FROM teglar t WHERE t.egasi_id = 8 ORDER BY 2;

 id
----
 48
(1 row)

INSERT 0 1
 egasi_id |   nom
----------+---------
        8 | #kitob
        8 | #o'qish
(2 rows)

Shahnozaning (id 8) birinchi vazifasi va birinchi teglari — #O'qish → #o'qish.

4.3 UPDATE OF matn — narxni kamaytirish

Trigger arzon emas: uchta so'rov, har vazifa yozilganda. Lekin vazifaning eng ko'p o'zgaradigan maydoni — bajarildi (belgilash), matn esa kam o'zgaradi. UPDATE OF matn trigger'ni faqat SET ro'yxatida matn bo'lganda chaqiradi. Buni EXPLAIN ANALYZE ko'rsatadi (EXPLAIN ANALYZE'ni o'qish darsida batafsil). Kanon chiqishi (PostgreSQL 18.6):

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
UPDATE vazifalar
SET bajarildi = true, bajarilgan_vaqt = '2026-10-20 12:00+05'
WHERE id = 9;

                       QUERY PLAN
--------------------------------------------------------
 Update on vazifalar (actual rows=0.00 loops=1)
   Buffers: shared hit=5
   ->  Seq Scan on vazifalar (actual rows=1.00 loops=1)
         Filter: (id = 9)
         Rows Removed by Filter: 47
         Buffers: shared hit=2
(6 rows)

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
UPDATE vazifalar
SET matn = 'Onamga dori olib borish #oila #dorixona'
WHERE id = 9;

                       QUERY PLAN
--------------------------------------------------------
 Update on vazifalar (actual rows=0.00 loops=1)
   Buffers: shared hit=5
   ->  Seq Scan on vazifalar (actual rows=1.00 loops=1)
         Filter: (id = 9)
         Rows Removed by Filter: 47
         Buffers: shared hit=2
 Trigger vazifa_teglari_trg: calls=1
(7 rows)

Farq oxirgi qatorda: ikkinchisida Trigger vazifa_teglari_trg: calls=1. Birinchisida trigger umuman chaqirilmadi. (EXPLAIN ANALYZE UPDATE'ni haqiqatan bajaradi. Bizning PostgreSQL 17.6 da chiqish biroz boshqacha — rows=1, Buffers qatorlarisiz: PG 18 da ANALYZE bilan Buffers ma'lumoti avtomatik qo'shiladi (postgresql.org/docs/18, "EXPLAIN"). Trigger qatori ikkalasida bir xil.)

Tekshirib ko'ring: Ilova vazifani saqlashda har doim hamma maydonlarni yuboradi: UPDATE vazifalar SET matn = $1, bajarildi = $2, … WHERE id = $3. Teglar trigger'i qachon chaqiriladi?

5. Boshqa hisoblangan maydonlar

5.1 Generated ustun — bitta qator ichida

Ba'zan hisob faqat o'sha qatordagi ustunlardan: jami = soni × narx. Trigger kerak emas — Postgres'da generated ustun (generated column) bor: qiymatini baza o'zi formuladan hisoblaydi va qo'lda yozishga ruxsat bermaydi. Biz o'z PostgreSQL 17.6 klasterimizda sinadik:

BEGIN;
CREATE TABLE t36_qator (
  soni int NOT NULL,
  narx int NOT NULL,
  jami int GENERATED ALWAYS AS (soni * narx) STORED
);
INSERT INTO t36_qator (soni, narx) VALUES (2, 35000);
UPDATE t36_qator SET soni = 3;
SELECT * FROM t36_qator;
INSERT INTO t36_qator (soni, narx, jami) VALUES (1, 5000, 5000);

BEGIN
CREATE TABLE
INSERT 0 1
UPDATE 1
 soni | narx  |  jami
------+-------+--------
    3 | 35000 | 105000
(1 row)

ERROR:  cannot insert a non-DEFAULT value into column "jami"
DETAIL:  Column "jami" is a generated column.

Xatodan keyin tranzaksiya faqat ROLLBACK; ni qabul qiladi — uni yozib, sinov jadvalini olib tashlaymiz.

soni o'zgardi — jami o'zi qayta hisoblandi (3 × 35 000). Qo'lda yozishga urinish — xato: "generated ustunga DEFAULT bo'lmagan qiymat yozib bo'lmaydi". Sinxronlash xatosi umuman mumkin emas — bu generated ustunning eng katta afzalligi.

STORED — qiymat diskda saqlanadi. PostgreSQL 18 da VIRTUAL ham qo'shildi va sukut shu bo'ldi: qiymat saqlanmaydi, o'qishda hisoblanadi (postgresql.org/docs/18, "Generated Columns"). Bizning 17.6 da faqat STORED; virtual variantni sinamadik. Cheklov: formula faqat o'sha qatordagi ustunlar va o'zgarmas (IMMUTABLE) funksiyalardan iborat bo'ladi, ichki so'rov (subquery) taqiqlangan — boshqa jadvalga qarab bo'lmaydi (hujjat, "Generated Columns"). Shuning uchun izohlar_soni generated ustun bo'la olmaydi.

5.2 Snapshot — ataylab "muzlatilgan" nusxa

Normalizatsiya darsining oxirgi mashqida savol qoldi: osh qimmatlasa, eski buyurtma summasi ham o'zgaradimi? Normal sxemada — ha, chunki narx bitta joyda. Lekin bu noto'g'ri: mijoz 35 000 dan to'lagan. Yechim — snapshot: buyurtma paytidagi narxni buyurtma qatoriga nusxalash. Biz sinadik:

BEGIN;
CREATE TABLE t36_taomlar (nom text PRIMARY KEY, narx int NOT NULL);
CREATE TABLE t36_qatorlar (
  buyurtma_id int,
  taom        text REFERENCES t36_taomlar,
  soni        int NOT NULL,
  narx        int NOT NULL,
  PRIMARY KEY (buyurtma_id, taom)
);
INSERT INTO t36_taomlar VALUES ('osh', 35000), ('manti', 30000);
INSERT INTO t36_qatorlar (buyurtma_id, taom, soni, narx)
SELECT 1, nom, 2, narx FROM t36_taomlar WHERE nom = 'osh';
UPDATE t36_taomlar SET narx = 40000 WHERE nom = 'osh';
SELECT q.buyurtma_id,
       q.soni * q.narx AS snapshot_bilan,
       q.soni * t.narx AS hozirgi_narx_bilan
FROM t36_qatorlar q JOIN t36_taomlar t ON t.nom = q.taom;
ROLLBACK;

BEGIN
CREATE TABLE
CREATE TABLE
INSERT 0 2
INSERT 0 1
UPDATE 1
 buyurtma_id | snapshot_bilan | hozirgi_narx_bilan
-------------+----------------+--------------------
           1 |          70000 |              80000
(1 row)

ROLLBACK

Snapshot bilan — 70 000 so'm (mijoz to'lagani). Hozirgi narx bilan — 80 000 (yolg'on tarix). Snapshot'ni sinxronlash kerak emas — aksincha, uni hech qachon yangilamaslik kerak. Rasmiy jihatdan bu ham takrorlash, lekin aslida boshqa fakt: "shu buyurtmadagi narx". Xuddi shunday: yetkazish manzili, chekdagi soliq stavkasi, shartnomadagi foiz.

5.3 Materialized view — tayyor hisobot

Ko'rinish (view) — saqlangan so'rov: unga jadval kabi murojaat qilinadi, lekin har safar so'rov qayta bajariladi. Materialized view — natijasi diskka yozilgan ko'rinish: o'qish tez, lekin ma'lumot eskiradi va qo'lda yangilanadi. Kanon tajribasi:

CREATE MATERIALIZED VIEW t36_statistika AS
  SELECT egasi_id, count(*) AS jami,
         count(*) FILTER (WHERE bajarildi) AS bajarilgan
  FROM vazifalar
  GROUP BY egasi_id;
SELECT * FROM t36_statistika WHERE egasi_id = 8;
INSERT INTO vazifalar (egasi_id, matn) VALUES (8, 'Yana bir vazifa');
SELECT * FROM t36_statistika WHERE egasi_id = 8;

SELECT 8
 egasi_id | jami | bajarilgan
----------+------+------------
        8 |    1 |          0
(1 row)

INSERT 0 1
 egasi_id | jami | bajarilgan
----------+------+------------
        8 |    1 |          0
(1 row)

Shahnoza ikkinchi vazifa qo'shdi, statistika esa hali "1" deydi — eskirgan. Yangilash:

REFRESH MATERIALIZED VIEW t36_statistika;
SELECT * FROM t36_statistika WHERE egasi_id = 8;
DROP MATERIALIZED VIEW t36_statistika;

REFRESH MATERIALIZED VIEW
 egasi_id | jami | bajarilgan
----------+------+------------
        8 |    2 |          0
(1 row)

DROP MATERIALIZED VIEW

Materialized view biroz kechikish mumkin bo'lgan joylar uchun qulay. Masalan: admin dashboard, kunlik hisobot, reyting. REFRESH butun so'rovni qayta bajaradi va shu vaqt o'qishni qulflaydi. REFRESH … CONCURRENTLY o'qishni to'smaydi, lekin view'da noyob indeks talab qiladi (hujjat bo'yicha; sinamadik).

6. Qachon arziydi?

Denormalizatsiya — optimallashtirish. Optimallashtirishning qoidasi: avval o'lchang. Bitta qatorli count(*) indeks bilan millisekunddan kam vaqt oladi (Indeks nima darsida o'lchaymiz). Counter'ni faqat shu son haqiqatan ko'p o'qilsa va sanash sekin bo'lsa qo'shing.

Counter'ning yashirin narxi: har izoh yozish vazifa qatorini ham yangilaydi. Bitta vazifaga bir vaqtda o'nlab izoh yozilsa, ular shu qator qulfi uchun navbatga turadi. Bu ta'sirni qulflar darsida o'lchaymiz.

7. Ko'p uchraydigan xatolar

1. O'lchamasdan denormallashtirish. "Tezroq bo'ladi" deb counter qo'shildi — lekin sahifa kuniga 100 marta ochiladi. Natija: murakkablik bor, foyda yo'q.

2. Counter'ni ilova kodida yangilash. API yo'lida bor, admin panelda yo'q, import skriptida yo'q — son asta-sekin buziladi. Trigger yoki generated ustun — bitta joyda kafolat.

3. UPDATE tarmog'ini unutish. Trigger INSERT va DELETE ni hisoblaydi, lekin izoh boshqa vazifaga ko'chirilsa (UPDATE OF vazifa_id) — ikkala vazifadagi son noto'g'ri.

4. Qayta hisoblash so'rovi yo'q. Counter qachondir buziladi (import, qo'lda tuzatish, replica rejimi). Uni topadigan va tuzatadigan so'rov oldindan yozilgan bo'lsin.

5. Snapshot'ni "tuzatish". "Buyurtmadagi narx eski ekan" deb hamma buyurtmalarni yangi narxga yangilash — moliyaviy tarixni buzish.

6. Materialized view'ni yangilashni unutish. Hisobot bir hafta oldingi raqamni ko'rsatadi va hech kim sezmaydi. REFRESH rejalashtirilgan vazifa bo'lsin va hisobotda "yangilangan vaqt" ko'rinsin.

8. Mashqlar

Holat 36 da. Hammasi BEGIN … ROLLBACK ichida.

1-mashq (oson)

Counter'ni ataylab buzing: SET LOCAL session_replication_role = replica bilan 6-vazifaning izohlarini o'chiring va rejimni qaytaring (SET LOCAL session_replication_role = origin). Keyin izohlar_soni haqiqiy sondan farq qiladigan hamma vazifalarni chiqaring (id, izohlar_soni, haqiqiy) va faqat ularni qayta hisoblang. Ishora: LEFT JOIN + GROUP BY + HAVING; SET LOCAL faqat shu tranzaksiyada amal qiladi.

BEGIN;
SET LOCAL session_replication_role = replica;
DELETE FROM izohlar WHERE vazifa_id = 6;
SET LOCAL session_replication_role = origin;
SELECT v.id, v.izohlar_soni, count(i.id) AS haqiqiy
FROM vazifalar v
LEFT JOIN izohlar i ON i.vazifa_id = v.id
GROUP BY v.id
HAVING v.izohlar_soni <> count(i.id);
UPDATE vazifalar v
SET izohlar_soni = (SELECT count(*) FROM izohlar i
                    WHERE i.vazifa_id = v.id)
WHERE v.izohlar_soni <> (SELECT count(*) FROM izohlar i
                         WHERE i.vazifa_id = v.id);
ROLLBACK;

BEGIN
SET
DELETE 2
SET
 id | izohlar_soni | haqiqiy
----+--------------+---------
  6 |            2 |       0
(1 row)

UPDATE 1
ROLLBACK

2-mashq (o'rta)

vazifalar ga generated ustun qo'shing: matn_uzunligi — char_length(matn), STORED. Dilnozaning (egasi_id = 2) eng uzun matnli 3 ta vazifasini (id, matn_uzunligi) chiqaring, keyin 5-vazifa matnini 'Qisqa #ingliz' ga o'zgartirib, uzunlik o'zi yangilanganini ko'rsating. Ishora: «Generated ustun — bitta qator ichida» bo'limi; ALTER TABLE … ADD COLUMN — ALTER TABLE.

BEGIN;
ALTER TABLE vazifalar
  ADD COLUMN matn_uzunligi int
  GENERATED ALWAYS AS (char_length(matn)) STORED;
SELECT id, matn_uzunligi FROM vazifalar
WHERE egasi_id = 2
ORDER BY matn_uzunligi DESC, id
LIMIT 3;
UPDATE vazifalar SET matn = 'Qisqa #ingliz' WHERE id = 5;
SELECT id, matn_uzunligi FROM vazifalar WHERE id = 5;
ROLLBACK;

BEGIN
ALTER TABLE
 id | matn_uzunligi
----+---------------
  5 |            45
 26 |            41
 14 |            35
(3 rows)

UPDATE 1
 id | matn_uzunligi
----+---------------
  5 |            13
(1 row)

ROLLBACK

3-mashq (qiyin)

Teglar uchun counter cache: teglar.vazifalar_soni (sukut 0), Dilnoza (egasi_id = 2) teglari uchun backfill va vazifa_teglari dagi INSERT/DELETE da yangilaydigan trigger. Keyin Dilnozaga 'Gilam yuvish #gilam #uy' vazifasini qo'shing va uning matnida #gilam ni #tozalash ga almashtiring — teglar soni o'zi o'zgarsin. Oxirida Dilnozaning counter'i haqiqiy son bilan mos kelmaydigan teglari sonini chiqaring (0 bo'lishi kerak). Ishora: izohlar_sonini_yangila funksiyasi namuna; matn almashtirish — replace(matn, 'eski', 'yangi'); ikki trigger zanjir bo'lib ishlaydi — vazifa trigger'i ko'prikka yozadi, ko'prik trigger'i tegni yangilaydi.

BEGIN;
ALTER TABLE teglar
  ADD COLUMN vazifalar_soni int NOT NULL DEFAULT 0;
UPDATE teglar t
SET vazifalar_soni = (SELECT count(*) FROM vazifa_teglari vt
                      WHERE vt.teg_id = t.id)
WHERE t.egasi_id = 2;
CREATE FUNCTION teg_sonini_yangila() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    UPDATE teglar SET vazifalar_soni = vazifalar_soni + 1
    WHERE id = NEW.teg_id;
  ELSE
    UPDATE teglar SET vazifalar_soni = vazifalar_soni - 1
    WHERE id = OLD.teg_id;
  END IF;
  RETURN NULL;
END $$;
CREATE TRIGGER teg_soni_trg
AFTER INSERT OR DELETE ON vazifa_teglari
FOR EACH ROW EXECUTE FUNCTION teg_sonini_yangila();
INSERT INTO vazifalar (egasi_id, matn)
VALUES (2, 'Gilam yuvish #gilam #uy');
SELECT nom, vazifalar_soni FROM teglar
WHERE egasi_id = 2 AND nom IN ('#gilam', '#uy', '#tozalash')
ORDER BY nom;
UPDATE vazifalar
SET matn = replace(matn, '#gilam', '#tozalash')
WHERE egasi_id = 2 AND matn LIKE 'Gilam yuvish%';
SELECT nom, vazifalar_soni FROM teglar
WHERE egasi_id = 2 AND nom IN ('#gilam', '#uy', '#tozalash')
ORDER BY nom;
SELECT count(*) AS mos_kelmaydi
FROM teglar t
WHERE t.egasi_id = 2
  AND t.vazifalar_soni <> (SELECT count(*) FROM vazifa_teglari vt
                           WHERE vt.teg_id = t.id);
ROLLBACK;

BEGIN
ALTER TABLE
UPDATE 6
CREATE FUNCTION
CREATE TRIGGER
INSERT 0 1
  nom   | vazifalar_soni
--------+----------------
 #gilam |              1
 #uy    |              1
(2 rows)

UPDATE 1
    nom    | vazifalar_soni
-----------+----------------
 #gilam    |              0
 #tozalash |              1
 #uy       |              1
(3 rows)

 mos_kelmaydi
--------------
            0
(1 row)

ROLLBACK

9. SQL qadami

Holat 34 → holat 36. Bitta fayl: sql/009-hisoblangan.sql — izohlar_soni (CHECK ≥ 0, backfill), izohlar_soni_trg, vazifa_teglari_trg (to'liq matni yuqorida, «Counter cache» va «Teglar sinxroni» bo'limlarida). Fayl boshidagi kanon izohi qarorni o'zi tushuntiradi (qisqartirib):

-- 009-hisoblangan.sql — denormalizatsiya, trigger bilan sinxron.
-- 1) izohlar_soni — counter cache: ro'yxatda COUNT(*) qilmaslik.
-- 2) vazifa_teglari — matndagi #teglardan avtomatik.
-- Narxi: har izoh vazifa qatorini ham yangilaydi (qulf),
-- matn o'zgarsa teglar qayta yoziladi.

Bazani noldan qurib, «Counter qachon buziladi» bo'limidagi mos_kelmaydi so'rovi 0 berishini tekshiring. Commit:

git add sql/009-hisoblangan.sql
git commit -m "db: izohlar_soni (counter cache) va teglar sinxroni — trigger'lar (009)"

Muhim qoida: 009 qo'llangandan keyin o'zgartirilmaydi. Agar trigger'da xato topilsa — yangi fayl (0NN-…sql) bilan CREATE OR REPLACE FUNCTION. Kanon holatlari shu intizom bilan qurilgan: holat 36 = 001…009, keyingi holatlar faqat ustiga qo'shadi. Bu migratsiya intizomining asosi — vositalarini (Prisma Migrate va boshqalar) 27-qismda o'rganamiz.

10. Real ishda

• Ijtimoiy tarmoqlar. Post yonidagi "1,2 ming yoqtirish", "340 izoh" — tayyor son, har ochilishda qayta sanalmaydi. Juda katta hajmda bunday sonlar ko'pincha kechikish bilan yangilanadi (bu ularning ichki tuzilishi, biz sinamadik).
• Rails, Django, Prisma. Rails'da counter_cache: true — aynan shu naqsh, faqat ilova darajasida (shuning uchun «Ko'p uchraydigan xatolar» dagi 2-xavf unga ham tegishli).
• E-commerce va buxgalteriya. Buyurtma, chek, hisob-faktura — har doim snapshot: narx, soliq, manzil nusxasi.
• Analitika. Kunlik hisobotlar — materialized view yoki alohida yig'ma jadvallar, kechasi yangilanadi.
• Intervyuda: "Denormalizatsiya qachon kerak?", "counter'ni qanday sinxron saqlaysiz?" — javobda o'lchov, trigger va qayta hisoblash so'rovini tilga oling.

Xulosa

• Denormalizatsiya — ongli takrorlash: o'qish tezlashadi, yozish va sinxronlash qimmatlashadi.
• Trigger — jadval hodisasida baza o'zi chaqiradigan funksiya; AFTER … FOR EACH ROW, NEW/OLD, TG_OP.
• Counter cache uchun doim tekshiruv va qayta hisoblash so'rovi yozing — replica rejimi trigger'larni o'chiradi.
• UPDATE OF ustun trigger chaqiruvini kamaytiradi (lekin qiymat emas, SET ro'yxati tekshiriladi).
• Bir qator ichidagi hisob — generated ustun; tarixiy qiymat — snapshot; hisobot — materialized view + REFRESH.

Keyingi dars: Modellash naqshlari — daraxtlar (adjacency list, ltree, closure table), polimorf bog'lanish va moslashuvchan atributlar (EAV va jsonb).

Manbalar

• PostgreSQL 18 hujjati: "Triggers" va "CREATE TRIGGER", "PL/pgSQL — Trigger Functions", "Generated Columns", "CREATE MATERIALIZED VIEW", "REFRESH MATERIALIZED VIEW", session_replication_role — postgresql.org/docs/18, 2026-10-24 da ko'rildi.