Save 37% off PRO during our Black Friday Sale! »

RDS Postgres Configuration for Humans

E084eb5b13255d30b3800d7afb251147?s=47 OnGres
November 14, 2019

RDS Postgres Configuration for Humans

E084eb5b13255d30b3800d7afb251147?s=128

OnGres

November 14, 2019
Tweet

Transcript

  1. POSTGRESQL CONFIGURATION FOR HUMANS RDS POSTGRES CONFIGURATION FOR HUMANS ÁLVARO

    HERNÁNDEZ
  2. RUNNING NATIVELY POSTGRESQL ON KUBERNETES ` whoami` • Founder &

    CEO, OnGres • 20+ years PostgreSQL user and DBA • Mostly doing R&D to create new, innovative software on Postgres • Frequent speaker at PostgreSQL, database conferences • Principal Architect of ToroDB • Founder and President of the NPO Fundación PostgreSQL • AWS Data Hero Álvaro Hernández <aht@ongres.com> @ahachete
  3. POSTGRESQL CONFIGURATION FOR HUMANS //POSTGRESQL CONFIGURATION • Mainly postgresql.conf (here’s

    most of the meat). • Authentication: pg_hba.conf (and pg_ident.conf). • Replicas: recovery.conf (merged with postgresql.conf since v12). • Some initdb parameters. • Per-object settings (eg. fillfactor). Advanced stuff: • Developer parameters. • Compile-time #defines.
  4. POSTGRESQL CONFIGURATION FOR HUMANS //RDS CONFIGURATION • DB ParameterGroup (contains

    postgresql.conf). • Authentication (pg_hba.conf): provided. • Replication, HA: provided. • No initdb options. • Per-object settings is still possible. No Advanced stuff: • Developer parameters. • Compile-time #defines.
  5. POSTGRESQL CONFIGURATION FOR HUMANS //TUNABLE POSTGRESQL.CONF PARAMETERS

  6. POSTGRESQL CONFIGURATION FOR HUMANS //TUNABLE PARAMETERS IN RDS • Excluding

    extensions and dedicated rds parameters: aws --profile $PROFILE --region $REGION \ rds describe-db-parameters \ --db-parameter-group-name default.postgres10 \ | jq -r '.Parameters[] | select ( .IsModifiable ) | .ParameterName' \ | grep -v '\.' |wc -l 185
  7. CLOUD NATIVE POSTGRESQL EN KUBERNETES SOME RDS TUNING RECOMMENDATIONS

  8. POSTGRESQL CONFIGURATION FOR HUMANS //DB connections 101 (max_connections) • max_connections

    is a hard limit • PostgreSQL will reject connections over this number • Unhappy users! • Default is LEAST(GB_MEM / 9, 5000) • What if I want more than 5000 connections? • Is 3-5K the appropriate sizing?
  9. POSTGRESQL CONFIGURATION FOR HUMANS //DB connections 101 (max_connections) • One

    process per connection (excl. parallelism!) • One process handled by one core • How many cores do you have? • Sure, you have a multi-process, time-sharing OS but what is the scheduling overhead with many processes? • Much worse: cache trashing! • Solution: use connection pooling (AWS: please include it in RDS!)
  10. POSTGRESQL CONFIGURATION FOR HUMANS //DB connections 101 (max_connections) pg_bench, scale

    2000, m4.large (2 vCPU, 8GB RAM, 1k IOPS)
  11. POSTGRESQL CONFIGURATION FOR HUMANS //shared_buffers • The first recommendation everybody

    tells you • Set it on RDS, by default to 1/4th of RAM • Done! • ¼ too low on low memory, too high on high memory • Benchmark, benchmark, benchmark • How high is work_mem * max_connections, maintenance_work_mem, etc?
  12. POSTGRESQL CONFIGURATION FOR HUMANS //work_mem • Max local process memory

    used for operations like sort and joins in queries • Not written in stone: users can SET it overriding its value • But if more memory is used, it spills to disk (and may use different algorithm) reducing performance • Not the same if you are OLTP, OLAP, DW (small to very large) • Raise it from defaults, but don’t forget it could be times max_connections
  13. POSTGRESQL CONFIGURATION FOR HUMANS //Vacuum and autovacuum • Almost always

    too conservative • Bloat is one of the most frequent operational burdens • Hard to get it right: analyze and re-tune periodically • Some parameters are set to “-1” which means “look at these numbers from the vacuum parameters” • autovacuum_{vacuum,analyze}_scale_factor: you may override on a per-table level
  14. POSTGRESQL CONFIGURATION FOR HUMANS //Vacuum and autovacuum • General advice:

    • Raise vacuum_cost_limit significantly • Reduce autovacuum_vacuum_cost_delay • Use more autovacuum_max_workers if you have many cores
  15. POSTGRESQL CONFIGURATION FOR HUMANS //GET DOCUMENTATION FROM POSTGRESQLCO.NF

  16. POSTGRESQL CONFIGURATION FOR HUMANS //UPLOAD AND MANAGE YOUR CONF (INCL.

    RDS) SUBSCRIBE ON POSTGESQLCO.NF TO BECOME A BETA TESTER!
  17. POSTGRESQL CONFIGURATION FOR HUMANS //THANK YOU Álvaro Hernández <aht@ongres.com> @ahachete

    / www.ongres.com QUESTIONS?