Master the art of SQL query optimization with our PostgreSQL Developer Track. Designed for developers experienced in SQL and eager to harness PostgreSQL’s full potential, this intensive 3-day training equips you with advanced skills to write efficient and effective queries.

Participants should have:

• Solid understanding of SQL fundamentals, including querying, data manipulation, and database design principles.
• Prior experience with relational databases and SQL syntax.
• Familiarity with Linux environments is beneficial but not mandatory.

Who Should Attend:

• Developers and SQL practitioners looking to transition to PostgreSQL.
• Database administrators seeking to enhance their SQL query optimization skills.
• IT professionals interested in mastering PostgreSQL's advanced features and functionalities.

Upon completion, you'll be proficient in crafting efficient SQL queries tailored to PostgreSQL, poised to drive performance and scalability in database applications.

Outline

Day 1

• Introduction to PostgreSQL

• Installation

  • Quick Overview of methods of Installation.

• Postgresql – Client program / GUI Client.

• Postgresql Service /processes

• Architectural Fundamentals (Logical and Physical layout)

• Creating a Database (different options with command & utility)

• Accessing a Database & OIDs (Demo & Practical)

• Meta Commands & PgPLSQL Environment & Options

• Starting, stopping and finding status of postmaster

• Hands-on

• Physical Architectural

• Logical Architectural

• Exploring utilities in Postgresql (process and server / postmaster)

• Working with above said process related utilities

• Schemas in Postgresql

• Schema Search PATH

• Hands-on Exercise / implementation

• User Security

• Creating users

• Using and assigning user roles

• User authentication

• Hands-on Exercise

• Cluster Security

• Tablespace

• Built-in Table Space

• User Tablespaces

• Managing Tablespaces

Day 2

• Concurrency control

• Introduction

• Transaction Isolation (Levels with hands-on)

• Explicit Locking

• Hands-on Exercise / implementation

• Configuration files in postgresql

• Server configuration

• Connectivity configuration

• User auth. & privileges

  • Superuser
  • Creating normal users

• Access Control

• Performance Tips

• Using EXPLAIN

• EXPLAIN ANALYZE

• Statistics

• Explicit JOIN Clauses

• Disable Autocommit

• Use COPY

• Remove Indexes

• Remove Foreign Key Constraints

• Run ANALYZE

• Hands-on Exercise / implementation

Day 3

• Data Dictionary

• System Catalog Schema

• System Information Tables

• System Information Functions

• Hands-on Exercise

• Routine Database Maintenance Tasks

• Routine Vacuuming

• Routine Reindexing

• Understand Auto-vacuuming

• Log File Maintenance

• Backup and Restore

• SQL Dump

• Pg_dump & pg_dumpall

• File System Level Backup

• Setting up WAL archiving

• Continuous Archiving Concept

• Hands-on Exercise / implementation