Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
Handling a High Performance PostgreSQL Database
Search
Rain
May 25, 2021
Technology
0
21
Handling a High Performance PostgreSQL Database
Rain
May 25, 2021
Tweet
Share
More Decks by Rain
See All by Rain
System Observability: We can improve only what we can observe
rainrainwu
0
63
Mock out dependencies while testing in Python
rainrainwu
0
170
Scheduling Async Tasks with Python Celery
rainrainwu
0
64
Other Decks in Technology
See All in Technology
KubeCon + CloudNativeCon Japan 2025 Recap Opening & Choose Your Own Adventureシリーズまとめ
mmmatsuda
0
260
無意味な開発生産性の議論から抜け出すための予兆検知とお金とAI
i35_267
3
11k
敢えて生成AIを使わないマネジメント業務
kzkmaeda
2
370
ビズリーチが挑む メトリクスを活用した技術的負債の解消 / dev-productivity-con2025
visional_engineering_and_design
3
6.6k
論文紹介:LLMDet (CVPR2025 Highlight)
tattaka
0
310
モバイル界のMCPを考える
naoto33
0
420
開発生産性を測る前にやるべきこと - 組織改善の実践 / Before Measuring Dev Productivity
kaonavi
3
1.1k
開発生産性を組織全体の「生産性」へ! 部門間連携の壁を越える実践的ステップ
sudo5in5k
2
6.2k
【5分でわかる】セーフィー エンジニア向け会社紹介
safie_recruit
0
27k
怖くない!はじめてのClaude Code
shinya337
0
380
DBのスキルで生き残る技術 - AI時代におけるテーブル設計の勘所
soudai
PRO
18
6.1k
Delegating the chores of authenticating users to Keycloak
ahus1
0
140
Featured
See All Featured
Build your cross-platform service in a week with App Engine
jlugia
231
18k
JavaScript: Past, Present, and Future - NDC Porto 2020
reverentgeek
48
5.4k
Understanding Cognitive Biases in Performance Measurement
bluesmoon
29
1.8k
The Cult of Friendly URLs
andyhume
79
6.5k
Practical Orchestrator
shlominoach
188
11k
[RailsConf 2023] Rails as a piece of cake
palkan
55
5.6k
GitHub's CSS Performance
jonrohan
1031
460k
Statistics for Hackers
jakevdp
799
220k
No one is an island. Learnings from fostering a developers community.
thoeni
21
3.4k
Fireside Chat
paigeccino
37
3.5k
The Invisible Side of Design
smashingmag
301
51k
Refactoring Trust on Your Teams (GOTO; Chicago 2020)
rmw
34
3.1k
Transcript
Handling a High Performance PostgreSQL Database
Hello! I am Rain! ex- A. SWE @iCHEF ex- SWE
@NetDB 2 GitHub: RainrainWu LinkedIn: Rain Wu
Outline ⬡ 資料庫基本概念 ⬡ 如何構思解決方案 ⬡ 具體方案與適合情境 3
1. 資料庫基本概念 在進入主題前,先來聊些必備知識
“ 資料庫是一種讓我們能穩定儲 存大量資料的軟體,以實現資料 與邏輯的解耦。 5
“ 其中又可依據是否對存入的資 料有嚴謹的 Schema 要求分為 RDBMS 和 NoSQL 資料庫,這 次所討論的
PostgreSQL 就是 一種 RDBMS。 6
資料庫有哪些操作 ⬡ CRUD ∙ Create ∙ Read ∙ Update ∙
Delete 7
為什麼 資料庫 會變慢 8
有很多原因 ⬡ 讀取很多資料 ⬡ 搜尋特定資料效率低落 ⬡ 為確保一致性所以有交易隔離機制 ⬡ ... 9
資料庫在道德上必須滿足 ACID ⬡ Atomic ⬡ Consistency ⬡ Isolation ⬡ Durability
10
2. 如何構思解決方案
搜集關鍵 資訊 針對可能的方法搜集資料,逐個 驗證是否適用於當前情境 12
可能有用的資訊 ⬡ 資料表的統計資訊 ⬡ Query plan ⬡ Transaction 統計資訊 ⬡
Monitor 13
可以思考的問題 ⬡ 同樣目標可不可以換一種做法 ⬡ 同樣事情可不可以只做一次 ⬡ 為了高效能有哪些東西可以降低優先級 ⬡ 這資料庫的底層實現是否適合當前情境 14
決策流程飛輪 15 驗證方法適用性 依據資訊提出 候選方案 依據異常行為 搜集資訊
3. 具體方案與適合情境
Tune your SQL command ⬡ 找出耗時不合理的指令 ⬡ 看看 query plan
做了哪些事 17
Cache ⬡ 同樣的資料不用每次都搜尋 ⬡ 存在快取中能減少資料庫的工作量 18
Denormalize ⬡ 基於正規化的原則資料庫不該有冗余 的欄位 ⬡ 但有些常用數值每次計算很耗資源, 同時變動較快也不適用 Cache ⬡ 比如
FB 貼文按讚數、TG 未讀訊息數 19
Scale-up ⬡ CPU, RAM 吃滿了? ⬡ 那就加更多資源或換成更高級別的 代管服務吧 20
Scale-out (Clustering) ⬡ 多幾台機器分攤工作量 ⬡ 相關概念包含讀寫分離、HA ⬡ 不過為了強一致性通常只有一個負 責接收寫入的節點 ⬡
較適用於 Read >> Write 情境 21
Write-ahead Log (WAL) PostgreSQL 的可靠性機制,在 執行 transaction 前須先像日誌 一樣寫入 Log
內永久儲存 22
23 Primary WAL PG node Standby WAL PG node Standby
WAL PG node pgpool-II Write Read Read Proxy Synchronization
24 Primary WAL PG node Standby WAL PG node Standby
WAL PG node pgpool-II Write Read Read Proxy Synchronization 10 W 100 R 10 W 50 R 50 R 10 W 10 W
讀寫分離 主要是分攤讀的工作量,正好目 前多數服務都是 Read >> Write,但仍須考量資料同步的延 遲 25
Indexes ⬡ 透過維護額外的資料結構讓查找效 率更高 ⬡ 但變相的會犧牲寫入效率 ⬡ 比如二分搜索樹、倒排索引 26
PG 常用 Index GIN 倒排索引,儲存關鍵字與所在文 本之間的映射關係,適合做關鍵 字搜尋 27
28 National Successful University Doc 1 National Failed University Doc
2
Forward Index ⬡ Doc1 : National, Successful, University ⬡ Doc2
: National, Failed, University 29
Inverted Index ⬡ National : Doc1, Doc2 ⬡ Successful :
Doc1 ⬡ University : Doc1, Doc2 ⬡ Failed : Doc2 30
PG 常用 Index BRIN 統計資訊邊界分群,比如時間範 圍、地理位置的 Bounding Box, 適用於銷售日誌或區域查詢 31
Lower Isolation Level ⬡ 根據對交易隔離的要求,資料庫有 多個不同的隔離等級 ⬡ 隔離越是嚴謹效能損失就越大,可 以思考所需要的等級 32
Isolation 層級 ⬡ Read Uncommitted ⬡ Read Committed ⬡ Repeatable
Read ⬡ Serilizable 33
34
Isolation 的主流實作策略 ⬡ Shared-exclusive Lock (SX Lock) ∙ MySQL, MS
SQL ⬡ Multiversion Concurrency Control (MVCC) ∙ PostgreSQL, Oracle 35
SX Lock ⬡ 每筆資料可發行多把 S Lock 和一把 X Lock ⬡
發行 S Lock 時不可有發行中的 X Lock,而發 行 X Lock 時不可有發行中的 S/X Lock ⬡ 只有 Read-Read 不會互相 block,Read-Write 和 Write-Write 都會 36
MVCC ⬡ 每筆紀錄更新或刪除時不直接改動當前資料, 而是新增一個新的版本或刪除標記 ⬡ 每次執行交易時,依據時間點控制該筆交易對 其他紀錄可見的版本 ⬡ 只有 Write-Write
會因為版本先後順序性而 被 block,而 Read-Write 不會 37
常聽到有人批評 PostgreSQL 做 Clustering 成效不如 MySQL ? ⬡ 讀寫分離解決了 MySQL
中 SX Lock 機制的 Read-Write block 問題,而 PostgreSQL 中 MVCC 機制沒這問題 ⬡ 他的 Read 流量沒有大到需要讀寫分離 ⬡ 他配置有問題 38
39 Primary WAL PG node Standby WAL PG node Standby
WAL PG node pgpool-II Write Read Read Proxy Synchronization 10 W 100 R 10 W 50 R 50 R 10 W 10 W
OLTP v.s. OLAP ⬡ 兩者之間有個很關鍵的行為差異,就 是 range scan ⬡ 計算
real time 指標、產報表都需要 ⬡ 資料庫儲存資料的結構影響很大 40
Index-organized Table 41
Heap Table 42
有沒有 Range Scan 差很多 ⬡ Heap Table 基本上要 Serial scan
⬡ 當你使用一個 OLTP server 做分析或報表,覺 得他做得很慢時,你可能需要另個 OLAP server 而不是開腦洞優化 OLTP server 43
The End