All courses
Hamburger Icon

Intermediate PostgreSQL

Course Summary

This course is for those with some experience in working with databases and SQL. Explore advanced data types, functions, and querying techniques, enabling you to work with complex data effectively. Additionally, you will dive into indexing, query optimization, and learn how to monitor database activity to enhance performance. By the end of this course, you will have a deeper understanding of PostgreSQL's capabilities and be proficient in leveraging its features for more sophisticated applications.

Purpose
Gain a deeper understanding of PostgreSQL for working with compex data effectively.
Audience
This course is intended for those with some experience working with databases and SQL using PostgreSQL.
Role
Business Analysts | Developers | Anyone in a Data Role
Skill level
Intermediate
Style
Lecture | Hands-on Activities
Duration
3 days
Related technologies
SQL

 

Productivity objectives
  • Identify advanced data types, functions, and querying techniques
  • Apply PostgreSQL features for more sophisticated applications.

What you'll learn:

In this course, you'll learn:
  • Review of basic SQL and database concepts
    • Quick overview of PostgreSQL essentials
  • Understanding indexes and their types in PostgreSQL
    • B-Tree, Hash, GiST, GIN, and BRIN indexes
    • When and where to use each index type for optimal performance
  • Creating and managing indexes for performance
    • Using CREATE INDEX and DROP INDEX commands
    • Analyzing and interpreting index statistics
  • Analyzing query plans and execution
    • Using EXPLAIN to understand query execution plans
    • Identifying performance bottlenecks in query plans
  • Monitoring DB Activity and Query Performance
    • Using pg_stat_statements to track query performance
    • Analyzing query performance using pg_stat_activity and pg_stat_progress_* views
  • Working with advanced data types (JSON, Arrays, HSTORE, etc.)
    • Storing and retrieving JSON data in PostgreSQL
    • Utilizing arrays for efficient data handling
    • Working with the HSTORE data type for key-value pairs
  • Utilizing built-in functions and creating custom functions
    • Overview of commonly used functions (string functions, aggregate functions, etc.)
    • Creating user-defined functions with PL/pgSQL
  • Introduction to triggers and their applications
    • Creating and managing triggers
    • Implementing triggers for automatic data updates and validations
  • Handling errors with exception blocks
    • Using EXCEPTION blocks for error handling in functions and procedures
    • Understanding error codes and messages in PostgreSQL
  • Exploring full-text search capabilities in PostgreSQL
    • Configuring and performing full-text searches
    • Using advanced search features for relevance ranking
  • Understanding PostgreSQL Architecture
    • Overview of PostgreSQL's internal components (Shared buffers, WAL, etc.)
    • How PostgreSQL reads and writes data efficiently
  • Performance Tuning Techniques
    • Optimizing PostgreSQL configuration for specific workloads
    • Fine-tuning memory settings and other performance-related parameters
  • Monitoring and Logging
    • Configuring logging for monitoring and troubleshooting
    • Using log data for performance analysis
  • Understanding VACUUM and ANALYZE for Maintenance
    • Managing dead tuples and reclaiming space with VACUUM
    • Updating statistics with ANALYZE for query optimization
  • Exploring PostgreSQL Extensions for Additional Functionality
    • Introduction to popular extensions (PostGIS for spatial data, pg_stat_statements for query analysis, etc.)
real world content

Real-World Content

Project-focused demos and labs using your tool stack and environment, not some canned "training room" lab.
expert practitioners

Expert Practitioners

Industry experts that bring their battle scars into the classroom.
experiential learning

Experiential Learning

More coding than lecture, coupled with architectural and design discussions.
fully customized

Tailored Outlines

Once-size-fits-all doesn't apply to training teams. That's where we come in!

Dive in and learn more

When transforming your workforce, it’s important to have expert advice and tailored solutions. We can help. Tell us your unique needs and we'll explore ways to address them.

Let's chat

By filling out this form and clicking submit, you acknowledge our privacy policy.