This course develops the skills needed to build, query, and maintain PostgreSQL databases beyond the fundamentals. Participants learn to write complex SQL queries, design reliable data structures, create reusable database objects, manage transactions, work with semi-structured data, and improve query performance. The five-day duration provides additional scope for database programming, security, routine maintenance, and an integrated business database capstone.
Duration 5 Days – 35 hrs.
Objectives
- Apply appropriate PostgreSQL data types, relationships, and constraints.
- Write complex queries using joins, subqueries, and common table expressions.
- Develop analytical queries using window functions.
- Create and manage views and materialized views.
- Query and manipulate arrays and JSONB data.
- Manage transactions, isolation levels, and concurrent database operations.
- Interpret execution plans and improve queries through appropriate indexing.
- Develop PL/pgSQL functions, procedures, and triggers.
- Configure role privileges and perform routine maintenance and logical backup operations.
- Integrate intermediate PostgreSQL features into a practical database solution.
Target Audience
- Developers who build applications using PostgreSQL.
- Database professionals progressing beyond introductory SQL.
- Data analysts who need complex reporting queries.
- Application support engineers responsible for database operations.
- Technical professionals transitioning from another relational database platform.
Prerequisites
- Basic understanding of relational databases, tables, keys, and relationships.
- Ability to write SQL statements using SELECT, INSERT, UPDATE, and DELETE.
- Familiarity with basic joins, aggregate functions, GROUP BY, and HAVING.
- Experience creating tables and applying primary and foreign keys.
- Basic familiarity with PostgreSQL and either psql or pgAdmin.
Course Outline
Day 1: Data Structures and Complex SQL Queries
Module 1: PostgreSQL Foundations and Database Objects
- Databases, schemas, and object organization
- Working with psql and pgAdmin
- Object naming and schema qualification
- PostgreSQL data types and explicit type conversion
- Identity columns and sequences
- NULL handling and conditional expressions
Module 2: Relational Design and Data Integrity
- Reviewing table relationships and normalization
- Selecting appropriate data types
- Primary key, foreign key, unique, check, and not-null constraints
- Referential actions and cascading operations
- Adding and modifying constraints
- Identifying and resolving common data integrity issues
Module 3: Complex Joins, Subqueries, and Set Operations
- Inner, outer, self, and cross joins
- Managing duplicate rows in joined results
- Correlated and non-correlated subqueries
- EXISTS and NOT EXISTS
- UNION, UNION ALL, INTERSECT, and EXCEPT
- Combining query techniques for business reporting
Day 2: Analytical SQL and Reusable Data Access
Module 4: Common Table Expressions and Recursive Queries
- Structuring queries using WITH
- Combining multiple common table expressions
- Data-modifying statements in WITH
- Recursive query structure and termination
- Querying hierarchical data
- Readability and performance considerations
Module 5: Window Functions and Analytical Queries
- Window functions compared with aggregate functions
- PARTITION BY and window ordering
- ROW_NUMBER, RANK, and DENSE_RANK
- LAG and LEAD
- Running totals and moving averages
- Window frames and their effect on results
- Combining window functions with common table expressions
Module 6: Views and Materialized Views
- Creating, replacing, and removing views
- Using views to simplify reusable queries
- Updatable views and their limitations
- WITH CHECK OPTION
- Creating and refreshing materialized views
- Indexing materialized views
- Choosing between views and materialized views
Day 3: Data Manipulation and Transaction Management
Module 7: Intermediate Data Manipulation and Data Transfer
- INSERT, UPDATE, and DELETE with RETURNING
- Handling conflicts with INSERT … ON CONFLICT
- Updating and deleting data using related tables
- Bulk data loading considerations
- Importing and exporting data with COPY and \copy
- Handling delimiters, NULL values, and file formats
Module 8: Arrays and JSONB
- Creating, accessing, and querying arrays
- Array operators and functions
- Expanding arrays using UNNEST
- JSON and JSONB fundamentals
- Extracting and filtering JSONB values
- Updating JSONB documents
- Choosing between relational columns, arrays, and JSONB
Module 9: Transactions and Concurrency
- Transaction boundaries and ACID properties
- BEGIN, COMMIT, ROLLBACK, and savepoints
- Multiversion concurrency control
- Transaction isolation levels
- Row locking and SELECT … FOR UPDATE
- Blocking and deadlocks
- Transaction retry considerations
- Keeping transactions efficient and consistent
Day 4: Query Performance and Database Programming
Module 10: Execution Plans and Query Optimization
- Reading EXPLAIN and EXPLAIN ANALYZE output
- Estimated rows, actual rows, and execution costs
- Sequential scans, index scans, and bitmap scans
- Join strategies and sorting operations
- Identifying inefficient filters and joins
- Planner statistics and the role of ANALYZE
- Comparing query plans before and after changes
Module 11: Index Design and Management
- B-tree indexes and common use cases
- Overview of GIN, GiST, and BRIN indexes
- Multicolumn indexes and column order
- Expression and partial indexes
- Covering indexes and index-only scans
- Indexing JSONB data
- Index maintenance overhead and trade-offs
- Identifying unused and overlapping indexes
Module 12: PL/pgSQL Functions, Procedures, and Triggers
- Choosing between functions and procedures
- Parameters, variables, and return values
- Conditional logic and loops
- Returning query results
- Basic exception handling
- Trigger functions and trigger events
- Row-level and statement-level triggers
- Appropriate uses and limitations of database programming
Day 5: Security, Maintenance, and Integrated Capstone
Module 13: Roles and Access Control
- Login roles and group roles
- Object ownership and role membership
- Database, schema, table, and sequence privileges
- GRANT and REVOKE
- Default privileges
- Applying least-privilege access
- Managing access for application and reporting users
Module 14: Routine Maintenance and Logical Backup
- VACUUM, autovacuum, and ANALYZE fundamentals
- Dead tuples and table maintenance
- Inspecting sessions using pg_stat_activity
- Reviewing table and index statistics
- Identifying long-running queries and blocked sessions
- Logical backups using pg_dump
- Restoring backups using pg_restore and psql
- Backup scope, dependencies, and restore verification
Module 15: Capstone — Sales and Inventory Database
- Extend a supplied sales and inventory schema with appropriate constraints.
- Develop reporting queries using common table expressions and window functions.
- Create a reporting view or materialized view.
- Store and query flexible product attributes using JSONB.
- Implement a transactional order operation with stock validation.
- Create a function, procedure, or trigger for a defined business requirement.
- Examine query execution plans and apply suitable indexes.
- Configure privileges for application and reporting roles.
- Produce a logical backup and verify restoration in a separate database.

