.webp)
.svg)
Program overview
Online
On-site
Hybrid
PostgreSQL Performance Tuning, High Availability and Replication
An advanced course for teams already running PostgreSQL who need it faster and more resilient. Three days covering workload baselining, optimiser and index tuning, vacuum and write path optimisation, partitioning, lock contention, streaming and logical replication, automated failover with Patroni, and disaster recovery testing. Assumes working PostgreSQL administration knowledge and runs on clusters large enough for plan choices to matter.
Duration:
3 Days
Level:
Advanced
.avif)
1500+ users onboarded
Who will Benefit from this Training?
Training Objectives
Build a high-performing, job-ready tech team.
Personalise your team’s upskilling roadmap and design a befitting, hands-on training program with Uptut
Key training modules
Comprehensive, hands-on modules designed to take you from basics to advanced concepts
Download Curriculum
- Establishing a Performance Baseline
- Defining what good looks like before changing anything
- pg_stat_statements for total impact ranking rather than single query time
- Wait event analysis to separate CPU, I/O, lock and network bound problems
- Benchmarking correctly with pgbench and realistic workloads
- Capturing before and after evidence for every change
- Hands-on: baseline a supplied workload and rank the tuning candidates
- Configuration and Resource Tuning
- Shared buffers, effective cache size and operating system cache interaction
- work_mem sizing and diagnosing spills to disk
- Parallel query configuration and when parallelism hurts
- Planner cost parameters and tuning for SSD storage
- Connection limits, pooling modes and the cost of high connection counts
- Hands-on: tune an instance for a defined workload and quantify the gain
- The Optimiser and Execution Plans
- Plan structure, operators and reading bottom up
- Estimated versus actual rows as the primary diagnostic
- Statistics, extended statistics and correlated columns
- Join algorithm selection and join order search
- Plan regressions and controlling planner behaviour
- Hands-on: diagnose and repair a set of misestimated plans
- Index Strategy and Maintenance
- Choosing between B-tree, GIN, GiST, BRIN and hash for a workload
- Composite ordering, covering indexes and index-only scans
- Partial and expression indexes for skewed data
- B-tree skip scan behaviour and its effect on composite index design
- Index bloat detection and rebuilding concurrently
- Hands-on: redesign an index set and prove the write cost is acceptable
- Vacuum, Bloat and the Write Path
- Autovacuum tuning per table for high churn workloads
- Bloat measurement and reclamation without downtime
- HOT updates, fill factor and reducing index write amplification
- Checkpoint tuning, full page writes and WAL volume reduction
- Long transactions, replication slots and blocked cleanup
- Hands-on: tune a heavy write workload and halve its WAL generation
- Partitioning and Very Large Tables
- Range, list and hash partitioning design decisions
- Partition pruning at plan time and execution time
- Partition-wise joins and aggregation
- Automated partition maintenance and retention with pg_partman
- Migrating an existing large table to a partitioned structure online
- Hands-on: partition a live table with minimal locking and verify pruning
- Locking, Concurrency and Contention
- Lock modes, lock queues and how one blocked session stalls many
- Diagnosing blocking chains and deadlocks from the logs
- DDL locking and safe online schema changes
- Contention hotspots: sequences, counters and hot rows
- Lock timeouts, statement timeouts and defensive session settings
- Hands-on: reproduce a production style blocking incident and resolve it
- Streaming Replication in Depth
- Physical replication mechanics and WAL shipping
- Asynchronous, synchronous, remote_apply and quorum commit trade-offs
- Replication slots, retention and the disk risk of inactive slots
- Cascading replicas and delayed standbys as a protection layer
- Hot standby query conflicts and feedback settings
- Hands-on: build a replication topology and measure the latency cost of each commit mode
- Automated Failover and Cluster Management
- Patroni architecture, distributed consensus and the role of etcd or Consul
- Automatic failover, switchover and controlled maintenance
- Fencing, watchdog and split-brain prevention
- Connection routing with HAProxy, PgBouncer or service discovery
- repmgr and pg_auto_failover as alternatives
- Hands-on: kill the primary and recover the cluster with no data loss
- Logical Replication and Scaling Out
- Publications, subscriptions and selective replication design
- Cross-version replication for near-zero-downtime upgrades
- Read scaling, workload separation and routing read traffic
- Conflict handling, sequence management and DDL limitations
- Sharding options and when Citus becomes the right answer
- Hands-on: migrate a database across major versions with seconds of downtime
- Disaster Recovery and Operational Assurance
- Recovery point and recovery time objectives as architecture inputs
- pgBackRest for parallel backup, retention and restore verification
- Cross-region and cross-cloud recovery topologies
- Restore testing as a scheduled, automated activity
- Writing runbooks the on-call engineer can follow at three in the morning
- Managed service differences on RDS, Aurora and Cloud SQL
- Hands-on: capstone disaster scenario recovered against a stated objective
Hands-on Experience with Tools
Training Delivery Format
Flexible, comprehensive training designed to fit your schedule and learning preferences
Opt-in Certifications
AWS, Scrum.org, DASA & more
.avif)
100% Live
on-site/online training
.avif)
Hands-on
Labs and capstone projects
.avif)
Lifetime Access
to training material and sessions
Skill-Gap Assessment
Analysing skill gap and assessing business requirements to craft a unique program
1
Personalisation
Customising curriculum and projects to prepare your team for challenges within your industry
2
Implementation
Supplementing training with consulting support to ensure implementation in real projects
3
Why this course
- Measured, not guessed: Every tuning change is baselined, applied and re-measured, so teams learn to prove improvement rather than assume it.
- Failover rehearsed under pressure: Participants promote replicas, handle split-brain risk and recover a broken cluster against the clock.
- Targets real availability numbers: Architecture choices are tied to stated recovery point and recovery time objectives rather than discussed in the abstract.
- Self-managed and cloud: Covers Patroni-managed clusters alongside what changes on RDS, Aurora and Cloud SQL.
Training objectives
- Baseline a workload and identify the queries and resources worth tuning
- Tune memory, connection and planner configuration for a specific workload
- Interpret execution plans and diagnose optimiser misestimation
- Design index strategies including partial, covering and expression indexes
- Tune vacuum, checkpoint and write path behaviour under sustained load
- Partition very large tables and verify pruning actually occurs
- Diagnose and resolve lock contention and blocking chains
- Configure streaming replication including synchronous and quorum commit
- Implement automated failover with Patroni and prevent split-brain
- Use logical replication for scaling out and near-zero-downtime migration
- Design, document and test a disaster recovery plan against defined objectives
Who will benefit
- Database administrators operating production PostgreSQL
- Platform, DevOps and site reliability engineers
- Data engineers running high volume PostgreSQL workloads
- Solution architects designing highly available data tiers
- Teams preparing for growth, audit or availability commitments
.avif)
Lead the Digital Landscape with Cutting-Edge Tech and In-House " Techsperts "
Discover the power of digital transformation with train-to-deliver programs from Uptut's experts. Backed by 70,000+ professionals across the world's leading tech innovators.
.svg)
.svg)
.svg)
.svg)
.svg)
Recommended Training
Continue your learning journey with these hand-picked courses and bundles
Frequently Asked Questions
2. Will my team get any practical experience with this training?
.svg)
With our focus on experiential learning, we have made the training as hands-on as possible with assignments, quizzes and capstone projects, and a lab where trainees will learn by doing tasks live.
3. What is your mode of delivery - online or on-site?
We conduct both online and on-site training sessions. You can choose any according to the convenience of your team.
4. Will trainees get certified?
Yes, all trainees will get certificates issued by Uptut under the guidance of industry experts.
5. What do we do if we need further support after the training?
We have an incredible team of mentors that are available for consultations in case your team needs further assistance. Our experienced team of mentors is ready to guide your team and resolve their queries to utilize the training in the best possible way. Just book a consultation to get support.