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
