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.

  • Multi-AZ (RDS) or Multiple Physical Locations (self-managed PG)

  • Physical Backups

    • 35 days retention

    • Regular restore testing

  • Logical Backups

    • Scheduled exports/dumps and application re-drive

    • Logical replication

  • Huge Pages

  • Autovacuum Logging (RDS: need “force” setting)

    • Logging Level = INFO

    • Minimum duration = 10 seconds

  • PostgreSQL quarterly updates

    • Stable minor releases from community for security and critical bug fixes – similar to Oracle PSU/SPUs (RDS)

      • Some Aurora minor releases have new development work (Aurora)

    • Remember to upgrade extensions; it’s not automatic

  • Connection Pooling

    • Centralized and decentralized (app-tier) architectures exist

    • Recycle server connections (e.g. server_lifetime)

  • Performance Insights (RDS) or Active Session Wait Events Monitoring (self-managed PG)

    • Keep the history

  • Enhanced Monitoring (RDS) or OS Monitoring (self-managed PG)

    • 10 second or lower granularity (1 second granularity is great)

  • 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

    • DBLoad [Average Active Sessions]

    • 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 35 other followers