Skip to Content
View all events

PostgreSQL Bootcamp

Published by O'Reilly Media, Inc.

Beginner content levelBeginner

Learning Postgres from the ground up

Course Outcomes:

  • Perform basic administrative tasks in PostgreSQL
  • Evaluate and optimize query performance
  • Get familiar with common mistakes in SQL
  • Learn when and how to use different index types in PostgreSQL
  • Get comparable with PostgreSQL database and CLI

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.

Day 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 built-in information schema to query for metadata.

Day 2: Working with Indexes in PostgreSQL

On the second day, 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.

What you’ll learn and how you can apply it

Day 1: PostgreSQL Architecture and Common Administrative Tasks

  • Understand PostgreSQL architecture and main memory structures
  • Create table, schemas, constraints and other database objects
  • Understand correlation and how it affect in performance
  • Manage users, roles, and permissions

Day 2: 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 bloat and how it affects performance and cost
  • 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
  • Produce and evaluate simple execution plans
  • 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.

Day 1: PostgreSQL Architecture and Common Administrative Tasks

Intro (10 minutes)

  • The DBA spectrum
  • PostgreSQL and RDMBSs

PostgreSQL architecture fundamentals (30 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 databases; tables, schemas; views; materialized views;
  • Using the search path
  • Q&A
  • Break

Importing and exporting data using COPY (30 minutes)

  • Interactive lab: PostgreSQL Fundamentals: Importing and Exporting Data Using COPY
  • Presentation: getting data in and out of the database using the COPY command
  • Q&A

Maintaining data integrity (40 minutes)

  • Presentation: Column data types; creating and managing constraints (not null, check, unique, foreign key);
  • Q&A

Creating and managing tables (15 minutes)

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

Maintaining primary keys (30 minutes)

  • Creating auto incrementing primary keys
  • Q&A
  • Break

Managing users and permissions (30 minutes)

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

Unique PostgreSQL Features (20 minutes)

  • Special data types (array, json, json);

Day 2: Working with Indexes in PostgreSQL

SQL query optimization (20 minutes)

  • The path of a query in PostgreSQL
  • Producing and reading execution plans

B-tree index (20 minutes)

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

B-tree index features (30 minutes)

  • Presentation: Inclusive indexes; partial indexes; function-based indexes
  • Q&A

Correlation (20 minutes)

  • Presentation: Understanding correlation and how it affect performance
  • Q&A
  • Break

Hash index (50 minutes)

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

Block range index (30 minutes)

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

Index maintenance (30 minutes)

  • Presentation: Routine index maintenance; Common misconceptions;
  • Q&A

Bloat (30 minutes)

  • Presentation: Understanding and reducing bloat in tables and indexes;
  • Q&A

Conclusion (10 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