How to Scale Postgres - Automation, Tuning & Sharding

Slide 1

Slide 1 text

@LukasFittl How to Scale Postgres: Automation, Tuning & Sharding

Slide 2

Slide 2 text

@LukasFittl

Slide 3

Slide 3 text

Scaling Postgres

Slide 4

Slide 4 text

Scaling Postgres

Slide 5

Slide 5 text

Automation Handling 100s of database servers

Slide 6

Slide 6 text

Consistency is key

Slide 7

Slide 7 text

Infrastructure as Code

Slide 8

Slide 8 text

Postgres Infrastructure as Code

Slide 9

Slide 9 text

Demo: Managing Configuration using Terraform

Slide 10

Slide 10 text

Cloud PaaS Synchronized Conﬁguration Terraform

Slide 11

Slide 11 text

Cloud PaaS Synchronized Conﬁguration Terraform Access Control (Roles, pg_hba.conf) Terraform

Slide 12

Slide 12 text

Cloud PaaS Synchronized Conﬁguration Terraform Parameter Groups Access Control (Roles, pg_hba.conf) Roles: Terraform HBA: Built-in Automatic Failover (for HA & Planned Updates) Built-in

Slide 13

Slide 13 text

Cloud PaaS Synchronized Conﬁguration 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

Slide 14

Slide 14 text

Slide 15

Slide 15 text

Slide 16

Slide 16 text

Cloud PaaS Self-Managed VM Synchronized Conﬁguration 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 ?

Slide 17

Slide 17 text

Cloud PaaS Self-Managed VM Synchronized Conﬁguration 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 ?

Slide 18

Slide 18 text

pg_auto_failover: Simple, automated failover

Slide 19

Slide 19 text

pg_auto_failover

Slide 20

Slide 20 text

Demo: Postgres HA using pg_auto_failover

Slide 21

Slide 21 text

Tuning Making The Most Of Your Database Server

Slide 22

Slide 22 text

work_mem tuning

Slide 23

Slide 23 text

Out Of Memory vs Operations Spill To Disk

Slide 24

Slide 24 text

Temporary Files Written pg_stat_statements.temp_blks_written pg_stat_database.temp_bytes

Slide 25

Slide 25 text

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 …

Slide 26

Slide 26 text

When Sorts Spill To Disk, Increase work_mem However, be aware of OOMs!

Slide 27

Slide 27 text

When you get a lot of Out of Memory Errors Reduce work_mem!

Slide 28

Slide 28 text

VACUUM

Slide 29

Slide 29 text

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

Slide 30

Slide 30 text

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 …

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

Use Table Partitioning For Append-Only + Delete Workloads (e.g. Timeseries)

Slide 33

Slide 33 text

Checkpoints

Slide 34

Slide 34 text

Data Directory WAL WAL WAL Buffer Cache Checkpointer WAL Checkpoints Are Important For I/O Tuning

Slide 35

Slide 35 text

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

Slide 36

Slide 36 text

Checkpoint Statistics pg_stat_bgwriter checkpoints_timed: # of scheduled checkpoints checkpoints_req: # of requested checkpoints 1. Time Between Checkpoints 2. % of Timed Checkpoints

Slide 37

Slide 37 text

Increase max_wal_size / Reduce checkpoint_timeout To Have More Timed Checkpoints (but be careful with recovery times)

Slide 38

Slide 38 text

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)

Slide 39

Slide 39 text

Demo: Postgres 13 WAL Monitoring

Slide 40

Slide 40 text

Sharding Scaling Beyond The Limits of a Single Server

Slide 41

Slide 41 text

Citus: Extension for Sharding Postgres

Slide 42

Slide 42 text

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

Slide 43

Slide 43 text

Demo: Hyperscale (Citus) on Kubernetes with Azure Arc

Slide 44

Slide 44 text

Thank you! lukas@fittl.com @LukasFittl