PostgreSQL Bootcamp
Published by O'Reilly Media, Inc.
Learning Postgres from the ground up
In this course you’ll:
- Perform basic administrative tasks in PostgreSQL
- Learn when and how to use different index types in PostgreSQL
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
- 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 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:
- Read chapters 1–3, 8, and 11 in Practical SQL (book)
- Get familiar with the DB Fiddleplatform (optional if yo uwould liketo participate in the hands-on portion as it is used for some exercises)
Recommended follow-up:
- Read chapters 6, 7, 12, and 15 in Practical SQL (book)
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
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
Day 2: Working with Indexes in PostgreSQL
SQL query optimization (10 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
B-tree index features (20 minutes)
- Presentation: Inclusive indexes; partial indexes; function-based indexes
- Break
Hash index (50 minutes)
- Presentation: Hash index; when to use a hash index versus B-tree
- Interactive lab: PostgreSQL Fundamentals: Hash Index
Block range index (50 minutes)
- Presentation: Block range index; when to use a block range index versus B-tree
- Interactive lab: PostgreSQL Fundamentals: Block Range Index
- Break
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.