Foundations of PostgreSQL:

Introduction and Installing PostgreSQL

• Understanding PostgreSQL's history and advantages
• Installation methods: Docker/Direct Install
• Connecting to PostgreSQL: Dbeaver/PgAdmin

Entity-Relationship (ER) Design

• Basics of ER modeling and database design
• Using PgAdmin ERD Tool

SQL Fundamentals

• Data Definition Language (DDL): Keys, attribute types, constraints
• Data Manipulation Language (DML): INSERT, UPDATE, DELETE, transactions
• SELECT Query Fundamentals: Structure, operators, conditions, sorting, grouping, and aggregations

Advanced SQL Techniques

• Joins and CTE: Complex joins and Common Table Expressions
• Subqueries and CASE WHEN: Advanced query construction

Mastering SQL in PostgreSQL:

Window Functions and Advanced Queries

• Window Functions: Analytic, aggregate, and ranking functions
• Practical Queries (Exercise): Real-world query scenarios

Working With Data Types

• Working With JSON Data: Storing and querying JSON data
• Recursive Queries for Tree Structures/Arrays: Handling hierarchical data
• Popular SQL/PG Functions: String manipulation, date/time functions, statistics

Database Optimization Techniques

• Analyzing query plans with EXPLAIN and monitoring queries with pg_stat_statements
• Index Types and Usage Guide: Understanding and optimizing index usage
• Views and Materialized Views: Creating efficient views and optimizing with materialized views

Backup, Recovery, and Security

• Backup and Recovery Strategies: Point-in-time recovery, restore procedures
• Log Management and Replication: Managing logs, setting up replication
• User Access Management: Creating and managing users, roles, and privileges
• Security and Encryption: Implementing security measures and data encryption

Specialized PostgreSQL Topics:

 Advanced Programming in PostgreSQL

• Writing PL/PgSQL Code: IF, FOR, WHILE loops, and conditional constructs
• Functions and Stored Procedures: Using built-in and creating user-defined functions
• Triggers: Implementing triggers for data auditing

Database Internals and Advanced Concepts

• Lateral Join and Flattening: Understanding lateral joins and data flattening
• Internal PostgreSQL Tables: Exploring internal system tables
• Foreign Data Wrappers and Cross-Database Queries: Integrating external data sources
• Postgres Extensions and PostGIS: Overview, installation, and usage of useful extensions

Performance Optimization and Scalability

• Distributed PG Using Citus: Scaling horizontally with Citus
• High Availability: Ensuring continuous availability and Consistency levels
• Query Parallelism: Configuring and optimizing parallel execution
• Managing Large Databases: Partitioning, tablespaces, and disk space management
• Modern Online PG Tools: Exploring tools like SupaBase, CockroachDB, TimescaleDB, Grafana
• Dashboards, CDC Tools and so on