3.7 out of 5
3.7
42 reviews on Udemy

Learn Partitioning in PostgreSQL from Scratch

A-Z guide to design and implement partitions in PostgreSQL ( Postgres ) Database
Instructor:
Rajkumar V
292 students enrolled
English
Understand what, when and how to partition
Implement partitions in production system
Write optimized queries to make use of the designed partitions
Handle maintenance tasks related to partitions
Automate all the tasks that has got to do with partitions

Learn the key terminologies, basic concepts, implementation techniques that you will need to build a, fully functional partitioned tables with PostgreSQL database. Every concepts or topics are covered with hands on demo session, along with English Caption / Subtitles to provide better clarity.

Many of the key concepts learned in this course can be applied on other databases as well. 

Handling huge volume of data / big tables in RDBMS

There are plenty of opportunities in data domain, and being able to learn and appreciate the importance of data volume handling through Partitions in RDBMS, will give you a confidence to tackle the challenges that you encounter in real time projects.

Content and Overview

Through this 7 session course, with more than 40 lectures, 6 Quiz chapters along with additional resources, you will 

  • Understand what, when and how to Partition.
  • Gain confidence in implementing Partitions in Production system
  • Know how to write optimized queries on the partitioned tables
  • Be able to handle maintenance tasks related to partitions
  • Understand how to automate all the tasks related to partitions

You can test the knowledge gained through the sessions by attending quizzes and every use case mentioned in the course are explained with demo sessions thereby enabling you to practice the newly learned skills. 

Downloadable Resources

You can also download the source code files used during the demo sessions, to practice at your end. The source code files are available for download as part of the last section named “Resources”.

Learners who complete this course will gain the knowledge and confidence to implement a fully functional and automated Partitioning solutions in the projects.

Course Introduction

1
Introduction

After this introduction session, you will be able to understand:

  • Who is this Course for?
  • Key Takeaways
  • Pre-requisites
  • What does this Course cover?

Building the Foundation

1
Module Overview

Get to know the list of topics covered as part of this section - Building the Foundation

2
What is Partitioning and What to Partition?

Understand, What is Partitioning and What to Partition in PostgreSQL database

3
Why to Partition?

Understand, why to partition a table

4
When to Partition?

Understand, When to Partition a table

5
Determining the Partition Key

Understand the factors to be considered for determining the Partition key

6
Tablespace

Understand

  • What is a Tablespace and
  • How it is related to Partitioning a table
7
Inheritance - Part - 1

Understand 

  • The importance of Inheritance
  • Facts about Inheritance
8
Demo - Inheritance - Part - 1

Demo session for the concepts learned in previous session - Inheritance - Part - 1

9
Inheritance - Part - 2

Understand 

  • The importance of Inheritance
  • Facts about Inheritance
10
Demo - Inheritance - Part - 2

Demo session for the concepts learned in previous session - Inheritance - Part - 2

11
Inheritance - Part - 3

Understand 

  • The importance of Inheritance
  • Facts about Inheritance
12
Demo - Inheritance - Part - 3

Demo session for the concepts learned in previous session - Inheritance - Part - 3

13
Inheritance - Limitations

Understand how constraints are used as part of Inheritance in PostgreSQL database

14
Check constraints

Understand the concept Check Constraints

15
Functions and Triggers

Understand the concepts, Functions and Triggers

16
Rules

Understand the concept Rules, through a hands-on session

17
Vacuum

Understand the concept Vacuum, through a hands on demo session

18
Analyze & Explain

Understand how to use, Analyze and Explain commands in Postgres

19
Module Summary

Summary of the Module

20
Partitioning Basics
21
Check Constraints
22
Vacuum

In order to answer the questions, you should have attended the Vacuum session and attempted the assignment task mentioned as part of the session

Types and Methods of Partitioning

1
Range and List Partitions

Understand the different types of Partitioning such as

  • Range Partitioning and
  • List Partitioning
2
Demo on Range and List Partitions

Understand the different types of Partitioning through a hands-on demo session

3
Demo on Trigger based Partition method

Understand Trigger based Partitioning with a demo

4
Demo on Rules based Partition method

Understand Rules based Partitioning with a demo

5
Triggers based Partitions vs Rules based Partitions

Understand 

  • Wwhen to apply Trigger based Partitioning and 
  • When to use Rule based Partitioning methods
6
Types and Methods of Partitioning

Optimized Query Execution

1
Module Overview

Module overview

2
Key Rules

Understand the key rules to be followed while developing a query on partitioned tables

3
Constraint Exclusion and Its impact on Query plans

Understand 

  • What is Constraint Exclusion and 
  • Its impact on Query plans
4
Demo - constraint exclusion – PARTITION mode

Understand

  • How to enable partition mode of the parameter constraint exclusion and
  • How it affects query planner
5
Demo - constraint exclusion – ON mode

Understand

  • How to enable ON mode of the parameter constraint exclusion and
  • How it affects query planner
6
Demo - constraint exclusion – OFF mode

Understand

  • How to enable OFF mode of the parameter constraint exclusion and
  • How it affects query planner
7
Optimized Query Execution

Production Use Cases

1
Module Overview

Module Overview

2
How to Partition existing tables with data in it?

Get to know, how to partition existing tables with data in it

3
Automate partition table creation by scheduling

Get to know, how to automate partition table creation by scheduling

4
Automate data insertion into corresponding partitions

Get to know, how to automate data insertion into corresponding partitioned tables

5
Selecting Partitioned tables in PostgreSQL

Get to know, how to list partitioned tables in PostgreSQL database

6
How to make a partitioned table back to Non partitioned table?

Get to know, how to make a partitioned table back to Non partitioned table.

7
Retention handling with Partitions

Get to know, how to handle retention policies with Partitioning in an automated way

8
Handling partition key value changes with update trigger

Get to know, how to handle partition key value changes

Limitations and Cautionary Notes

1
Limitations and Cautionary Notes

Understand 

  • Limitations associated with Partitioned tables and 
  • Tips to avoid some common mistakes while handling partitions.
2
Limitations and Cautionary Notes

Conclusion

1
What Next?

Concluding session with reference to helpful links and Thank You Note.

2
Source Codes - Resources

Please feel free to download the source codes used throughout this course. 

Cheers!!!

You can view and review the lecture materials indefinitely, like an on-demand channel.
Definitely! If you have an internet connection, courses on Udemy are available on any device at any time. If you don't have an internet connection, some instructors also let their students download course lectures. That's up to the instructor though, so make sure you get on their good side!
3.7
3.7 out of 5
42 Ratings

Detailed Rating

Stars 5
9
Stars 4
12
Stars 3
17
Stars 2
3
Stars 1
1
b0c2329151c29af5518deba4df77416f
30-Day Money-Back Guarantee

Includes

1 hours on-demand video
1 article
Full lifetime access
Access on mobile and TV
Certificate of Completion