Skip to Content
View all events

PostgreSQL Bootcamp in 3 Weeks

Published by O'Reilly Media, Inc.

Beginner content levelBeginner

Learning Postgres from the ground up

This live event utilizes interactive environments

In this course you’ll:

  • Perform basic administrative tasks in PostgreSQL
  • Evaluate and optimize query performance
  • Learn when and how to use different index types in PostgreSQL

Course description

Despite the seemingly unlimited number of databases out there, PostgreSQL continues to be one of the most popular databases for developers and data professionals. From hobbyists to small startups, open source projects and Fortune 500 companies, everybody is using PostgreSQL.

Join expert Haki Benita to learn PostgreSQL from the ground up, understanding both the fundamentals and the advanced features that distinguish PostgreSQL as one of the most versatile databases available. You’ll perform real-life database tasks using online interactive labs on an actual PostgreSQL database, thereby gaining actionable knowledge that you can implement in your own work.

Week 1: PostgreSQL Architecture and Common Administrative Tasks

During this session, you’ll review PostgreSQL architecture and learn how to perform common administrative tasks such as creating databases, schemas, tables, views, and constraints. You’ll also learn how to manage permissions and use the build information schema to query for metadata.

Week 2: Common SQL Mistakes and the PostgreSQL Query Optimizer

This week, you’ll explore common types of reports and experience firsthand some common mistakes in SQL. You’ll learn how missing values are represented and handled by different SQL functions, how timestamps and time zones are handled, how the PostgreSQL query optimizer works, and how the database finds the best execution plan. You’ll produce and examine execution plans and learn how to spot potential performance problems.

Week 3: Working with Indexes in PostgreSQL

In the final week, you’ll experiment with different types of indexes available in PostgreSQL and explore variations of the popular B-tree index. You’ll review how a hash index and a block range index work and discuss common SQL patterns that can prevent the database from optimizing a query and lead to degraded performance.

NOTE: With today’s registration, you’ll be signed up for all three sessions. Although you can attend any of the sessions individually, we recommend participating in all three weeks.

Hands-on learning with interactive labs

Most exercises are provided as O'Reilly interactive labs—complete development environments that are preconfigured with everything you need. There's nothing to install or configure; just click a link and get started!

Interactive labs are sandboxed, so you can explore, experiment, and tinker without fear of breaking anything. And you can revisit them anytime after class ends to practice and refine your skills.

What you’ll learn and how you can apply it

Week 1: PostgreSQL Architecture and Common Administrative Tasks

  • Understand PostgreSQL architecture and main memory structures
  • Create table, schemas, constraints and other database objects
  • Manage users, roles, and permissions

Week 2: Common SQL Mistakes and the PostgreSQL Query Optimizer

  • Write correct SQL to produce accurate information
  • Understand how statistics are used by the query optimizer
  • Produce and evaluate simple execution plans

Week 3: Working with Indexes in PostgreSQL

  • Understand how the B-tree index works and when to use partial, inclusive, and function based B-tree indexes
  • Understand how a hash index works and when it should be used
  • Understand how a block range index works and when it should be used
  • Write SQL that best utilizes indexes

This live event is for you because...

  • You are a developer, DataOps engineer, DBA, or sysadmin.
  • You work with databases.
  • You want to specialize in PostgreSQL.

Prerequisites

  • Basic knowledge of SQL

Recommended preparation:

Recommended follow-up:

Schedule

The time frames are only estimates and may vary according to how the class is progressing.

Week 1: PostgreSQL Architecture and Common Administrative Tasks

PostgreSQL architecture fundamentals (25 minutes)

  • Presentation: About the PostgreSQL project; terminology (cluster, database); basic architecture (client -> connection -> server); memory structures (cache, work mem); concurrency control (MVCC)

Common administrative tasks (50 minutes)

  • Presentation: Creating tables, schemas and constraints; creating views and materialized views; using the search path
  • Q&A

Importing and exporting data using COPY (25 minutes)

  • Interactive lab: PostgreSQL Fundamentals: Importing and Exporting Data Using COPY
  • Q&A
  • Break

Maintaining data integrity (50 minutes)

  • Presentation: Column data types; special data types (jsonb, uuid); creating and managing constraints (not null, check, unique, foreign key); creating auto incrementing primary keys
  • Q&A
  • Break

Creating and managing tables (15 minutes)

  • Interactive lab: PostgreSQL Fundamentals: Creating and Managing Tables
  • Q&A

Managing users and permissions (30 minutes)

  • Presentation: Managing users and permissions; analyzing and vacuuming tables; using the information schema
  • Q&A

Week 2: Common SQL Mistakes and the PostgreSQL Query Optimizer

Writing correct SQL (60 minutes)

  • Presentation: Common mistakes with arithmetics in SQL; working with missing data (nulls); how to correctly compare null values
  • Hands-on exercises: Find the average discount rate on a product; find the average discount rate by product; find how many unique users purchased a product
  • Break

Working with timestamps in SQL (60 minutes)

  • Presentation: How timestamps are represented in the database; time zone and daylight savings; how to manipulate timestamps in PostgreSQL—functions and the interval type; common mistakes in SQL using timestamps; when to use half open ranges to avoid overlap
  • Hands-on exercises: Find total sales at month; find your timezone name and current local time; find the busiest hour of the day in all branches; find the total sales in a given month using a specific timezone
  • Break

SQL query optimization (60 minutes)

  • Presentation: Order of execution; the path of a query in PostgreSQL (parse -> rewrite -> plan -> execute); producing and reading execution plans; how the database is using statistics to produce execution plans
  • Q&A

Week 3: Working with Indexes in PostgreSQL

B-tree index (30 minutes)

  • Presentation: How B-tree works
  • Interactive lab: PostgreSQL Fundamentals: B-Tree Index Features
  • Q&A

B-tree index features (30 minutes)

  • Presentation: Inclusive indexes; partial indexes; function-based indexes
  • Break

Hash index (25 minutes)

  • Presentation: Hash index; when to use a hash index versus B-tree
  • Interactive lab: PostgreSQL Fundamentals: Hash Index

Block range index (35 minutes)

  • Presentation: Block range index; when to use a block range index versus B-tree
  • Interactive lab: PostgreSQL Fundamentals: Block Range Index
  • Break

SQL tips and tricks (45 minutes)

  • Presentation: Lesser known but useful features of PostgreSQL; productivity tips for writing SQL

Conclusion (15 minutes)

  • Presentation: Recap
  • Q&A

Your Instructor

  • Haki Benita

    Haki Benita leads the development of a large ticketing and payment system at Pcentra, working with large PostgreSQL databases and other technologies such as Python, Django, and TypeScript. A full stack developer and a team leader with over 15 years of experience, he’s been a DBA for data warehouse systems in the pharma and logistics industries; served as a lead DBA in charge of designing database schemas and ETL processes and integrating with various BI tools; and led a team of 10 developers DBAs. Haki maintains a popular blog, where he shares the technical aspects of his work, with a focus on optimizing large databases and the challenges of scaling a web app to millions of users.

    Xlinksearch

Skill covered

PostgreSQL