DSS 2022 | Workshop | Distributed SQL Tuning and Optimization in YugabyteDB

Master query tuning and SQL optimizations techniques in YugabyteDB

Level: Intermediate

Instructor: Franck Pachot

Time Needed: 2.5 hours

As a distributed PostgreSQL-compatible database, YugabyteDB has a ton of similarities and a few differences when compared to open-source Postgres. YugabyteDB does behave and look like Postgres in runtime which allows you to reuse most optimization techniques you've already mastered with Postgres. At the same time, YugabyteDB is a distributed database that can store your data across multiple geographies and use thousands of CPUs distributed across hundreds of nodes which requires you to consider query tuning patterns specific to distributed systems.

During this workshop, you'll learn and receive hands-on experience with techniques, concepts, and toolsets used for performance optimization and query tuning in YugabyteDB. By following simple examples, you'll start with generic SQL optimization concepts and tactics, and then do a deep dive into advanced optimization techniques specific to YugabyteDB's distributed nature.

What You’ll Learn and How You Can Apply It:

-Tables and indexes internals, and their scan operations
-Basics of hash or range sharding for tables and indexes
-Best practices for indexing distributed data, includes covering and partial indexes
-Types of joins and when they perform the best

Prerequisite

-Experienced SQL developers, or NoSQL developers learning SQL
-Practical experience creating tables and querying them
-Ability to start a YugabyteDB on Docker to do the hands-on exercises

Course Setup

The exercises will use simple tables to help focus the concepts. The exercises can run on any database environment, but it is ideal to start a database on your local machine with Docker.

Agenda

-Create table and indexes with hash sharding
-Specify the number of tablets
-Run SQL queries and look at execution plan to understand the performance
-See when range sharding is preferable
-Add secondary indexes
-Create covering indexes
-Create functional indexes
-Understand join methods
-Use pg_hint_plan

Learn more: https://distributedsql.org/

RESOURCES:
-YugabyteDB / Start Now: https://cloud.yugabyte.com
-Site: https://www.yugabyte.com
-GitHub: https://github.com/yugabyte

COMMUNITY:
-Slack: https://communityinviter.com/apps/yugabyte-db/register
-YouTube: https://www.youtube.com/c/YugaByte
-Instagram: https://www.instagram.com/yugabytedb/
-Twitter: https://twitter.com/yugabyte
-LinkedIn: https://www.linkedin.com/company/yugabyte

You can connect to all our community channels here: https://linktr.ee/yugabyte

#YugabyteDB #opensource #distributedsql #database