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
How to Scale Postgres - Automation, Tuning & Sh...
Search
Lukas Fittl
June 23, 2020
Technology
0
640
How to Scale Postgres - Automation, Tuning & Sharding
Talk at Postgres Vision 2020
Lukas Fittl
June 23, 2020
Tweet
Share
More Decks by Lukas Fittl
See All by Lukas Fittl
What's Missing for Postgres Monitoring
lfittl
0
230
A Map For Monitoring PostgreSQL
lfittl
2
380
Monitoring Postgres at Scale
lfittl
1
420
Monitoring PostgreSQL at Scale
lfittl
4
260
Postgres Performance for App Developers
lfittl
2
290
GraphQL ❤ PostgreSQL -- P.S. aka BeatQL
lfittl
1
590
Hacking PostgreSQL to Gain SQL Parsing Superpowers
lfittl
1
580
PostgreSQL at a Web Startup
lfittl
3
590
Advanced pg_stat_statements: Filtering, Regression Testing & more
lfittl
4
770
Other Decks in Technology
See All in Technology
SRE新規立ち上げ! Hubbleインフラのこれまでと展望
katsuya0515
0
160
ビジネス文書に特化した基盤モデル開発 / SaaSxML_Session_2
sansan_randd
0
260
専門分化が進む分業下でもユーザーが本当に欲しかったものを追求するプロダクトマネジメント/Focus on real user needs despite deep specialization and division of labor
moriyuya
0
1k
o11yツールを乗り換えた話
tak0x00
1
130
みんなのSRE 〜チーム全員でのSRE活動にするための4つの取り組み〜
kakehashi
PRO
2
140
AWS DDoS攻撃防御の最前線
ryutakondo
0
100
Claude Codeが働くAI中心の業務システム構築の挑戦―AIエージェント中心の働き方を目指して
os1ma
9
1.5k
「育てる」サーバーレス 〜チーム開発研修で学んだ、小さく始めて大きく拡張するAWS設計〜
yu_kod
1
250
AIに目を奪われすぎて、周りの困っている人間が見えなくなっていませんか?
cap120
1
430
Amazon Q Developerを活用したアーキテクチャのリファクタリング
k1nakayama
2
180
마라톤 끝의 단거리 스퍼트: 2025년의 AI
inureyes
PRO
1
680
僕たちが「開発しやすさ」を求め 模索し続けたアーキテクチャ #アーキテクチャ勉強会_findy
bengo4com
0
1.9k
Featured
See All Featured
The MySQL Ecosystem @ GitHub 2015
samlambert
251
13k
Large-scale JavaScript Application Architecture
addyosmani
512
110k
Why Our Code Smells
bkeepers
PRO
337
57k
10 Git Anti Patterns You Should be Aware of
lemiorhan
PRO
656
60k
JavaScript: Past, Present, and Future - NDC Porto 2020
reverentgeek
50
5.5k
BBQ
matthewcrist
89
9.8k
Responsive Adventures: Dirty Tricks From The Dark Corners of Front-End
smashingmag
251
21k
Templates, Plugins, & Blocks: Oh My! Creating the theme that thinks of everything
marktimemedia
31
2.5k
A designer walks into a library…
pauljervisheath
207
24k
The Language of Interfaces
destraynor
158
25k
Stop Working from a Prison Cell
hatefulcrawdad
271
21k
Dealing with People You Can't Stand - Big Design 2015
cassininazir
367
26k
Transcript
@LukasFittl How to Scale Postgres: Automation, Tuning & Sharding
@LukasFittl
Scaling Postgres
Scaling Postgres
Automation Handling 100s of database servers
Consistency is key
Infrastructure as Code
Postgres Infrastructure as Code
Demo: Managing Configuration using Terraform
Cloud PaaS Synchronized Configuration Terraform
Cloud PaaS Synchronized Configuration Terraform Access Control (Roles, pg_hba.conf) Terraform
Cloud PaaS Synchronized Configuration Terraform Parameter Groups Access Control (Roles,
pg_hba.conf) Roles: Terraform HBA: Built-in Automatic Failover (for HA & Planned Updates) Built-in
Cloud PaaS Synchronized Configuration Terraform Parameter Groups Access Control (Roles,
pg_hba.conf) Roles: Terraform HBA: Built-in Automatic Failover (for HA & Planned Updates) Built-in Read Replicas Built-in
Cloud PaaS Synchronized Configuration Terraform Parameter Groups Access Control (Roles,
pg_hba.conf) Roles: Terraform HBA: Built-in Automatic Failover (for HA & Planned Updates) Built-in Read Replicas Built-in Backups Built-in
Cloud PaaS Synchronized Configuration Terraform Parameter Groups Access Control (Roles,
pg_hba.conf) Roles: Terraform HBA: Built-in Automatic Failover (for HA & Planned Updates) Built-in Read Replicas Built-in Backups Built-in Connection Pooling Manual Setup
Cloud PaaS Self-Managed VM Synchronized Configuration Terraform Parameter Groups ?
Access Control (Roles, pg_hba.conf) Roles: Terraform HBA: Built-in ? Automatic Failover (for HA & Planned Updates) Built-in ? Read Replicas Built-in ? Backups Built-in ? Connection Pooling Manual Setup ?
Cloud PaaS Self-Managed VM Synchronized Configuration Terraform Parameter Groups ?
Access Control (Roles, pg_hba.conf) Roles: Terraform HBA: Built-in ? Automatic Failover (for HA & Planned Updates) Built-in pg_auto_failover Read Replicas Built-in ? Backups Built-in ? Connection Pooling Manual Setup ?
pg_auto_failover: Simple, automated failover
pg_auto_failover
Demo: Postgres HA using pg_auto_failover
Tuning Making The Most Of Your Database Server
work_mem tuning
Out Of Memory vs Operations Spill To Disk
Temporary Files Written pg_stat_statements.temp_blks_written pg_stat_database.temp_bytes
Temporary Files Written (Per Query) log_temp_files = 0 Jan 20
09:18:58pm PST 28847 LOG: temporary file: path "base/pgsql_ pgsql_tmp28847.9", size 50658332 Jan 20 09:18:58pm PST 28847 STATEMENT: WITH servers AS ( SELECT …
When Sorts Spill To Disk, Increase work_mem However, be aware
of OOMs!
When you get a lot of Out of Memory Errors
Reduce work_mem!
VACUUM
autovacuum => SELECT pid, query FROM pg_stat_activity WHERE query LIKE
'autovacuum: %'; 10469 | autovacuum: VACUUM ANALYZE public.schema_columns 12848 | autovacuum: VACUUM public.replication_follower_stats 28626 | autovacuum: VACUUM public.schema_index_stats | (to prevent wraparound) (3 rows) pg_stat_activity
autovacuum pg_stat_progress_vacuum relid: OID of the table phase: current VACUUM
phase heap_blks_total: Heap Blocks Total heap_blks_scanned: Heap Blocks Scanned heap_blks_vacuumed: Heap Blocks Vacuumed …
Reduce autovacuum_vacuum_cost_delay To Increase VACUUM Speed 80 MB/s 8 MB/s
(20ms) (2ms) PG 12+ Older PG Default OS / Disk Reads
Use Table Partitioning For Append-Only + Delete Workloads (e.g. Timeseries)
Checkpoints
Data Directory WAL WAL WAL Buffer Cache Checkpointer WAL Checkpoints
Are Important For I/O Tuning
16688 LOG: checkpoint starting: xlog xlog = WAL exceeded max_wal_size,
checkpoint has to happen quickly time = checkpoint_timeout reached, checkpoint impact spread over time
Checkpoint Statistics pg_stat_bgwriter checkpoints_timed: # of scheduled checkpoints checkpoints_req: #
of requested checkpoints 1. Time Between Checkpoints 2. % of Timed Checkpoints
Increase max_wal_size / Reduce checkpoint_timeout To Have More Timed Checkpoints
(but be careful with recovery times)
Tune checkpoint_completion_target To Control I/O Impact of Timed Checkpoints (Often
0.9 is a good value, but depends on I/O Subsystem & Workload)
Demo: Postgres 13 WAL Monitoring
Sharding Scaling Beyond The Limits of a Single Server
Citus: Extension for Sharding Postgres
Select from table Coordinator Table metadata Select from table_1001 Select
from table_1003 Select from table_1002 Select from table_1004 Data node N Data node 2 Data node 1 Table_1001 Table_1003 Table_1002 Table_1004 Each node PostgreSQL with Citus installed 1 shard = 1 PostgreSQL table Sharding data across multiple nodes
Demo: Hyperscale (Citus) on Kubernetes with Azure Arc
Thank you!
[email protected]
@LukasFittl