WisarWisar
Dasturlash kitobi/6-QISM — Database19 daqiqa

6.15-bob: Connection pooling va ER modeling (DB dizayni)

6-QISM — Ma'lumotlar bazasi (Database) · 15-mavzu (yakuniy)

1. Kirish va motivatsiya

6-QISMning yakuniy bobiga keldik. Butun qism davomida DB asoslari 6.1-bob, MongoDB (6.2-6.3), SQL (6.4-6.10), ORM'lar (6.11-6.14) — hammasini o'rgandik. Bu bob ikki muhim, yakunlovchi mavzuni birlashtiradi: connection pooling (DB ulanishlarini samarali boshqarish — chuqur) va ER modeling (Entity-Relationship — DB'ni boshidan to'g'ri loyihalash). Birinchisi — production tezligi va barqarorligi; ikkinchisi — butun DB dizaynining poydevori (6-QISMni umumlashtiradi: dizayndan optimizatsiyagacha).

Connection pooling ni 6.5/6.6 da ishlatdik (ORM'lar ham ichida qo'llaydi). Lekin u chuqurroq tushunishga arziydi: DB ulanishi — qimmat resurs (har ulanish CPU/RAM oladi); ko'p foydalanuvchili ilovada ulanishlarni to'g'ri boshqarmasa — DB ulanishlardan tugaydi (production'da eng ko'p uchraydigan falokat). Pool — ulanishlarni qayta ishlatadi; lekin uning hajmini to'g'ri sozlash, va ulkan masshtabda PgBouncer kabi tashqi pooler — alohida bilim.

ER modeling — DB'ni kod yozishdan oldin to'g'ri loyihalash. Ko'p loyiha shoshib, o'ylamasdan jadval yaratadi keyinroq muammo (noto'g'ri bog'lanish, takror — qayta yozish qimmat). ER (Entity-Relationship) diagrammasi — biznes talablarini entity (mavjudot), atribut, bog'lanish (cardinality)ga aylantirib, vizual loyihalash. Bu — normalizatsiya 6.9-bob, relations 6.1-bob — hammasining amaliy birlashmasi. Yaxshi DB dizayni — yaxshi ilovaning yarmidan ko'pi.

O'xshatish 1 (pool): DB ulanishi — restoran stoli. Har mijozga (so'rov) alohida yangi stol yasash (har so'rovda ulanish) — bemaqsad. Pool — tayyor stollar to'plami: mijoz keladi, bo'sh stolga o'tiradi, ketganda stol bo'shaydi (qayta ishlatiladi). Stollar soni (pool hajmi) cheklangan — juda kam (navbat), juda ko'p (joy/xizmat yetmaydi). To'g'ri son — kalit.

O'xshatish 2 (ER modeling): ER diagramma — uy qurishdan oldingi arxitektura chizmasi. Chizmasiz g'isht terish (o'ylamasdan jadval) — devor qiyshiq, xona noto'g'ri (qayta buzish). Chizma (ER) — xonalar (entity), eshik-derazalar (atribut), xonalar orasidagi yo'laklar (bog'lanish) — oldindan rejalashtirilgan. Keyin qurish (kod) — aniq, xatosiz.

Nega muhim?

  • Production barqarorligi — pool noto'g'ri "too many connections" (DB qulaydi).
  • Tezlik — pool ulanishni qayta ishlatadi (har so'rovda ulanmaslik).
  • DB dizayni poydevori — ER modeling to'g'ri loyihalash (qayta yozishni oldini oladi).
  • 6-QISM yakuni — dizayndan (ER) optimizatsiyagacha (pool) — hammasi birga.

2. Nazariya — chuqur tushuntirish

2.1. DB ulanishi — qimmat resurs

Har DB ulanishi (connection) — qimmat (mojoauth):

text
  Bir ulanish:
  - DB serverda jarayon/thread (PostgreSQL — har ulanish alohida jarayon!)
  - CPU + RAM (har ulanish ~5-10MB)
  - Ulanish o'rnatish vaqti (TCP, autentifikatsiya — millisekund)

   DB'da MAX ulanish chegarasi bor (PostgreSQL default ~100)
   Chegaradan oshsa: "too many connections" (yangi so'rov rad — falokat)

Asosiy muammo: DB ulanishlari cheklangan (max_connections). Har so'rovda yangi ulanish — (1) sekin (o'rnatish vaqti), (2) tez tugaydi (chegara). Ko'p foydalanuvchili ilovada bu — production falokat (DB qulaydi).

2.2. Connection pool — yechim (chuqur)

Connection pool — oldindan ochilgan ulanishlar to'plami, qayta ishlatiladigan (6.5: 2.5):

text
  Pool (masalan 10 ulanish):
  ┌──────────────────────────────┐
  │  [bo'sh] [band] [bo'sh] ...   │   10 ta tayyor ulanish
  └──────────────────────────────┘
  So'rov keladi  bo'sh ulanishni OLADI  ishlatadi  QAYTARADI (bo'shaydi)
  Bo'sh yo'q bo'lsa  kutadi (navbat) yoki xato (sozlamaga qarab)

   1000 so'rov, lekin faqat 10 ulanish (qayta ishlatish — 2.1 muammosini hal)

Pool mohiyati: ulanishni o'rnatib qo'yadi (bir marta), qayta ishlatadi (har so'rovda emas). Ulanish soni cheklangan (pool hajmi), lekin so'rovlar ko'p — ulanishlar navbatda almashinadi. Bu — tezlik + barqarorlik (DB chegarasidan oshmaydi).

2.3. Pool hajmini sozlash (eng muhim qaror)

Pool hajmi (pool size) — juda muhim parametr (mojoauth/pgbouncer):

text
  Juda KICHIK pool:
   so'rovlar bo'sh ulanish kutadi (sekin, navbat uzun)

  Juda KATTA pool:
   DB ko'p ulanish bilan band (CPU/RAM isrof, kontekst almashinuvi sekin)
   DB max_connections'dan oshishi mumkin (xato)

  Optimal formula (pgbouncer):
  pool_size ≈ (cpu_cores * 2) + disk_count
   masalan 4 yadro: ~8-10 (boshlang'ich)

"Ko'proq pool = tezroq" — NOTO'G'RI: ko'p ulanish DB'ni sekinlashtiradi (CPU kontekst almashinuvi). Optimal — kichik, samarali pool (cpu*2 atrofida). OLTP ilova uchun 10-30 (manba). O'lchab sozlash (monitoring — 2.10).

2.4. Pool sozlamalari (parametrlar)

text
  max (connectionLimit)  — maksimal ulanish (pool hajmi — 2.3)
  min                    — minimal (doim ochiq — tez javob)
  idleTimeout            — bo'sh ulanishni yopish vaqti
  acquireTimeout         — bo'sh ulanish kutish vaqti (oshsa — xato)
  connectionTimeout      — ulanish o'rnatish vaqti
js
// pg 6.6-bob yoki ORM pool sozlamasi
const pool = new Pool({
  max: 10,                          // max ulanish (2.3)
  min: 2,                           // min (doim tayyor)
  idleTimeoutMillis: 30000,         // 30s bo'sh  yop
  connectionTimeoutMillis: 5000,    // 5s ulanmasa  xato
});

2.5. Ko'p instansiya muammosi (masshtab)

Bir nechta ilova instansiyasi (server — 10.2) bo'lsa, pool muammosi:

text
  Muammo:
  3 ilova instansiyasi × har biri 10 pool = 30 ulanish
  10 instansiya × 10 = 100 ulanish  DB max'ga yetadi!

   Ko'p instansiyada har biri o'z pool'iga ega (jami ko'payadi)
   DB max_connections'dan oshishi mumkin

Ko'p server (horizontal scale — 9, 10) — har biri pool ochadi jami ulanish ko'payadi. DB chegarasi bir xil (max_connections). Yechim: pool hajmini hisoblash (instansiya × pool ≤ max), yoki tashqi pooler (PgBouncer — 2.6).

2.6. PgBouncer (tashqi connection pooler)

PgBouncer — PostgreSQL uchun tashqi, markaziy connection pooler (pgbouncer.org):

text
  Ilova pool'lari (har biri kichik)
         
  ┌─────────────┐
  │  PgBouncer  │    markaziy pooler (minglab client  kam DB ulanish)
  └─────────────┘
        (kam ulanish)
  ┌─────────────┐
  │ PostgreSQL  │   (max_connections — himoyalangan)
  └─────────────┘

   5000 client  PgBouncer  25 DB ulanish (oneuptime)

PgBouncer (oneuptime): ilova bilan DB orasida turuvchi pooler. Minglab client ulanishini kam DB ulanishiga "siqadi" (multiplexing). Ulkan masshtab (10K+ ulanish) uchun zarur. Pool rejimlari: session/transaction/statement. Cloud (Neon, Supabase) ko'pincha pooler beradi.

Serverless — ilova pool ishlamaydi: serverless funksiya (Lambda, Vercel — 10.2) har chaqiruvda qisqa umr ko'radi va bir-biriga bog'lanmagan. Har funksiya nusxasi o'z pool'ini ochsa — minglab funksiya bir vaqtda ishga tushganda DB darhol ulanishlardan tugaydi 2.1-bob. Yechim — tashqi pooler (PgBouncer/Neon/Supabase pooler, transaction rejimida): funksiya poolerga ulanadi, poolerning o'zi DB ulanishlarini kam tutadi. Ya'ni serverless'da pool ilovada emas, tashqarida turadi.

2.7. Pool best practices (xulosa)

text
   Pool hajmi: (cpu*2) atrofida (juda katta emas — 2.3)
   Ulanishni QAYTAR (release — 6.5: 2.10); ORM avtomatik
   Ko'p instansiya: jami ulanish ≤ DB max 2.5-bob
   Ulkan masshtab: PgBouncer 2.6-bob
   Monitoring: waiting clients, ulanish foydalanishi 2.10-bob
   Pool xatolarini boshqar (timeout, error event)

2.8. ER modeling nima (DB dizayni)

ER (Entity-Relationship) modeling — DB'ni vizual, mantiqiy loyihalash (lucidchart):

text
  ER modeling uch element:
  ENTITY (mavjudot)     — asosiy "narsa" (User, Product, Order)  jadval
  ATTRIBUTE (atribut)   — entity xususiyati (ism, narx)  ustun
  RELATIONSHIP (bog'lanish) — entity'lar orasidagi aloqa  FK

  ER diagramma — bularni VIZUAL ko'rsatadi (kod yozishdan oldin)

ER modeling — kod oldidan: biznes talablarini (so'z bilan) entity/atribut/bog'lanish (struktura)ga aylantirish. Bu — 6-QISMning hammasini (normalizatsiya 6.9, relations 6.1) amaliy birlashtiradi. Yaxshi ER — yaxshi DB.

2.9. Entity va atribut aniqlash

text
  Biznes talabidan entity ajratish (otlar — nouns):
  "Foydalanuvchilar mahsulot buyurtma qiladi"
   Entity'lar: User, Product, Order (asosiy "narsalar")

  Atributlar (har entity xususiyati):
  User: id, ism, email, parol
  Product: id, nom, narx, zaxira
  Order: id, sana, holat, summa

  Atribut turlari:
  - Oddiy (ism), Kalit (PK — id), Hosilaviy (yosh — tug'ilgandan), Ko'p qiymatli (telefonlar — 6.9: 1NF)

Entity'larni topish: biznes tavsifidagi otlar (foydalanuvchi, buyurtma, mahsulot) — odatda entity. Sifatlar/xususiyatlar — atribut. Fe'llar (buyurtma qiladi) — bog'lanish 2.10-bob. Bu — talablardan strukturaga o'tish.

2.10. Relationship va cardinality (bog'lanish turi)

Cardinality — bog'lanishdagi son (nechta-nechta — gleek):

text
  One-to-One (1:1):   bir entity — bir entity
    User ─── Profile (har user bitta profil)

  One-to-Many (1:N):  bir entity — ko'p entity (ENG KENG TARQALGAN)
    User ──< Order (bir user ko'p buyurtma; bir buyurtma bir user)

  Many-to-Many (M:N): ko'p — ko'p
    Product >──< Order (bir mahsulot ko'p buyurtmada; bir buyurtma ko'p mahsulot)
     junction (oraliq) jadval kerak (OrderItems — 2.11)
text
  ER notatsiya (crow's foot):
  ──│   bitta (one)
  ──<   ko'p (many)
  ──o   ixtiyoriy (optional — 0 ham bo'lishi mumkin)

Cardinality — eng muhim qaror (gleek): har bog'lanish nechta-nechta? 1:1 (kam), 1:N (eng ko'p — FK), M:N (junction jadval — 2.11). Noto'g'ri cardinality — noto'g'ri schema (qayta yozish). Optionality — majburiymi (har user buyurtma qilishi shartmi — yo'q, ixtiyoriy).

2.11. Many-to-Many — junction jadval

M:N bog'lanish to'g'ridan ifodalanmaydi — oraliq (junction) jadval kerak (miro):

text
  Product M:N Order — to'g'ridan FK bilan bo'lmaydi
   OrderItems (junction) jadval:

  products:        order_items:              orders:
  | id | nom |     | order_id | product_id | | id | sana |
  | 1  | Olma|     | 10       | 1          | | 10 | ... |
                   | 10       | 2          |   (buyurtma 10 da mahsulot 1 va 2)

   M:N = ikkita 1:N (Product 1:N OrderItems N:1 Order)
   junction'da qo'shimcha maydon ham (miqdor, narx — 6.3: snapshot)

Junction jadval (miro): M:N — har doim oraliq jadval (ikki FK + qo'shimcha maydon). Masalan OrderItems (order_id + product_id + miqdor + narx). Bu — relatsion DB'da M:N'ning yagona to'g'ri yo'li (6.9: normalizatsiya).

2.12. ER diagrammadan jadvalga (o'tish)

text
  ER  SQL jadval qoidalari:
  Entity         jadval
  Atribut        ustun
  PK             PRIMARY KEY 6.4-bob
  1:1            FK (bir tomonda, unique)
  1:N            FK ("ko'p" tomonda — Order.user_id)
  M:N            junction jadval 2.11-bob
  Hosilaviy      saqlanmaydi (hisoblanadi) yoki denormalizatsiya

ER jadval — mexanik o'tish (yuqoridagi qoidalar). ER to'g'ri bo'lsa — jadvallar to'g'ri. Bu — ER modeling'ning amaliy natijasi (chizmadan kodgacha — 1.8: Figma kabi, lekin DB uchun).

2.13. ER modeling jarayoni (qadamlar)

text
  1. Talablarni yig'ish (biznes nima kerak)
  2. Entity'larni aniqlash (otlar — 2.9)
  3. Atributlarni aniqlash (har entity xususiyati)
  4. Bog'lanishlarni aniqlash (cardinality — 2.10)
  5. Normalizatsiya (1NF-3NF — 6.9)
  6. ER diagramma chizish (vizual — dbdiagram.io, Lucidchart)
  7. Jadvalga o'tkazish (SQL/ORM — 2.12)
  8. Indeks/optimizatsiya rejasi (6.10)

ER modeling — jarayon (bir martalik emas). Talab entity atribut bog'lanish normalizatsiya diagramma jadval. Vositalar: dbdiagram.io (kod bilan ER), Lucidchart, draw.io. Boshlanishida vaqt sarflash — keyin qayta yozishdan saqlaydi.

2.14. Yaxshi DB dizayni prinsiplari

text
   Aniq, izchil nomlash (users, user_id — konvensiya — lucidchart)
   Har jadval — bitta mavzu (entity — single responsibility)
   To'g'ri PK (sun'iy id yoki tabiiy kalit)
   Normalizatsiya (3NF — takror yo'q — 6.9); tanlab denormalizatsiya
   To'g'ri cardinality (1:N, M:N — junction — 2.10)
   FK + ON DELETE (yaxlitlik — 6.6: 2.7)
   Indeks rejasi (tez-qidiriladigan — 6.10)
   Kelajakni o'yla (o'sish, o'zgarish — migration — 6.14)

2.15. Real misol — e-commerce ER (xulosa)

text
  E-commerce ER (6-QISM birlashmasi):

  User ──< Order ──< OrderItem >── Product
   │         │                       │
   │ 1:N     │ 1:N (junction M:N)    │
   │         │                       │
   └──< Address              Category ──< Product (1:N)
       (1:N)

  - User 1:N Order (foydalanuvchi ko'p buyurtma)
  - User 1:N Address (ko'p manzil)
  - Order M:N Product (OrderItem junction — miqdor, narx)
  - Category 1:N Product (kategoriya ko'p mahsulot)

Bu — butun 6-QISM amalda: entity (User/Product/Order), bog'lanishlar (1:N, M:N), junction (OrderItem), normalizatsiya 6.9-bob, FK 6.6-bob, indeks 6.10-bob. Yaxshi ER — yaxshi ilova poydevori.

2.16. Best practices (14)

text
  POOL:
   Optimal hajm (cpu*2 — juda katta emas — 2.3)
   Ulanishni qaytar (6.5: 2.10); ko'p instansiya hisobi 2.5-bob
   Ulkan masshtab: PgBouncer 2.6-bob; monitoring 2.10-bob

  ER MODELING:
   Kod oldidan loyihalash (entity/atribut/cardinality — 2.8-2.10)
   Normalizatsiya (3NF — 6.9); to'g'ri bog'lanish
   Aniq nomlash; FK + yaxlitlik; indeks rejasi
   Diagramma (dbdiagram.io); kelajakni o'yla (migration — 6.14)

3. Tushunchalar — tez ma'lumotnoma

text
  CONNECTION POOL 2.2-bob:
  - Ulanishlarni qayta ishlatish; hajm: cpu*2 atrofida 2.3-bob
  - max/min/idleTimeout 2.4-bob; ko'p instansiya hisobi 2.5-bob
  - PgBouncer (tashqi pooler — ulkan masshtab — 2.6)

  ER MODELING 2.8-bob:
  - Entity (jadval) + Atribut (ustun) + Relationship (FK)
  - Cardinality: 1:1, 1:N (FK), M:N (junction — 2.10, 2.11)
  - ER  jadval 2.12-bob; jarayon 2.13-bob; 3NF (6.9)

4. Batafsil misollar

Misol 1 — Pool sozlash (optimal — 2.3, 2.4)

js
// PostgreSQL pool 6.6-bob — optimal sozlama
import pg from "pg";

const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,                          // (cpu*2) atrofida 2.3-bob — juda katta emas!
  min: 2,                           // doim tayyor (tez javob)
  idleTimeoutMillis: 30000,         // 30s bo'sh  yop (2.4)
  connectionTimeoutMillis: 5000,    // 5s ulanmasa  xato
});

// Pool holatini kuzatish (monitoring — 2.16)
setInterval(() => {
  console.log(`Pool: jami=${pool.totalCount} bo'sh=${pool.idleCount} kutayotgan=${pool.waitingCount}`);
}, 60000);
// waitingCount > 0 doimiy  pool kichik (oshiring yoki so'rovni optimizatsiya qiling — 2.10)

Misol 2 — Ko'p instansiya hisobi (2.5)

text
  DB max_connections = 100
  PgBouncer'siz hisob:

  Instansiyalar soni × pool hajmi ≤ max_connections (ehtiyot bilan)
  Masalan: 8 instansiya × 10 pool = 80 (≤ 100 — OK, zaxira bilan)

   8 instansiya × 20 pool = 160 (> 100 — "too many connections"!)
   pool hajmini kamaytiring (8 × 10 = 80) yoki PgBouncer (2.6)

Misol 3 — PgBouncer config (ulkan masshtab — 2.6)

ini
; pgbouncer.ini (oneuptime — soddalashtirilgan)
[databases]
mydb = host=localhost port=5432 dbname=mydb

[pgbouncer]
pool_mode = transaction          ; transaction rejimi (eng samarali)
max_client_conn = 5000           ; minglab client (2.6)
default_pool_size = 25           ; DB'ga kam ulanish (2.6)
min_pool_size = 5
reserve_pool_size = 10
; 5000 client  25 DB ulanish (multiplexing — 2.6)

Ilova PgBouncer'ga ulanadi (5432 o'rniga 6432), PgBouncer DB'ga kam ulanish bilan. Minglab client o'nlab DB ulanish (oneuptime).

Misol 4 — Entity va atribut aniqlash (ER — 2.9)

text
  Talab: "Foydalanuvchilar mahsulotlarni savatga qo'shib buyurtma beradi.
          Har buyurtma yetkazib berish manziliga ega."

  Entity'lar (otlar — 2.9):
  - User (foydalanuvchi)
  - Product (mahsulot)
  - Order (buyurtma)
  - Address (manzil)
  - CartItem / OrderItem (savat/buyurtma elementi)

  Atributlar:
  User: id(PK), ism, email, parol, created_at
  Product: id(PK), nom, narx, zaxira, category_id(FK)
  Order: id(PK), user_id(FK), address_id(FK), holat, summa, created_at

Misol 5 — Bog'lanishlar va cardinality (2.10)

text
  Bog'lanishlarni aniqlash (fe'llardan — 2.10):

  User 1:N Order        (foydalanuvchi ko'p buyurtma; buyurtma bir foydalanuvchi)
  User 1:N Address      (foydalanuvchi ko'p manzil)
  Category 1:N Product  (kategoriya ko'p mahsulot)
  Order M:N Product     (buyurtma ko'p mahsulot, mahsulot ko'p buyurtmada)
     OrderItem junction (order_id, product_id, miqdor, narx)
  Order N:1 Address     (buyurtma bir yetkazish manzili)

Misol 6 — ER SQL jadval (2.12)

sql
-- ER diagrammadan jadvalga (2.12)
CREATE TABLE users (
  id SERIAL PRIMARY KEY, ism VARCHAR(50), email VARCHAR(100) UNIQUE
);
CREATE TABLE categories (id SERIAL PRIMARY KEY, nom VARCHAR(50));
CREATE TABLE products (
  id SERIAL PRIMARY KEY, nom VARCHAR(100), narx DECIMAL(10,2), zaxira INT,
  category_id INT REFERENCES categories(id)            -- 1:N (2.10)
);
CREATE TABLE addresses (
  id SERIAL PRIMARY KEY, user_id INT REFERENCES users(id) ON DELETE CASCADE,  -- 1:N
  shahar VARCHAR(50), kocha VARCHAR(100)
);
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INT REFERENCES users(id),                    -- 1:N (2.10)
  address_id INT REFERENCES addresses(id),             -- N:1
  holat VARCHAR(20) DEFAULT 'yangi', summa DECIMAL(10,2)
);
CREATE TABLE order_items (                              -- M:N junction (2.11)
  order_id INT REFERENCES orders(id) ON DELETE CASCADE,
  product_id INT REFERENCES products(id),
  miqdor INT, narx DECIMAL(10,2),                       -- snapshot (6.3)
  PRIMARY KEY (order_id, product_id)
);
-- Indekslar (6.10)
CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_products_category ON products(category_id);

Misol 7 — dbdiagram.io notatsiyasi (ER kod bilan — 2.13)

text
// dbdiagram.io DSL (ER ni kod bilan — 2.13)
Table users {
  id int [pk, increment]
  email varchar [unique]
}
Table orders {
  id int [pk, increment]
  user_id int [ref: > users.id]    // 1:N (orders.user_id  users.id)
  summa decimal
}
Table products { id int [pk]; nom varchar }
Table order_items {
  order_id int [ref: > orders.id]    // M:N junction 2.11-bob
  product_id int [ref: > products.id]
  miqdor int
}
// > : many-to-one; - : one-to-one; <> : many-to-many

Misol 8 — Pool + ER birga (to'liq backend — 2.15)

js
// To'liq: optimal pool + ER asosli modellar (6-QISM xulosasi)
import pg from "pg";
const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL, max: 10 });   // pool (2.3)

// ER bog'lanishlari asosida so'rov (1:N, M:N — JOIN — 6.7)
export async function buyurtmaToliq(orderId) {
  const { rows } = await pool.query(`
    SELECT o.id, o.summa, u.ism AS mijoz, a.shahar,
           json_agg(json_build_object('nom', p.nom, 'miqdor', oi.miqdor)) AS mahsulotlar
    FROM orders o
    JOIN users u ON o.user_id = u.id           -- 1:N 2.10-bob
    JOIN addresses a ON o.address_id = a.id     -- N:1
    JOIN order_items oi ON oi.order_id = o.id   -- M:N junction 2.11-bob
    JOIN products p ON oi.product_id = p.id
    WHERE o.id = $1
    GROUP BY o.id, u.ism, a.shahar
  `, [orderId]);                                // parametrli (14)
  return rows[0];
}
// ER (struktura) + pool (samarali ulanish) + JOIN (bog'lanish) — hammasi birga

5. To'g'ri va noto'g'ri holatlar

1) Har so'rovda yangi ulanish

text
 createConnection har so'rovda (sekin, chegara — 2.1)
 pool (qayta ishlatish — 2.2)

2) Juda katta pool

js
//  "ko'proq = tezroq" (noto'g'ri — DB sekinlashadi — 2.3)
new Pool({ max: 100 });

//  optimal (cpu*2)
new Pool({ max: 10 });

3) Ko'p instansiya, pool hisobsiz

text
 10 instansiya × 20 pool = 200 > DB max (qulaydi — 2.5)
 jami ≤ max; yoki PgBouncer (2.6)

4) ER'siz to'g'ridan jadval yaratish

text
 o'ylamasdan jadval (noto'g'ri bog'lanish — qayta yozish — 2.8)
 avval ER modeling (entity/cardinality — 2.13)

5) M:N to'g'ridan (junctionsiz)

text
 M:N ni FK bilan ifodalashga urinish (imkonsiz — 2.11)
 junction jadval (OrderItem)

6. Keng tarqalgan xatolar va yechimlari

Xato 1 — too many connections / remaining connection slots

Sababi: ulanishlar DB max'dan oshdi (pool katta yoki release yo'q — 2.5). Yechimi: pool hajmini kamaytiring; release tekshiring (6.5: 2.10); PgBouncer 2.6-bob.

Xato 2 — connection timeout (pool to'la)

Sababi: pool kichik, so'rovlar kutadi 2.3-bob. Yechimi: pool hajmini moslab oshiring; sekin so'rovlarni optimizatsiya qiling 6.10-bob; release.

Xato 3 — Ulanish "leak" (oshib boradi)

Sababi: ulanish qaytarilmagan (release/end yo'q — 6.5: 2.10). Yechimi: finally'da release; ORM avtomatik.

Xato 4 — Noto'g'ri DB dizayni (keyin muammo)

Sababi: ER modelingsiz, shoshib jadval 2.8-bob. Yechimi: ER modeling (qaytadan loyihalash); migration bilan to'g'rilash 6.14-bob.

Xato 5 — M:N bog'lanish noto'g'ri

Sababi: junction jadval yo'q 2.11-bob. Yechimi: junction (ikki FK + qo'shimcha maydon).

Xato 6 — Cardinality noto'g'ri tanlangan

Sababi: 1:N yoki M:N noto'g'ri baholangan 2.10-bob. Yechimi: talabni qayta tahlil; to'g'ri cardinality; ER qayta.

7. Integratsiya — bu mavzu stack'ning qayerida uchraydi

  • MySQL/PostgreSQL (6.5, 6.6): pool (mysql2/pg).
  • ORM'lar (6.11-6.13): pool ichida; ER model.
  • Normalizatsiya 6.9-bob: ER modeling asosi.
  • Relations 6.1-bob: cardinality, FK.
  • JOIN 6.7-bob: ER bog'lanishlari.
  • Query optimization 6.10-bob: pool, indeks rejasi.
  • Migration 6.14-bob: ER o'zgarishi.
  • DevOps 10.2-bob: ko'p instansiya, PgBouncer.
  • Arxitektura (9): masshtab, scalability.
  • Cloud DB 10.6-bob: managed pooler.

8. Eng yaxshi amaliyotlar (best practices)

  • Pool optimal hajm (cpu*2 atrofida — juda katta emas — 2.3).
  • Ulanishni qaytar (release; ORM avtomatik — 6.5: 2.10).
  • Ko'p instansiya: jami ulanish ≤ DB max 2.5-bob.
  • Ulkan masshtab: PgBouncer 2.6-bob; pool monitoring (waiting clients — 2.10).
  • ER modeling kod oldidan (entity/atribut/cardinality — 2.8-2.13).
  • To'g'ri cardinality (1:N FK, M:N junction — 2.10, 2.11).
  • Normalizatsiya (3NF) + tanlab denormalizatsiya 6.9-bob.
  • Aniq nomlash, FK + yaxlitlik, indeks rejasi (2.14, 6.6, 6.10).
  • Diagramma vositasi (dbdiagram.io — 2.13); kelajakni o'yla (migration — 6.14).
  • ER jadval qoidalari bo'yicha 2.12-bob.

9. Amaliy loyiha: "To'liq DB Dizayni (ER Jadval Pool)"

6-QISMni yakunlovchi loyiha — DB dizaynini boshidan oxirigacha.

Maqsad

Murakkab ilova (e-commerce yoki ijtimoiy tarmoq) uchun to'liq DB dizaynini qurish: ER modeling, jadvalga o'tkazish, indeks rejasi va optimal pool.

Talablar (requirements)

  1. Talab tahlili: ilova talablarini yozing (biznes — 2.13).
  2. Entity/atribut: entity'larni (otlar) va atributlarni aniqlang (Misol 4, 2.9).
  3. Cardinality: bog'lanishlar (1:1, 1:N, M:N) va optionality (Misol 5, 2.10).
  4. ER diagramma: dbdiagram.io yoki ASCII bilan chizing (Misol 7, 2.13).
  5. Normalizatsiya: 3NF tekshiring (takror yo'q — 6.9).
  6. Jadvalga o'tkazish: SQL (PK/FK/junction — Misol 6, 2.12).
  7. Indeks rejasi: tez-qidiriladigan/JOIN ustunlari 6.10-bob.
  8. Pool sozlash: optimal hajm; monitoring (Misol 1, 2.3).
  9. Ko'p instansiya hisobi: pool × instansiya ≤ max 2.5-bob.
  10. (Bonus) PgBouncer: ulkan masshtab uchun reja 2.6-bob.

Maslahatlar (hint)

  • Entity — otlar; bog'lanish — fe'llar (2.9, 2.10).
  • M:N junction jadval (2.11, 5-xato).
  • ER jadval qoidalari 2.12-bob.
  • Pool: cpu*2 atrofida (2.3, 2-xato).
  • Ko'p instansiya: jami ≤ max (2.5, 3-xato).
  • 3NF 6.9-bob; indeks 6.10-bob.

"Tayyor" mezonlari (acceptance criteria)

  • Talab tahlili + entity/atribut.
  • Cardinality (1:N, M:N) to'g'ri.
  • ER diagramma chizilgan.
  • 3NF normalizatsiya.
  • SQL jadvallar (PK/FK/junction).
  • Indeks rejasi.
  • Optimal pool + monitoring.
  • Ko'p instansiya hisobi.
  • (Bonus) PgBouncer rejasi.

Yechim kodi ataylab berilmagan — bu loyihani o'zingiz yozib ko'ring.

10. Xulosa va keyingi QISMga ko'prik

Bu bobda 6-QISMni yakunlovchi ikki mavzuni o'rgandik:

  • Connection pooling: DB ulanishi qimmat resurs 2.1-bob; pool qayta ishlatadi 2.2-bob; optimal hajm (cpu*2 — juda katta emas — 2.3); ko'p instansiya hisobi 2.5-bob; PgBouncer (ulkan masshtab — 2.6).
  • ER modeling: entity/atribut/relationship 2.8-bob; entity aniqlash (otlar — 2.9); cardinality (1:1/1:N/M:N — 2.10); junction (M:N — 2.11); ER jadval 2.12-bob; jarayon 2.13-bob; dizayn prinsiplari 2.14-bob.

6-QISM (Ma'lumotlar bazasi) TUGADI! DB asoslari (SQL vs NoSQL) MongoDB (CRUD, aggregation, relations) SQL (CRUD, JOIN, window, stored procedures) normalizatsiya/ACID/indeks/optimizatsiya ORM'lar (Sequelize, Prisma, TypeORM) migration/seeding connection pool/ER modeling — to'liq, professional DB bilimi.

Keyingi — 7-QISM: TypeScript. JavaScript'ni (2-QISM) va backend (5), DB (6) ni bildik. Ko'p joyda (Prisma — 6.12, TypeORM — 6.13, NestJS — 8) TypeScript'ni ko'rdik. Endi uni chuqur, boshidan o'rganamiz: nega TS, turlar, interface, generics, utility types, decorators. Bu — zamonaviy backend (NestJS — 8) va frontend (React — 11) uchun zarur poydevor.

Foydalanilgan rasmiy/ishonchli manbalar

  • pgbouncer.org — PgBouncer config; OneUptime — connection pooling with PgBouncer 2026
  • Lucidchart / SmartDraw — Entity Relationship Diagram (ERD); Gleek — ER cardinality
  • MojoAuth — connection pooling strategies; Miro — many-to-many in ER diagrams

Izohlar (0)

Izoh yozish uchun kiring.

  • Hozircha izoh yo'q. Birinchi bo'ling!
6.15-bob: Connection pooling va ER modeling (DB dizayni) — Wisar