Learn Partitioning in PostgreSQL from Scratch
3.8 (18 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
114 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Learn Partitioning in PostgreSQL from Scratch to your Wishlist.

Add to Wishlist

Learn Partitioning in PostgreSQL from Scratch

A-Z guide to design and implement partitions in PostgreSQL ( Postgres ) Database
3.8 (18 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
114 students enrolled
Created by Rajkumar V
Last updated 11/2016
English
Current price: $10 Original price: $50 Discount: 80% off
5 hours left at this price!
30-Day Money-Back Guarantee
Includes:
  • 1.5 hours on-demand video
  • 1 Article
  • 1 Supplemental Resource
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • 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
View Curriculum
Requirements
  • Download and Install PostgreSQL 9.4 or latest
  • Download and Install pgAdmin III (Optional but good to have)
Description

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.

Who is the target audience?
  • Database Administrators
  • Database or Data Warehouse programmers
  • Application developers using PostgreSQL as their backend
  • ETL developers
Compare to Other PostgreSQL Courses
Curriculum For This Course
42 Lectures
01:29:07
+
Course Introduction
1 Lecture 05:55

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

  • Who is this Course for?
  • Key Takeaways
  • Pre-requisites
  • What does this Course cover?
Preview 05:55
+
Building the Foundation
19 Lectures 35:13

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

Module Overview
01:01

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

Preview 00:55

Understand, why to partition a table

Why to Partition?
01:27

Understand, When to Partition a table

Preview 01:55

Understand the factors to be considered for determining the Partition key

Determining the Partition Key
02:29

Understand

  • What is a Tablespace and
  • How it is related to Partitioning a table
Tablespace
02:20

Understand 

  • The importance of Inheritance
  • Facts about Inheritance
Inheritance - Part - 1
01:20

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

Demo - Inheritance - Part - 1
01:33

Understand 

  • The importance of Inheritance
  • Facts about Inheritance
Inheritance - Part - 2
01:03

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

Demo - Inheritance - Part - 2
02:55

Understand 

  • The importance of Inheritance
  • Facts about Inheritance
Inheritance - Part - 3
00:42

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

Demo - Inheritance - Part - 3
02:07

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

Inheritance - Limitations
02:02

Understand the concept Check Constraints

Check constraints
02:18

Understand the concepts, Functions and Triggers

Functions and Triggers
02:21

Understand the concept Rules, through a hands-on session

Preview 02:16

Understand the concept Vacuum, through a hands on demo session

Vacuum
03:10

Understand how to use, Analyze and Explain commands in Postgres

Analyze & Explain
02:30

Summary of the Module

Module Summary
00:49

Partitioning Basics
5 questions

Check Constraints
1 question

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

Vacuum
2 questions
+
Types and Methods of Partitioning
5 Lectures 11:15

Understand the different types of Partitioning such as

  • Range Partitioning and
  • List Partitioning
Range and List Partitions
02:35

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

Demo on Range and List Partitions
02:23

Understand Trigger based Partitioning with a demo

Demo on Trigger based Partition method
02:36

Understand Rules based Partitioning with a demo

Demo on Rules based Partition method
01:39

Understand 

  • Wwhen to apply Trigger based Partitioning and 
  • When to use Rule based Partitioning methods
Triggers based Partitions vs Rules based Partitions
02:02

Types and Methods of Partitioning
4 questions
+
Optimized Query Execution
6 Lectures 07:59

Module overview

Module Overview
01:06

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

Key Rules
01:31

Understand 

  • What is Constraint Exclusion and 
  • Its impact on Query plans
Constraint Exclusion and Its impact on Query plans
00:56

Understand

  • How to enable partition mode of the parameter constraint exclusion and
  • How it affects query planner
Preview 01:22

Understand

  • How to enable ON mode of the parameter constraint exclusion and
  • How it affects query planner
Demo - constraint exclusion – ON mode
01:14

Understand

  • How to enable OFF mode of the parameter constraint exclusion and
  • How it affects query planner
Demo - constraint exclusion – OFF mode
01:50

Optimized Query Execution
2 questions
+
Production Use Cases
8 Lectures 23:33

Module Overview

Preview 01:10

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

How to Partition existing tables with data in it?
03:30

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

Automate partition table creation by scheduling
04:26

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

Automate data insertion into corresponding partitions
02:03

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

Selecting Partitioned tables in PostgreSQL
01:09

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

How to make a partitioned table back to Non partitioned table?
04:03

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

Retention handling with Partitions
03:29

Get to know, how to handle partition key value changes

Handling partition key value changes with update trigger
03:43
+
Limitations and Cautionary Notes
1 Lecture 03:53

Understand 

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

Limitations and Cautionary Notes
2 questions
+
Conclusion
2 Lectures 01:19

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

What Next?
01:16

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

Cheers!!!

Source Codes - Resources
00:03
About the Instructor
Rajkumar V
3.9 Average rating
26 Reviews
196 Students
3 Courses
Data Architect

My name is Rajkumar and I am so excited to contribute the learning from my industrial experience.

With more than a decade of experience in IT, I have spent the majority of my time dealing with "DATA", that includes data modeling, data profiling, cleansing, data transformation, storage, retrieval, optimization, governance, mining and reporting. 

I have played various roles in my career that includes Developer, Data Modeler, Tester, Project Lead, Product Consultant, Data Architect, ETL Specialist, Solution Architect, Release Manager etc.

To sum up, I am absolutely passionate about anything to do with "DATA" and I am looking forward to share my passion and knowledge with you!