Course Outline
1. Introduction to PostgreSQL from a Consultant's Perspective
- PostgreSQL positioning (comparative view with Oracle, SQL Server, MySQL)
-
"Why is PostgreSQL different?"
- Open-source yet exhibiting enterprise-grade behavior
- Approach to standards (SQL compliance)
- Usage scenarios where PostgreSQL excels and struggles
2. PostgreSQL Architecture (Conceptual Mapping)
- PostgreSQL process model (multi-process vs. multi-thread)
-
Memory architecture
- shared_buffers
- work_mem
- maintenance_work_mem
- WAL (Write-Ahead Logging) logic
-
MVCC approach (PostgreSQL's most critical differentiator)
- Read consistency
- Origin of the need for VACUUM
3. PostgreSQL Data Types and Differentiating Structures
-
PostgreSQL data types (advanced types)
- JSON / JSONB
- ARRAY
- UUID
- ENUM
-
Critical differences for SQL consultants:
- Difference between JSON and JSONB
- Indexability
- Impact of type selection on performance
4. Indexing Logic: "Same SQL, Different Results"
-
PostgreSQL index types:
- B-Tree
- Hash
- GIN
- GiST
- BRIN
- Which index for which scenario?
- Covering index approach (INCLUDE)
- Partial index concept
5. Query Planner & Execution Plan Reading
- PostgreSQL query planner logic
- EXPLAIN vs EXPLAIN ANALYZE
- Cost-based optimization
- Impact of incorrect statistics
6. Performance Optimization in PostgreSQL
-
VACUUM & Autovacuum
- Why is it necessary?
- Consequences of incorrect configuration
- ANALYZE and statistics
- Concept of dead tuples
- PostgreSQL behavior in high-transaction systems
7. Transaction & Concurrency Model
- Isolation levels (PostgreSQL behavior)
- Lock mechanisms
- Blocking & deadlock scenarios
- Long-running transaction problems
8. Stored Procedure, Function, and Trigger Logic
- PostgreSQL function structure
- PL/pgSQL fundamental differences
- Difference between stored procedure and function
- Trigger usage scenarios
9. Security & Authorization in PostgreSQL
- Role vs. user difference
- Schema-level authorization
- Row Level Security (RLS)
- Multi-tenant scenarios
10. Operational Issues in PostgreSQL (Consultant Level)
- Backup & restore (logical vs. physical)
- pg_dump / pg_restore
- Replication overview
- Introduction to high-availability concepts
11. SQL to PostgreSQL Migration Mindset
- Not just syntax, but an approach difference <>
- Most common mistakes
- Anti-patterns
- "Leave this SQL habit behind" for PostgreSQL
12. Closing – PostgreSQL from a Consultant's Perspective
- When is PostgreSQL the right choice?
- How to present PostgreSQL to clients?
- PostgreSQL skill set roadmap for SQL consultants
- Q&A and case study discussion
Requirements
Requirements
- Fundamental understanding of relational databases and SQL query logic
- Experience working with SQL-based databases (e.g., SQL Server, Oracle, MySQL, etc.)
- Experience writing SQL queries and performing data analysis
Audience
- Professionals actively engaged in SQL database consulting
- Database experts with SQL knowledge aiming to transition to PostgreSQL
- Technical teams seeking to effectively utilize PostgreSQL leveraging their existing SQL expertise
Testimonials (2)
The provided examples and labs
Christophe OSTER - EU Lisa
Course - PostgreSQL Advanced DBA
1. A very well-structured training program 2. The warm atmosphere the trainer created, along with his outstanding personal professionalism 3. That the trainer explained everything as if he were talking to a complete beginner, without slipping into any technical jargon.
