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 & Sharding
Search
Lukas Fittl
June 23, 2020
Technology
0
530
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
180
A Map For Monitoring PostgreSQL
lfittl
2
350
Monitoring Postgres at Scale
lfittl
1
350
Monitoring PostgreSQL at Scale
lfittl
4
220
Postgres Performance for App Developers
lfittl
2
230
GraphQL ❤ PostgreSQL -- P.S. aka BeatQL
lfittl
1
540
Hacking PostgreSQL to Gain SQL Parsing Superpowers
lfittl
1
460
PostgreSQL at a Web Startup
lfittl
3
550
Advanced pg_stat_statements: Filtering, Regression Testing & more
lfittl
4
710
Other Decks in Technology
See All in Technology
20240724_cm_odyssey_hibiyatech
hiashisan
0
110
テスト・設計研修【MIXI 24新卒技術研修】
mixi_engineers
PRO
0
170
ACRiルーム最新情報とAMD GPUサーバーのご紹介
anjn
0
150
フルリモートワークはエンジニアの夢を叶えたか? #cm_odyssey
mamohacy
2
600
ペパボのオブザーバビリティ研修2024 説明資料
kesompochy
0
1.1k
[NIKKEI Tech Talk] KDDI/KAG Scrum & Community for Engineering Training
curanosuke
2
220
テストケースの自動生成に生成AIの導入を試みた話と生成AIによる今後の期待
shift_evolve
0
180
RAGのサービスをリリースして1年3ヶ月が経ちました
segavvy
4
900
サービス開発を前に進めるために 新米リードエンジニアが 取り組んだこと / Steps Taken by a Novice Lead Engineer to Advance Service Development
nologyance
0
180
CEL(Common Expression Language)で書いた条件にマッチしたIAM Policyを見つける / iam-policy-finder
fujiwara3
0
710
[NIKKEI Tech Talk]Bias for Action!! 実践から学ぶための仕組とコミュニティ / Community for Practice and Learning
kanamasa
0
260
スレットハンティングについて知っておきたいこと
hacket
0
130
Featured
See All Featured
Thoughts on Productivity
jonyablonski
64
4.1k
We Have a Design System, Now What?
morganepeng
46
7k
"I'm Feeling Lucky" - Building Great Search Experiences for Today's Users (#IAC19)
danielanewman
224
21k
Six Lessons from altMBA
skipperchong
24
3.2k
KATA
mclloyd
20
13k
How to name files
jennybc
67
96k
Designing the Hi-DPI Web
ddemaree
276
34k
Mobile First: as difficult as doing things right
swwweet
219
8.8k
Producing Creativity
orderedlist
PRO
340
39k
Intergalactic Javascript Robots from Outer Space
tanoku
266
26k
Adopting Sorbet at Scale
ufuk
71
8.8k
JavaScript: Past, Present, and Future - NDC Porto 2020
reverentgeek
44
4.7k
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