Thank you for sending your enquiry! One of our team members will contact you shortly.
Thank you for sending your booking! One of our team members will contact you shortly.
Course Outline
1. Introduction to PostgreSQL from a Consultant's Perspective
- Positioning of PostgreSQL (comparative look at Oracle / SQL Server / MySQL)
-
"Why is PostgreSQL different?"
- Open-source but enterprise-grade behavior
- Standard compliance (SQL standards)
- Use cases where PostgreSQL is strong and weak
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 critical difference)
- Read consistency
- The root cause of the vacuum need
3. PostgreSQL Data Types and Distinguishing Features
-
PostgreSQL data types (advanced types)
- JSON / JSONB
- ARRAY
- UUID
- ENUM
-
Critical differences for SQL consultants:
- JSON vs JSONB difference
- Indexability
- The 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
- The impact of incorrect statistics
6. Performance Optimization in PostgreSQL
-
Vacuum & Autovacuum
- Why is it necessary?
- Incorrect configuration results
- Analyze and statistics
- Dead tuple concept
- PostgreSQL behavior in high-transaction 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 basic differences
- Difference between stored procedure and function
- Trigger usage scenarios
9. Security & Authorization in PostgreSQL
- Role vs user difference
- Schema-based authorization
- Row Level Security (RLS)
- Multi-tenant scenarios
10. Operational Topics 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 difference
- Common mistakes
- Anti-patterns
- "Leave this SQL habit behind in PostgreSQL"
12. Closing – PostgreSQL from a Consultant's Perspective
- When is PostgreSQL the right choice?
- How to explain PostgreSQL to a client?
- Skill set roadmap for SQL consultants transitioning to PostgreSQL
- Q&A and case discussion
Requirements
Prerequisites
- Basic understanding of relational databases and SQL query logic
- Experience working with SQL-based databases (e.g., SQL Server, Oracle, MySQL)
- Experience writing SQL queries and performing data analysis
Audience
- Professionals actively working with SQL databases
- Database experts looking to transition to PostgreSQL who have SQL knowledge
- Technical teams wanting to use PostgreSQL effectively alongside their existing SQL knowledge
21 Hours
Testimonials (5)
The provided examples and labs
Christophe OSTER - EU Lisa
Course - PostgreSQL Advanced DBA
Amount (a lot) of knowledge provided. Cheerful aprroach to the subject.
Oskar Debowski - Asseco Poland S.A
Course - PostgreSQL Administration, Optimization and Replication
advance topics hands on + discussion like timescaleDB and hypertable , trainer's knowledge on the subject :)
Shivam - Paessler LLC
Course - PostgreSQL Fundamentals
By the end of the 1st day, one attendee suggested more interaction in practices and the trainers took this in for the second day.
Daniela - Unit4
Course - PostgreSQL 16 for Developers and Administrators
A factual and practical approach to the subject. A nice atmosphere, conducive to asking questions.
