Skip to content
  • Sign In
  • Try Now
View all events
PostgreSQL

PostgreSQL Fundamentals

Published by O'Reilly Media, Inc.

Beginner content levelBeginner

Level up your data work in PostgreSQL

This live event utilizes interactive environments

PostgreSQL has been one of the most widely used relational databases over the last decade and is still popular today. From hobbyists to small startups, open source projects to Fortune 500 companies, everybody is using PostgreSQL.

Join expert Haki Benita to explore the basic and unique features that make PostgreSQL so versatile. You’ll examine PostgreSQL architecture; learn how to perform common administrative tasks, including creating users, tables, constraints, and indexes; and try your hand at evaluating and executing queries. You’ll also optimize SQL queries using different types of indexes and optimize database objects for storage and speed.

Hands-on learning with interactive scenarios

All exercises and labs are provided as O'Reilly interactive scenarios—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 scenarios 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

By the end of this live online course, you’ll understand:

  • Basic PostgreSQL architecture
  • How to use PostgreSQL to evaluate and execute queries
  • How different types of indexes work
  • How to avoid common mistakes

And you’ll be able to:

  • Perform common administrative tasks
  • Execute a query and identify potential optimizations
  • Optimize SQL queries using different types of indexes
  • Optimize database objects for storage and speed

This live event is for you because...

  • You’re a developer, DataOps engineer, or sysadmin.
  • You work with databases and want to learn more about PostgreSQL.
  • You want to specialize in PostgreSQL.

Prerequisites

  • Familiarity with SQL for manipulating and selecting data

Recommended preparation:

Recommended follow-up:

Schedule

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

PostgreSQL architecture fundamentals (30 minutes)

  • Presentation: Introduction; terminology (cluster, database); basic architecture (client -> connection -> server); memory structures (cache, work_mem)
  • Q&A

Anatomy of an SQL query (10 minutes)

  • Presentation: Order of execution
  • Group discussion: Identify simple issues in queries based on order of execution
  • Q&A

Common administrative tasks (30 minutes)

  • Presentation: Creating tables and constraints; using the information schema; managing users and permissions; importing data into PostgreSQL
  • Interactive scenarios: Create Tables for a URL Shortener System; Import Data into PostgreSQL Using COPY from a CSV File
  • Q&A
  • Break

Basic indexes (30 minutes)

  • Presentation: B-tree; unique index; composite, inclusive, partial, and function-based indexes
  • Interactive scenarios: Create an Index to Speed Up Lookup of a Full URL by Key; Create Index to Speed Up Lookup for Unused Short URLs; Create Index to Speed Up Lookup of Full URLs by Domain
  • Q&A

Special index types (30 minutes)

  • Presentation: Hash index; block range index (BRIN)
  • Interactive scenarios: Create Index to Quickly Find Keys Pointing to a Full URL; Create Index to Speed Up Queries by Date
  • Q&A
  • Break

SQL query performance (30 minutes)

  • Presentation: The path of a query (parse -> rewrite -> plan -> execute); analyzing and vacuuming tables; producing execution plans; understanding system statistics and cardinality estimates
  • Q&A
  • Break

PostgreSQL tips and tricks (10 minutes)

  • Presentation: Speeding up bulk loads (unlogged tables, disable constraints, update only what needs updating, load data sorted, etc.); working with timestamps (time zones, interval, etc.)

Wrap-up and Q&A (10 minutes)

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