Postgresql Database Administration On Win/Linux-Part 3 (Pt)

PostgreSQL Database Administration : Walk-in a Beginner, Walk-Out an Expert (Performance Tuning)

What you'll learn

PostgreSQL Installation from Source Code.

Segregation of PostgreSQL Files and Best Practices.

Performance tuning PostgreSQL Server Parameters.

Query Optimization.

Performance Monitoring and Events.

Optimizer and Extended Statistics.

Error Reporting and logging.

PgBadger.

Pg_Buffercache.

Pg_Prewarm.

Internals of Shared Buffers and Clock Sweep Algorithm.

Requirements

Basic Knowledge of Database Concepts

PostgreSQL Database Administration on Windows/Linux- Part 1

Working Knowledge on Windows and Linux

Internet access is required to download PostgreSQL Server

Description

PostgreSQL is an advanced, enterprise class open source Object-Relational Database Management System (ORDBMS). Its is a relational database that supports both SQL (relational) and JSON (non-relational) querying.PostgreSQL possesses robust feature sets including Multi-Version Concurrency Control (MVCC), point in time recovery, granular access controls, tablespaces, asynchronous replication, nested transactions, online/hot backups, a refined query planner/optimizer, and write ahead logging. It supports international character sets, multi-byte character encodings, Unicode, and it is locale-aware for sorting, case-sensitivity, and formatting. PostgreSQL is highly scalable both in the quantity of data it can manage and in the number of concurrent users it can accommodate.The course provides the learner with an extensive knowledge of PostgreSQL Database Administration with real time examples.Topics covered are:PostgreSQL Installation from Source Code.Segregation of PostgreSQL Files and Best Practices.Move Pg_Wal and Tempfiles.Performance tuning PostgreSQL Server Parameters.Internals of Shared Buffers and Clock Sweep Algorithm.Autovacuum Tuning.Checkpoint Tuning.Max_Connections.Asynchronous Behaviour.Work_Mem Tuning.Mainteanance_Work_Mem.Autovacuum_Work_Mem.Pg_Stat_Activity.Effective_Cache_Size.Null Value ConsiderationsPartial Indexes.Covering Indexes.Pg_Buffercache Extension.Pg_Prewarm.Query Optimization.Table Access Methods and Joins.Pg_Stat_Bgwriter.Index Optimization.Query Tuning.Performance Monitoring and Events.Optimizer and Extended Statistics.Error Reporting and logging.Pg_Stat_Statements.PgBadger.Server Monitoring.

Overview

Section 1: Introduction

Lecture 1 Introduction to Performance Tuning

Section 2: Source Code Installation and Segregation of Postgresql Files

Lecture 2 Source Code Install Introduction

Lecture 3 Source Code Install Demo

Lecture 4 Move Pg_Wal Files from Default Location to Desired Location.

Lecture 5 Move Temp Files to Desired Location Intro.

Lecture 6 Move Temp Files to Desired Location Demo.

Lecture 7 Move User Objects - Default Tablespace

Section 3: Parameter Tuning - Part 1

Lecture 8 Memory Parameters - Shared Buffer Intro

Lecture 9 Clock Sweep Algorithm

Lecture 10 Shared Buffer Demo

Lecture 11 Pg_Buffercache Extension

Lecture 12 Pg_Prewarm Extension

Lecture 13 Work_Mem Tuning Intro

Lecture 14 Work_Mem Tuning Demo

Lecture 15 Maintenance_ Work_Mem

Lecture 16 AutoVacuum_Work_Mem

Lecture 17 Max Connection Tuning Intro

Lecture 18 Max Connection Tuning Demo - 1

Lecture 19 Max Connection Tuning Demo - 2

Lecture 20 Autovacuum Tuning Part - 1

Lecture 21 Autovacuum Tuning Part - 2

Lecture 22 Autovacuum Tuning Part - 3

Lecture 23 Autovacuum Tuning Part - 4

Section 4: Parameter Tuning - Part 2

Lecture 24 Checkpoint Tuning Intro

Lecture 25 Checkpoint Timeout

Lecture 26 Checkpoint Completion Target

Lecture 27 Max_Wal_Size

Lecture 28 Pg_Stat_Bgwriter

Lecture 29 Effective Cache Size

Lecture 30 Asynchronous Behavior

Section 5: Query Optimization Part -1

Lecture 31 Statement Processing.

Lecture 32 Components of Explain Plan.

Lecture 33 Table Access Method

Lecture 34 Joins Part - 1

Lecture 35 Joins Part - 2

Lecture 36 Joins Part - 3

Section 6: Query Optimization Part -2

Lecture 37 Index Optimization Intro

Lecture 38 Index on Expression

Lecture 39 Partial Indexes

Lecture 40 Multi-Column Indexes

Lecture 41 Null Value Considerations.

Lecture 42 Covering Indexes

Lecture 43 Useful Index Queries - Demo

Section 7: Query Optimization Part -3

Lecture 44 Extended Statistics Intro

Lecture 45 Extended Statistics Demo

Lecture 46 Query Tuning Part -1

Lecture 47 Query Tuning Part - 2

Lecture 48 Query Tuning Part - 3

Section 8: Postgresql Monitoring

Lecture 49 Error Logging and Reporting.

Lecture 50 PgBadger Introduction

Lecture 51 PgBadger Demo - 1

Lecture 52 PgBadger Demo - 2

Lecture 53 Pg_Stat_Statements Introduction

Lecture 54 Pg_Stat_Statements Demo

Lecture 55 Server OS Monitoring - Windows

Lecture 56 Server OS Monitoring - Linux

Database Administrators,Accidental DBA's,Developers,Architects and Analyst,Students,Anyone who is curious about PostgreSQL