Database

引言:當業務主表需要翻轉 某個 SaaS 系統長期以「客戶資料表」為核心:所有訂單、文件、操作紀錄都用客戶 ID 當外鍵。但這個客戶表的資料來源是外部 POS 系統匯入,每天同步幾百筆,schema 由廠商定義。 業務發展後問題浮現:自己 SaaS 的會員表(users)才是真正的「人」 — 有登入、有偏好、有應用內行為。新功能(個人化推薦、訂閱管理、社交綁定)都需要以 users 為主軸。 於是有了一個經典的 schema migration 需求:主表翻轉(primary table pivot)。把業務邏輯的中心從 customers(外部 POS 匯入)轉到 users(SaaS 自家會員),但歷史資料、新進資料、系統相依、回滾風險全部都要顧到。 名詞解釋 開始拆解之前,定義幾個會反覆出現的詞: 詞 定義 主表翻轉(Primary table pivot) 業務主要 entity 從表 A 改成表 B Dual-write 寫入時同時寫舊欄位 + 新欄位,回滾安全 Partial cut-over 分階段切換,read 跟 write 不同步切 Hard cut-over(stop-the-world) 一次切完,downtime 短但 risk 大 Backfill 歷史資料補齊新欄位的 batch update Idempotent migration 重跑無副作用,cron / retry 安全 DISTINCT ON PostgreSQL 專屬去重 syntax,搭 ORDER BY 取每組第一筆 Pseudo entity 為了統一查詢介面而建的「假」實體 Link table ORM 多對多 join 表(user_id + entity_id) 為什麼選 dual-write 而非 stop-the-world 最直接的搬家方式是「stop-the-world cut-over」:選一個維護窗口,停寫入、跑 batch script、改完所有 reference、開機。但這個 SaaS 的條件不允許: ...

問題現象：登入成功卻被拒於門外 最近在 Staging 環境遇到一個詭異的問題：使用者登入成功，拿到了有效的 JWT Token，但存取任何需要認證的 API 都回傳 401 Unauthorized。 # 登入成功，拿到 token POST /api/auth/local → 200 OK { "jwt": "eyJhbGc...xxxxx...your-jwt-token", "user": { "id": 1001, "email": "[email protected]" } } # 但存取個人資料失敗 GET /api/users/me → 401 Unauthorized Token 驗證通過、使用者存在、帳號未被封鎖。問題到底在哪？ 根本原因：遺失的 Link Table 經過一番追查，發現問題出在資料庫同步時漏掉了關聯表（Link Table）。 什麼是 Link Table？ 在關聯式資料庫中，多對多關係需要透過中間表來建立。這個中間表就是 Link Table（也稱為 Junction Table、Join Table、或 Pivot Table）。 使用者與角色的關係： 一個使用者可以有多個角色（User → Roles） 一個角色可以分配給多個使用者（Role → Users） 這是典型的多對多關係 各種 ORM 的 Link Table 命名 不同框架的 Link Table 命名慣例不同，但概念完全相同： ORM/Framework Link Table 範例 備註 Django user_groups, user_permissions 使用 _ 連接 Laravel role_user, permission_role 字母順序排列 TypeORM user_roles_role 較長的命名 Prisma _UserToRole 以 _ 開頭 Sequelize UserRoles 駝峰命名 問題的本質：資料不完整 當我們同步資料庫時，通常會注意主要的資料表： ...

「奇怪，我明明刪了 52 萬筆資料，為什麼資料表還是 207MB？」 這是我今天在清理資料庫時遇到的真實情況。如果你也曾經困惑過這個問題，這篇文章會告訴你背後的原因。 事情是這樣的 專案的 user_notifications 資料表累積了幾十萬筆推播通知記錄，佔用了 207MB 空間。為了控制資料庫大小，我寫了一個 cron job 來清理超過 7 天的舊資料： // 刪除 7 天前的通知 const sevenDaysAgo = new Date(Date.now() - 7 * 24 * 60 * 60 * 1000); await strapi.db.query('api::user-notification.user-notification').deleteMany({ where: { createdAt: { $lt: sevenDaysAgo.toISOString() } }, }); 執行結果很漂亮： [Cleanup] Successfully deleted 521604 old user notifications 刪除了 521,604 筆！只剩下約 2 萬筆近期資料。 但當我打開 DBeaver 檢查時… 207MB？資料都刪了，空間怎麼沒變？ 為什麼會這樣？理解 PostgreSQL 的 MVCC 這不是 bug，而是 PostgreSQL 的設計特性。 DELETE 不是真的刪除 PostgreSQL 使用 MVCC（Multi-Version Concurrency Control） 來處理並發交易。當你執行 DELETE 時，PostgreSQL 不會真的把資料從磁碟上移除，而是： 將該行標記為「已刪除」（稱為 dead tuple） 保留原始資料，直到沒有任何交易需要參照它 新的查詢看不到這些行，但它們仍佔用磁碟空間 為什麼要這樣設計？ 效能考量：標記刪除比實際移除資料快非常多 並發安全：其他正在執行的 transaction 可能還需要看到舊版本 ACID 保證：確保 transaction isolation 不處理會怎樣？ ...