PostgreSQL kursi

Kuchli va ilg'or SQL database. JSONB, full-text search va murakkab so'rovlar.

O'rta · 64 ta dars · ~6 hafta

Mavzular: PostgreSQL asoslari, Advanced SQL, JSONB, Full-text Search, Window Functions, CTE, Partitioning, Replication, Performance Tuning, Extensions

Kirish va O'rnatish

1. PostgreSQL nima? MySQL dan farqi, qachon PostgreSQL tanlash kerak
2. O'rnatish — Windows, Mac, Linux, Docker bilan
3. psql CLI va pgAdmin — asosiy ishlatish
4. Ma'lumot turlari — INTEGER, SERIAL, TEXT, BOOLEAN, TIMESTAMP, UUID, ARRAY, JSONB
5. Schema — nima, yaratish, search_path, public schema

DDL va DML

1. Database yaratish — CREATE DATABASE, psql buyruqlari, \l, \c, \dt
2. CREATE TABLE — constraints, PRIMARY KEY, FOREIGN KEY, CHECK
3. ALTER TABLE, DROP — ustun qo'shish, o'zgartirish, o'chirish
4. INSERT, SELECT — WHERE, AND, OR, BETWEEN, IN, LIKE, IS NULL
5. UPDATE, DELETE — RETURNING, xavfli holatlar
6. UPSERT — INSERT ON CONFLICT DO UPDATE / DO NOTHING
7. Aggregate funksiyalar, GROUP BY, HAVING, FILTER

JOIN va Munosabatlar

1. INNER, LEFT, RIGHT, FULL, CROSS, SELF JOIN
2. One-to-Many — loyihalash va so'rovlar
3. Many-to-Many — junction table, so'rovlar
4. One-to-One — qachon kerak

Ilg'or SQL

1. Subquery — WHERE, FROM, SELECT ichida, EXISTS
2. CTE — WITH, rekursiv CTE, materialized CTE
3. Window Functions — ROW_NUMBER, RANK, DENSE_RANK, NTILE
4. Window Functions — SUM, AVG, LAG, LEAD, FIRST_VALUE, LAST_VALUE
5. UNION, INTERSECT, EXCEPT
6. LATERAL JOIN — nima, qachon kerak

PostgreSQL Maxsus Imkoniyatlari

1. JSONB — saqlash, o'qish, yangilash, operatorlar
2. JSONB — GIN index, so'rovlarni tezlashtirish
3. ARRAY — yaratish, operatorlar, ANY, ALL, unnest
4. Full-text Search — tsvector, tsquery, to_tsvector, plainto_tsquery
5. Full-text Search — GIN index, ranking, highlight
6. UUID — gen_random_uuid, uuid-ossp extension
7. ENUM type — yaratish, ishlatish, o'zgartirish
8. Generated Columns va Computed Values

Funksiyalar va Protseduralar

1. Built-in funksiyalar — string, number, date, conditional
2. PL/pgSQL — asoslar, o'zgaruvchilar, shartlar, sikllar
3. Function yaratish — RETURNS, LANGUAGE, SECURITY DEFINER
4. Stored Procedure — Function dan farqi, CALL
5. Trigger — BEFORE, AFTER, INSTEAD OF, trigger funksiya
6. VIEW va MATERIALIZED VIEW — farqi, REFRESH

Index va Performance

1. Index turlari — B-tree, Hash, GIN, GiST, BRIN
2. Composite Index, Partial Index, Covering Index
3. EXPLAIN ANALYZE — query plan o'qish, Seq Scan vs Index Scan
4. Query optimizatsiya — N+1, join strategiyalari, qoidalar
5. VACUUM, ANALYZE, AUTOVACUUM — nima uchun kerak
6. Connection Pool — PgBouncer, sozlash
7. Table Partitioning — RANGE, LIST, HASH

Transaction va Concurrency

1. Transaction — ACID, BEGIN, COMMIT, ROLLBACK, SAVEPOINT
2. Isolation Levels — Read Committed, Repeatable Read, Serializable
3. Locking — Row lock, Advisory lock, Deadlock
4. MVCC — Multi-Version Concurrency Control, qanday ishlaydi

Ma'lumotlar Bazasini Loyihalash

1. Normalizatsiya — 1NF, 2NF, 3NF, BCNF
2. Denormalizatsiya — qachon va nima uchun
3. ER Diagram — jadvallar va munosabatlarni loyihalash
4. Naming Convention va Schema Design best practices

Xavfsizlik va Boshqaruv

1. User, Role, GRANT, REVOKE — huquqlarni boshqarish
2. Row Level Security — RLS, policy yaratish
3. SQL Injection — qanday himoyalanish, parameterized query
4. Backup va Restore — pg_dump, pg_restore, pg_basebackup
5. Replication — Streaming Replication, asoslar
6. Monitoring — pg_stat_activity, pg_stat_statements, slow queries

Extensions

1. Extensions nima — CREATE EXTENSION, mashhur extensionlar
2. PostGIS — geografik ma'lumotlar, location based queries
3. pg_trgm — fuzzy search, ILIKE tezlashtirish
4. TimescaleDB — time-series ma'lumotlar

PostgreSQL va Node.js

1. Node.js — pg moduli, connection pool, CRUD
2. Node.js — Transaction, xatolarni boshqarish
3. Real loyiha — to'liq DB schema loyihalash va yozish