PostgreSQL 17: Core Concepts and Advanced SQL Techniques

Overview

Google, IBM & Meta Certificates — All 10,000+ Courses at 40% Off

One annual plan covers every course and certificate on Coursera. 40% off for a limited time.

This course focuses on mastering PostgreSQL 17's core features and advanced SQL techniques, which are vital for database professionals. You'll explore new enhancements in PostgreSQL 17, along with an in-depth understanding of transaction management, indexing, and complex SQL queries. By the end of the course, you'll be proficient in using PostgreSQL 17 to improve database performance, security, and efficiency, and will be able to apply advanced SQL techniques to real-world database challenges. What sets this course apart is its blend of foundational PostgreSQL concepts with practical, hands-on exercises that help bridge the gap between theory and real-world database management. This course is ideal for database administrators, developers, and anyone seeking to deepen their knowledge of PostgreSQL and SQL, with a foundational understanding of databases required. This course is part one of a three-course Specialization designed to provide a comprehensive learning pathway in this subject area. While it delivers standalone value and practical skills, learners seeking a more integrated and in-depth progression may benefit from completing the full Specialization.

Syllabus

What is New in PostgreSQL 17

This module explores the latest enhancements introduced in PostgreSQL 17, including new system views for performance monitoring, advanced partition management capabilities, and expanded JSON functionalities. Learners will gain practical insights into how these features can improve database administration and development workflows.

Understanding Transactions and Locking

This module explores the critical concepts of transactions and locking in PostgreSQL, including how transactional DDLs work, the implications of different isolation levels, and strategies for managing concurrency. Learners will also examine alternative solutions for generating unique values and understand the impact of operations like VACUUM FULL on database performance.

Making Use of Indexes

This module delves into advanced indexing strategies in PostgreSQL, exploring how different index types, operator classes, and clustering techniques can dramatically impact query performance and storage efficiency. Learners will discover when and how to use various indexes, optimize queries, and extend PostgreSQL's indexing capabilities for specialized use cases such as full-text and fuzzy searches.

Handling Advanced SQL

This module delves into advanced SQL techniques, including performance optimization with indexing, efficient data aggregation using grouping sets and window functions, and handling complex data types like JSON and range types in PostgreSQL. Learners will also explore recursion, parallel query support, and methods for managing duplicates and partitioning data. By the end, you'll be equipped to write more powerful and efficient SQL queries for sophisticated data analysis tasks.