BE003 - Postgres Intermediate

Introduction

PostgreSQL is a powerful, open-source relational database management system which is very popular and widely used.

This workshop aims to provide the attendees with more details about PostgreSQL Index and Explain Query. Not only about how to use them in the real world, through all the sections but also the workshop will go a little deeper on how does it work under the hood, why and when we use it to make more wise decision to balance between performance and resource consuming.

Please note that the workshop only cover PostgreSQL version ≥ 9.4.

Duration

3 hours

Target audiences

• Backend Engineers/Data Analyst who mostly use basic Postgres features such as SELECT/UPDATE/INSERT/DELETE for daily tasks, and have not use or know how to use Explain Query to debug/optimize the SQL query.

• Backend Engineers who want to use Index more efficiently and more confidently.

Requirements for the attendees

• Having basic knowledge about the database, especially RDBMS.

• Know basic SQL queries

Learning outcome

After this workshop, you will:

• Know how to Explain query and read the Query Plan to know what the SQL query is doing behind in order to optimize the performance and resource usage.

• Getting more knowledge about Index:

  • Index Types

  • Single/Multi Column(s) Index, Combine Multi-Index

  • Unique, Partial Index

  • Index on Expression

• Besides, you will know how to use Index more efficiently and confidently.

What won’t be included in this workshop

• This workshop won't cover on how Database/PostgreSQL works.

• We won't go in-depth on how to optimize/scale PostgreSQL.

• The workshop only walks you through the points in section Outcome.