Postgres

• Big Table Problems


  • Adding Columns
  • Adding Constraints
  • Index Creation
  • Unused Indexes
  • Skewed Data
  • Vacuum
  • Large single table
  • Wasted Table Space
• Big Table Problem: Index Creation

  1. CREATE INDEX ON customers (last_name, first_name)

  2. Locking bad

  3. Create Index Concurrently

  4. Quick lock

  5. Runtime tradeoff

  6. You break it you clean it up

  7. CREATE INDEX CONCURRENTLY
• Big Table Problem: Unused Indexes


  1. Index analysis/cleanup

  2. Combine btrees where they make sense

  3. Pg_stat_user_indexes

  4. Pg_statio_user_indexes

  5. Look for unused indexes

  6. Unused on primary != unused in cluster

  7. Reset for active stats

  8. Gather data on all nodes
• Big Table Problem: Skewed Data

  1. Create Partial indexes

  2. CREATE INDEX foo … WHERE bar = 1

  3. Faster updates

  4. Only relevant data
• Big Table Problem: Vacuum


  1. Vacuum required for performance

  2. High xacts = wraparound autovacs

  3. Long vacuums & less frequent vacuums

  4. Vacuum-related configuration

  5. Autovacuum_workers = 6

  6. Maintenance_work_mem = 30GB

  7. Tune autovacuum

  8. autovacuum_vacuum_insert_scale_factor=0

  9. autovacuum_vacuum_insert_threshold=<constant>
• Big Table Problem: Large single table

  1. Partitioning

  2. Queries don't need to know

  3. Can tune/index partitions individually

  4. Break into smaller tables

  5. ATTACH PARTITION

  6. DETACH PARTITION
• Partitioning helps with data lifecycle management
• Partitioning is not a magic bullet
• Migrating is a "project"
• Partitioner's Paradox
• Big Table Problem: Wasted Table Space

  1. Optimize Table Size

  2. Fixed-size, largest to smallest

  3. Variable length or NULLable last

  4. Order by size
• 30% savings can be achieved by optimizing table size
• High Transaction Problems


  • WAL Generation
  • High xid
  • pgBouncer
• WAL Size Workarounds


  1. Async archive/restore

  2. Daemon mode

  3. Pgbackrest

  4. Switches to streaming

  5. Replica catches up

  6. Force restore failure
• High Transaction Problems: High xid

  1. Tune VACUUM

  2. Remove SAVEPOINTs

  3. Use bigints for ids
• PgBouncer solution


  1. Multi-Bouncer

  2. Use Systemd to multiplex

  3. Semi-Arbitrary numbers of concurrent PgBouncers
• Read Replica Problems


  • Management
  • Different Query Workloads
  • Lag
  • SAVEPOINT
  • Long running analytics
• Read Replica Problems: Management

  1. Automate tool chains

  2. Managed Postgres + APIs

  3. Centralize Monitoring & Data Collection
• Read Replica Problems: Different Query Workloads

  1. Tuning/analysis over time/trending

  2. Can write back to primary to give stats history

  3. Third-party services
• Read Replica Problems: Lag


  1. Statement timeouts

  2. Max standby archive delay

  3. Max standby streaming delay
• Read Replica Problems: SAVEPOINT

  1. Underlying app changes

  2. Review subtransactions
• Read Replica Problems: Long running analytics

  1. Hot_standby_feedback = off

  2. Not streaming

  3. Dedicating specific replicas to analytics only

  4. Archive only replica
• How did we tame the Mastodon?
• Ways to tame the Mastodon


  • Minimize locks
  • Be smart about indexing
  • Per table vacuum tuning
  • Partition if you can
  • Design schema to minimize space
  • Look at multi pgBouncer
  • Planned replicas