資料儲存與處理效能 FAQ

範圍 —— 資料工程面試跨引擎而非針對單一引擎所問的效能題：回答任何「讓它變快」問題的方法、最佳化資料的儲存方式、最佳化資料的處理方式、在 Spark、關聯式資料庫、MPP 查詢引擎與單機工具中避免記憶體不足（OOM）、資料的重新排序與合併（sorting、clustering、Z-order、compaction、MERGE、external merge sort、LSM compaction）、儲存系統的讀取與寫入效能，以及讓查詢變快的 SQL 改寫。各引擎的內部機制留在各自的深入解析；本文件是跨引擎的視角，並連到那些文件。 另見：./spark.md —— shuffle、資料傾斜、記憶體，以及 Spark 自己的故障對照表；./sql_explain.md —— 讀懂告訴你時間花在哪裡的執行計畫；./sql_for_data.md —— §10 的索引與資料倉儲調校手段；./batch_pipeline.md —— 管線中的檔案格式、table format 與 compaction；./sql_vs_nosql.md —— B-tree 與 LSM 儲存引擎；../faq_performance_tune.md —— 應用程式層級的 profiling。

幾乎每一道資料效能題，底下都是同一個答案：少讀、少搬、少佔記憶體，並以讀取端需要的形狀寫入。 以下各節就是把這句話套用到儲存、處理、記憶體、排序與讀寫路徑上。

0) 一句話回答 ★★★★★優先度 5／5 — 必備 — 幾乎每一輪面試都會出現

「我會先量測時間花在哪裡 —— 看查詢計畫或 Spark UI，而不是用猜的。接著依這個順序減少工作量： 少讀資料（columnar 格式、partition pruning、透過 clustering 讓 min/max 統計資訊跳過檔案）、 少搬資料（join 之前先過濾與聚合、broadcast 小的那一側、修正資料傾斜）、少佔記憶體（用串流取代 collect、足夠的 partition 數、讓引擎 spill），以及少寫（用增量 MERGE 取代整批重建、每個檔案 幾百 MB）。做完這些才加硬體。」

1) 回答任何效能題的方法 ★★★★★優先度 5／5 — 必備 — 幾乎每一輪面試都會出現

1-1) 時間花在的四個地方

一個資料 job 的時間花在四個地方 —— 讀取、搬移、運算與寫入 —— 而修正方式取決於哪一個佔大宗， 所以第一步永遠是先查清楚。

調校之前先問：

1. 目標是什麼？「每日 job 必須在 06:00 前跑完」或「p95 低於 2 秒」—— 要一個數字。
2. 什麼改變了？ 資料量、新的 join、新出現的傾斜 key、某個設定、引擎升級。
3. 哪個階段佔大宗？ 讀計畫或 UI；最慢的 stage 或節點就是要處理的工作。
4. 是一個 task 慢還是全部都慢？ 一個 task 慢是資料傾斜；全部都慢是資料量或資源問題。

1-2) 依效益排序的調校手段

便宜的結構性改變勝過昂貴的調校：移除工作比把同樣的工作做得更快更有價值，而加硬體擺在最後， 因為它掩蓋問題，而且每天都在花錢。

2) 最佳化儲存 ★★★★★優先度 5／5 — 必備 — 幾乎每一輪面試都會出現

2-1) 格式、壓縮與編碼

分析用途就存成 columnar（Parquet 或 ORC）並搭配快速的 codec：查詢只讀它指名的欄位， 同一欄中相似的值壓縮效果好，而每個 chunk 的統計資訊讓讀取端不必打開就能跳過資料。

在 Parquet 的 column chunk 內，writer 在壓縮之前還會先編碼：低基數字串用 dictionary 編碼、重複值用 run-length 編碼、排序過的整數與時間戳用 delta 編碼。把資料排序（見 §5） 能讓這三種都更有效，這就是為什麼排序過的檔案常常小 2–5 倍。

2-2) 檔案與分區的大小

目標是每個檔案大約 128 MB 到 1 GB，每個分區至少裝得下幾個檔案的資料量；太多小檔案在開檔與 metadata 上的成本會超過資料本身。

• 依幾乎每個查詢都會過濾的低基數欄位分區 —— 通常是日期。絕不要依 user_id 或其他高基數 key 分區。
• 在分區內，依次常被過濾的欄位排序或 cluster，讓 min/max 統計資訊能跳過檔案 （見 ./schema_design.md）。
• Table format（Iceberg、Delta）把檔案清單存在 metadata 裡，所以規劃時不必列目錄 —— 但它無法 讓小檔案讀起來不慢。

2-3) 資料型別與寬度

一列中的每個位元組都會被讀取、shuffle、快取，並在每一份副本中再存一次，所以用最窄的正確 型別是免費的加速。

• 整數 surrogate key 比長字串 key join 得更快、壓縮得更好。
• 日期與時間戳存成 DATE / TIMESTAMP，不要存成字串 —— 它們更小、比較正確，而且能做 partition pruning。
• 金額用 DECIMAL(p, s)；只有在可接受捨入誤差的地方才用 DOUBLE。
• 把查詢真正會用到的 JSON 欄位抽成有型別的欄位；裝著 JSON 的 STRING 每次查詢都要重新解析， 而且沒有統計資訊。
• 從寬的原始資料表升級到 silver / gold 時，拿掉沒人讀的欄位。

2-4) 生命週期：熱、溫、冷

不是所有資料都值得同樣的儲存：讓近期、常被讀的資料保持快速，其餘的移到較便宜的層級， 沒人查詢的舊原始明細就用聚合結果取代。

• 讓舊的資料表 snapshot 過期（Delta 的 VACUUM、Iceberg 的 expire_snapshots）—— 否則 每次改寫都會永遠留著舊檔案。
• 一旦 silver 可信且可重播，就對 bronze / 原始落地資料設 TTL。
• 舊明細不再被查詢時，把它彙總成每日聚合。

3) 最佳化處理 ★★★★★優先度 5／5 — 必備 — 幾乎每一輪面試都會出現

3-1) 少讀：pruning 與 pushdown

最快的位元組是從未被讀取的那一個：讓引擎在任何資料抵達 task 之前，就跳過分區、檔案、 row group 與欄位。

確認 pushdown 真的發生，而不是假設它發生了：

# PySpark — the plan should list the filter under PushedFilters / PartitionFilters
orders = spark.read.parquet("s3://lake/silver/orders/")
recent = (orders
          .where("order_date >= '2026-11-01' AND country = 'TW'")
          .select("order_id", "user_id", "amount"))
recent.explain("formatted")
# FileScan parquet ... PartitionFilters: [isnotnull(order_date), (order_date >= 2026-11-01)]
#                      PushedFilters:    [IsNotNull(country), EqualTo(country,TW)]
#                      ReadSchema:       struct<order_id:bigint,user_id:bigint,amount:decimal(12,2)>

以下情況會讓 pushdown 失效：欄位外面包了函式（在依 ts 的日期分區的欄位上寫 WHERE to_date(ts) = …）、改變型別的 cast，以及過濾條件中的 Python UDF。

3-2) 少搬：shuffle 與 join

Shuffle 會把每一列寫到磁碟並透過網路送出，所以最便宜的 join 就是 shuffle 位元組最少的那個。

• 在 join 之前先過濾與聚合，而不是之後 —— 見 §7-2 的改寫。
• Broadcast 小的那一側，讓大的那一側完全不必移動（Spark 的 spark.sql.autoBroadcastJoinThreshold，預設 10 MB；Trino 的 broadcast distribution）。
• Co-locate 總是以同一個 key join 的資料表 —— Spark / Hive 的 bucketing、Redshift 的 distribution key —— 讓 join 不需要 exchange。
• 修正資料傾斜 —— 一個熱門 key 會讓一個 task 做掉大部分的工作；見 ./spark.md。
• 資料本來就唯一時，避免用 DISTINCT 與（會去重的）UNION —— 兩者都是隱藏的 shuffle。

3-3) 少算：增量與預先計算

為了加一天的資料而重算一整年的歷史，是批次平台最常見的浪費；只處理有變動的部分，並預先 計算常被讀取的結果。

3-4) 少寫

• 只改寫有變動的分區或檔案；table format 的 MERGE 只會碰到含有符合列的檔案。
• 頻繁的小量更新偏好 merge-on-read（讀時合併），讀取量大、以大批次更新的資料表偏好 copy-on-write（寫時複製）（見 §5-4）。
• 把許多小寫入合併成較少、較大的寫入 —— 對資料倉儲或 data lake 逐列 insert，每一筆都會產生 一個檔案或一次 commit。

4) 避免記憶體不足（OOM） ★★★★★優先度 5／5 — 必備 — 幾乎每一輪面試都會出現

4-1) 引擎為什麼會記憶體不足

當某個無上限的東西被實體化到一個有上限的地方，引擎就會記憶體不足：一個 key 的所有列擠在 一個 task、整個結果放在一個節點、用錯的一側建 hash table，或是太多查詢同時各自拿走自己那一份。

五種型態，每個引擎都有：

答案的另一半，是 buffer 滿了時引擎會怎麼做：

4-2) Spark

Driver OOM 幾乎都是有東西被拉到 driver；executor OOM 幾乎都是某個 partition 太大 —— 資料傾斜、 shuffle partition 太少，或是列數爆炸。

預防措施，依檢查順序：

1. 絕不對大結果 collect() 或 toPandas() —— 把它寫出去；driver 必須看到資料列時，用 limit 或 toLocalIterator()。
2. 足夠的 shuffle partition，讓每個約 100–200 MB；開啟 AQE，讓它能合併與切分 partition。
3. 先修正資料傾斜再加記憶體 —— AQE skew join、隔離熱門 key 或對它加 salt。
4. 讓 broadcast 名副其實 —— 門檻是以估計值衡量的；實際上有 2 GB 的「小」表會把 driver 與 每個 executor 一起拖垮。
5. 限制列數成長 —— 檢查 join 的放大倍數，避免對無上限的群組做 collect_list / collect_set。
6. Off-heap —— 「Container killed … exceeding memory limits」代表 Python worker、Arrow 或原生 buffer 超出了 spark.executor.memoryOverhead，而不是 heap。

# Write the big result out instead of collecting it
result.write.mode("overwrite").parquet("s3://lake/tmp/report/")

# If the driver really needs rows, iterate one partition at a time
for row in result.toLocalIterator():
    handle(row)

# Bound what reaches the driver; fail fast instead of OOM-ing (default 1g).
# It is read when the SparkContext starts — spark.conf.set at runtime does not change it:
#   spark-submit --conf spark.driver.maxResultSize=1g ...
#   SparkSession.builder.config("spark.driver.maxResultSize", "1g").getOrCreate()

完整的錯誤 → 原因 → 修正對照表在 ./spark.md §15-1， 背後的記憶體區域見 §9-1。

4-3) 關聯式資料庫（PostgreSQL、MySQL）

資料庫很少死於單一查詢；它死於每個操作的記憶體乘上並行度 —— 而抓整個結果集的 client 往往先死。

伺服器端：

• PostgreSQL 的 work_mem（預設 4 MB）是每個 sort 或 hash 節點、每個查詢、每條連線 各自一份 —— hash 節點可能使用 work_mem × hash_mem_multiplier（預設 2.0）。一個有五個 sort 與 hash 的查詢跑在 200 條連線上，要的記憶體可能遠超過 work_mem × 200。 針對沉重的報表以 session 或 role 為單位調高，不要全域調高。
• 在上限之下，sort 或 hash 會 spill（Sort Method: external merge、Batches: 8）—— 變慢，但不致命。修正 spill 見 ./sql_explain.md §6-3。
• 使用 connection pooler（PgBouncer），讓並行度受 pool 限制，而不是受應用程式執行緒數限制。
• MySQL 的對應設定是 sort_buffer_size 與 join_buffer_size（每個 session），以及 tmp_table_size / max_heap_table_size（超過的記憶體內暫存表會改寫到磁碟）。

Client 端 —— 資料管線中常見的那種：

# psycopg2: a *named* cursor is a server-side cursor; rows arrive in batches of itersize
import psycopg2

conn = psycopg2.connect(dsn)
with conn.cursor(name="export_orders") as cur:   # the name makes it server-side
    cur.itersize = 10_000
    cur.execute("SELECT order_id, user_id, amount FROM orders WHERE order_date >= %s",
                ("2026-11-01",))
    for row in cur:                              # never holds more than one batch
        write(row)

• 連 PostgreSQL 的 JDBC 只有在 autocommit 關閉且 result set 為 forward-only 時才會遵守 setFetchSize(n)；否則 driver 會把每一列都緩存起來。
• MySQL Connector/J 用 setFetchSize(Integer.MIN_VALUE) 串流，或用 useCursorFetch=true 搭配 正的 fetch size。
• 避免在應用程式裡組出 100 000 個項目的 IN (...) 清單 —— 把 key 載入暫存表再 join。

4-4) MPP 查詢引擎（Trino、Presto、Athena）

Trino 把 hash join 的 build side 放在記憶體中，達到上限就讓查詢失敗，所以答案是把 build side 變小：把較小的表放在右邊、給 optimizer 統計資訊，並在 join 之前先縮減資料。

• Build side —— Trino 用 join 的右側建 hash table。有資料表統計資訊時，cost-based optimizer 會重排 join 順序；沒有的話就保留你寫的順序，所以把大的事實表寫在左邊。
• Distribution —— broadcast join 把 build side 複製到每個 worker（快，但每個 worker 都要裝下全部）；partitioned join 對兩側都做 hash（每個 worker 只裝一部分）。大的 build side 必須用 partitioned。
• 高基數的 GROUP BY 與多欄的 COUNT(DISTINCT) 會佔用大型 hash table —— 預先聚合， 或用 approx_distinct。
• 不要把巨大的結果拉到 client：CREATE TABLE AS SELECT（CTAS）或 INSERT INTO 會從各個 worker 平行寫出。
• Athena 的 「Query exhausted resources at this scale factor」 是同一種故障；修正方式相同， 再加上分區，讓需要讀的資料本來就更少。

4-5) 單機工具（pandas、DuckDB、Polars）

pandas 需要把整個資料集放進 RAM，通常是檔案大小的好幾倍；只讀你需要的欄與列、分塊讀取， 或換成會串流處理的引擎。

import pandas as pd

# 1) only the columns you need, with compact dtypes
cols = ["user_id", "country", "amount"]
dtypes = {"user_id": "int64", "country": "category", "amount": "float64"}

# 2) in chunks: aggregate each, then combine the small partial results
partials = []
for chunk in pd.read_csv("orders.csv", usecols=cols, dtype=dtypes, chunksize=1_000_000):
    partials.append(chunk.groupby("country", observed=True)["amount"].sum())
revenue_by_country = pd.concat(partials).groupby(level=0).sum()

• DuckDB 透過 spill 到暫存目錄來執行超過記憶體的 sort、join 與聚合： SET memory_limit = '4GB'; SET temp_directory = '/tmp/duckdb';，而且它能直接就地查詢 Parquet，不必先載入。
• Polars 的 lazy API（scan_parquet … collect(engine="streaming")）以批次處理。
• 分塊模式只對可分解的聚合（sum、count、min、max）有效；中位數或 distinct 計數需要不同的 做法（sketch，或真正的引擎）。

4-6) 預防檢查清單

• [ ] 沒有任何無上限的東西送到單一地方 —— driver、client 或 coordinator。
• [ ] Partition 有刻意調整大小（Spark 中約 100–200 MB），而不是留著預設值。
• [ ] 知道最熱門 key 所佔的比例；先修正資料傾斜，再加記憶體。
• [ ] 每個 join 的 build / broadcast side 確實很小。
• [ ] 檢查過 join 的放大倍數（前後各做一次 COUNT(*)）。
• [ ] 每個查詢的記憶體 × 並行度放得進機器（work_mem、Trino 查詢上限、pool 大小）。
• [ ] Client 用 fetch size 或 server-side cursor 串流讀取。
• [ ] 加記憶體是最後的修正手段，而且有記錄為什麼需要它。

5) 資料的重新排序與合併 ★★★★★優先度 5／5 — 必備 — 幾乎每一輪面試都會出現

5-1) 為什麼磁碟上的順序很重要

依查詢會過濾的欄位排序資料，能讓每個檔案的 min/max 範圍變窄，讀取端就能跳過大部分檔案； 它也能改善壓縮，並讓 join 或聚合省掉自己的排序。

Unsorted by country — every file's range covers everything; nothing can be skipped
  file 1: country min=AR max=US      file 2: min=BR max=US      file 3: min=AU max=VN

Sorted by country — a filter on country = 'TW' opens one file
  file 1: country min=AR max=FR      file 2: min=GB max=PL      file 3: min=TW max=VN

排序過的資料的好處：

• Data skipping —— min/max、zone map 與 bloom filter 能 prune 掉更多資料。
• 壓縮 —— 連續相同的值有利於 run-length 與 dictionary 編碼。
• 免排序的 operator —— merge join 或串流聚合可以直接利用既有的順序。

代價是寫入時的一次排序，也就是一次 shuffle —— 由 writer 付一次，而不是每個讀取者每次讀取都付。

5-2) Sort、cluster 還是 Z-order

線性排序對單一開頭欄位很有效；Z-order 與 Hilbert 曲線（以及 liquid clustering）把多個欄位 交錯排列，讓對其中任何一欄的過濾都能跳過一部分資料 —— 代價是對第一欄能跳過的資料比純排序少。

-- Delta Lake: compact and co-locate by two filter columns
OPTIMIZE events WHERE event_date >= '2026-11-01' ZORDER BY (user_id, event_type);

-- Iceberg (Spark procedure): rewrite files sorted by a Z-order of two columns
CALL lake.system.rewrite_data_files(
  table      => 'analytics.events',
  strategy   => 'sort',
  sort_order => 'zorder(user_id, event_type)'
);

# Spark: sort within each output file without a global sort (no single-reducer bottleneck)
from pyspark.sql import functions as F

FILES_PER_DATE = 8                            # size so each file lands at ~128 MB – 1 GB
(events
   .repartition("event_date",                 # a few writers per date, not one: a big day
                F.pmod(F.hash("user_id"), F.lit(FILES_PER_DATE)))   # is split across 8 tasks
   .sortWithinPartitions("country", "user_id")
   .write.partitionBy("event_date")
   .mode("overwrite")
   .parquet("s3://lake/silver/events/"))

orderBy 是全域排序（range-partitioned shuffle）；sortWithinPartitions 在每個 partition 內部各自排序，而 data skipping 需要的就只有這樣。

5-3) Compaction：合併小檔案

Compaction 把許多小檔案改寫成較少的大檔案；bin-packing 便宜，只修正檔案數，而 sort compaction 還會恢復讓跳過機制有效的佈局。

• 依排程 compact 近期分區；舊分區早就已經 compact 過了。
• Compaction 也是一次 commit：在樂觀並行控制下，它可能和碰到同一批檔案的 writer 衝突 —— 把它排在大量寫入的時段之外，或只 compact writer 已經寫完的分區。
• 之後要讓 snapshot 過期，否則小檔案仍然存著。

小檔案及其成因在管線中的視角見 ./batch_pipeline.md §7-4。

5-4) 合併資料集：MERGE、copy-on-write 與 merge-on-read

MERGE 把一批變更 upsert 進資料表；在 lakehouse 中，copy-on-write（寫時複製）會改寫變更碰到的 每個檔案（寫入慢、讀取快），而 merge-on-read（讀時合併）寫入小的 delete 或 delta 檔，由讀取端 即時套用（寫入快，在 compact 之前讀取較慢）。

陷阱：來源中每個 key 最多只能有一列。 如果兩筆來源列對到同一筆目標列，MERGE 會報錯 （PostgreSQL：“MERGE command cannot affect row a second time”；Delta 與 Spark： 「multiple source rows matched」錯誤）。先去重：

MERGE INTO dim_customer AS t
USING (
  SELECT customer_id, email, country, updated_at
  FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) AS rn
    FROM   staging_customer_changes
    WHERE  updated_at IS NOT NULL                -- DESC puts NULLs first: quarantine them instead
  ) ranked
  WHERE rn = 1                                   -- one row per key: the latest change
) AS s
ON t.customer_id = s.customer_id
WHEN MATCHED AND s.updated_at > t.updated_at THEN
  UPDATE SET email = s.email, country = s.country, updated_at = s.updated_at
WHEN NOT MATCHED THEN
  INSERT (customer_id, email, country, updated_at)
  VALUES (s.customer_id, s.email, s.country, s.updated_at);

• s.updated_at > t.updated_at 這個保護條件讓重播或亂序的批次變得無害。它需要兩邊的時間戳 都不是 null —— NULL 永遠不會比較為較大 —— 所以來源端會先排除沒有時間戳的列（送進隔離表， 而不是直接丟掉）。
• 可以的話，把分區欄位加進 ON 子句（AND t.event_date = s.event_date），讓引擎 prune 目標檔案，而不是掃描整張表 —— 但前提是該欄位對同一個 key 永遠不會改變，而且永遠不是 NULL（NULL = NULL 不為真，所以兩個 NULL 日期不會相符 —— 若可能出現 NULL，改用 IS NOT DISTINCT FROM 這類 null-safe 比較）。如果某列的 event_date 會變動，舊的那列就不再相符，WHEN NOT MATCHED 會插入一筆重複資料。

5-5) 大規模合併排序資料：external merge sort

資料比記憶體大時，引擎會分成放得進記憶體的區塊排序，把每個排序好的 run 寫到磁碟，再用 min-heap 對這些 run 做串流式的 k-way merge —— sort spill、sort-merge join 與 LSM compaction 背後都是同一個演算法。

Phase 1 — run generation           Phase 2 — k-way merge
  read M bytes → sort → run 1        heap holds the head of each run
  read M bytes → sort → run 2        pop the smallest, emit it, push that run's next row
  ...                                memory = one buffer per run, not the data
  read M bytes → sort → run k

import heapq

# Each run is already sorted; heapq.merge streams them with a heap of size k
run_1 = [1, 4, 9]
run_2 = [2, 3, 10]
run_3 = [5, 6, 7]

merged = list(heapq.merge(run_1, run_2, run_3))
print(merged)   # [1, 2, 3, 4, 5, 6, 7, 9, 10]

• 成本：N 列、k 個 run 時為 O(N log k) 次比較，而每一列大約讀寫兩次 —— 這就是為什麼 spill 比較慢但仍會完成。
• 如果 run 的數量多於 buffer 能容納的數量，引擎會分好幾輪合併；記憶體越多，run 越少越長， 合併輪數也越少。

5-6) LSM compaction：在儲存引擎內部合併

LSM-tree 儲存（RocksDB、Cassandra、HBase）把寫入變成循序附加，並在背景合併排序好的檔案； compaction 策略決定你要付哪一種成本。

B-tree 與 LSM 的背景知識，以及三種放大，見 ./sql_vs_nosql.md §3。

6) 儲存系統的讀取與寫入效能 ★★★★★優先度 5／5 — 必備 — 幾乎每一輪面試都會出現

6-1) 永遠要說出的取捨

每一個讓讀取變快的結構 —— 索引、排序順序、實體化的副本 —— 都必須在每次寫入時維護，所以 答案的第一句話，是這個工作負載需要的是哪一邊。

這就是 RUM 取捨 —— 讀取開銷、更新開銷與記憶體 / 空間開銷：你能降低其中任兩個， 無法三個都降。

6-2) 更快的寫入

送出較少但較大的批次、每一列維護較少的結構，以及循序而非隨機寫入，寫入就會變快。

# PostgreSQL: COPY streams rows in one statement — typically far faster than INSERT per row
import io
import psycopg2

rows = [(1, "TW", 120.50), (2, "JP", 80.00), (3, "US", 42.10)]
buf = io.StringIO("".join(f"{oid}\t{country}\t{amount}\n" for oid, country, amount in rows))

with psycopg2.connect(dsn) as conn, conn.cursor() as cur:
    cur.copy_expert("COPY orders_stage (order_id, country, amount) FROM STDIN", buf)

6-3) 更快的讀取

當查詢需要的資料不必掃描就能找到、已經在記憶體中，或已預先計算成查詢要的形狀時，讀取就會變快。

索引規則見 ./sql_for_data.md §10-1； 如何判讀索引有沒有被用到見 ./sql_explain.md。

6-4) 物件儲存與 data lake

在 S3 類的儲存上，成本是按請求與按檔案計，而不是按 seek，所以效能來自較少、較大的檔案、 能避免列目錄的 metadata，以及分散在多個 prefix 的 key。

• S3 每個 prefix 至少能承受每秒 3,500 次寫入與 5,500 次讀取；單一熱門 prefix 會被節流 （503 Slow Down）—— 把 key 分散到多個 prefix（分區本來就會這樣）。
• 列目錄很慢而且要分頁；table format（Iceberg、Delta）改從 metadata 讀取檔案清單。
• 在物件儲存上 rename 就是複製 —— 靠 rename 暫存目錄來 commit 的協定既慢又不安全； table format 改以寫入一個 metadata 指標來 commit。
• Parquet reader 會先抓 footer，再用 ranged GET 只抓它需要的 column chunk —— 所以只讀 少數欄位的寬檔案很便宜，小檔案則不然。

6-5) 熱點與競爭

系統的速度取決於它最忙的那個分區、鎖或 key，所以要讓寫入平均分散，避免把每一筆更新都送到 同一個地方。

• 分散式儲存中的單調遞增 key（以時間戳作為 HBase / Bigtable row key 的前綴、循序的 DynamoDB partition key）會把每一筆新寫入都送到同一個節點 —— 在前面加上 hash 或 bucket 編號。
• 熱門計數器（UPDATE stats SET views = views + 1 WHERE id = 1）會在同一列的鎖上排隊 —— 把計數器切成 N 列、讀取時加總，或先在串流中聚合。
• PostgreSQL 中的長交易會擋住 vacuum，於是 dead row 累積、讀取變慢 —— 讓批次交易保持 簡短並分塊進行（見 §7-4）。

7) 讓查詢變快的 SQL 改寫 ★★★★★優先度 5／5 — 必備 — 幾乎每一輪面試都會出現

7-1) 改寫對照表

大多數慢 SQL 的修正方式，是給 optimizer 一個它用得上的 predicate、在昂貴的步驟之前先減少 列數，或是要求比查詢目前要求的更少的東西。

接著用 EXPLAIN 確認 —— optimizer 已經幫你做過的改寫不會帶來任何好處，而 PostgreSQL 12+ 已經會把只被引用一次的非遞迴 CTE inline（除非標成 MATERIALIZED）。

7-2) 改寫實例：先聚合再 join

在十億筆訂單上算「每個國家的營收」，不需要讓十億列抵達 join —— 只需要每個使用者一列。

-- Before: every order row is joined to users, then grouped
SELECT u.country, SUM(o.amount) AS revenue
FROM   orders o
JOIN   users  u ON u.user_id = o.user_id
WHERE  o.order_date >= DATE '2026-10-01'
GROUP  BY u.country;

-- After: shrink orders to one row per user first, then join the much smaller result
WITH user_revenue AS (
  SELECT user_id, SUM(amount) AS revenue
  FROM   orders
  WHERE  order_date >= DATE '2026-10-01'
  GROUP  BY user_id
)
SELECT u.country, SUM(r.revenue) AS revenue
FROM   user_revenue r
JOIN   users        u ON u.user_id = r.user_id
GROUP  BY u.country;

它回傳相同的答案，因為 SUM 是可分解的 —— 每個使用者小計的總和就是總計。它不適用於跨 join 的 COUNT(DISTINCT …)，也不適用於以「平均的平均」計算的平均值（見 ./analytics.md §2-3）。

7-3) 改寫實例：keyset 分頁

-- Before: page 2001 reads 100 050 rows and returns 50
SELECT order_id, created_at, amount
FROM   orders
ORDER  BY created_at DESC, order_id DESC
OFFSET 100000 LIMIT 50;

-- After: remember the last row of the previous page; an index on (created_at, order_id) seeks to it
SELECT order_id, created_at, amount
FROM   orders
WHERE  (created_at, order_id) < (TIMESTAMP '2026-10-03 08:15:00', 918273)
ORDER  BY created_at DESC, order_id DESC
LIMIT  50;

Tie-breaker（order_id）讓順序成為全序，所以兩列時間戳相同時也不會有列被跳過或重複。 排序鍵必須是 NOT NULL：與 NULL 做列比較的結果是 unknown，所以一頁若停在 created_at 為 NULL 的列，下一頁會是空的 —— 把這些欄位宣告為 NOT NULL，或在 ORDER BY 與 predicate 兩處都用 COALESCE 處理。代價：不能「跳到第 2001 頁」—— 只有下一頁 / 上一頁。

7-4) 分塊進行大量更新與刪除

一次刪除兩億列的 DELETE 會在整段期間持有鎖、一口氣寫出所有 WAL，並留下 vacuum 積壓；分成 有上限的區塊刪除，能讓每個交易都保持簡短。

-- PostgreSQL: delete in batches of 10 000 by the full primary key; repeat until it affects 0 rows.
-- (Not by ctid: on a partitioned table two partitions can share a ctid. And the key of a table
--  partitioned by event_date must include event_date, so match (event_date, event_id), not event_id.)
DELETE FROM events
WHERE  (event_date, event_id) IN (
  SELECT event_date, event_id
  FROM   events
  WHERE  event_date < DATE '2025-01-01'
  LIMIT  10000
);

• 在分區資料表上更好的做法：DROP 或 detach 舊分區 —— 只動 metadata，不必逐列刪除。
• 在 lakehouse 中，DELETE 會改寫檔案（copy-on-write）或寫入 delete 檔（merge-on-read）； 用分區過濾條件限定範圍，之後再 compact。

8) 快問快答 ★★★★☆優先度 4／5 — 高價值 — 這裡有缺口就會掉關