Model Online / On-Site
Duration
Modules 5 modules

Course Objective & Scope

Running PostgreSQL reliably in 24/7 production requires tuning out-of-the-box defaults to match hardware, staying ahead of Autovacuum lag and Transaction ID Wraparound, and proving that backups can restore to an exact point in time. Unlike developer SQL courses, this programme is dedicated entirely to DBA and production infrastructure operations.

It covers process and memory architecture, physical storage layout (PGDATA, WAL, checkpoints), postgresql.conf tuning, role security and pg_hba.conf, Autovacuum tuning and bloat remediation, connection pooling with PgBouncer, physical backups and Point-in-Time Recovery (PITR) with pgBackRest, Streaming/Logical Replication, and automated failover with Patroni.

Who Should Attend?

  • Database Administrators (DBAs) managing production PostgreSQL clusters
  • DevOps, SRE and Systems/Cloud Engineers operating database infrastructure
  • Infrastructure teams migrating mission-critical systems from commercial engines to PostgreSQL
  • Prerequisite: basic Linux command-line proficiency and foundational SQL knowledge

Key Takeaways

  • Tune shared_buffers, work_mem, WAL and checkpoint parameters for specific hardware and workloads
  • Configure global and per-table Autovacuum settings to prevent table bloat and Transaction ID Wraparound
  • Implement full/differential/incremental backups with pgBackRest and execute Point-in-Time Recovery (PITR) drills
  • Deploy physical Streaming Replication, Logical Replication and Patroni-managed high availability
  • Monitor live cluster health using system views (pg_stat_activity, pg_stat_statements) and Prometheus exporters

Curriculum

PostgreSQL Internals, Memory and Storage Layout
  • Postmaster, background processes (checkpointer, bgwriter, walwriter, autovacuum) and the process-per-connection model
  • Shared and local memory tuning: shared_buffers, work_mem, maintenance_work_mem, effective_cache_size
  • PGDATA layout, Tablespaces, Heap page anatomy, TOAST and the Write-Ahead Log (WAL) lifecycle
Security, Connection Pooling and Production Configuration
  • Hardening pg_hba.conf, SCRAM-SHA-256, TLS encryption and least-privilege Roles/Grants/RLS
  • Why idle connections hurt PostgreSQL and deploying PgBouncer (session vs transaction pooling)
  • Operational logging configuration (log_min_duration_statement, log_lock_waits, log_checkpoints)
MVCC Maintenance, Autovacuum Tuning and Bloat Control
  • Dead tuples, the Free Space Map (FSM), Visibility Map and Heap-Only Tuple (HOT) updates
  • Tuning Autovacuum scale factors, cost limits and per-table overrides for write-heavy tables
  • Preventing Transaction ID (XID) Wraparound, measuring table/index bloat and running pg_repack / REINDEX CONCURRENTLY
Backup, Restore and Point-in-Time Recovery (PITR)
  • Logical backups (pg_dump, pg_restore) vs physical base backups (pg_basebackup)
  • Enterprise backup pipelines, WAL archiving and retention policies with pgBackRest
  • Disaster recovery lab: executing Point-in-Time Recovery (PITR) to right before an accidental data loss
Replication, High Availability (Patroni) and Major Upgrades
  • Physical Streaming Replication (async vs sync), Replication Slots and read-scale replicas
  • Logical Replication for selective sync and near-zero-downtime major upgrades (pg_upgrade vs logical cutover)
  • Automated failover with Patroni, etcd/Consul and HAProxy plus a production DBA checklist

Other Courses in This Category

  • MySQL and MariaDB Development, Tuning and Administration

    InnoDB storage engine and clustered index architecture, query and index optimisation, transaction and lock analysis, backups, GTID replication and high availability — for developer and DBA teams running MySQL and MariaDB.

    Databases Corporate
    Live Course
    5 modules
    View
  • Oracle Database Architecture, SQL Tuning and Administration

    Oracle Multitenant (CDB/PDB) architecture, SGA/PGA memory and background processes, tablespace/segment management, B-Tree/Bitmap indexes, partitioning, AWR/ASH tuning, RMAN backups and Data Guard essentials.

    Databases Corporate
    Live Course
    5 modules
    View
  • MongoDB Document Data Modelling, Aggregation and Operations

    BSON and document schema design patterns (embedding vs referencing), the Aggregation Pipeline, compound/multikey indexes and the ESR rule, multi-document ACID transactions, Change Streams, Replica Sets and Sharding.

    Databases Corporate
    Live Course
    5 modules
    View
  • In-Memory Data Structures, Caching and Messaging with Redis

    Redis data structures (Strings, Hashes, Lists, Sets, Sorted Sets, Streams, HyperLogLog), caching patterns (cache-aside, stampede protection), distributed locks, rate limiting, Lua/pipelining, RDB/AOF persistence and Redis Cluster.

    Databases Corporate
    Live Course
    5 modules
    View
  • Distributed NoSQL Databases with Apache Cassandra

    Masterless ring architecture, consistent hashing, tunable consistency (LOCAL_QUORUM), LSM-Tree/SSTable storage internals, query-driven CQL data modelling and cluster operations — for high-write distributed systems.

    Databases Corporate
    Live Course
    5 modules
    View
  • Search and Real-Time Analytics with Elasticsearch and OpenSearch

    Inverted index and Lucene internals, text analysis (tokenizers/filters), mapping design, Query DSL and BM25 relevance tuning, Aggregations, hybrid/vector search and cluster lifecycle management (ILM/ISM).

    Databases Corporate
    Live Course
    5 modules
    View

Get in touch