"Postgresql"
- 資料庫大版本升級
MySQL 5.7→8.0、PostgreSQL 13→16 等大版本升級的相容性評估、備份保險、平行驗證、切換策略與升級後監控
- 規模演進
可插拔 Storage Backend 架構 — SQLite 預設、PostgreSQL 觸發切換、時間序列 DB 長期演進
- 功能分層與 Backend 選擇
SQLite 層和 PostgreSQL 層各自承載哪些功能 — 分界線是查詢模式而非資料量、觸發升級的是功能需求而非規模成長
- 從 collector 資料做基礎 funnel 分析
SQLite 層能做什麼程度的 funnel、PostgreSQL 層提供什麼進階能力、JSONL 匯出後的臨時分析
- MySQL → PostgreSQL：從 SQL dialect diff 跑出來的 Type A 6-phase migration
MySQL → PostgreSQL 是 Type A 高 schema 差 migration 的標準形態 — SQL dialect / collation / case sensitivity / replication 模型差異主導；用 pgloader / AWS DMS / 自管 dual-write 三條 path、5 個 production 踩雷（auto_increment vs SERIAL / charset 跟 collation / case sensitivity / index syntax / triggers）
- PostgreSQL Patroni HA：從 leader 失聯到 client 重連的 5 段 failover lifecycle
Patroni 把 PostgreSQL HA 拆成 detection / election / promotion / reconfiguration / recovery 五段 lifecycle、每段都有獨立配置跟 failure mode；DCS quorum + watchdog 防 split-brain、async/sync replication 取捨、5 個 production 踩雷、跟 PgBouncer / HAProxy / cert-manager 整合
- PostgreSQL Replication Topology：async / sync / quorum 三模式跟 LSN + replication slot 的三軸組合
PostgreSQL streaming replication 不是「sync 或 async」、是 *durability / latency / consistency* 三軸組合 + LSN-based 進度追蹤 + replication slot 治理。本文走 3 軸取捨模型、async / sync / quorum-based sync 行為對比、LSN + replication slot 機制、配置 step-by-step、5 production 踩雷（standby lag 暴衝 / sync standby 退回 async / orphan replication slot / cascading replication 雪崩 / failover 後 timeline 分歧）、跟 Patroni HA + logical replication 整合
- PostgreSQL Online Schema Change：先用 ALTER 內建特性、不能解才 pg_repack / pg-osc
PostgreSQL ALTER TABLE 對多數變更已是 *fast catalog-only*（add column nullable / drop column / 改 default），不必走 ghost table tool。本文走 PG 內建 fast DDL 行為、何時必須走 pg_repack / pg-osc、兩工具機制對比（trigger-based vs WAL-shipping）、配置 step-by-step、5 production 踩雷（lock 升級 / VACUUM FULL 誤用 / pg_repack version mismatch / concurrent index 失敗清理 / generated stored column 不能 online）、跟 MySQL gh-ost / pt-osc sibling 對比
- PostgreSQL Connection Scaling：process-per-connection model 跟為什麼 pooler 是必裝
PG 每個 client connection fork 一個 backend process（不是 thread）、RAM 成本 5-15MB/connection、context switch 跟 fork() cost 在 100+ connection 後線性放大、所以 pooler 不是 *optional optimization* 而是 *production prerequisite*。本文走 process-per-connection model 跟 MySQL thread-per-connection 對比、max_connections + shared_buffers + work_mem 三 GUC 互動、application-side pool vs middleware pool vs RDS Proxy 三層選擇、5 production 踩雷（connection storm / fork() cost 在 burst 流量 / shared_buffers 跟 connection 數壓縮 / double-pool 配置錯誤 / max_connections 設太大反而慢）、跟 PgBouncer config 互補不重複
- PostgreSQL Index Selection：B-tree / GIN / GiST / BRIN / Hash 對應 workload 的決策樹
PG 有 6 種 index method（B-tree / Hash / GIN / GiST / SP-GiST / BRIN）跟 partial / expression / covering 三種變體、不是「都用 B-tree 就好」。每種 index 有自己的 query pattern、儲存代價、write amplification 跟 maintenance 成本。本文走 6 種 index 的適用 workload 對照、決策樹、partial / expression / covering / multi-column 變體、5 production 踩雷（過度 index / partial 條件不對 / B-tree 對 JSON 無效 / BRIN 對非 correlated 資料無效 / multi-column 順序錯）、跟 query-optimization 的 EXPLAIN 互補
- PostgreSQL Citus Distributed：用 extension 把 PG 變成 sharded cluster
Citus 是 PG extension、把單機 PG 變成 *coordinator + worker* sharded cluster、保留 PG SQL + 加 distributed table + reference table + columnar storage。本文走 Citus 架構（coordinator / worker / distribution column）、3 種 table type（distributed / reference / local）、配置 step-by-step、5 production 踩雷（distribution column 選錯 / cross-shard transaction / reference table 過大 / colocate 不對齊 / worker failover）、跟 MySQL Vitess sharding sibling 對比
- PostgreSQL SQL Features：PG 早就有的、MySQL 8.0 才補的、PG 仍領先的
PG 在 SQL features 上長期領先 MySQL — CTE / window function / lateral / partial index / FTS / JSONB / GIN index / materialized view 在 PG 早 5-15 年。MySQL 8.0（2018）補多數但 *index / storage / extension* 層仍是 PG 結構優勢。本文整理 PG 早期就有的特性、MySQL 8.0 補的差異、PG 仍領先的、跟 MySQL modern-sql-features sibling 反向視角
- PostgreSQL BDR / Multi-Master：active-active 寫入的 3 種路徑跟 conflict 治理
PG 預設是 single-primary、active-active 多寫入入口需要 *BDR (EDB)* / *pgEdge* / *Bucardo* 等 extension。本文走 3 種 multi-master 方案對比、conflict detection + resolution model、async vs sync 取捨、配置 step-by-step（pgEdge 為主）、5 production 踩雷（last-write-wins data loss / sequence collision / DDL replication / conflict log 治理 / failover 後 timeline 分歧）、跟 MySQL Group Replication sibling 對比
- PostgreSQL Query Optimization：EXPLAIN ANALYZE / pg_hint_plan / auto_explain 三層工具跟 4 個 case
PG query 慢的根因常是 *planner 選錯 plan 或 statistics 過時*。本文從 4 個 production case 開場（seq scan vs index / hash vs nested loop / 多 column 統計缺 / parallel query 沒觸發）、走 EXPLAIN / EXPLAIN ANALYZE / auto_explain 三層工具、pg_hint_plan extension 跟 planner GUC 取捨、5 production 踩雷（ANALYZE 過時 / multi-column statistics / cost-base setting 不對齊硬體 / random_page_cost SSD 沒調 / parallel query 配置）、跟 MySQL query-optimization sibling 對比
- PostgreSQL MVCC + Lock Model：為什麼 PG 比 MySQL 少 deadlock、但 vacuum 是別的代價
PG 用 *MVCC-heavy + 少 explicit lock* 的並行控制、跟 MySQL InnoDB 的 *lock-based*（record / gap / next-key）相反。本文走 MVCC 機制（tuple version + xmin/xmax + visibility）、PG 4 種 lock（row-level / table-level / advisory / predicate）、預測 SERIALIZABLE 行為、5 production 踩雷（idle transaction 卡 vacuum / SELECT FOR UPDATE 跨 transaction / advisory lock 沒釋放 / bloat 不是 vacuum 問題 / predicate lock 在 SSI 下 rollback）、跟 MySQL lock-contention sibling 對比
- PostgreSQL JSONB Deep Dive：Binary Storage + GIN Index 為什麼是結構性優勢
PG JSONB（9.4+）是 *binary 儲存的 JSON*、可直接 GIN index、是 PG 在 JSON workload 的結構性優勢、跟 MongoDB / MySQL 8.0 JSON_TABLE 比仍領先。本文走 JSON vs JSONB 差異、GIN index 機制（jsonb_ops vs jsonb_path_ops）、operator + path query、partial JSONB indexing、5 production 踩雷（大 JSONB 跟 TOAST / nested update / index 選錯 op class / jsonb_path_query 跟 jsonb_path_exists 行為差 / partial index 條件搞錯）、何時用 JSONB vs 拆 column
- PostgreSQL Extension Ecosystem：把 PG 變成 vector DB / time-series / sharded 的 plugin 生態
PG 的 extension 機制不只是 plugin、是 *結構性產品線擴張* — pgvector 讓 PG 變 vector DB、TimescaleDB 變 time-series、Citus 變 sharded、PostGIS 變 GIS。本文走 PG extension lifecycle、6 個 production-critical extension（pg_stat_statements / pg_partman / pg_repack / pgvector / TimescaleDB / PostGIS）、5 production 踩雷（extension version 跟 PG version 對齊 / managed PG 限制 / upgrade order / shared_preload_libraries 衝突 / extension 跟 logical replication 互動）、cloud vendor 對 extension 的限制
- PostgreSQL Full-Text Search：tsvector / tsquery / GIN index 跟 pg_trgm fuzzy 三層搜尋
PG 內建 full-text search 用 *tsvector / tsquery / GIN index* 三件組、適合中小規模搜尋（< 100M 文件）；pg_trgm 提供 fuzzy match。本文走 FTS 機制（tsvector 是 lexeme + position 的 vector）、3 種 query（match / ranking / weighted）、multi-language support、跟 pg_trgm fuzzy match 互補、5 production 踩雷（dictionary 選錯 / GIN 跟 GiST 取捨 / ranking 評分權重 / multi-language column 處理 / 何時不該用 PG FTS 改 Elasticsearch）
- PostgreSQL Replication Slot Management：Physical / Logical / Failover Slot 治理
PG replication slot 是 *primary 端的 standby 進度紀錄*、防 WAL premature deletion。但 orphan slot 會吃 disk、failover 後 logical slot 不會自動跟新 primary、是 PG 操作的 hidden complexity。本文走 physical / logical slot 差異、slot lifecycle、failover slot synchronization（PG 17+ 新特性）、orphan slot 治理、5 production 踩雷（orphan slot disk 爆 / logical slot lag / failover 後 slot 丟 / wal_keep_size 跟 slot 衝突 / connection 同時打 slot 數量限制）
- TimescaleDB Deep Dive：Hypertable / Continuous Aggregate / Compression 把 PG 變 Time-Series DB
TimescaleDB 是 PG extension（不是 fork）、用 *hypertable* 自動 partition by time、加 *continuous aggregate* 做 incremental materialized view、加 *compression* 對舊 chunk 壓 90%+、把 PG 變成 InfluxDB / Prometheus 級 time-series DB。本文走 hypertable 機制、continuous aggregate 跟普通 MV 差異、compression policy、retention policy、5 production 踩雷（chunk size 不對 / CAGG refresh 落後 / compression 後 update 限制 / hypertable 不能加 FK / TimescaleDB 跟 PG 主版本對齊）、跟 PG 原生 partitioning 對比
- pgvector Deep Dive：HNSW / IVFFlat 取捨跟跟專業 Vector DB 對比
pgvector 是 PG extension、加 *vector* type 跟兩種 ANN index（IVFFlat / HNSW）、把 PG 變成可用 vector DB。本文走 vector type + distance operator、IVFFlat vs HNSW 取捨（build time / recall / memory）、quantization 跟 dimension reduction、5 production 踩雷（dimension 超 2000 限制 / HNSW build 太慢 / IVFFlat 不重建 recall 漂移 / hybrid search 設計 / memory budget）、跟 Pinecone / Weaviate / Milvus 對比的決策框架
- PostGIS Deep Dive：Geometry / Geography 型別、GiST 空間索引跟 ST_* 函式生態
PostGIS 是 PG extension、加 *geometry* / *geography* 型別、GiST 空間索引跟 1000+ ST_* 函式、把 PG 變成功能完整 GIS DB（跟 Oracle Spatial / SQL Server geography 並列）。本文走 geometry vs geography 取捨、SRID 跟投影系統、GiST 空間索引機制、5 production 踩雷（geometry 用錯 SRID / geography 不能用所有 ST_ 函式 / GiST index 不對 ST_DWithin 生效 / cluster on geom 後 BRIN 失效 / EWKB vs WKB 跨工具相容）、GIS workload 的 PG vs 專業 GIS DB 對比
- PostgreSQL autovacuum tuning：為什麼你的 autovacuum 永遠追不上 bloat
MVCC 怎麼產生 dead tuple、autovacuum cost-based throttle 為什麼預設保守、per-table tuning 怎麼設、5 個 production 踩雷（cost_limit 太低 / 長 transaction blocks vacuum / anti-wraparound 在 peak / partition vacuum 滿 worker / index bloat 沒處理）、跟 partitioning + monitoring 整合
- Migration Playbook：Cloud SQL for PostgreSQL → Cloud Spanner
Cloud SQL → Spanner 是 paradigm shift 級遷移、不是 drop-in。本 playbook 走 6 規格面 Driver / Diff / Phase / Evidence / Cutover / Cleanup：Driver 段明示 sizing barrier（100 pu 起跳）跟 < 50ms write latency 兩條 no-go；Diff 段加 sizing / cost 第 7 規格面；Phase 0 含 sizing audit；Evidence 段補 cost crossover 報告；對照 9.C10 Google internal dogfood 邊界跟 Standard Chartered 受監管 banking case
- PostgreSQL declarative partitioning：partition 不是切表、是讓 planner pruning
Declarative partitioning 的真實價值是 query planner pruning + maintenance scope 縮小、不是「把大表切小」；RANGE / LIST / HASH 取捨、partition key 選法、5 個 production 踩雷（key 選錯不 prune / unique 不 enforce 跨 partition / ATTACH 鎖太久 / partition 數爆 / DETACH 不 reclaim 空間）、跟 autovacuum + index 設計整合
- PostgreSQL Logical Replication + Debezium CDC：replication slot × failure × recovery 對照
PostgreSQL logical replication slot 跟 Debezium CDC 的失效模式對照表：slot lag 撐爆 primary disk / schema change 斷流 / 初始 COPY 鎖表 / zombie slot 不釋放 / replay storm 後 offset reset；publication / subscription / pgoutput 配置、跟 Kafka outbox pattern 整合
- PostgreSQL PITR + WAL archiving：從 base backup 到 point-in-time recovery 的完整鏈
Base backup + WAL archive 構成 PITR 的雙軌資料、archive_command + restore_command 配置、用 pgBackRest / WAL-G 替代手寫腳本、5 個 production 踩雷（archive 靜默失敗 / archive lag / 錯誤 target time / base backup 過期未清 / timeline 分歧 recovery 模糊）、跟 Patroni + monitoring 整合
- PostgreSQL major version upgrade (14 → 17)：為什麼這篇不套 5 type migration
PostgreSQL major version upgrade 是 *5 type 漏類* 的實證 — source/target 同 vendor、5 維度都 Low 但 *upgrade-specific audit* 是核心；本文結構接近 deep article methodology 的 6-section + 額外 upgrade audit 段；涵蓋 pg_upgrade / logical replication / blue-green 三方法、extension 相容性、5 production 踩雷
- PostgreSQL → Aurora Migration：protocol 相容、operational 重設計
Aurora 號稱 PostgreSQL-compatible 但 operational model 不同（storage decouple / cluster endpoint / instance class / 自家備份）；遷移流程是混合（protocol drop-in + operational phased）、5 個 production 踩雷（extension 不支援 / replication slot 不直通 / autovacuum 行為差 / IAM 認證強制 / cost model 換算）、跟 Patroni / read replica / DR 對位
- PostgreSQL → Aurora DSQL Migration：PG wire-compatible Distributed SQL 的 Paradigm Shift
Aurora DSQL（2024-12 re:Invent preview / 2025-05 GA）是 AWS 推的 PG wire-compatible *active-active distributed SQL*、跟 self-managed PG / Aurora PG 不同 paradigm（OCC + snapshot isolation + multi-region strong consistency）。Migration 結構是 *protocol drop-in + paradigm shift*：app SQL 不太改、但 transaction retry / extension 缺位 / 多 region 一致性需重設計。本文走 DSQL vs Aurora PG vs self-managed PG 三軸對比、為什麼遷的三條 driver（global write / operational zero-touch / region resiliency）、Type E phased plan、5 production 踩雷（transaction retry 沒處理 / extension 缺位 / sequence throughput 限制 / Aurora PG 直升 DSQL 不可行 / region failover semantic）、跟 PG → Aurora 跟 PG → CockroachDB 對比
- PostgreSQL → CockroachDB：三維皆 High 的多重歸類 migration
PostgreSQL → CockroachDB 是 Schema / Operational / Paradigm 三維皆 High 的 multi-axis migration、實證 [#127](/report/content-structure-by-max-diff-dimension/) 的「多重歸類跟 tie-breaking」規則；主結構走 Type E paradigm shift、Schema 差 + Operational redesign 抽出獨立段；涵蓋 transaction model 重設計、SQL dialect gap、5 個 production 踩雷
- PostgreSQL Partition Redesign：當 monthly partition 越跑越慢
PostgreSQL partition redesign 是 Type F「topology re-layout」第 2 個 dogfood — 從 monthly partition 改 daily / 從 range 改 list / 從單軸改 sub-partition；6 維 audit 皆 Low + topology 軸 High；涵蓋 partition 不平衡偵測、ATTACH/DETACH 線上重劃、5 個 production 踩雷、跟 partition_pruning + autovacuum 整合
- PostgreSQL Multi-Region GDPR Rollout：政策驅動的 migration 屬本 methodology 嗎
PostgreSQL 單 region → multi-region 同時滿足 GDPR EU residency 是 *政策驅動* 兼 *topology 變動* 兼 *operational redesign* 的多軸 migration；驗證 [#128](/report/data-topology-as-audit-dimension/) self-aware limitation 提出的 residency axis 候選 — residency 是 driver 還是獨立 audit 軸；涵蓋 logical replication 配 GDPR / 5 個 production 踩雷 / cross-region cost
- 從自管 PostgreSQL / MySQL 遷到 Aurora：operational redesign migration playbook
PostgreSQL / MySQL → Aurora 的 Type C operational redesign hybrid playbook、6 規格面（Driver / Diff audit / Phase plan / Evidence / Cutover / Cleanup）、Standard Chartered 合規 lead time 模型、Netflix 非 all-purpose store 邊界
- Cosmos DB for PostgreSQL：基於 Citus 的分散式 PostgreSQL、跟核心 Cosmos DB 是不同產品、何時選它而非核心 Cosmos 或一般 PG
Cosmos DB for PostgreSQL（2022、Citus-based distributed PG）的定位釐清：它是分散式 PostgreSQL、不是 NoSQL Cosmos DB；distribution column / coordinator-worker 架構、何時選它而非核心 Cosmos DB、何時夠用一般 Azure Database for PostgreSQL — 命名混淆的選型陷阱
- PostgreSQL pgBouncer 配置 + 連線池治理
pgBouncer transaction pooling 配置、跟 application connection pool 的分層、production 故障演練（pool exhaustion / stale connection / DNS failover）跟容量規劃
- Aurora PostgreSQL I/O-Optimized Cost
Aurora PostgreSQL Standard 與 I/O-Optimized 的成本模型、I/O 壓力、workload 判斷、遷移與回退條件
- Managed PostgreSQL Comparison
RDS PostgreSQL、Aurora PostgreSQL、Cloud SQL、Azure Database for PostgreSQL、Neon、Supabase、Crunchy Bridge 的責任邊界比較
- PostgreSQL Connection Pool Lab
PostgreSQL application pool、PgBouncer、backend connection、pool exhaustion 與 failover reconnect 的操作說明
- PostgreSQL Connection Pooler Comparison
PostgreSQL PgBouncer、Odyssey、RDS Proxy、application pool 與 transaction pooling 的選型比較
- PostgreSQL Cross-region DR
PostgreSQL 跨區災難復原、physical replica、logical replication、backup restore、RPO / RTO 與 failover runbook
- PostgreSQL Developer / DBA Responsibility Split
PostgreSQL application developer、DBA、platform team 在 schema、query、migration、backup、incident 與 capacity 的責任分工
- PostgreSQL HA Failover Drill
PostgreSQL Patroni 或 managed failover 的 promotion、client reconnect、pooler behavior 與 incident timeline
- PostgreSQL Local Lab Quickstart
PostgreSQL local lab 的 Docker Compose、schema seed、sample workload、basic metric 與 teardown
- PostgreSQL Logical Decoding Plugins
PostgreSQL logical decoding output plugin、pgoutput、wal2json、test_decoding、CDC connector 與 plugin 選型
- PostgreSQL pg_partman Advanced
PostgreSQL pg_partman 自動分區、premake、retention、maintenance job、partition migration 與 runbook
- PostgreSQL PITR Restore Drill
PostgreSQL base backup、WAL archive、target time restore、validation query 與 RPO / RTO evidence 的操作說明
- PostgreSQL Schema Migration Evidence Lab
PostgreSQL expand / contract migration、validation query、rollback condition 與 release gate evidence 的操作說明
- PostgreSQL Security / RLS / Audit Logging
PostgreSQL role、grant、Row Level Security、pgAudit、log policy、PII access evidence 與合規路由
- PostgreSQL to YugabyteDB / TiDB Migration
PostgreSQL 轉向 YugabyteDB、TiDB 類 distributed SQL 的 compatibility audit、data topology、transaction、cutover 與 rollback
- Specialized PostgreSQL Variants
pgvectorscale、Citus、TimescaleDB、PostGIS、AlloyDB、Cosmos DB for PostgreSQL、serverless PG 等 PostgreSQL 變體的選型邊界
- PostgreSQL to SQLite Simplification
PostgreSQL 降低操作成本轉向 SQLite 的適用條件、資料責任縮小、export/import、runbook 與 no-go condition
- SQLite to PostgreSQL Migration
SQLite 升級到 PostgreSQL 的 driver、schema diff、data copy、dual run、cutover、rollback 與 cleanup