PRO LEVEL
0%0 / 28
01 / 28

SQL Asoslari & SELECT

PostgreSQL bilan 0 dan professional darajagacha: SELECT sintaksisi, filtrlar, tartiblash, cheklash va real loyihalarda qo'llaniladigan eng muhim usullar.

Asosiy SELECT sintaksisi
select_basics.sql
SELECT id, email, full_name, age, created_at
FROM users
WHERE age >= 18 
  AND status = 'active'
  AND created_at >= '2025-01-01'
ORDER BY created_at DESC, full_name ASC
LIMIT 20 
OFFSET 0;
Eng muhim qoida: Hech qachon SELECT * ishlatmang. Faqat kerakli ustunlarni tanlang — bu tarmoq yuklamasini va xotira sarfini kamaytiradi.
WHERE shartlarida yaxshi amaliyotlar
where_best_practices.sql
SELECT * FROM orders 
WHERE created_at BETWEEN '2025-03-01' AND '2025-03-31'
  AND amount > 50000
  AND status IN ('paid', 'shipped');
Production maslahati: Sana va vaqt bilan ishlashda doim BETWEEN yoki >= + < ishlatish tavsiya etiladi.
02 / 28

Data Types & DDL

PostgreSQL’da mavjud bo‘lgan barcha muhim ma’lumot turlari, ularning afzalliklari va real loyihalarda qanday tanlash kerakligi.

Eng muhim Data Types taqqoslash

TEXT

Eng tavsiya etiladigan matn turi. Uzunlik cheklovi yo‘q.

NUMERIC / DECIMAL

Pul va aniq hisoblar uchun majburiy. FLOAT ishlatmang!

TIMESTAMPTZ

Vaqt mintaqasi bilan saqlaydi. Har doim shuni ishlating.

JSONB

NoSQL uslubidagi ma’lumotlar uchun eng kuchli va tez turi.

ddl_best_practice.sql
CREATE TABLE orders (
    id              BIGSERIAL PRIMARY KEY,
    user_id         BIGINT NOT NULL,
    total_amount    NUMERIC(12,2) CHECK (total_amount > 0),
    status          VARCHAR(20) DEFAULT 'pending',
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    metadata        JSONB,
    CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
Muhim: Pul miqdorlari uchun FLOAT yoki DOUBLE PRECISION ishlatish katta xatolarga olib keladi. Doim NUMERIC ishlating.
03 / 28

Constraints & Keys

Ma'lumotlar yaxlitligini ta'minlash: Primary Key, Foreign Key, Unique, Check va NOT NULL. Real loyihalarda qanday to'g'ri qo'llash kerak.

Asosiy Constraints turlari

PRIMARY KEY

Har bir qatorni noyob identifikatsiya qiladi. Avtomatik index yaratadi.

FOREIGN KEY

Boshqa jadval bilan bog'lanish. Ma'lumotlar yaxlitligini saqlaydi.

UNIQUE + NOT NULL

Email, username kabi noyob maydonlar uchun majburiy.

CHECK

Qo'shimcha biznes qoidalarini majburlash (masalan, age >= 16).

constraints.sql
CREATE TABLE orders (
    id              BIGSERIAL PRIMARY KEY,
    user_id         BIGINT NOT NULL,
    total_amount    NUMERIC(12,2) CHECK (total_amount > 0),
    status          VARCHAR(20) DEFAULT 'pending',
    created_at      TIMESTAMPTZ DEFAULT NOW(),

    CONSTRAINT fk_user 
        FOREIGN KEY (user_id) 
        REFERENCES users(id) 
        ON DELETE CASCADE 
        ON UPDATE RESTRICT,

    CONSTRAINT check_status 
        CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled'))
);
Production maslahati: ON DELETE CASCADE ni ehtiyotkorlik bilan ishlating. Ba'zida ON DELETE SET NULL yoki RESTRICT xavfsizroq bo'ladi.
04 / 28

DML: INSERT, UPDATE, DELETE

Ma'lumot qo'shish, yangilash va o'chirish. RETURNING, bulk insert va xavfsiz update/delete usullari.

INSERT va RETURNING
insert.sql
INSERT INTO users (email, full_name, age)
VALUES ('ali@example.com', 'Ali Qodirov', 28)
RETURNING id, created_at;
Bulk Insert (tezroq)
bulk_insert.sql
INSERT INTO products (name, price, category)
VALUES 
    ('Laptop', 1299.99, 'electronics'),
    ('Mouse', 29.99, 'accessories')
RETURNING id;
Xavf: UPDATE yoki DELETE da WHERE shartini unutish butun jadvalni o'zgartirishi mumkin!
05 / 28

JOINlar & Subqueries

INNER, LEFT, RIGHT, FULL JOIN, CROSS JOIN va subquery'lardan foydalanish. Real loyihalarda qaysi JOINni qachon ishlatish kerak.

Eng ko'p ishlatiladigan JOINlar
joins.sql
SELECT 
    u.full_name, 
    o.total_amount, 
    o.status
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01'
ORDER BY o.total_amount DESC;
LEFT JOIN — eng xavfsiz va ko'p ishlatiladigan. Foydalanuvchi bo'lsa ham, buyurtma bo'lmasa ham natija chiqadi.
Subquery misoli
subquery.sql
SELECT full_name 
FROM users 
WHERE id IN (
    SELECT user_id 
    FROM orders 
    WHERE total_amount > 1000000
);
06 / 28

GROUP BY & Aggregations

COUNT, SUM, AVG, MIN, MAX va HAVING filtri. Guruhlash va statistika chiqarishning professional usullari.

group_by.sql
SELECT 
    category,
    COUNT(*) as product_count,
    AVG(price) as avg_price,
    SUM(stock) as total_stock
FROM products
GROUP BY category
HAVING COUNT(*) > 5 
   AND AVG(price) > 50
ORDER BY avg_price DESC;
HAVING — guruhlangandan keyin filtrlaydi. WHERE esa guruhlashdan oldin ishlaydi.
07 / 28

Window Functions

ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, running total va PARTITION BY orqali kuchli tahlillar.

window_functions.sql
SELECT 
    full_name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) as rank_all,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank_in_dept,
    SUM(salary) OVER (PARTITION BY department) as dept_total
FROM employees;
Window funksiyalari qatorlarni guruhlamaydi, lekin har bir qator uchun hisoblash imkonini beradi. Bu juda kuchli vosita.
08 / 28

CTE & Recursive Queries

WITH operatori, murakkab so'rovlarni o'qiladigan qilish va rekursiv CTE (daraxt, ierarxiya).

cte_example.sql
WITH active_users AS (
    SELECT * FROM users 
    WHERE status = 'active'
),
high_value_orders AS (
    SELECT user_id, SUM(amount) as total 
    FROM orders 
    GROUP BY user_id 
    HAVING SUM(amount) > 500000
)
SELECT au.full_name, hvo.total
FROM active_users au
JOIN high_value_orders hvo ON au.id = hvo.user_id;
09 / 28

Indexes & EXPLAIN

Indeks turlari (B-Tree, Hash, GIN, GiST), qachon yaratish kerakligi va so'rov rejasini tahlil qilish.

explain.sql
EXPLAIN ANALYZE 
SELECT * FROM users 
WHERE email = 'test@example.com';
Indeks yaratishdan oldin har doim EXPLAIN ANALYZE ishlatib, haqiqiy tezlikni ko'ring.
10 / 28

Query Optimization

Seq Scan, Index Scan, Bitmap Scan, join strategiyalari va so'rovlarni tezlashtirishning professional usullari.

Tezlashtirish bo'yicha maslahatlar
  • Keraksiz SELECT * dan voz keching
  • Filtrlarni indekslangan ustunlarga qo'ying
  • LIMIT va OFFSET ni ehtiyotkorlik bilan ishlating (katta OFFSET sekin)
  • Complex join'larni CTE yoki Materialized View bilan almashtiring
11 / 28

Transactions & ACID

ACID tamoyillari (Atomicity, Consistency, Isolation, Durability) va PostgreSQL’da tranzaksiyalarni to‘g‘ri boshqarish. Real loyihalarda tranzaksiya muhimligi.

ACID nima va nima uchun kerak?

Atomicity

Tranzaksiya butunlay bajariladi yoki umuman bajarilmaydi.

Consistency

Tranzaksiya oldin va keyin bazani yaxlit holatda saqlaydi.

Isolation

Bir tranzaksiya boshqasiga aralashmaydi (Isolation levels).

Durability

Tranzaksiya muvaffaqiyatli tugagach, natija doimiy saqlanadi.

Oddiy tranzaksiya misoli
transaction_basic.sql
BEGIN;

UPDATE accounts 
SET balance = balance - 500 
WHERE id = 1;

UPDATE accounts 
SET balance = balance + 500 
WHERE id = 2;

COMMIT;
Agar ikkinchi UPDATE muvaffaqiyatsiz bo‘lsa, birinchi o‘zgartirish ham bekor qilinadi (ROLLBACK avtomatik amalga oshiriladi).
SAVEPOINT bilan murakkab tranzaksiya
savepoint.sql
BEGIN;

UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
SAVEPOINT after_first_transfer;

UPDATE accounts SET balance = balance + 1000 WHERE id = 2;

-- Agar ikkinchi operatsiya muvaffaqiyatsiz bo‘lsa:
ROLLBACK TO after_first_transfer;

COMMIT;
Production maslahati: Har doim muhim operatsiyalarni (pul o‘tkazish, buyurtma yaratish) tranzaksiya ichida bajaring.
12 / 28

Locking & Concurrency

PostgreSQL’da row-level locking, deadlocks, SELECT FOR UPDATE va yuqori yuklama ostida to‘g‘ri ishlash usullari.

Eng muhim locking turlari
locking.sql
SELECT * 
FROM accounts 
WHERE id = 1 
FOR UPDATE;   -- Row ni qulflaydi

SELECT * 
FROM accounts 
WHERE id = 1 
FOR UPDATE SKIP LOCKED; -- Boshqa tranzaksiya qulflaganini o‘tkazib yuboradi
Deadlock yuzaga kelganda PostgreSQL avtomatik bir tranzaksiyani o‘ldiradi. Har doim bir xil tartibda qulflashga harakat qiling.
13 / 28

JSON & JSONB

PostgreSQL’da JSON va JSONB farqlari, JSONB ning afzalliklari, operatorlar (@>, ?, ->, #>>) va GIN indekslari.

JSON vs JSONB
JSON
Matn sifatida saqlanadi. Har safar parse qilinadi. Sekin.
JSONB
Binar formatda saqlanadi. Tez qidirish va indekslash mumkin.
jsonb_examples.sql
SELECT 
    id, 
    metadata->>'city' AS city,
    metadata->'address'->>'street' AS street
FROM users
WHERE metadata @> '{"status": "active", "verified": true}';
Eng muhim operatorlar: @> (contains), ? (key exists), -> (object), #>> (text).
14 / 28

Full-Text Search

tsvector va tsquery yordamida Google kabi qidiruv tizimini yaratish. to_tsvector, to_tsquery va GIN indekslari.

full_text_search.sql
SELECT title, body 
FROM articles
WHERE to_tsvector('english', body) @@ to_tsquery('english', 'postgresql & performance');

CREATE INDEX idx_articles_fts 
    ON articles USING GIN (to_tsvector('english', body));
15 / 28

PL/pgSQL Functions

PostgreSQL’da server tomonida dasturlash: funksiyalar, parametrlar, shartli operatorlar, tsikllar va xatolarni boshqarish.

plpgsql_function.sql
CREATE OR REPLACE FUNCTION calculate_discount(
    p_amount NUMERIC,
    p_customer_type VARCHAR
) RETURNS NUMERIC AS $$
DECLARE
    discount_rate NUMERIC := 0;
BEGIN
    IF p_customer_type = 'VIP' THEN
        discount_rate := 0.20;
    ELSIF p_amount > 1000 THEN
        discount_rate := 0.15;
    END IF;

    RETURN p_amount * (1 - discount_rate);
END;
$$ LANGUAGE plpgsql;
16 / 28

Triggers & Events

Ma'lumot o‘zgarganda avtomatik ishlaydigan triggerlar. Auditing, log yozish va biznes qoidalarini majburlash.

trigger_example.sql
CREATE OR REPLACE FUNCTION log_user_changes()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO user_audit (user_id, action, old_data, new_data, changed_at)
    VALUES (
        NEW.id, 
        TG_OP, 
        row_to_json(OLD), 
        row_to_json(NEW), 
        NOW()
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_user_audit
    AFTER INSERT OR UPDATE OR DELETE ON users
    FOR EACH ROW EXECUTE FUNCTION log_user_changes();
17 / 28

Views & Materialized Views

Oddiy View va Materialized View farqlari, qachon qaysisini ishlatish va yangilash strategiyalari.

materialized_view.sql
CREATE MATERIALIZED VIEW sales_summary AS
SELECT 
    date_trunc('month', created_at) AS month,
    SUM(total_amount) AS total_sales,
    COUNT(*) AS order_count
FROM orders
GROUP BY 1;

REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;
CONCURRENTLY kalit so‘zi bilan Materialized View ni yangilashda jadval qulflanmaydi.
18 / 28

Stored Procedures

Funksiyalardan farqli ravishda tranzaksiyalarni ichida boshqarish imkoniyati (COMMIT/ROLLBACK).

stored_procedure.sql
CREATE OR REPLACE PROCEDURE transfer_money(
    from_account INT, 
    to_account INT, 
    amount NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE accounts SET balance = balance - amount 
    WHERE id = from_account;

    UPDATE accounts SET balance = balance + amount 
    WHERE id = to_account;

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
$$;
19 / 28

Extensions & Custom Types

PostgreSQL kengaytmalari (uuid-ossp, pg_trgm, PostGIS) va o‘z ma’lumot turlarini yaratish.

extensions.sql
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS pg_trgm;

SELECT uuid_generate_v4();

CREATE TYPE user_status AS ENUM ('active', 'inactive', 'banned');
20 / 28

Backup & Restore

pg_dump, pg_restore, point-in-time recovery va productionda ma’lumotlarni xavfsiz saqlash strategiyalari.

backup_commands.sh
# To'liq backup
pg_dump -U postgres -d mydb -F c > backup_$(date +%Y%m%d).dump

# Restore
pg_restore -U postgres -d mydb -c backup_20250326.dump

# Faqat schema
pg_dump -U postgres -d mydb --schema-only > schema.sql
Har kuni to‘liq backup + har soatda WAL arxivlash (Point-in-Time Recovery) qilish tavsiya etiladi.
21 / 28

Replication & HA

PostgreSQL’da yuqori mavjudlik (High Availability) va ma’lumotlar sinxronizatsiyasi. Streaming Replication, Failover va Load Balancing.

Streaming Replication asoslari
replication_setup.sql
-- Primary serverda (postgresql.conf)
wal_level = replica
max_wal_senders = 10
wal_keep_size = 1GB

-- Replica serverda recovery.conf yoki postgresql.conf
primary_conninfo = 'host=primary_ip port=5432 user=repl_user password=strongpass'
restore_command = 'cp /archive/%f %p'
Hot Standby — Replica serverda o‘qish mumkin. Bu o‘qish yukini primarydan yengillashtiradi.
Replica serverda hot_standby = on bo‘lishi shart, aks holda faqat recovery holatida ishlaydi.
22 / 28

Monitoring & pg_stat

PostgreSQL monitoringi: pg_stat_activity, pg_stat_statements, pg_stat_database va real vaqtda tizim kuzatuv usullari.

monitoring.sql
SELECT 
    pid, 
    datname, 
    usename, 
    application_name,
    state,
    query,
    wait_event,
    now() - query_start as duration
FROM pg_stat_activity 
WHERE state = 'active' 
  AND pid <> pg_backend_pid()
ORDER BY duration DESC;

SELECT * FROM pg_stat_statements 
ORDER BY total_exec_time DESC 
LIMIT 10;
pg_stat_statements ni yoqish uchun extension o‘rnating: CREATE EXTENSION pg_stat_statements;
23 / 28

Security & Row Level Security

PostgreSQL xavfsizligi: Role, Privilege, RLS (Row Level Security) va ma’lumotni foydalanuvchiga qarab cheklash.

row_level_security.sql
ALTER TABLE employees ENABLE ROW LEVEL SECURITY;

CREATE POLICY employee_self_policy ON employees
    USING (id = current_setting('app.current_user_id')::bigint);

CREATE POLICY manager_policy ON employees
    USING (department = current_setting('app.current_department'));
RLS yoqilganda avvalgi GRANT lar yetarli bo‘lmaydi. Har doim policy yaratishni unutmang.
24 / 28

Advanced Indexing

Partial Index, Expression Index, BRIN, GIN, GiST va murakkab indekslash strategiyalari.

advanced_index.sql
CREATE INDEX idx_active_users 
    ON users(id) 
    WHERE status = 'active';

CREATE INDEX idx_lower_email 
    ON users (lower(email));

CREATE INDEX idx_orders_brin 
    ON orders USING BRIN (created_at);
Partial Index — juda samarali. Faqat tez-tez ishlatiladigan qatorlarni indekslaydi va joy tejaydi.
25 / 28

Logical Replication & CDC

Logical Replication, Publication/Subscription va Change Data Capture (CDC) orqali real vaqtda ma’lumot sinxronizatsiyasi.

logical_replication.sql
CREATE PUBLICATION sales_pub FOR TABLE orders, customers;

CREATE SUBSCRIPTION sales_sub 
    CONNECTION 'host=primary port=5432 user=repl password=pass dbname=mydb'
    PUBLICATION sales_pub;
26 / 28

VACUUM & Maintenance

Bloat muammosi, Autovacuum sozlamalari, VACUUM FULL, ANALYZE va bazani sog‘lom saqlash strategiyalari.

vacuum.sql
VACUUM ANALYZE orders;                    -- oddiy tozalash
VACUUM FULL orders;                      -- jadvalni qayta yozadi (qimmat)

SELECT relname, last_vacuum, last_analyze 
FROM pg_stat_user_tables 
WHERE relname = 'orders';
VACUUM FULL jadvalni butunlay qulflaydi. Productionda ish vaqtida ishlatmang.
27 / 28

Performance Tuning

postgresql.conf ning eng muhim parametrlarini sozlash: shared_buffers, work_mem, max_connections, effective_cache_size va boshqalar.

postgresql.conf_tuning
shared_buffers = 25% of RAM          -- katta serverlarda 8-16GB
work_mem = 64MB                     -- sort va hash join uchun
maintenance_work_mem = 1GB
effective_cache_size = 75% of RAM
max_connections = 200
random_page_cost = 1.1              -- SSD uchun
Har bir server uchun tuning individual bo‘ladi. Hech qachon “copy-paste” konfiguratsiya ishlatmang.
28 / 28

Pro Tips & Best Practices

Senior va Lead darajasida PostgreSQL loyihalarida qo‘llaniladigan eng muhim maslahatlar, anti-patternlar va 2026 yil uchun tavsiyalar.

Eng muhim Pro Tips
  • Har doim TIMESTAMPTZ va NUMERIC ishlating
  • SELECT * dan butunlay voz keching
  • Murakkab hisobotlarni Materialized View yoki Cache orqali qiling
  • Har bir muhim operatsiyani tranzaksiya ichida bajaring
  • Indeks yaratishdan oldin EXPLAIN ANALYZE ishlatib tekshiring
  • RLS + Policy bilan xavfsizlikni kuchaytiring
  • Monitoringni doimiy ravishda sozlang (Prometheus + Grafana)

🏆 PostgreSQL Pro Kursi Yakunlandi!

Siz endi PostgreSQL bo‘yicha chuqur bilim egasisiz. Real production loyihalarda bu bilimlarni qo‘llashingiz mumkin.

Keyingi qadam: O‘z loyihangizda bu texnikalarni sinab ko‘ring va monitoring tizimini sozlang.