VIEW va MATERIALIZED VIEW — farqi, REFRESH

~20 daq · PostgreSQL · Funksiyalar va Protseduralar

Bir xil murakkab so'rov ko'p joyda takrorlanmoqda — faol foydalanuvchilarning oxirgi buyurtmalari bilan:

SELECT f.ism, f.email, b.mahsulot, b.summa
FROM foydalanuvchilar f
JOIN buyurtmalar b ON f.id = b.foydalanuvchi_id
WHERE f.faol = TRUE
  AND b.sana > NOW() - INTERVAL '30 days';

Bu so'rovni har joyda qayta yozish — kod takrori va xatoga moyil. VIEW bu so'rovga nom berib, oddiy jadval kabi ishlatishga imkon beradi.

VIEW nima?

VIEW — saqlangan so'rov. Jadvaldek ko'rinadi, lekin ichida so'rov yashiringan. Ma'lumot saqlamaydi — har chaqirilganda so'rov bajariladi.

CREATE VIEW faol_buyurtmalar AS
SELECT f.ism, f.email, b.mahsulot, b.summa
FROM foydalanuvchilar f
JOIN buyurtmalar b ON f.id = b.foydalanuvchi_id
WHERE f.faol = TRUE
  AND b.sana > NOW() - INTERVAL '30 days';

Endi oddiy jadval kabi ishlatiladi:

SELECT * FROM faol_buyurtmalar;
SELECT * FROM faol_buyurtmalar WHERE summa > 100000;
SELECT ism, SUM(summa) FROM faol_buyurtmalar GROUP BY ism;

VIEW ning afzalliklari

1. Takrorni kamaytiradi     — bir so'rov, ko'p joyda
2. Xavfsizlik               — foydalanuvchiga jadval o'rniga view berish
3. Murakkablikni yashiradi   — oddiy nom, murakkab so'rov ichida
4. O'zgarishlarni izolyatsiya — jadval o'zgarsa, view yangilanadi

VIEW ni yangilash va o'chirish

-- O'chirish
DROP VIEW faol_buyurtmalar;

-- Agar mavjud bo'lsa o'chirish
DROP VIEW IF EXISTS faol_buyurtmalar;

-- Almashtirish (o'chirib, qayta yaratish)
CREATE OR REPLACE VIEW faol_buyurtmalar AS
SELECT ...; -- yangi so'rov

MATERIALIZED VIEW — natijani saqlash

Oddiy VIEW har chaqirilganda so'rovni bajaradi. Murakkab so'rov bo'lsa — sekin. MATERIALIZED VIEW natijani diskda saqlaydi — tez o'qiladi, lekin yangilanmaydigan.

CREATE MATERIALIZED VIEW oylik_statistika AS
SELECT
  DATE_TRUNC('month', sana) AS oy,
  COUNT(*)                  AS buyurtmalar,
  SUM(summa)                AS jami_summa
FROM buyurtmalar
GROUP BY DATE_TRUNC('month', sana);

Oddiy jadval kabi o'qiladi:

SELECT * FROM oylik_statistika ORDER BY oy DESC;

REFRESH — materialized view ni yangilash

Asl jadval o'zgarganda materialized view avtomatik yangilanmaydi — qo'lda yangilash kerak:

REFRESH MATERIALIZED VIEW oylik_statistika;

Yangilash vaqtida view bloklanadi. Bloklansiz yangilash:

REFRESH MATERIALIZED VIEW CONCURRENTLY oylik_statistika;
-- CONCURRENTLY uchun unikal indeks kerak

Avtomatik yangilash — pg_cron bilan

-- Har kecha yarim tunda yangilash (pg_cron extension)
SELECT cron.schedule('0 0 * * *', 'REFRESH MATERIALIZED VIEW oylik_statistika');

VIEW vs MATERIALIZED VIEW

                  VIEW              MATERIALIZED VIEW
────────────────  ────────────────  ─────────────────────
Ma'lumot saqlash  ❌ yo'q            ✅ diskda saqlanadi
Tezlik            so'rovga qarab    ✅ tez (kesh)
Yangilik          ✅ har doim yangi  ❌ REFRESH kerak
Indeks            ❌                 ✅ qo'shish mumkin
Qachon            oddiy so'rovlar   sekin, kam o'zgaradigan

Tez-tez uchraydigan xatolar

Materialized view ni yangilashni unutish:

-- Asl jadvalga yangi ma'lumot qo'shildi
INSERT INTO buyurtmalar ...;

-- Materialized view hali eski natijani ko'rsatadi
SELECT * FROM oylik_statistika; -- yangi buyurtma yo'q!

-- Yangilash kerak
REFRESH MATERIALIZED VIEW oylik_statistika;

View da ORDER BY ishlatish:

CREATE VIEW tartiblangan AS
SELECT * FROM mahsulotlar ORDER BY narxi;
-- Bu ishlaydi, lekin view dan so'rovda tartib kafolatlanmaydi
-- ORDER BY view ni ishlatganda yozilsin

Xulosa

-- Oddiy view — har doim yangi, sekinroq
CREATE VIEW nom AS SELECT ...;
SELECT * FROM nom;
DROP VIEW nom;
CREATE OR REPLACE VIEW nom AS SELECT ...;

-- Materialized view — tez, lekin yangilash kerak
CREATE MATERIALIZED VIEW nom AS SELECT ...;
REFRESH MATERIALIZED VIEW nom;
REFRESH MATERIALIZED VIEW CONCURRENTLY nom;

VIEW — murakkab so'rovga nom. MATERIALIZED VIEW — natijani keshlab, tez o'qish uchun.

Keyingi dars

37-dars: Index turlari — B-tree, Hash, GIN, GiST, BRIN