Course Outline
1. Introduction to PostgreSQL from a Consultant’s Perspective
- PostgreSQL positioning (comparative view against Oracle, SQL Server, MySQL)
- “Why is PostgreSQL different?”
- Open-source yet enterprise-grade behavior
- Approach to standards (SQL compliance)
- Use cases where PostgreSQL excels and its limitations
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) concept
- MVCC approach (PostgreSQL’s most critical differentiator)
- Read consistency
- Origin of the need for Vacuum
3. PostgreSQL Data Types and Structure-Defining Features
- PostgreSQL data types (advanced types)
- JSON / JSONB
- ARRAY
- UUID
- ENUM
- Critical differentiators for SQL consultants:
- Difference between JSON and JSONB
- Indexing capabilities
- Impact of data type selection on performance
4. Indexing Logic: “Same SQL, Different Outcome”
- 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 misconfiguration
- Analyze and statistics
- Dead tuple concept
- PostgreSQL behavior in transaction-heavy systems
7. Transaction & Concurrency Model
- Isolation levels (PostgreSQL behavior)
- Lock mechanisms
- Blocking & deadlock scenarios
- Long-running transaction issues
8. Stored Procedure, Function, and Trigger Logic
- PostgreSQL function structure
- PL/pgSQL key differences
- Difference between stored procedures and functions
- Use cases for triggers
9. Security & Authorization in PostgreSQL
- Role vs user difference
- Schema-based authorization
- Row Level Security (RLS)
- Multi-tenant scenarios
10. Operational Considerations in PostgreSQL (Consultant Level)
- Backup & restore (logical vs physical)
- pg_dump / pg_restore
- Replication overview
- Introduction to high availability concepts
11. SQL → PostgreSQL Migration Mindset
- Not just syntax, but <> approach differences
- Most common mistakes
- Anti-patterns
- “Leave this SQL habit behind when moving to PostgreSQL”
12. Closing – PostgreSQL from a Consultant’s Perspective
- When is PostgreSQL the right choice?
- How to explain PostgreSQL to clients?
- PostgreSQL skill set roadmap for SQL consultants
- Q&A and case discussions
Requirements
Requirements
- Fundamental understanding of relational databases and SQL querying concepts
- 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 specialists with SQL knowledge aiming to transition to PostgreSQL
- Technical teams looking to effectively utilize PostgreSQL leveraging their existing SQL expertise
Testimonials (2)
Tuning strategies.
Jeffrey Zieg - Matrix Consulting
Course - PostgreSQL Performance Tuning
Logging behaviour when the instance is under stress, and the hierarchy/nomenclature of instances, databases, files, etc.
