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
410
Monitoring PostgreSQL at Scale
lfittl
4
250
Postgres Performance for App Developers
lfittl
2
290
GraphQL ❤ PostgreSQL -- P.S. aka BeatQL
lfittl
1
580
Hacking PostgreSQL to Gain SQL Parsing Superpowers
lfittl
1
560
PostgreSQL at a Web Startup
lfittl
3
590
Advanced pg_stat_statements: Filtering, Regression Testing & more
lfittl
4
760
Other Decks in Technology
See All in Technology
白金鉱業Meetup_Vol.19_PoCはデモで語れ!顧客の本音とインサイトを引き出すソリューション構築
brainpadpr
2
470
CI/CDとタスク共有で加速するVibe Coding
tnbe21
0
230
2025/6/21 日本学術会議公開シンポジウム発表資料
keisuke198619
2
470
Claude Code Actionを使ったコード品質改善の取り組み
potix2
PRO
2
1.1k
Claude Code どこまでも/ Claude Code Everywhere
nwiizo
53
31k
IIWレポートからみるID業界で話題のMCP
fujie
0
700
20250625 Snowflake Summit 2025活用事例 レポート / Nowcast Snowflake Summit 2025 Case Study Report
kkuv
1
170
ObsidianをMCP連携させてみる
ttnyt8701
2
140
Azure AI Foundryでマルチエージェントワークフロー
seosoft
0
140
初めてのAzure FunctionsをClaude Codeで作ってみた / My first Azure Functions using Claude Code
hideakiaoyagi
1
180
Кто отправит outbox? Валентин Удальцов, автор канала Пых
lamodatech
0
260
In Praise of "Normal" Engineers (LDX3)
charity
2
1.2k
Featured
See All Featured
Agile that works and the tools we love
rasmusluckow
329
21k
How GitHub (no longer) Works
holman
314
140k
GraphQLの誤解/rethinking-graphql
sonatard
71
11k
jQuery: Nuts, Bolts and Bling
dougneiner
63
7.8k
Understanding Cognitive Biases in Performance Measurement
bluesmoon
29
1.8k
Build The Right Thing And Hit Your Dates
maggiecrowley
36
2.7k
The Straight Up "How To Draw Better" Workshop
denniskardys
233
140k
Sharpening the Axe: The Primacy of Toolmaking
bcantrill
43
2.4k
YesSQL, Process and Tooling at Scale
rocio
173
14k
[Rails World 2023 - Day 1 Closing Keynote] - The Magic of Rails
eileencodes
35
2.3k
Docker and Python
trallard
44
3.4k
Product Roadmaps are Hard
iamctodd
PRO
53
11k
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