PostgreSQL optimization – the best of YouTube series

By /

The Best of Series

momjian.us/presentations

Key take away: optimizer is making three major decisions:
1.) Access Method – Sequential, Index Scan, Bitmap Heap Scan
2.) Join Method – Nested Loop, Hash Join, Merge Join
3.) Join Order

This one covers many of the IMPORTANT postgresql.conf parameters and there purpose towards the 2nd half of the video. Nice!
PostgreSQL Open Source High Availability – Failover to Standby (generally used during hardware / software failures) – Backup and Recovery (generally used to recover from human error)

The presentation above addresses requirements open source (mostly free solutions) for PostgreSQL functionality to:

– High Availability – No application reconfigure after fail-over/switch – RepMgr (an open source tool for managing PostgreSQL replication and failover)

– RPO (recovery point objective / maximum amount of committed data lost) = 0 after fail-over/switch-over

– Fail-over should be automatic – switch over is ALWAYS manual / planned – Uses HAProxy to Route with Keepalived daemon to manage virtual IP that always routes to the active node

– Back-up and recovery – Barman (Python based tool) to backup and recover clusters and databases

– Automatic deployment and configuration – Terraform / Ansible

– Monitoring – Prometheus / Grafana

   • Prometheus (https: / /prometheus.io)

   • Grafana (https: //grafana.com)

   • Scalable

   • Collectors for Postgres, OS, Keepalived, HAProxy

      (https: //github.com/wrouesnel/postgres _exporter)

      (https: / /github.com/prometheus/node _exporter)

      (https:/ /github.com/cafebazaar/keepalived-exporter)

      (no separate exporter is needed for HAProxy Metrics)

– Encryption TLS in transit / at rest

– Auditing

– Automatic maintenance

– Performance insights in queries

Post Views: 15

Leave a Comment

Scroll to Top