Advanced Database Administration & Performance Engineering

Duration 5 days – 35 hrs

 

Overview

 

This advanced, hands-on training equips database administrators, engineers, and support teams with the skills to diagnose and optimize database performance, design high availability (HA) setups, implement backup & recovery, and apply security best practices for both PostgreSQL and Microsoft SQL Server.

 

Participants will work through real production scenarios: slow queries, index bloat, blocking/deadlocks, replication lag, storage/IO bottlenecks, and recovery drills. The course emphasizes repeatable troubleshooting playbooks and practical tuning techniques aligned with enterprise operations.

 

Objectives

 

• Establish performance baselines and identify bottlenecks (CPU, memory, IO, locks, network).
• Analyze query execution plans and apply systematic query optimization techniques.
• Design and validate effective indexing strategies (including composite/covering indexes).
• Tune database configuration parameters safely (PostgreSQL + SQL Server best practices).
• Detect and resolve blocking, deadlocks, and concurrency-related slowdowns.
• Implement HA and replication approach appropriate to enterprise requirements.
• Execute backup, restore, and point-in-time recovery (PITR) / disaster recovery drills.
• Apply security best practices: roles/permissions, encryption options, auditing, hardening.
• Troubleshoot production incidents using a structured playbook and evidence-driven approach.
• Create operational runbooks/checklists for monitoring, maintenance, and recovery readiness.

Target Audience

 

• Database Administrators (DBAs)
• Platform/Infrastructure Engineers supporting databases
• DevOps/SRE teams managing database reliability
• Application Developers responsible for performance-sensitive systems
• L2/L3 Support Engineers handling production incidents

 

Prerequisites

• Working knowledge of SQL (SELECT/JOIN/GROUP BY, basic DDL/DML)

• Basic understanding of database concepts (transactions, locks, indexes)

• Familiarity with either PostgreSQL or SQL Server administration basics (recommended)

• Comfortable with command-line and basic OS concepts (Windows/Linux)

 

Course Outline

 

Day 1 — Performance Tuning Foundations + Baseline & Observability

• Performance tuning mindset: measure → hypothesize → change → validate
• Key bottleneck categories: CPU, memory, IO, locks, network, connection pools
• Workload profiling: OLTP vs reporting vs mixed workloads
• Baseline creation: KPIs, throughput, latency, wait events

 

PostgreSQL Focus

• Collecting performance signals (pg_stat views, slow query logging)
• Intro to explain/analyze, buffers, timing interpretation

 

SQL Server Focus

• DMVs for performance diagnostics
• Query Store overview and practical usage patterns

 

Labs

• Build a baseline and identify top wait/lock/query contributors
• Turn on appropriate logging/telemetry without “killing” production performance

 

Day 2 — Query Optimization & Execution Plan Analysis (Both Platforms)

• Reading execution plans: scans vs seeks, joins, cardinality estimation
• Common anti-patterns: non-sargable predicates, implicit conversions, RBAR, OR conditions
• Parameter sensitivity / plan regression concepts

 

PostgreSQL

• EXPLAIN (ANALYZE, BUFFERS), planner behavior, work_mem implications

 

SQL Server

• Actual vs estimated plans, stats usage, parameter sniffing mitigation patterns

 

Labs

• Optimize slow queries using plan analysis
• Fix plan regressions and validate improvements with repeatable tests

 

Day 3 — Indexing Strategies, Statistics & Maintenance

• Index design principles: selectivity, composite order, covering strategies
• When NOT to index; write-heavy vs read-heavy tradeoffs
• Statistics and histogram concepts; stale stats impact
• Maintenance: bloat, fragmentation, vacuum/reindex, index rebuild/reorg

 

PostgreSQL

• Bloat detection, VACUUM/ANALYZE strategy, autovacuum tuning basics

 

SQL Server

• Index fragmentation management, stats update strategies, fill factor concepts

 

Labs

• Design indexes for real query patterns
• Fix performance caused by bloat/fragmentation and validate gains

 

Day 4 — High Availability, Replication, Backup & Recovery (Drill Day)

• HA vs DR vs backups: what each solves
• RPO/RTO design considerations
• Replication models and failure modes; detecting replication lag

 

PostgreSQL

• Streaming replication concepts
• Backup types and PITR concepts (WAL); recovery verification

 

SQL Server

• HA/replication options overview (enterprise patterns)
• Backup chain (full/diff/log), restore strategies, point-in-time restore concepts

 

Labs

• Simulate failure, perform controlled failover (lab scenario)
• Execute restore + recovery drill and validate application functionality

 

Day 5 — Security Best Practices + Real-World Troubleshooting Capstone

• Security hardening checklist
  • Roles/least privilege, separation of duties
  • Connection security, secrets handling, auditing basics
• Common production incidents
  • Lock storms, deadlocks, runaway queries
  • Connection saturation, memory pressure, tempdb/temp spills
  • Disk full, corruption signals (what to do first)

 

Capstone Lab (Scenario-Based)

• “Production outage” simulation:
  • Diagnose symptoms → collect evidence → apply fixes → validate → document root cause
• Create a team runbook:
  • Monitoring checklist, escalation flow, recovery steps, preventative maintenance plan