Happiness Hints

These are a collection of things that I personally think every production PostgreSQL environment should have. This is generally stuff that my peers and I wouldn’t even discuss or debate; we just assume that it’s always a good idea.

Note that these apply regardless of whether you manage PostgreSQL on your own hardware in your own datacenter, or whether you use hosted or managed PostgreSQL from a provider like Amazon RDS.

Version: jer_s/2019-11-07

  • Checksums Enabled

  • Multiple Physical Locations (Multi-AZ on AWS)

  • Physical Backups

    • Minimum 1 month retention

    • Regular restore testing

  • Logical Backups

    • Scheduled exports/dumps and redrive/replay

    • Logical replication

  • Huge Pages

  • Logging

    • Retention same minimum as backups (on AWS use max and publish to Cloudwatch)

    • Log autovacuum min duration = 10 seconds (or lower)

    • Log lock waits

    • On AWS, configure forced autovacuum logging at level WARNING

  • PostgreSQL Updates

  • Connection Pooling

    • Centralized and decentralized (app-tier) architectures exist

    • Recycle server connections (e.g. server_lifetime)

  • Active Session Monitoring (AWS Performance Insights)

    • Keep the history

  • OS Monitoring (AWS Enhanced Monitoring)

    • 10 second (or lower) collection

  • Preload pg_stat_statements

  • Limit on Temp Usage by Default (esp. Aurora)

    • Log temp usage when close to the limit

  • Alarms

    • Maximum used transaction IDs

    • Average Active Sessions [“DBLoad” Cloudwatch Metric]

    • Free disk space (RDS and self-managed) / Free local storage (Aurora)

    • Memory / swap

    • Replica Lag (RDS and self-managed)

Comments are closed.


This is my personal website. The views expressed here are mine alone and may not reflect the views of my employer.

contact: 312-725-9249 or schneider @ ardentperf.com




Enter your email address to receive notifications of new posts by email.

Join 36 other followers