PostgreSQL Administration

Duration : 4 Days

Date : 08, 09, 10, 11 Nov 2023

Overview

• The main purpose of the training is to understand SQL w.r.t. PostgreSQL database and
• writing SQL queries keeping performance in mind.
• Also, the part of the training covers importance of indexes, its impact on performance, its
• maintenance.
• Keeping future automation in mind training covers concepts of database programming –
• batches, procedures, triggers & overview of cursors.

Lab Setup

HW: 1 system per participant with 8GB RAM and 300GB Hard Disk Space
OS: Windows
SW:
• PostgreSQL Installed & Configured (9.5 / 9.6)
• Acrobat & MS-Office Installed
• Open Internet Connectivity

Course Contents

Day 1

Query-writing concepts (Quick Overview)

• Database concepts (Overview)
• Database Objects (Overview)
• Postgresql – as an database
• Working with postgresql
• Meta commands of postgresql
• Quick understanding of postgresql architecture
• Physical file structure of postgresql / DB (Important)
• Logical architecture of postgresql (Important)
• Creating DB & tables (Practical)
• SQL Queries
  • Select / DML /DDL commands
  • Select query with condition fetch
  • Select with multiple conditions
  • Logical operators
    • AND
    • OR
    • NOT
  • Relational operators
  • Sorting data
  • Aggregate functions
  • Grouping data
  • Condition after grouping
  • Removing duplicates

Day 2

• Windows functions (Overview & Importance)
• Fetching data from multiple source(s)
• Types of Joins
• Need of Joins
• Demo
• Outer vs Inner joins
• Demo
• Self Join
• Demo
• Sub-Queries & its usages
• Multiple column Sub-query
• Join vs sub-query
• Pseudo columns & Tables (Inline views)
• Index as object of database
• Need and use of index
• Rules of making indexes
• Maintenance of DB / Objects and Indexes (w.r.t. PostgreSQL)

Views in DB

Advantages of Views

Working with Materialized View

Hands-on

Identify parts of query

Sequence of query execution

Joins (Hands-on / Questions)

• Joins?
• Types of Joins

Other Advanced Concepts

• Views & Indexes
• Query Optimization
• SubQueries

Day 3

• Variables
• Declaring variables
• SET versus SELECT
• So-called global variables

Testing conditions

• IF / ELSE statement
• Using CASE where possible

Looping

• Syntax of WHILE
• Breaking out of a loop

Stored Procedures

• Creating stored procedures
• Executing stored procedures

Parameters and return values

• Passing parameters
• Default values / WHERE clauses
• Using RETURN

Triggers

• Understand Triggers
• Need and Usage
• Types of Triggers
  • Insert, update and delete triggers
• Magic tables – Using the generated tables (eg INSERTED)
• Demo

Functions

• What they are
• Example of user-defined function

Day 4

• Configuration Files in PostgreSQL
• Managing Free Space
• Tuning Free space Map Settings
• Postgresql.conf
• Administering Tablespace
• Query Optimizations, plan executions
• Implementation of table partitioning concept with hands-on