先看懂資料如何流動，再親手建立可驗證的 CRUD

Week 1 Core 學習清單

• 先閉卷寫出 SQL、driver、SQLAlchemy、domain／adapter 各自的責任。
• 啟動 PostgreSQL 18，保存 SELECT version() raw output。
• 用 Alembic migration 建立 events 與 seats。
• 用純 SQL 完成 INSERT／SELECT／UPDATE，執行前先預測 row count。
• 理解 uuidv7()、foreign key、UNIQUE(event_id, label) 與 status check。
• 跑過累積 ticketal/ 的 domain import-boundary test。
• 跑過 application use case 單元測試與 canonical SQLAlchemy CRUD Lab。
• 閉卷解釋 session、transaction、driver 與 event loop 的邊界。
• MVCC 與 EXPLAIN 本週只做 bridge，不閱讀完整 supplements。
• 保存錯誤模型與至少 24 小時後的變化題結果。

Optional supplements 在 supplements/；只有 Gate 卡住或出現對應問題時才讀，不屬於 Week 1 Core checklist。

本專案 Schema 規則（先讀，之後每週都適用）

Ticketal 的資料表固定為四張，不得改名/合併/新增：events · seats · holds · bookings。

• 逐週長出來：W1 只先建 events + seats；holds 於 W3 引入、bookings 於 W3–W4 引入。先建子集沒問題，但表名與主鍵型別必須一致。
• seats 主鍵用 uuidv7()（PG18）；events/holds/bookings 用 bigint GENERATED ALWAYS AS IDENTITY。
• 不要加 users 表：本專案刻意排除身分/認證，聚焦資料庫並行。booking 要識別顧客用一個 customer_ref text 欄位即可。
• holds 與 bookings 不可合併：holds 是高頻 insert/delete 的暫時保留，是 W4 觀察 bloat 的主角；bookings 是確認後的訂單。

怎麼用這份手冊

每個區塊固定五步：

1. 讀什麼：精確到「讀哪幾節、幾分鐘、讀到能答檢核題就收手」。標準是「能通過檢核題」，不是「讀完」。
2. 自我檢核：先自己答，再對解答。答不出來＝回去讀。
3. 動手：完整程式碼，直接複製。
4. Evidence：先寫預測，再保存自己的 raw output；本文輸出只用來辨認形狀。
5. 延遲重測：至少 24 小時後換一個輸入再做一次。

前置：環境（沒做完後面跑不動）

1. 用 Docker Compose 起 PostgreSQL 18 + Redis

用專案根目錄的 docker-compose.yml（見技術棧附檔），然後：

docker compose up -d          # 背景起 postgres:18 + redis:7
docker compose ps             # 兩個都要 healthy

預期輸出：ticketal-db 與 ticketal-redis 兩行，STATUS 皆為 ... (healthy)。 （W1 還用不到 Redis，但環境一次備齊，W3 就不用再動。）

2. 使用唯一的累積 Python 專案（uv）

cd "PostgreSQL Deep Dive/ticketal"
uv sync
uv run alembic upgrade head

⚠️ 別 uv add pgserver——那是雲端 routine 用的，本機用 Docker 容器。

3. 連線測試

docker compose exec db psql -U postgres -d ticketal -c "SELECT version();"

預期輸出（重點看到 PostgreSQL 18.x）：

                                   version
------------------------------------------------------------------------------
 PostgreSQL 18.x on x86_64-pc-linux-gnu, compiled by gcc ...
(1 row)

✅ 前置驗收：SELECT version(); 跑出 18.x 即可開始。連線字串是 postgresql+asyncpg://postgres:dev@localhost:5432/ticketal。

Part A — psql / pgcli 基本功（30 分鐘，純動手不讀文件）

讀什麼

psql 指令不用讀文件，跟著做一輪就會。目標是之後做實驗不卡在「怎麼連、怎麼看表」。

動手 + 預期輸出

進互動模式：

docker compose exec db psql -U postgres -d ticketal

依序輸入這些反斜線指令（psql meta-command），每個看一下輸出：

自我檢核

1. \dt 和 \d seats 差在哪？
2. 什麼時候會想開 \x？
3. \timing on 之後，耗時數字是「規劃 + 執行」還是只有執行？

Part B — 讀懂 Alembic schema，再用純 SQL CRUD

讀什麼

先不讀外部文件。Schema 只由 ticketal/migrations/ 建立；這一段先看 migration 產生的資料庫結構，再用純 SQL 查改資料。Part E 才看 SQLAlchemy 如何表達同樣操作。

動手 1：由 Alembic 建表（canonical schema）

先打開 ../../ticketal/migrations/versions/0001_week1_events_seats.py，預測會建立哪些 constraint，再執行：

cd "PostgreSQL Deep Dive/ticketal"
uv run alembic upgrade head
uv run alembic current

第一次 upgrade 會看到 Running upgrade -> 0001；已套用時可能沒有 upgrade 訊息。alembic current 必須顯示 0001 (head)。

[!warning] 不要在學習腳本使用 Base.metadata.drop_all()／create_all()。前者會刪資料，後者會繞過 migration history；兩者都違反本課程的 schema source of truth。

💡 為什麼 seats 用 uuidv7() 而不是整數或 uuid v4？ uuidv7()（PG18 內建）產生的是時間排序的 UUID。當主鍵時，新列的鍵值大致遞增，插入集中在 B-tree 最右側、減少 page split；而隨機的 uuid v4 會讓插入散落各處、加速索引膨脹。這個「為什麼」到 W5 講索引時會完全打通——現在先照 canonical 用它。

動手 2：看結構

\d seats

預期輸出（對照標準答案）：

                                   Table "public.seats"
  Column  |         Type          | Collation | Nullable |            Default
----------+-----------------------+-----------+----------+--------------------------------
 id       | uuid                  |           | not null | uuidv7()
 event_id | bigint                |           | not null |
 label    | character varying(20) |           | not null |
 status   | character varying(20) |           | not null | 'available'::character varying
 version  | integer               |           | not null | 0
Indexes:
    "seats_pkey" PRIMARY KEY, btree (id)
    "uq_seats_event_label" UNIQUE CONSTRAINT, btree (event_id, label)
Check constraints:
    "ck_seats_status" CHECK (status::text = ANY (ARRAY['available', 'held', 'sold']::text[]))
Foreign-key constraints:
    "seats_event_id_fkey" FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE

怎麼算對：id 型別是 uuid、Default 是 uuidv7()；有 version default 0、uq_seats_event_label、ck_seats_status、主鍵與外鍵。

動手 3：塞資料（INSERT）

INSERT INTO events (name) VALUES ('Jazz Night') RETURNING id \gset

INSERT INTO seats (event_id, label)
SELECT :id, 'A'||g FROM generate_series(1,5) g;

預期輸出：

INSERT 0 1
INSERT 0 5

怎麼算對：\gset 把本次新增活動的 id 存成 psql 變數，重跑時不會錯把座位接到 event 1。INSERT 0 5 的 5 = 一次塞了 5 個座位 A1~A5；座位 id 由 uuidv7() 產生。

動手 4：查 / 改（READ / UPDATE）

SELECT id, label, status, version FROM seats WHERE event_id = :id ORDER BY label;
SELECT label FROM seats WHERE event_id = :id AND status = 'available';
UPDATE seats SET status = 'sold' WHERE event_id = :id AND label = 'A1';
SELECT count(*) AS available FROM seats WHERE event_id = :id AND status = 'available';

預期輸出（id 是真的 UUID；你在 PG18 看到的會是時間排序的值，此處示範環境的值看起來較隨機，結構相同）：

                  id                  | label |  status   | version
--------------------------------------+-------+-----------+---------
 05311f80-ac15-4ddd-b76e-827e032fb406 | A1    | available |       0
 e23e2aa8-d6bd-4060-ac5e-48861606bb67 | A2    | available |       0
 86123d84-e15a-4dfa-a420-2a8b0a24636a | A3    | available |       0
 239d3123-98f1-49bc-a47d-cb36724d134e | A4    | available |       0
 06695f39-56ae-42d7-ac71-633d783b09bf | A5    | available |       0
(5 rows)

 label
-------
 A1
 A2
 A3
 A4
 A5
(5 rows)

UPDATE 1

 available
-----------
         4
(1 row)

怎麼算對：UPDATE 1 + 可售數量從 5 變 4。

自我檢核

1. events.id 用 bigint identity、seats.id 用 uuid uuidv7()——為什麼不統一？（提示：canonical 規則 + B-tree）
2. 為什麼 seats 一建好就自動有一個索引？
3. INSERT 一個 event_id = 999（不存在的活動）會怎樣？
4. UPDATE seats SET status='sold'（忘了加 WHERE）會發生什麼？

Part C — 第一次親眼看見 MVCC（通往 Week 2 的橋）

Week 1 最重要的 5 分鐘。不用任何 extension，PostgreSQL 每列都偷偷帶系統欄位 ctid（實體位置）、xmin/xmax（版本資訊），直接 SELECT 就看得到。

動手

-- 1) 看某列現在的實體位置與版本（挑一個還沒被改過的，例如 A2）
SELECT ctid, xmin, xmax, label, status FROM seats WHERE event_id = :id AND label = 'A2';
-- 2) 改它
UPDATE seats SET status = 'sold' WHERE event_id = :id AND label = 'A2';
-- 3) 再看一次同一列
SELECT ctid, xmin, xmax, label, status FROM seats WHERE event_id = :id AND label = 'A2';

預期輸出（數字會不同，重點看 ctid 變了、xmin 變大）：

 ctid  | xmin | xmax | label |  status
-------+------+------+-------+-----------
 (0,2) |  734 |    0 | A2    | available
(1 row)

UPDATE 1

 ctid  | xmin | xmax | label | status
-------+------+------+-------+--------
 (0,7) |  736 |    0 | A2    | sold
(1 row)

這代表什麼（關鍵解讀）

• ctid 從 (0,2) 變成 (0,7)：UPDATE 沒有就地修改，而是在新的實體位置寫了一個新版本。
• xmin 變大：新版本由一個更新的交易建立。
• 舊版本 (0,2) 去哪了？還在磁碟上，只是被標記成過期、對新交易不可見——這就是死亡 tuple。
• 👉 死亡 tuple 累積 = 表膨脹（bloat） = Week 4 要用 VACUUM 清的東西。你剛親手製造了一個。
• 👉 為什麼舊版本要留著？因為可能還有別的交易需要看到舊值——這就是 MVCC「讀不擋寫」 的機制，Week 2 深入。

自我檢核

1. 做完上面，seats 表實體上有幾個 A2 的版本？對外查詢看得到幾個？
2. 把 A2 連續 UPDATE 三次，會留下幾個死亡 tuple？
3. （預習）誰負責清掉死亡 tuple？

Part D — EXPLAIN 入門（看懂查詢計畫）

讀什麼

官方 Using EXPLAIN：只讀「14.1.1 EXPLAIN Basics」（約 10 分鐘）。能答下面檢核題就收手，ANALYZE/COSTS 進階留到 Week 5。

動手 1：EXPLAIN（只估算，不執行）

EXPLAIN SELECT * FROM seats WHERE event_id = :id AND status = 'available';

預期輸出（此時 A1、A2 已賣，剩 3 個可售）：

                       QUERY PLAN
--------------------------------------------------------
 Seq Scan on seats  (cost=0.00..15.88 rows=2 width=144)
   Filter: ((status)::text = 'available'::text)
(2 rows)

逐欄解讀： - Seq Scan：全表掃描（小表沒索引很正常）。Week 5 會讓它變 Index Scan。 - cost=0.00..15.88：規劃器估的成本（啟動..總），是相對單位不是毫秒。 - rows=2：估計回傳 2 列（規劃器的猜測）。 - width=144：每列平均位元組（比整數主鍵版寬，因為 uuid 佔 16 bytes）。

動手 2：EXPLAIN ANALYZE（真的執行，給真實數字）

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM seats WHERE event_id = :id AND status = 'available';

PG18 起 ANALYZE 自動含 BUFFERS，可只寫 EXPLAIN ANALYZE；PG16 要手動加 BUFFERS。

預期輸出：

                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Seq Scan on seats  (cost=0.00..15.88 rows=2 width=144) (actual time=0.007..0.008 rows=3 loops=1)
   Filter: ((status)::text = 'available'::text)
   Rows Removed by Filter: 2
   Buffers: shared hit=1
 Planning:
   Buffers: shared hit=67
 Planning Time: 0.283 ms
 Execution Time: 0.030 ms
(8 rows)

關鍵觀察（EXPLAIN 的精髓）： - 括號裡的 actual ... rows=3：規劃器估 rows=2，實際 rows=3。估計 vs 真實有落差，因為統計資訊還沒更新（剛塞完資料）。Week 5 會學 ANALYZE 更新統計來修正。 - Rows Removed by Filter: 2：掃了 5 列、丟掉 2 列（A1、A2 已 sold）、留 3 列。 - Buffers: shared hit=1：從 buffer cache 命中 1 個 page（沒碰磁碟）。

自我檢核

1. EXPLAIN 和 EXPLAIN ANALYZE 最大差別？哪個會真的執行（含 UPDATE/DELETE）？
2. cost 的單位是毫秒嗎？
3. 估計 rows=2 跟實際 rows=3 不一致，最可能的原因是什麼？

Part E — 用 SQLAlchemy 2.0 改寫（Week 1 里程碑 M1）

把 Part B 用純 SQL 做的事，改用 SQLAlchemy 2.0 async ORM 表達。你會發現 ORM 產生的 SQL 跟你手寫的幾乎一樣——這時你才是「懂 ORM」而非「被 ORM 黑箱」。

flowchart LR
    API[api: HTTP/Pydantic] --> APP[application: use case]
    APP --> PORT[domain: repository port]
    ADAPTER[adapters: SQLAlchemy repository] -. implements .-> PORT
    ADAPTER --> PG[(PostgreSQL 18)]
    MIG[Alembic migrations] --> PG

依賴方向由外往內；Alembic 管 schema，ORM model 只是 mapping，兩者角色不同。

讀什麼（精確範圍，別讀整頁）

官方 SQLAlchemy asyncio 文件，只讀這幾節（約 25 分鐘）： 1. 開頭到安裝註記：知道 async 需要 greenlet（sqlalchemy[asyncio] 已含）即可。 2. 「Synopsis - ORM」：下面程式碼的藍本，重點看 create_async_engine / async_sessionmaker / AsyncSession / session.scalars(...)。 3. 「Preventing Implicit IO when Using AsyncSession」：記住一個雷——async 下不能依賴 lazy load，關聯要用 selectinload 顯式載入。 - 其餘（events、run_sync 進階、inspector）先跳過。

ORM 宣告語法：另讀 ORM Quick Start（很短，全讀，約 10 分鐘），重點是 Mapped[...] + mapped_column(...)。

收手標準

能答本節檢核題、且 canonical CRUD Lab 跑出預期輸出，就算讀懂了。不用把 asyncio 那頁讀完。

Worked example：三檔概念版與 canonical 專案的對應

下方三檔只作為「把 ORM 操作壓縮到最小」的閱讀用 worked example，不要複製、不要執行，也不要在 repo 根目錄另建第二套 models.py/database.py/main.py。正式累積程式位於：

• ORM models：../../ticketal/src/ticketal/adapters/db/models.py
• Engine/session：../../ticketal/src/ticketal/adapters/db/session.py
• Repository：../../ticketal/src/ticketal/adapters/db/repository.py
• Use case：../../ticketal/src/ticketal/application/list_available_seats.py
• API composition：../../ticketal/src/ticketal/api/app.py

先讀下方 worked example，再打開 canonical 程式回答：「每一段為什麼被拆到那一層？」實際驗證以 canonical tests 為準。

models.py

import uuid
from datetime import datetime
from sqlalchemy import String, ForeignKey, func, text
from sqlalchemy.ext.asyncio import AsyncAttrs
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship


class Base(AsyncAttrs, DeclarativeBase):
    pass


class Event(Base):
    __tablename__ = "events"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(200))
    starts_at: Mapped[datetime] = mapped_column(server_default=func.now())
    seats: Mapped[list["Seat"]] = relationship(back_populates="event")


class Seat(Base):
    __tablename__ = "seats"
    # uuid 主鍵，交給 DB 的 uuidv7() 產生（canonical）
    id: Mapped[uuid.UUID] = mapped_column(primary_key=True, server_default=text("uuidv7()"))
    event_id: Mapped[int] = mapped_column(ForeignKey("events.id"))
    label: Mapped[str] = mapped_column(String(20))
    status: Mapped[str] = mapped_column(String(20), server_default=text("'available'"))
    version: Mapped[int] = mapped_column(server_default=text("0"))  # 樂觀鎖，W3 用
    event: Mapped["Event"] = relationship(back_populates="seats")

database.py

from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker, AsyncSession

# 連到本機 Docker 的 ticketal 資料庫。driver 是 asyncpg。
DATABASE_URL = "postgresql+asyncpg://postgres:dev@localhost:5432/ticketal"

engine = create_async_engine(DATABASE_URL, echo=True)  # echo=True 印出 ORM 產生的 SQL
async_session = async_sessionmaker(engine, expire_on_commit=False)

expire_on_commit=False 是 async 慣例：避免 commit 後存取屬性又觸發一次無法 await 的隱式查詢。

conceptual_flow.py（只讀，不建立這個檔案）

import asyncio
from sqlalchemy import select, func
from database import engine, async_session
from models import Base, Event, Seat


async def main():
    # CREATE：一個活動 + 3 個座位（id 由 uuidv7() 自動產生）
    async with async_session() as s:
        async with s.begin():
            ev = Event(name="Jazz Night")
            ev.seats = [Seat(label=f"A{i}") for i in range(1, 4)]
            s.add(ev)
        print("Inserted event id =", ev.id, "with", len(ev.seats), "seats")
        print("Sample seat id (uuid) =", ev.seats[0].id)

    # READ：查可售座位
    async with async_session() as s:
        rows = await s.scalars(select(Seat).where(Seat.status == "available"))
        print("Available seats:", [se.label for se in rows.all()])

    # UPDATE：賣掉 A1
    async with async_session() as s:
        async with s.begin():
            seat = await s.scalar(select(Seat).where(Seat.label == "A1"))
            seat.status = "sold"
        print("Sold:", seat.label, "->", seat.status)

    # READ：可售數量
    async with async_session() as s:
        cnt = await s.scalar(
            select(func.count()).select_from(Seat).where(Seat.status == "available")
        )
        print("Available count now:", cnt)

    await engine.dispose()


asyncio.run(main())

真正要執行的是 canonical、可重跑且不會刪 schema 的 Lab：

cd "PostgreSQL Deep Dive/ticketal"
uv run python -m ticketal.labs.week_01_crud

輸出形狀（event id 會不同；先自己預測最後兩個數字）：

CREATE event_id=<本次 id> seats=3
READ available=['A1', 'A2', 'A3']
UPDATE A1 available->sold
DELETE A3 rows=1
VERIFY remaining=2 available=1

怎麼算對：CREATE 3 個座位；A1 sold、A3 delete 後剩 A1/A2 共 2 個，其中只有 A2 available，所以最後必須是 remaining=2 available=1。把完整輸出保存到 evidence；只看本文輸出不算完成。

自我檢核

1. Seat.id 用 Mapped[uuid.UUID] + server_default=text("uuidv7()")——為什麼用 server_default 而不是 Python 端產生 UUID？
2. 為什麼 database.py 的 URL 是 postgresql+asyncpg:// 而不是 postgresql://？
3. echo=True 印的 SQL 用 $1 佔位符，為什麼不直接把值寫進 SQL？
4. 想一次查活動「以及它所有座位」，為什麼不能直接 event.seats（async 下）？該怎麼做？

Week 1 驗收清單

全部打勾才進 Week 2：

• docker compose up -d 起好 db + redis，SELECT version() 看到 18.x
• 能在 psql 用 \dt \d \timing 查看表與耗時
• 用 Alembic 建好 canonical 的 events/seats，再用純 SQL 塞資料、查改一遍
• 親手看到 UPDATE 後 ctid 改變、舊版本變死亡 tuple，並能解釋為什麼
• 能讀懂一個 EXPLAIN 輸出的 Seq Scan / cost / rows / width
• ticketal.labs.week_01_crud 跑出預期輸出，且能指出每一步的 transaction 邊界
• 能說出為什麼 seats 用 uuidv7()、以及本專案為什麼不建 users 表
• 自我檢核至少答對 5 題（答錯的回去讀對應段落）
• 從 PostgreSQL Deep Dive 執行 ./scripts/validate-week.sh 1 通過
• evidence/week-01/manifest.yaml 有 environment、command、raw output path 與人工 Gate
• 至少 24 小時後完成一題不同輸入的 CRUD／async session 變化題

附錄：「我怎麼知道有沒有讀懂官方文件？」通用方法

之後每一週都適用：

1. 先定義收手標準，再開始讀：讀前先看「這段讀完要能做什麼/答什麼」。標準是「能通過檢核」，不是「讀到最後一行」。 官方文件 80% 是參考手冊，本來就不是給你從頭讀的。
2. 費曼測試：闔上文件，用自己的話對想像的同事講一遍。講得卡住處＝沒讀懂，回去針對那裡讀。
3. 預測 → 驗證：讀完一個機制先預測 psql 會輸出什麼，再跑對照。預測對＝真懂；預測錯＝心智模型有洞，正好補。
4. 能向下追一層：能回答「為什麼這樣設計？不這樣會怎樣？」才算從「會用」進到「理解」。
5. 記錄反直覺：每個「原本以為 X、其實是 Y」寫進 Backend-Learning 筆記。Week 結束回看就是你的學習成果。

用這套標準：Part E 的官方 asyncio 文件，你不需要讀完整頁——讀到能答那 4 題、canonical CRUD Lab 跑出預期輸出，就是讀懂了，往下走。