Intermediate PostgreSQL: Performance, Optimization, and Advanced Features
Build High-Performance and Scalable Applications with PostgreSQL
Duration
Level
Intermediate Level
Design and Tailor this course
As per your team needs
Overview
This course is designed for professionals with prior experience in SQL and databases who want to deepen their knowledge of PostgreSQL. It covers advanced querying techniques, indexing strategies, performance optimization, and database monitoring. Participants will gain hands-on experience in working with complex data types, improving query performance, and leveraging PostgreSQL features to build scalable and efficient applications.
Audience
- Database developers
- Data analysts
- Backend developers
- Data engineers
Prerequisites
- Basic knowledge of SQL
- Familiarity with relational databases
- Prior experience with PostgreSQL (recommended)
Curriculum
- Review of SQL concepts (joins, aggregations, subqueries)
- PostgreSQL architecture overview
- Data types and constraints
- Schema design and normalization basics
- Role-based access control (users, roles, privileges)
- Working with psql and basic administration commands
- Types of indexes: B-Tree, Hash, GiST, GIN, BRIN
- Index creation strategies and best practices
- Composite and partial indexes
- Query execution plans using EXPLAIN and EXPLAIN ANALYZE
- Identifying slow queries and optimization techniques
- Index maintenance and reindexing
- Monitoring database activity and sessions
- Using pg_stat_activity and pg_stat_database
- Query performance tracking and analysis
- Lock monitoring and deadlock detection
- Identifying long-running queries
- Performance benchmarking basics
- JSON vs JSONB: storage and performance
- Querying and indexing JSON data
- Working with arrays and array functions
- HSTORE usage and comparison with JSON
- Custom data types and domains
- Use cases and real-world implementation
- Built-in PostgreSQL functions overview
- Creating user-defined functions (PL/pgSQL)
- Function parameters and return types
- Exception handling and debugging
- Writing stored procedures (CALL statements)
- Performance considerations for functions
- Introduction to triggers and trigger types
- BEFORE vs AFTER triggers
- Row-level vs statement-level triggers
- Creating and managing triggers
- Event triggers and use cases
- Automating business logic with triggers
- Introduction to full-text search concepts
- Text search configurations and dictionaries
- Creating tsvector and tsquery
- Indexing full-text search data
- Ranking and relevance tuning
- Implementing advanced search features
- Core components: WAL, shared buffers, background processes
- Data storage and page structure
- MVCC (Multi-Version Concurrency Control)
- Transaction lifecycle and isolation levels
- Checkpointing and crash recovery
- Data read/write mechanisms
- PostgreSQL configuration parameters (postgresql.conf)
- Memory tuning (work_mem, shared_buffers)
- CPU and I/O optimization strategies
- Query tuning techniques
- Connection pooling and scaling strategies
- Workload-specific tuning (OLTP vs OLAP)
- Logging configuration and log levels
- Understanding PostgreSQL log files
- Tracking slow queries using logs
- Using auto_explain and extensions
- Log analysis tools and techniques
- Auditing and compliance logging
- VACUUM, VACUUM FULL, and ANALYZE
- Autovacuum tuning and monitoring
- Index bloat detection and management
- Backup and restore strategies (pg_dump, pg_restore)
- Partitioning and data archiving
- Routine maintenance best practices
- Overview of PostgreSQL extensions
- Installing and managing extensions
- Popular extensions (pg_stat_statements, PostGIS)
- Query performance analysis tools
- Logical replication and data streaming
- Custom extensions and use cases
Duration
Level
Intermediate Level
Design and Tailor this course
As per your team needs
