Course Outline

? Day 1: Advanced Relational Modeling and Data Types

Session 1: Advanced Table Design

• Normalization vs denormalization trade-offs
• Using composite types and domain types
• Constraints beyond basics: CHECK, EXCLUDE, and custom constraints

Session 2: PostgreSQL Advanced Data Types

• Arrays and multidimensional arrays
• JSON vs JSONB: use cases and performance
• Hstore for key-value storage

Session 3: Table Inheritance and Partitioning

• Table inheritance concepts and use cases
• Declarative partitioning: range, list, hash
• Partition pruning and query performance

Lab Activities:

• Design complex tables with composite types and constraints
• Store and query JSONB data efficiently
• Create partitioned tables and test data distribution

? Day 2: Complex Relationships and Indexing Strategies

Session 1: Modeling Complex Relationships

• Advanced foreign key configurations and cascading rules
• Many-to-many relationships with join tables
• Recursive CTEs for hierarchical data modeling

Session 2: Indexing for Advanced Data Types

• GiST, GIN, and BRIN indexes explained
• Indexing JSONB and array columns
• Partial and expression indexes

Session 3: Performance Optimization Techniques

• Analyzing query plans for complex models
• Using materialized views and indexed views
• Query tuning with statistics and planner hints

Lab Activities:

• Implement recursive queries for tree/hierarchy data
• Create and benchmark different index types on JSONB columns
• Tune queries using EXPLAIN and ANALYZE

? Day 3: Advanced Modeling Patterns and Real-World Applications

Session 1: Event Sourcing and Temporal Data Modeling

• Designing audit trails and change history tables
• Using temporal data types and range types
• Time-series data modeling techniques

Session 2: Full-Text Search and Geographic Data

• PostgreSQL full-text search basics and advanced queries
• Integration with PostGIS for spatial data modeling
• Indexing and querying geospatial data

Session 3: Data Integrity and Security

• Row-level security (RLS) policies
• JSON schema validation and triggers
• Backup and disaster recovery best practices

Lab Activities:

• Design a temporal data model with audit history
• Implement full-text search on JSONB documents
• Configure row-level security on sensitive tables