SQL
Bitta SQL so'rov database ichida Parser, Planner va Index orqali qanday natijaga aylanishini ko'ring.
Database · Obyekt-relatsion
Ulanish postmaster'dan alohida backend jarayoniga o'tib, planner, executor va shared buffers orqali row'larni topishini kuzating. Keyin UPDATE qanday qilib eski row'ni o'chirmasdan yangi versiya yozishini ko'ring.
UPDATE users ... Yozayotgan session.
xid = 1001 Transaction'ga ID beriladi va row topiladi.
xmax = 1001 Eski versiya o'chirilmaydi, faqat belgilanadi.
xmin = 1001 Yangi qiymatli versiya jadvalga qo'shiladi.
pg_wal/ O'zgarishlar jurnali: crash recovery va replication uchun.
SELECT · snapshot Bir vaqtda o'qiyotgan boshqa session.
pg_xact: committed Transaction holati commit qilingan deb belgilanadi.
dead tuple → bo'sh joy Hech kimga kerak bo'lmagan eski versiyalarni tozalaydi.
01 Kirish
PostgreSQL — ochiq kodli obyekt-relatsion database. U SQL standartiga yaqinligi, ishonchliligi va kengaytirilishi bilan mashhur: o'z turlaringiz, funksiyalaringiz va Extension'lar qo'shish mumkin.
Har bir ulanish uchun alohida OS jarayoni — Backend process ochiladi. Jarayonlar page'larni umumiy xotira — Shared buffers orqali bo'lishadi, har bir o'zgarish esa avval WALga yoziladi.
MVCC row versiyalari orqali ishlaydi: UPDATE eski row'ni ustidan yozmaydi, yangi versiya qo'shadi. Hech kimga kerak bo'lmay qolgan eski versiyalarni VACUUM tozalaydi.
Hayotiy o'xshatish
PostgreSQL — katta kutubxona: har bir kitobxonga alohida kutubxonachi (backend) biriktiriladi, hammasi bitta umumiy zaldan (shared buffers) foydalanadi. Kitobning yangi nashri chiqsa, eskisi darhol olib tashlanmaydi: uni o'qiyotganlar tugatgach, farrosh (VACUUM) javonni bo'shatadi.
02 Qanday ishlaydi
Pastga aylantiring: SELECT * FROM users WHERE city = 'Toshkent'; ulanishdan boshlab rejaga, so'ng page'lar va tuple'larga aylanadi.
Laravel pgsql driver'i orqali 127.0.0.1:5432 ga ulanadi va SELECT * FROM users WHERE city = 'Toshkent'; yuboradi.
Ulanish ochilayotganda Postmaster fork() qilib yangi backend jarayonini yaratadi. Autentifikatsiya pg_hba.conf qoidalari bo'yicha shu jarayonda bajariladi.
Ulanish yopilguncha uning barcha so'rovlarini shu Backend process bajaradi. Ulanish ko'p bo'lsa, jarayonlar ham ko'p: shuning uchun pooling kerak bo'ladi.
SQL daraxtga aylanadi. Keyin users jadvali, city ustuni va uning turi tizim kataloglaridan aniqlanadi.
users oddiy jadval, shuning uchun o'zgarish yo'q. Agar u view bo'lganida, rewriter uni ichidagi so'rov bilan almashtirardi.
ANALYZE yig'gan statistikaga ko'ra city = 'Toshkent' kam row qaytaradi. idx_users_city bo'yicha Index Scan Seq Scan'dan arzon chiqadi.
Index Scan node'i index'dan birma-bir row manzilini (TID) oladi va tuple'ni so'raydi.
Kerakli 8KB page Shared buffersda bo'lsa, darhol o'qiladi. Bo'lmasa, fayldan yuklanadi; OS'ning o'z fayl keshi ham yordam beradi.
Heap'dagi har bir Tuple'ning xmin va xmax qiymatlari transaction snapshot'i bilan solishtiriladi. Faqat shu snapshot uchun ko'rinadigan versiyalar qaytadi.
Mos kelgan 3 ta tuple row'larga aylanadi.
Row'lar ulanish orqali yuboriladi, oxirida SELECT 3 buyruq yakuni keladi.
03 Arxitektura
Bitta PostgreSQL server (cluster) — jarayonlar to'plami, ular orasidagi umumiy xotira va diskdagi papkalar.
PostgreSQL cluster bitta postmaster
Bitta port, bitta data papka, ichida ko'p database.
Jarayonlar OS process'lar
Har biri o'z vazifasiga ega alohida jarayon.
ulanishlarni qabul qiladi har ulanishga 1 checkpointer, autovacuum Shared memory umumiy xotira
Barcha jarayonlar ko'radigan xotira.
page keshi WAL navbati lock'lar Disk data papka
Barcha fayllar shu papkada.
jadval va index fayllari WAL segmentlari transaction holatlari Mantiqiy tuzilma obyektlar
Foydalanuvchi ko'radigan obyektlar ierarxiyasi.
shop, analytics public, billing LOGIN, GRANT 04 Asosiy qismlar
Asosiy jarayon: ulanishlarni qabul qiladi, backend va fon jarayonlarini ishga tushiradi.
Bitta ulanishga xizmat qiladigan alohida OS jarayoni: parse, plan va execute shu yerda.
Statistikaga qarab eng arzon bajarish rejasini tanlaydi. EXPLAIN aynan uning qarorini ko'rsatadi.
Barcha backend'lar bo'lishadigan 8KB'lik page'lar keshi.
Write-Ahead Log: har bir o'zgarish data fayllardan oldin shu jurnalga yoziladi.
Dead tuple'larni tozalaydigan va statistikani yangilaydigan fon jarayoni.
Vaqti-vaqti bilan o'zgargan page'larni diskka yozib, tiklash nuqtasini belgilaydi.
Konsol client: SQL va \dt, \d users kabi meta-buyruqlar.
05 Vizual misollar
Har bir tushuncha o'z diagrammasi bilan: ustiga olib boring, bosing va jarayonni kuzating.
PostgreSQL thread emas, jarayonlar bilan ishlaydi. Ular orasidagi bog'lovchi — shared memory.
psql · PDO · pgAdmin PostgreSQL protokoli orqali 5432-portga ulanadi.
asosiy jarayon :5432 Ulanishni qabul qilib, yangi backend jarayonini yaratadi.
har ulanishga bittadan So'rovlarni parse, plan va execute qiladi.
shared buffers · WAL buffers · lock'lar Barcha jarayonlar ko'radigan umumiy xotira.
checkpointer · bgwriter · walwriter · autovacuum Page'larni diskka yozish, WAL va tozalash ishlari.
base/ · pg_wal/ · pg_xact/ Jadval fayllari, WAL segmentlari va transaction holatlari.
Primary WAL yozuvlarini standby'ga oqim sifatida yuboradi. Standby ularni qo'llab, o'qish so'rovlariga javob bera oladi.
INSERT ...; COMMIT
Yozuvlar faqat primary'ga boradi. WAL stream
WAL sender jarayoni yangi WAL yozuvlarini standby'dagi WAL receiver'ga uzatadi. COMMIT OK
Default holatda primary standby'ni kutmaydi. synchronous_standby_names bilan sinxron rejimni yoqish mumkin. replay pozitsiyasi
Standby WAL'ning qayergacha qabul qilingani va qo'llanganini xabar qiladi. SELECT (read-only)
Hot standby o'qish so'rovlarini qabul qiladi, yozishni rad etadi. rows
Natija primary'dan biroz orqada bo'lishi mumkin (replication lag). Jadvallar ro'yxati, haqiqiy bajarilish rejasi va JSONB ustun uchun GIN index.
\dt
List of relations
Schema | Name | Type | Owner
--------+--------+-------+----------
public | orders | table | postgres
public | users | table | postgres
(2 rows)
EXPLAIN ANALYZE SELECT * FROM users WHERE city = 'Toshkent';
QUERY PLAN
----------------------------------------------------------
Index Scan using idx_users_city on users (cost=0.29..12.35 rows=3 width=48) (actual time=0.031..0.036 rows=3 loops=1)
Index Cond: ((city)::text = 'Toshkent'::text)
Planning Time: 0.118 ms
Execution Time: 0.062 ms
ALTER TABLE orders ADD COLUMN meta jsonb;
ALTER TABLE
CREATE INDEX idx_orders_meta ON orders USING gin (meta);
CREATE INDEX
SELECT id, meta->>'delivery' AS delivery FROM orders WHERE meta @> '{"gift": true}';
id | delivery
----+----------
13 | courier
(1 row)
Ikkalasi ham ishonchli relatsion database, lekin ichki tuzilishi va kuchli tomonlari farq qiladi.
| PostgreSQL | MySQL (InnoDB) | |
|---|---|---|
| Ulanish modeli | Har ulanishga alohida jarayon | Har ulanishga thread |
| MVCC | Eski versiyalar jadvalning o'zida, VACUUM tozalaydi | Eski versiyalar undo log'da, purge tozalaydi |
| Default isolation | READ COMMITTED | REPEATABLE READ |
| Jadval tuzilishi | Heap + alohida index'lar | Primary key bo'yicha clustered index |
| Index turlari | B-tree, Hash, GIN, GiST, SP-GiST, BRIN | B-tree, full-text, spatial |
| JSON | json va jsonb, GIN index bilan |
Binary JSON, generated column yoki multi-valued index orqali |
| DDL transaction'da | Ha, ROLLBACK qilish mumkin |
Yo'q, DDL avtomatik commit qiladi |
| Kengaytirish | Extension'lar: PostGIS, pg_trgm va boshqalar | Plugin'lar va storage engine'lar |
06 Interaktiv simulyatsiya
UPDATE users SET city = 'Samarqand' WHERE id = 3; — eski row joyida qoladi, yangisi qo'shiladi, parallel o'quvchi esa kutmaydi. «UPDATE'ni bajarish» tugmasini bosing yoki bosqichma-bosqich yuring. Istalgan tugun ustiga boring yoki bosing: uning vazifasi ochiladi.
UPDATE users ... Yozayotgan session.
xid = 1001 Transaction'ga ID beriladi va row topiladi.
xmax = 1001 Eski versiya o'chirilmaydi, faqat belgilanadi.
xmin = 1001 Yangi qiymatli versiya jadvalga qo'shiladi.
pg_wal/ O'zgarishlar jurnali: crash recovery va replication uchun.
SELECT · snapshot Bir vaqtda o'qiyotgan boshqa session.
pg_xact: committed Transaction holati commit qilingan deb belgilanadi.
dead tuple → bo'sh joy Hech kimga kerak bo'lmagan eski versiyalarni tozalaydi.
«UPDATE'ni bajarish» tugmasini bosing
Yoki istalgan tugunni tanlang: uning vazifasi shu yerda ochiladi.
Tanlangan tugun
07 Laboratoriya
So'rov yozing va RUN QUERY ni bosing: database uni Parser, Planner, Index va Rows bosqichlari orqali qanday bajarishini ko'rasiz. Namuna jadvallar: users va orders.
Qo'llab-quvvatlanadi: SELECT, WHERE (AND, OR, LIKE, IN), JOIN, GROUP BY, COUNT/SUM/AVG/MIN/MAX, ORDER BY, LIMIT. Ctrl + Enter — ishga tushirish.
Sxema
usersordersNatija shu yerda chiqadi.
08 Kod va oqim
Chapda "har bir shahardagi eng ko'p xarid qilgan mijoz" hisoboti, o'ngda uning bosqichlari. users va orders jadvallaridagi namunaviy ma'lumot ustida.
-- Har bir shahardagi eng ko'p xarid qilgan mijozWITH paid AS ( SELECT user_id, SUM(total) AS spent FROM orders WHERE status = 'paid' GROUP BY user_id),ranked AS ( SELECT u.city, u.name, p.spent, RANK() OVER (PARTITION BY u.city ORDER BY p.spent DESC) AS place FROM paid AS p JOIN users AS u ON u.id = p.user_id)SELECT city, name, spentFROM rankedWHERE place = 1ORDER BY spent DESC;
SUM(total) Har bir user'ning to'langan summasi.
u.id = p.user_id Summaga ism va shahar qo'shiladi.
PARTITION BY city Har bir shahar ichida o'rin hisoblanadi.
faqat birinchilar Har shahardan bitta g'olib qoladi.
2 rows Yakuniy ro'yxat.
09 Mavzular
20 ta mavzu, har biri qisqa izoh va kichik diagramma bilan. Ochgan mavzuingiz progressga qo'shiladi.
Ochiq kodli obyekt-relatsion database: kuchli SQL, ishonchli transaction'lar va boy turlar. Laravel'da pgsql driver bilan ishlatiladi.
Postmaster har bir ulanish uchun fork() qilib alohida backend jarayonini yaratadi. Bir jarayon qulasa, boshqa ulanishlar odatda zarar ko'rmaydi, lekin har bir ulanish xotira talab qiladi.
Rasmiy konsol client. SQL'dan tashqari \dt (jadvallar), \d users (tuzilma), \x (vertikal chiqish) kabi meta-buyruqlari bor.
psql -h 127.0.0.1 -U shop_app -d shop bigint, text, numeric, timestamptz, uuid, jsonb, massivlar va diapazonlar. Avtomatik ID uchun identity ustun ishlatiladi.
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY PostgreSQL'da user va group — bitta tushuncha: role. Kim qayerdan va qaysi usul bilan ulanishini pg_hba.conf belgilaydi, nimaga ruxsati borligini esa GRANT.
CREATE ROLE shop_app LOGIN PASSWORD 'change-me'; Default darajada har bir so'rov o'z snapshot'ini oladi. DDL ham transaction ichida ishlaydi: migratsiya yarmida xato bo'lsa, ROLLBACK hammasini qaytaradi.
BEGIN; ALTER TABLE users ADD COLUMN phone text; ROLLBACK; Har bir row versiyasi — tuple, unda xmin (yaratgan transaction) va xmax (o'chirgan transaction) bor. Snapshot qaysi versiya ko'rinishini shu ikkisiga qarab hal qiladi.
Dead tuple'lar joyini qayta ishlatish uchun bo'shatadi va statistikani yangilaydi. Autovacuum buni avtomatik qiladi; uni o'chirish jadval shishishiga olib keladi.
VACUUM (VERBOSE, ANALYZE) orders; .env da pgsql connection ko'rsatiladi, PHP'da pdo_pgsql extension kerak. Migration'lar va Eloquent o'zgarishsiz ishlaydi.
DB_CONNECTION=pgsql DB_HOST=127.0.0.1 DB_PORT=5432 DB_DATABASE=shop So'rovni haqiqatan bajaradi va har bir node uchun taxminiy hamda haqiqiy row'lar va vaqtni ko'rsatadi. Taxmin va haqiqat juda farq qilsa, statistika eskirgan bo'lishi mumkin.
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE city = 'Toshkent'; Default index turi. Partial index faqat shartga mos row'larni, expression index esa funksiya natijasini indekslaydi.
CREATE INDEX idx_orders_pending ON orders (user_id) WHERE status = 'pending'; GIN bitta qiymat ichidagi ko'p elementni indekslaydi: jsonb kalitlari, massiv elementlari, matndagi so'zlar. @> va to'liq matnli qidiruvni tezlashtiradi.
CREATE INDEX idx_orders_meta ON orders USING gin (meta); GiST geometriya, diapazonlar va PostGIS uchun. BRIN juda kichik index: tabiiy tartibda o'sadigan ulkan jadvallar (log'lar, vaqt bo'yicha) uchun page bloklari oralig'ini saqlaydi.
CREATE INDEX idx_logs_created ON logs USING brin (created_at); JSONB JSON'ni parse qilingan binary ko'rinishda saqlaydi: ->, ->>, @>, ? operatorlari va GIN index bilan tez qidiriladi.
SELECT id FROM orders WHERE meta @> '{"gift": true}'; WITH murakkab so'rovni nomlangan bosqichlarga bo'ladi. WITH RECURSIVE daraxt va graflarni (kategoriyalar, izohlar) aylanib chiqadi.
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 5) SELECT * FROM n; Har bir o'zgarish avval WAL'ga yoziladi, data page'lar esa keyinroq. Checkpoint o'zgargan page'larni diskka yozib, crash'dan keyin WAL'ni qayerdan o'qishni boshlash kerakligini belgilaydi.
Streaming replication butun cluster'ning WAL'ini standby'ga uzatadi. Logical replication esa tanlangan jadvallar o'zgarishlarini publication/subscription orqali yuboradi.
CREATE PUBLICATION shop_pub FOR TABLE orders; Database'ga yangi turlar, funksiyalar va index'lar qo'shadi: PostGIS (geo), pg_trgm (LIKE '%...%' uchun), pg_stat_statements (so'rov statistikasi).
CREATE EXTENSION IF NOT EXISTS pg_trgm; pg_dump bitta database'ning mantiqiy nusxasini oladi, pg_restore uni tiklaydi. Fizik backup (pg_basebackup) va WAL arxivi bilan istalgan daqiqagacha tiklash mumkin.
pg_dump -Fc shop > shop.dump Har ulanish alohida jarayon bo'lgani uchun minglab ulanish qimmat. PgBouncer kabi pooler ko'p client'ni oz sonli server ulanishlariga taqsimlaydi.
10 Amaliyot
Har bir ulanish alohida jarayon va xotira. max_connections ga yetganda yangi client'lar too many clients already xatosini oladi.
PgBouncer yoki boshqa pooler ishlating, ilova pool hajmini server imkoniyatiga moslang.
Dead tuple'lar to'planib, jadval va index'lar shishadi, so'rovlar sekinlashadi. Uzoq muddatda transaction ID wraparound xavfi paydo bo'ladi.
Autovacuum'ni yoqiq qoldiring va katta jadvallar uchun uning sozlamalarini jadval darajasida kuchaytiring.
idle in transaction holatidagi session eski snapshot'ni ushlab turadi va VACUUM undan keyingi dead tuple'larni tozalay olmaydi.
Transaction'larni qisqa qiling va idle_in_transaction_session_timeout ni sozlang.
PostgreSQL FK ustuniga index'ni avtomatik yaratmaydi. JOIN'lar va asosiy row o'chirilganda bog'liq row'larni tekshirish sekin ishlaydi.
orders.user_id kabi FK ustunlariga o'zingiz index qo'ying.
EXPLAIN ANALYZE so'rovni haqiqatan bajaradi: DELETE yoki UPDATE bilan ishlatilsa, ma'lumot o'zgaradi.
Yozuvchi so'rovlarni BEGIN; EXPLAIN ANALYZE ...; ROLLBACK; ichida tekshiring.
Asosiy maydonlar JSON ichida bo'lsa, NOT NULL, foreign key va aniq turlar yo'qoladi, so'rovlar murakkablashadi.
Asosiy maydonlarni oddiy ustunlarda saqlang, jsonb ni faqat moslashuvchan qo'shimcha ma'lumot uchun ishlating.
Production'da ilova va PostgreSQL orasida PgBouncer kabi pooler bo'lsin.
Qaysi so'rovlar umumiy vaqtni eng ko'p olayotganini ko'ring va avval o'shalarni optimallashtiring.
Vaqtni vaqt zonasi bilan saqlang: server yoki foydalanuvchi zonasi o'zgarganda ham qiymatlar to'g'ri qoladi.
Har bir foreign key ustuni uchun index bormi — migration yozayotganda tekshiring.
Muntazam backup, WAL arxivlash va tiklash mashqi birgalikda ma'lumotni istalgan daqiqaga qaytarish imkonini beradi.
11 Mini test
5 ta savol. Javobni tanlang: darhol to'g'ri yoki noto'g'ri ekanini va sababini ko'rasiz.
xmin va xmax orqali qaysi transaction'larga ko'rinishi aniqlanadi.CREATE EXTENSION bilan o'rnatiladigan qo'shimcha modul: yangi turlar, funksiyalar yoki index usullari.LOGIN huquqi bo'lsa ulanish mumkin.