Capstone 2: Тайлан, index, транзакц

Танилцуулга

Өмнөх хичээлд та Номын сангийн системийн суурийг тавьсан: гурван migration-оор бүтээгдсэн schema, гучин мянган зээлтэй бодит хэмжээний өгөгдөл, туршигдсан backup. Энэ хичээлд тэр сан дээр ажил эхэлнэ — бодит системд өдөр бүр болдог гурван зүйл.

Нэгдүгээрт, тайлан. Номын санч өглөө бүр хугацаа хэтэрсэн зээлүүдийг харна, гишүүн ирэхэд түүний түүхийг нээнэ. Эдгээр query-нүүдийг 30 000 зээл дээр ажиллуулж, Бүлэг 9-ийн EXPLAIN QUERY PLAN-ийн PostgreSQL хувилбар болох EXPLAIN ANALYZE-ээр сервер яг юу хийж байгааг, хэр удаж байгааг хэмжинэ.

Хоёрдугаарт, хурдасгах. Хэмжилтээс харагдах асуудлыг index-ээр засна — гэхдээ psql-д гараар биш, дөрөв дэх migration-оор, учир нь index бол бүтцийн нэг хэсэг бөгөөд бүх орчинд ижил байх ёстой. Мөн PostgreSQL-ийн нэг онцгой боломж — зөвхөн хэрэгтэй мөрүүдийг индексжүүлдэг partial index — -тэй танилцана.

Гуравдугаарт, үйлдэл. Ном олгох нь энгийн INSERT мэт боловч цаана нь бизнесийн дүрэм бий: гишүүн идэвхтэй байх ёстой, номын хувь үлдсэн байх ёстой. Хоёр номын санч нэгэн зэрэг сүүлийн хувийг өөр хоёр хүнд олгох гэж оролдвол юу болох вэ? Бүлэг 12-ын транзакц ба түгжээг ашиглан энэ үйлдлийг аюулгүй Python функц болгон бичиж, нэгэн зэрэг ажиллах байдлыг өөрийн нүдээр туршина.

Зүйрлэл

Шинэ эмнэлэг нээгдсэн гэж бодъё. Барилга баригдаж, тоног төхөөрөмж суурилуулагдсан — энэ бол өмнөх хичээл. Одоо эхний өвчтөнүүд ирж эхэлнэ, ажлын урсгалыг хэмжих цаг ирлээ: өвчтөн бүртгэлээс эмч хүртэл хэдэн минут явж байна вэ? Хэмжилт бүртгэлийн дэвтэр нэрийн дарааллаар биш, ирсэн дарааллаар бичигддэг тул эмнэлгийн карт хайхад үүрд удаж байгааг харуулна — тиймээс нэрийн хуудаслагч тавина (index). Мөн хамгийн сүүлийн хэвтэх ортой холбоотой дүрэм: хоёр бүртгэлийн ажилтан нэг мөчид ижил сүүлийн орыг хоёр өөр өвчтөнд олгож болохгүй. Тиймээс ор олгохын өмнө ажилтан тэр орны картыг гартаа барьж авдаг — нөгөө ажилтан карт буцаж ирэх хүртэл хүлээнэ. Энэ бол түгжээ.

Бэлтгэл

capstone хавтсанд, виртуал орчин идэвхтэй, $env:DATABASE_URL нь library_final руу заасан PowerShell цонхонд ажиллана. Эхлээд нэг чухал алхам — өмнөх хичээлд бөөнөөр өгөгдөл орсны дараа PostgreSQL-д хүснэгтүүдийн тухай статистик цуглуулахыг хэлнэ:

powershell

psql -U postgres -d library_final -c "ANALYZE"

ANALYZE нь "хүснэгт бүрийн мөрийн тоо, утгуудын тархалтын тухай статистик шинэчил" гэсэн тушаал. PostgreSQL query бүрт хамгийн хурдан замыг сонгохдоо энэ статистикт тулгуурладаг — query planner гэж нэрлэгддэг хэсэг. Статистик нь ардаа автоматаар шинэчлэгддэг ч бөөн ачааллын дараа хэсэг хугацаанд хуучирсан байж болно. Өгөгдөл их хэмжээгээр орсны дараа ANALYZE-ийг гараар ажиллуулах нь мэргэжлийн дадал.

Анхны жишээ: хэмжилт

Тайлан 1: гишүүний түүх

Гишүүн 42 номын санд ирлээ. Номын санч түүний бүх зээлийг харна. psql-д (psql -U postgres -d library_final):

sql

EXPLAIN ANALYZE
SELECT l.loaned_on, l.due_on, l.returned_on, b.title
FROM loans AS l
JOIN books AS b ON b.id = l.book_id
WHERE l.member_id = 42
ORDER BY l.loaned_on;

Query өөрөө юу гэж хэлж байна вэ гэвэл: “42-р гишүүний зээлүүдийг номтой нь холбож, авсан огноогоор эрэмбэлэн огноо болон номын нэрийг ав.” EXPLAIN ANALYZE гэсэн угтвар нь query-г жинхэнээр ажиллуулж, үр дүнгийн оронд сервер юу хийснийг, алхам бүр хэр удсаныг тайлагнана. Үр дүнгийн гол хэсэг (тоонууд таны компьютерт бага зэрэг өөр):

 Sort  (actual time=2.71..2.72 rows=15 loops=1)
   Sort Key: l.loaned_on
   ->  Hash Join  (actual time=0.31..2.69 rows=15 loops=1)
         Hash Cond: (l.book_id = b.id)
         ->  Seq Scan on loans l  (actual time=0.02..2.35 rows=15 loops=1)
               Filter: (member_id = 42)
               Rows Removed by Filter: 29985
         ->  Hash  (actual time=0.25..0.25 rows=500 loops=1)
               ->  Seq Scan on books b  (actual time=0.01..0.12 rows=500 loops=1)
 Planning Time: 0.31 ms
 Execution Time: 2.78 ms

Энэ төлөвлөгөөг доороос дээш уншина — сервер хамгийн гүн алхмаас эхэлдэг. Гол мөр нь Seq Scan on loans: “loans хүснэгтийг эхнээс нь дуустал дараалан унш” — Бүлэг 9-ийн SCAN. Filter: (member_id = 42) ба Rows Removed by Filter: 29985 хамтдаа хэлж байна: “30 000 мөр бүрийг шалгаж, 29 985-ыг нь хаяж, 15-ыг үлдээлээ.” Энэ 15 нь санамсаргүй биш — seed томъёоны дагуу гишүүн бүр яг 15 зээлтэй. 15 мөрийн төлөө 30 000 мөр уншсан. Үлдсэн хэсэг: номнуудыг санах ойд hash хүснэгт болгож (Hash), зээлүүдтэй тааруулж (Hash Join — Бүлэг 7-ийн JOIN-ийн нэг аргачлал), огноогоор эрэмбэлсэн (Sort). Нийт 2.78 миллисекунд.

2.78 миллисекунд бага мэт. Гэвч хоёр зүйлийг санаарай. Нэгдүгээрт, хугацаа хүснэгтийн хэмжээтэй шууд пропорциональ: 3 сая зээлтэй номын санд энэ 280 миллисекунд болно — нэг гишүүний түүхийг нээхэд. Хоёрдугаарт, олон номын санч зэрэг ажиллахад тус бүрийн query бүх хүснэгтийг уншина. Бүлэг 9-ийн index хичээлийн гол санаа энд PostgreSQL дээр дахин харагдаж байна.

Хүлээгдээгүй нээлт: FOREIGN KEY index биш

\d loans гэж харъя:

Indexes:
    "loans_pkey" PRIMARY KEY, btree (id)
Check constraints:
    ...
Foreign-key constraints:
    "loans_book_id_fkey" FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE RESTRICT
    "loans_member_id_fkey" FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE RESTRICT

Indexes хэсэгт зөвхөн PRIMARY KEY-ийн index. Бүлэг 9-д PRIMARY KEY ба UNIQUE автоматаар index үүсгэдэг гэж сурсан — энэ үнэн. Харин FOREIGN KEY үүсгэдэггүй. PostgreSQL заагдаж буй талд (books.id) index шаарддаг, заалт хийж буй талд (loans.book_id) шаарддаггүй. Энэ бол PostgreSQL-ийн хамгийн түгээмэл гэнэтийн зүйлсийн нэг: хөгжүүлэгчид “FOREIGN KEY тавьсан, тэгэхээр хурдан байх ёстой” гэж бодоод JOIN болон WHERE нь FOREIGN KEY баганаар явагддаг query-нүүд яагаад удаан байгааг гайхдаг. Мөн өөр нэг далд зардал бий: гишүүнийг устгах гэж оролдоход PostgreSQL RESTRICT дүрмийг шалгахын тулд loans-аас тэр гишүүний зээлийг хайх ёстой — index-гүйгээр бүхэл хүснэгтийг уншина.

Тайлан 2: хугацаа хэтэрсэн зээлүүд

sql

EXPLAIN ANALYZE
SELECT l.id, l.member_id, l.book_id, l.due_on
FROM loans AS l
WHERE l.returned_on IS NULL
  AND l.due_on < DATE '2026-10-20';

"Буцаагдаагүй, 2026-10-20-ноос өмнө хугацаа нь дууссан зээлүүдийг ав." Төлөвлөгөөний гол мөр:

 Seq Scan on loans l  (actual time=2.10..2.41 rows=69 loops=1)
   Filter: ((returned_on IS NULL) AND (due_on < '2026-10-20'::date))
   Rows Removed by Filter: 29931

Дахиад бүх хүснэгт — 69 мөрийн төлөө. Энэ query-ний онцлог: буцаагдаагүй зээл нийт 30 000-аас ердөө 138 — хүснэгтийн 0.5 хувь — бөгөөд хугацаа хэтрэлтийн тайлан үргэлж зөвхөн тэр жижиг хэсгийг сонирхдог. Энэ нь partial index-ийн яг тохиромжтой нөхцөл.

69 тоо ч санамсаргүй биш. Үр дүнг бүлэглэж баталъя:

sql

SELECT DATE '2026-10-20' - due_on AS days_overdue, COUNT(*)
FROM loans
WHERE returned_on IS NULL AND due_on < DATE '2026-10-20'
GROUP BY days_overdue
ORDER BY days_overdue DESC;

 days_overdue | count
--------------+-------
            5 |    23
            3 |    23
            1 |    23
(3 rows)

10-р сарын 1, 3, 5-нд авсан, буцаагдаагүй зээлүүд — seed-ийн томъёоны шууд үр дүн.

Олон жишээ

Жишээ 1: index-үүдийг migration-оор нэмэх

powershell

alembic revision -m "add loan indexes"

python

def upgrade() -> None:
    """Upgrade schema."""
    op.create_index("ix_loans_member_id", "loans", ["member_id"])
    op.create_index("ix_loans_book_id", "loans", ["book_id"])
    op.create_index(
        "ix_loans_open_due",
        "loans",
        ["due_on"],
        postgresql_where=sa.text("returned_on IS NULL"),
    )


def downgrade() -> None:
    """Downgrade schema."""
    op.drop_index("ix_loans_open_due", table_name="loans")
    op.drop_index("ix_loans_book_id", table_name="loans")
    op.drop_index("ix_loans_member_id", table_name="loans")

Эхний хоёр бол Бүлэг 9-ийн энгийн index: CREATE INDEX ix_loans_member_id ON loans (member_id). Гурав дахь нь шинэ. postgresql_where нь Alembic-д "энэ index-д зөвхөн WHERE нөхцөлийг хангасан мөрүүдийг оруул" гэж хэлдэг — PostgreSQL-д CREATE INDEX ix_loans_open_due ON loans (due_on) WHERE returned_on IS NULL болно. Энэ index 30 000 мөрийн оронд зөвхөн 138 буцаагдаагүй зээлийг агуулна: жижиг, хурдан, буцаагдсан зээлүүд өөрчлөгдөхөд шинэчлэгдэх хэрэггүй. Бүлэг 9-ийн “index-ийн бичих зардал” асуудлыг ихээхэн багасгадаг.

SQL-ийг урьдчилан харж (alembic upgrade head --sql — гурван CREATE INDEX харагдана), дараа нь:

powershell

alembic upgrade head

Энэ хэмжээний хүснэгтэд index-үүд хэдхэн миллисекундэд үүснэ. Нэг удаа хэлэх зүйл: CREATE INDEX нь index үүсэх хооронд хүснэгтэд бичихийг түгждэг; сая сая мөртэй, ачаалалтай жинхэнэ server дээр PostgreSQL-ийн CREATE INDEX CONCURRENTLY хэлбэрийг хэрэглэдэг — түүнийг transaction-д ажиллуулах боломжгүй тул Alembic-д тусгай тохиргоо шаарддаг. Энэ бол дараагийн шатны сэдэв.

Жишээ 2: дахин хэмжих

Тайлан 1-ийг ижил EXPLAIN ANALYZE-ээр дахин ажиллуулъя. Гол өөрчлөлт:

   ->  Bitmap Heap Scan on loans l  (actual time=0.04..0.07 rows=15 loops=1)
         Recheck Cond: (member_id = 42)
         ->  Bitmap Index Scan on ix_loans_member_id  (actual time=0.02..0.02 rows=15 loops=1)
               Index Cond: (member_id = 42)
 ...
 Execution Time: 0.35 ms

Seq Scan алга болж, Bitmap Index Scan on ix_loans_member_id гарч ирсэн: “index-ээс 42-р гишүүний мөрүүдийн байршлыг ол.” Дараа нь Bitmap Heap Scan тэр байршлуудаас л мөрүүдийг уншина. Rows Removed by Filter мөр байхгүй — хаягдах мөр огт уншигдаагүй. Таны төлөвлөгөөнд Index Scan using ix_loans_member_id гэж харагдаж болно — энэ бол ижил index-ийг ашиглах өөр аргачлал, planner мөрийн тоо болон тархалтаас хамааран сонгодог; хоёулаа index ашиглаж байна. 2.78-аас 0.35 миллисекунд — найм орчим дахин. Хүснэгт өсөхөд энэ харьцаа өснө: index-ийн хайлт мөрийн тооноос бараг хамаардаггүй.

Тайлан 2:

 Index Scan using ix_loans_open_due on loans l  (actual time=0.02..0.05 rows=69 loops=1)
   Index Cond: (due_on < '2026-10-20'::date)

Partial index ашиглагдсан. returned_on IS NULL нөхцөл Filter-т ч, Index Cond-д ч байхгүйг анзаараарай — planner index-д зөвхөн буцаагдаагүй мөр байгааг мэдэж байгаа тул тэр нөхцөлийг огт шалгах шаардлагагүй. Partial index ашиглагдахын тулд query-ний WHERE нь index-ийн WHERE-ийг агуулсан байх ёстой: returned_on IS NULL-гүй query энэ index-ийг ашиглахгүй.

Жишээ 3: ном олгох — бизнесийн дүрэмтэй транзакц

Одоо үйлдэл. Номыг олгохдоо гурван зүйлийг шалгах ёстой: гишүүн байгаа бөгөөд идэвхтэй, ном байгаа, буцаагдаагүй зээлийн тоо хувийн тооноос бага. Бүлэг 14-ийн сүүлийн хичээлийн дүрмээр түгжээтэй, нарийн логиктой үйлдэлд цэвэр SQL сонгоно. library_ops.py:

python

from datetime import date, timedelta

from sqlalchemy import text


class LendingError(Exception):
    pass


def lend_book(engine, member_id: int, book_id: int, today: date) -> int:
    with engine.begin() as conn:
        book = conn.execute(
            text("SELECT copies FROM books WHERE id = :book_id FOR UPDATE"),
            {"book_id": book_id},
        ).first()
        if book is None:
            raise LendingError(f"Ном олдсонгүй: {book_id}")

        member = conn.execute(
            text("SELECT is_active FROM members WHERE id = :member_id"),
            {"member_id": member_id},
        ).first()
        if member is None or not member.is_active:
            raise LendingError(f"Гишүүн олдсонгүй эсвэл идэвхгүй: {member_id}")

        open_loans = conn.execute(
            text("SELECT COUNT(*) FROM loans WHERE book_id = :book_id AND returned_on IS NULL"),
            {"book_id": book_id},
        ).scalar_one()
        if open_loans >= book.copies:
            raise LendingError(f"Хувь үлдээгүй: {open_loans}/{book.copies}")

        loan_id = conn.execute(
            text("""
                INSERT INTO loans (book_id, member_id, loaned_on, due_on)
                VALUES (:book_id, :member_id, :today, :due_on)
                RETURNING id
            """),
            {"book_id": book_id, "member_id": member_id,
             "today": today, "due_on": today + timedelta(days=14)},
        ).scalar_one()
        return loan_id


def return_book(engine, loan_id: int, today: date) -> None:
    with engine.begin() as conn:
        result = conn.execute(
            text("UPDATE loans SET returned_on = :today WHERE id = :loan_id AND returned_on IS NULL"),
            {"today": today, "loan_id": loan_id},
        )
        if result.rowcount != 1:
            raise LendingError(f"Буцаах зээл олдсонгүй: {loan_id}")

lend_book-ийг алхам алхмаар уншъя. Бүх зүйл нэг engine.begin() блокт — Бүлэг 12-ын транзакц: блок амжилттай дуусвал COMMIT, дотор нь ямар ч алдаа (бидний LendingError ч мөн) гарвал ROLLBACK. Тиймээс шалгалт бүтэлгүйтэх үед өгөгдлийн санд юу ч өөрчлөгдөхгүй.

Эхний query-ний FOR UPDATE бол шинэ: "энэ номын мөрийг унш, мөн транзакц дуустал түгж." Бүлэг 12-ын түгжрэлийн хичээлд UPDATE мөрийг автоматаар түгждэгийг харсан. SELECT ... FOR UPDATE бол уншихдаа, өөрчлөхөөсөө өмнө, урьдчилан түгжих арга — зүйрлэлийн "картыг гартаа барих." Яагаад хэрэгтэй вэ? Түгжээгүй бол хоёр номын санч нэгэн мөчид ижил сүүлийн хувийг олгох гэж оролдоход хоёулаа "буцаагдаагүй зээл 0, хувь 1" гэж уншиж, хоёулаа INSERT хийнэ — нэг хувьтай номд хоёр идэвхтэй зээл. Хоёр query тус бүрдээ зөв, дараалал нь буруу. FOR UPDATE-тэй бол хоёр дахь номын санчийн SELECT эхнийх нь COMMIT хийх хүртэл хүлээж, дараа нь шинэчлэгдсэн байдлыг — "буцаагдаагүй зээл 1, хувь 1" — уншиж, зөв татгалзана.

Дараагийн алхмууд энгийн шалгалт: гишүүн байгаа эсэх, идэвхтэй эсэх (Бүлэг 4-ийн soft delete-ийн is_active); буцаагдаагүй зээлийн тоо — шинэ partial index энд ч тустай. .first() нэг мөр эсвэл None буцаана; .scalar_one() яг нэг утга. Эцэст нь Бүлэг 11-ийн RETURNING-тэй INSERT.

return_book — Бүлэг 11-ийн rowcount шалгах загвар: AND returned_on IS NULL нь аль хэдийн буцаагдсан зээлийг хоёр дахь удаа буцаахаас сэргийлнэ, rowcount 1 биш бол зээл олдоогүй эсвэл аль хэдийн буцаагдсан.

Жишээ 4: туршилт

Үр дүнг тодорхой болгохын тулд нэг хувьтай шинэ ном нэмье (psql):

sql

INSERT INTO books (title, year, genre, copies, isbn)
VALUES ('Өгөгдлийн сангийн гарын авлага', 2026, 'Програмчлал', 1, '978-99978-9999')
RETURNING id;

 id
-----
 501
(1 row)

demo_lend.py:

python

import os
from datetime import date

from sqlmodel import create_engine

from library_ops import LendingError, lend_book, return_book

engine = create_engine(os.environ["DATABASE_URL"])
today = date(2026, 10, 20)

loan_id = lend_book(engine, member_id=1, book_id=501, today=today)
print("Гишүүн 1 зээллээ:", loan_id)

try:
    lend_book(engine, member_id=2, book_id=501, today=today)
except LendingError as error:
    print("Гишүүн 2:", error)

return_book(engine, loan_id, today)
print("Гишүүн 1 буцаалаа")

loan_id_2 = lend_book(engine, member_id=2, book_id=501, today=today)
print("Гишүүн 2 зээллээ:", loan_id_2)

try:
    return_book(engine, loan_id, today)
except LendingError as error:
    print("Дахин буцаах:", error)

Гишүүн 1 зээллээ: 30001
Гишүүн 2: Хувь үлдээгүй: 1/1
Гишүүн 1 буцаалаа
Гишүүн 2 зээллээ: 30002
Дахин буцаах: Буцаах зээл олдсонгүй: 30001

Энэ код юу хийж байна вэ гэвэл: "гишүүн 1-д олго; гишүүн 2-т олгох гэж оролд; гишүүн 1-ийн зээлийг буцаа; гишүүн 2-т дахин олго; гишүүн 1-ийн зээлийг хоёр дахь удаа буцаах гэж оролд." Бизнесийн дүрэм бүр ажилласан. Бүтэлгүйтсэн оролдлого бүр өгөгдлийн санд юу ч үлдээгээгүй — SELECT COUNT(*) FROM loans WHERE book_id = 501-ээр шалгавал хоёр мөр.

Жишээ 5: түгжээг өөрийн нүдээр

Одоо нэгэн зэрэг ажиллахыг туршина — Бүлэг 12-ын түгжрэлийн хичээлийн хоёр цонхны арга. Эхлээд гишүүн 2-ын зээлийг буцаая (psql): UPDATE loans SET returned_on = DATE '2026-10-20' WHERE id = 30002; — ном 501 дахин чөлөөтэй.

Цонх А (psql) — өөр номын санч ном 501-ийг олгох явцдаа байна:

sql

BEGIN;
SELECT copies FROM books WHERE id = 501 FOR UPDATE;

Транзакц нээлттэй, мөр түгжигдсэн. COMMIT хийхгүй.

Цонх Б (PowerShell) — гишүүн 3-т олгох жижиг скрипт ажиллуулна:

powershell

python -c "import os; from datetime import date; from sqlmodel import create_engine; from library_ops import lend_book; print(lend_book(create_engine(os.environ['DATABASE_URL']), 3, 501, date(2026, 10, 20)))"

Энэ цонх юу ч хэвлэхгүй, хүлээж байна — Python-ы FOR UPDATE А цонхны түгжээг хүлээж байна. Энэ бол зүйрлэлийн "карт буцаж ирэх хүртэл хүлээх."

Цонх А — номын санч олголтоо дуусгая:

sql

INSERT INTO loans (book_id, member_id, loaned_on, due_on)
VALUES (501, 4, DATE '2026-10-20', DATE '2026-11-03');
COMMIT;

COMMIT-ийн мөчид Б цонх сэрнэ:

Traceback (most recent call last):
  ...
library_ops.LendingError: Хувь үлдээгүй: 1/1

Б цонхны функц түгжээ чөлөөлөгдмөгц номын мөрийг уншиж, дараа нь буцаагдаагүй зээлийг тоолоход А-ийн commit хийсэн зээлийг харж, зөв татгалзсан. Сүүлийн хувийг давхар олгох боломж байсангүй. Бүлэг 12-ын READ COMMITTED: хүлээлтийн дараах query бүр хамгийн сүүлийн commit хийгдсэн өгөгдлийг хардаг.

Хэрэв FOR UPDATE-ийг хассан бол Б цонх хүлээхгүй, А commit хийхээс өмнө "буцаагдаагүй зээл 0" гэж уншиж INSERT хийх байсан — ном 501 хоёр идэвхтэй зээлтэй болох байсан. Сонирхож байвал туршиж үзэж болно, дараа нь илүү зээлийг psql-ээр устгаарай.

Бүлэг 12-ын нэг анхааруулга энд бодит болно: А цонх COMMIT хийхээ мартаад гарсан бол Б үүрд хүлээх байсан. Жинхэнэ програмд lock_timeout тохируулах нь хүлээлтийг хязгаарлана.

Жишээ 6: хаалт — backup ба түүх

Өнөөдрийн ажлыг хадгалъя:

powershell

alembic history

7f8e...  -> 9d0e1f2a3b4c (head), add loan indexes
7f8e9d0c1b2a -> 3c4d5e6f7a8b, create book_authors and loans
1a2b3c4d5e6f -> 7f8e9d0c1b2a, create books
<base> -> 1a2b3c4d5e6f, create members and authors

(эхний мөрийн зүүн тал нь таны 3-р migration-ий дугаар.) Дөрвөн бүтцийн өөрчлөлт, бүгд дугаартай, тайлбартай, буцаагдах боломжтой.

powershell

pg_dump -U postgres -d library_final -F c -f library_final_2026-10-20.dump
pg_dump -U postgres -d library_final --schema-only -f schema_snapshot.sql
git add .
git commit -m "Loan indexes, lending operations"

Шинэ backup, шинэчлэгдсэн бүтцийн зураг — Git-ийн diff-ээс schema_snapshot.sql-д гурван CREATE INDEX нэмэгдсэнийг харна. Өмнөх хичээлийн нэгэн адил backup-ыг шинэ санд сэргээж шалгах нь журмын нэг хэсэг.

Туршаад үзээрэй

Хэрэв сонирхож байвал хугацаа хэтэрсэн зээлийн тайланг Бүлэг 14-ийн сүүлийн хичээлийн аргаар sql/overdue.sql файл болгож, library_final дээр Python-оос ажиллуулж 69 мөр гарахыг шалгаарай. Эсвэл lend_book-д нэмэлт дүрэм нэмж үзээрэй: нэг гишүүн нэгэн зэрэг гурваас олон буцаагдаагүй зээлтэй байж болохгүй — энэ шалгалтад гишүүний мөрийг бас FOR UPDATE-ээр түгжих хэрэгтэй юу гэдгийг бодоорой. Эдгээр нь зөвхөн санал бөгөөд дараагийн хичээл тэдгээрийг хийсэн гэж тооцохгүй.

Түгээмэл алдаанууд

Бөөн ачааллын дараа ANALYZE мартах. Статистик хуучирсан үед planner хүснэгтийг хоосон гэж бодож index-ийг үл тоомсорлож эсвэл буруу JOIN аргачлал сонгож болно. EXPLAIN ANALYZE-ийн rows= таамаглал ба actual ... rows= бодит тоо хоорондоо маш их зөрвөл статистик хуучирсан шинж.

EXPLAIN ANALYZE-ийг өөрчлөх query дээр хэрэглэх. EXPLAIN ANALYZE DELETE FROM loans WHERE ... нь төлөвлөгөөг харуулахаас гадна жинхэнээр устгана — ANALYZE query-г ажиллуулдаг. Бичих query-ний төлөвлөгөөг хэмжих бол BEGIN; EXPLAIN ANALYZE ...; ROLLBACK; гэж транзакцад ороо. Зөвхөн EXPLAIN (ANALYZE-гүй) query-г ажиллуулдаггүй, зөвхөн таамаглалыг харуулна.

FOREIGN KEY index үүсгэдэг гэж бодох. Энэ хичээлийн гол нээлт. Query болон устгалтад хэрэглэгддэг FOREIGN KEY баганад index-ийг өөрөө нэм.

Транзакцгүйгээр FOR UPDATE хэрэглэх. psql-д BEGIN-гүй SELECT ... FOR UPDATE бичвэл тэр query өөрийн автомат транзакцад ажиллаж, дуусмагц түгжээ чөлөөлөгдөнө — юуг ч хамгаалахгүй. Түгжээ шалгалт ба өөрчлөлтийг хамарсан нэг транзакцад л утгатай; lend_book бүх алхмыг нэг engine.begin()-д багтаасан нь яг ийм учиртай.

Partial index ашиглагдахгүй байхад гайхах. WHERE нь index-ийн нөхцөлийг (returned_on IS NULL) агуулаагүй query түүнийг ашиглахгүй. Index-ийн нөхцөлийг query-ний хэлбэртэй тааруул.

Товч дүгнэлт

Энэ хичээлд та бүтээсэн системээ бодит ажилд оруулж, хэмжиж, хамгааллаа. Бөөн ачааллын дараа ANALYZE-ээр planner-ийн статистикийг шинэчилсэн. EXPLAIN ANALYZE-ийн төлөвлөгөөг доороос дээш уншиж, Seq Scan ба Rows Removed by Filter: 29985-аас 15 мөрийн төлөө бүх хүснэгт уншигдаж байгааг харсан; PostgreSQL FOREIGN KEY-д index үүсгэдэггүйг нээсэн. Дөрөв дэх migration-оор хоёр энгийн index ба зөвхөн буцаагдаагүй 138 зээлийг агуулсан partial index нэмж, төлөвлөгөө Bitmap Index Scan, Index Scan болж, хугацаа найм орчим дахин буурсныг хэмжсэн. Ном олгох үйлдлийг нэг транзакцад SELECT ... FOR UPDATE түгжээ, гишүүн ба хувийн шалгалт, RETURNING-тэй INSERT-ээр бичиж, бүтэлгүйтэл бүр ROLLBACK болдгийг; буцаалтыг rowcount шалгалттай бичсэн. Хоёр цонхны туршилт түгжээ хүлээлгэж, сүүлийн хувийг давхар олгохоос сэргийлснийг харуулсан. Ажлыг түүх, backup, бүтцийн зураг, commit-оор хаасан.

Ингээд Capstone дууслаа. Дараагийн, сүүлийн хичээл шинэ сэдэв авчрахгүй — та сургалтын туршид юуг эзэмшсэнээ эргэн харж, эндээс хаашаа явж болохыг харна.