PostgreSQL Database Administration on Win/Linux-Part 3 (PT) from Udemy

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 Considerations
Partial 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.

What's inside

Learning objectives

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.

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.

Syllabus

Introduction

Introduction to Performance Tuning

How to Install Postgresql using Source code.

Source Code Install Introduction

Source Code Install Demo

Move Pg_Wal Files from Default Location to Desired Location.

Move Temp Files to Desired Location Intro.

Move Temp Files to Desired Location Demo.

Move User Objects - Default Tablespace

Parameter Tuning - Part 1

Memory Parameters - Shared Buffer Intro

Clock Sweep Algorithm

Shared Buffer Demo

Pg_Buffercache Extension

Pg_Prewarm Extension

Work_Mem Tuning Intro

Work_Mem Tuning Demo

Maintenance_ Work_Mem

AutoVacuum_Work_Mem

Max Connection Tuning Intro

Max Connection Tuning Demo - 1

Max Connection Tuning Demo - 2

Autovacuum Tuning Part - 1

Autovacuum Tuning Part - 2

Autovacuum Tuning Part - 3

Autovacuum Tuning Part - 4

Parameter Tuning - Part 2

Checkpoint Tuning Intro

Checkpoint Timeout

Checkpoint Completion Target

Max_Wal_Size

Pg_Stat_Bgwriter

Effective Cache Size

Asynchronous Behavior

Query Optimization Part -1

Statement Processing.

Components of Explain Plan.

Table Access Method

Joins Part - 1

Joins Part - 2

Joins Part - 3

Query Optimization Part -2

Index Optimization Intro

Index on Expression

Partial Indexes

Multi-Column Indexes

Null Value Considerations.

Covering Indexes

Useful Index Queries - Demo

Query Optimization Part -3

Extended Statistics Intro

Extended Statistics Demo

Query Tuning Part -1

Query Tuning Part - 3

Error Logging and Reporting.

PgBadger Introduction

PgBadger Demo - 1

PgBadger Demo - 2

Pg_Stat_Statements Introduction

Pg_Stat_Statements Demo

Server OS Monitoring - Windows

Server OS Monitoring - Linux

Good to know

Know what's good

, what to watch for

, and possible dealbreakers

Covers performance tuning, which is essential for database administrators to optimize database performance and ensure efficient resource utilization

Explores query optimization techniques, enabling database administrators to improve query execution speed and overall system responsiveness

Discusses error reporting and logging, which are crucial for identifying and resolving database issues, ensuring system stability and data integrity

Examines the internals of shared buffers and the clock sweep algorithm, providing a deeper understanding of memory management within PostgreSQL

Requires installing PostgreSQL from source code, which may present a challenge for beginners who are more familiar with package managers

Focuses on PostgreSQL administration on Windows and Linux, requiring learners to have access to both operating systems for comprehensive practice

Activities

Be better prepared before your course. Deepen your understanding during and after it. Supplement your coursework and achieve mastery of the topics covered in PostgreSQL Database Administration on Win/Linux-Part 3 (PT) with these activities:

Review PostgreSQL Fundamentals

Show steps

Reinforce your understanding of PostgreSQL fundamentals, including basic SQL syntax, data types, and database design principles, to better grasp the advanced administration concepts covered in the course.

Browse courses on Relational Databases

Show steps

Review basic SQL commands (SELECT, INSERT, UPDATE, DELETE).
Practice creating and managing tables with different data types.
Familiarize yourself with database normalization concepts.

Review SQL Fundamentals

Show steps

Solidify your understanding of SQL fundamentals, including SELECT, INSERT, UPDATE, DELETE statements, and basic JOIN operations, to better grasp PostgreSQL's query optimization techniques.

Browse courses on SQL

Show steps

Review SQL syntax and common commands.
Practice writing basic SQL queries.
Familiarize yourself with different JOIN types.

Review 'PostgreSQL Administration Cookbook'

Show steps

Supplement your learning with practical examples and solutions for PostgreSQL administration tasks.

View Putting Knowledge to Work on Amazon

Show steps

Browse the table of contents to identify relevant chapters.
Read through recipes related to performance tuning and monitoring.
Experiment with the provided code snippets in a test environment.

Seven other activities

Expand to see all activities and additional details

Show all ten activities

Read 'PostgreSQL Administration Cookbook'

Show steps

Gain practical insights into PostgreSQL administration by exploring real-world scenarios and solutions.

View PostgreSQL 9 Administration Cookbook - Second... on Amazon

Show steps

Obtain a copy of the 'PostgreSQL Administration Cookbook'.
Read chapters relevant to performance tuning and monitoring.
Experiment with the provided solutions in a test environment.

Optimize Sample Queries

Show steps

Sharpen your query optimization skills by analyzing and improving the performance of sample SQL queries.

Browse courses on Query Optimization

Show steps

Obtain a set of sample SQL queries with performance issues.
Use EXPLAIN ANALYZE to identify bottlenecks in the query execution plan.
Apply indexing techniques and rewrite queries to improve performance.
Measure the performance improvement after each optimization step.

Optimize Sample Queries

Show steps

Sharpen your query optimization skills by analyzing and improving the performance of sample SQL queries using EXPLAIN plans and indexing techniques.

Browse courses on Query Optimization

Show steps

Collect a set of sample SQL queries.
Analyze query performance using EXPLAIN.
Apply indexing and other optimization techniques.
Measure the performance improvement.

Document PostgreSQL Configuration

Show steps

Reinforce your understanding of PostgreSQL configuration parameters by creating a well-documented configuration file with explanations for each setting.

Browse courses on Configuration Files

Show steps

Create a template postgresql.conf file.
Research and document each parameter's function.
Organize the file for readability.

Read 'High Performance PostgreSQL'

Show steps

Deepen your understanding of PostgreSQL performance optimization by studying advanced techniques and strategies.

View PostgreSQL 10 High Performance - Third Edition:... on Amazon

Show steps

Obtain a copy of 'High Performance PostgreSQL'.
Focus on chapters related to query optimization and indexing.
Implement the recommended techniques in a test environment.

Build a Monitoring Dashboard

Show steps

Apply your knowledge of PostgreSQL performance monitoring by building a dashboard that visualizes key metrics using tools like pg_stat_statements and Grafana.

Browse courses on Performance Monitoring

Show steps

Set up pg_stat_statements extension.
Collect performance data.
Choose a visualization tool (e.g., Grafana).
Create a dashboard to display key metrics.

Contribute to PostgreSQL Documentation

Show steps

Solidify your understanding of PostgreSQL by contributing to the official documentation, improving clarity, adding examples, or translating existing content.

Browse courses on Documentation

Show steps

Identify areas in the documentation that need improvement.
Submit a patch with your proposed changes.
Follow the PostgreSQL contribution guidelines.

Career center

Learners who complete PostgreSQL Database Administration on Win/Linux-Part 3 (PT) will develop knowledge and skills that may be useful to these careers:

Database Performance Analyst

A database performance analyst evaluates and optimizes the performance of database systems. This course is highly applicable for a database performance analyst, as it provides explicit training in PostgreSQL performance tuning, query optimization, and monitoring. This course gives detailed instruction in shared buffers, auto vacuum, checkpoint tuning, and index optimization, which are important for a database performance analyst. The course also provides knowledge of error logging and reporting, which a database performance analyst can use to improve system health.

See salaries and explore the career path for Database Performance Analyst

Database Administrator

A database administrator is responsible for managing and maintaining database systems, ensuring their performance, security, and availability. This course is highly relevant for a database administrator, as it provides extensive instruction in PostgreSQL administration, including installation, performance tuning, query optimization, and monitoring. The course covers essential topics like parameter tuning, shared buffers, and index optimization, which are critical for a database administrator to maintain a healthy and efficient database environment. Learning about query tuning and extended statistics helps a database administrator understand how to improve the performance of the database.

See salaries and explore the career path for Database Administrator

Cloud Database Administrator

A cloud database administrator manages database systems running on cloud platforms. This course is highly applicable to a cloud database administrator who needs to know the specifics of PostgreSQL administration, including installation and performance tuning. A cloud database administrator benefits from the course's coverage of topics such as parameter tuning and query optimization. The course's focus on monitoring and logging further helps a cloud database administrator maintain a healthy and efficient database environment within the cloud.

See salaries and explore the career path for Cloud Database Administrator

Infrastructure Engineer

An infrastructure engineer manages the physical and virtual infrastructure that supports IT systems. This course is beneficial for an infrastructure engineer, particularly one that is responsible for systems that run PostgreSQL databases. This course provides instruction in topics such as installation, performance tuning, and server monitoring, which are all essential for a properly configured infrastructure. A key focus of the course is PostgreSQL administration, which maps well to the work of an infrastructure engineer that works with database implementations.

See salaries and explore the career path for Infrastructure Engineer

Site Reliability Engineer

A site reliability engineer ensures that systems are reliable, scalable, and performant. This course very helpful for a site reliability engineer, as it provides in depth knowledge of PostgreSQL. Specifically, topics like performance monitoring, error logging, and query optimization are useful. The course includes information on server monitoring which allows a site reliability engineer to ensure the database is healthy. A site reliability engineer can use the information provided in this course to diagnose and fix issues related to database performance.

See salaries and explore the career path for Site Reliability Engineer

Database Consultant

A database consultant provides expert advice and solutions for database-related issues. This course is helpful for a database consultant who needs the hands on knowledge of PostgreSQL administration and performance. The topics covered, such as query optimization, parameter tuning, and performance monitoring, are essential for a database consultant when making recommendations. This course can also assist in making design choices, as it gives insights into how the database operates internally.

See salaries and explore the career path for Database Consultant

Systems Administrator

A systems administrator manages the overall operation of computer systems, including servers and databases. This course may be useful for a systems administrator who is responsible for the PostgreSQL database as it provides specific knowledge in PostgreSQL, including installation and performance tuning. Topics like moving WAL files, temp files, and user objects help a systems administrator properly configure the file system for the database. This course further provides the knowledge needed to monitor the server's performance, which is essential for systems administrators managing databases.

See salaries and explore the career path for Systems Administrator

Application Developer

An application developer builds software applications that often interact with databases. This course may be useful to an application developer, particularly if they are working with applications that use PostgreSQL. Understanding how to optimize queries and indexes helps an application developer write more efficient code. Knowing about SQL and query tuning helps an application developer create performant applications and debug issues that may arise. The course also helps a developer understand the internals of the database, which may help with problem solving.

See salaries and explore the career path for Application Developer

Database Engineer

A database engineer designs, implements, and maintains complex database systems. This course may be useful for a database engineer, as it gives insights into the inner workings of PostgreSQL, including memory management and query optimization. Knowledge of how to optimize the database, by tuning parameters and working with indexes, enables a database engineer to create efficient data solutions. Understanding topics like table access methods helps a database engineer make better choices about database design. This course also provides the knowledge to perform server monitoring and tune parameters that are critical to the operation of a database.

See salaries and explore the career path for Database Engineer

Data Architect

A data architect designs and oversees the implementation of data management systems. This course may be useful for a data architect, as it gives insights into crucial database concepts, such as query optimization and performance tuning. Understanding how to optimize queries and indexes, as taught in this course, is necessary when creating data solutions. This course gives insight into how the database functions practically, which can help a data architect create more optimal designs. The course also covers table access methods, which influences how a data architect thinks about system design.

See salaries and explore the career path for Data Architect

Software Engineer

A software engineer designs, develops, and tests software systems. This course may be helpful for a software engineer, especially if they work on systems that use PostgreSQL. Knowing how to perform query optimization and index tuning, as covered in this course, helps software engineers write more efficient code. They can also use the specific topics from this course to troubleshoot problems. The course provides knowledge about how database systems function, which may improve a software engineer's general skillset.

See salaries and explore the career path for Software Engineer

Solutions Architect

A solutions architect designs technology solutions that meet specific business needs. This course may be useful for a solutions architect, particularly if they are designing systems that leverage PostgreSQL. This course provides knowledge about PostgreSQL administration, query optimization, and performance tuning. The information on how the database actually operates may help a solutions architect make better choices about the technology they incorporate into their solutions.

See salaries and explore the career path for Solutions Architect

Data Analyst

A data analyst examines data to draw conclusions, and often uses SQL to perform queries on databases. This course may be helpful for a data analyst, as it provides insights about how PostgreSQL is configured and performs. Knowing how to tune parameters and query optimization helps a data analyst understand how a database runs. The knowledge gained from this course may assist with an understanding of how to write more efficient queries, and how to diagnose performance issues.

See salaries and explore the career path for Data Analyst

Technical Support Engineer

A technical support engineer provides assistance to users who are experiencing issues with software and systems. This course may be useful for a technical support engineer, especially if they will be supporting systems that use PostgreSQL. Understanding error logging, reporting, and server monitoring are all useful when diagnosing issues. The knowledge gained from understanding PostgreSQL database administration helps a technical support engineer more effectively troubleshoot issues reported by users.

See salaries and explore the career path for Technical Support Engineer

Data Scientist

A data scientist analyzes and interprets complex data, often using databases to extract information. This course may be useful for a data scientist if they are required to work with a PostgreSQL database. Knowledge of server monitoring and query optimization, as taught by the course, can greatly improve the efficiency of data extraction. This course provides insight into the workings of a relational database, which may be helpful when using it for data science purposes.

See salaries and explore the career path for Data Scientist

Reading list

We've selected three books that we think will supplement your learning. Use these to develop background knowledge, enrich your coursework, and gain a deeper understanding of the topics covered in PostgreSQL Database Administration on Win/Linux-Part 3 (PT).

PostgreSQL 9 Administration Cookbook - Second...

Save

Provides practical solutions to common PostgreSQL administration tasks. It covers topics such as installation, configuration, performance tuning, and security. It serves as a valuable reference for real-world scenarios and complements the course material by offering hands-on guidance. This book is particularly useful for understanding best practices in PostgreSQL administration.

PostgreSQL 9 Administration Cookbook - Second...

Kindle Edition

$$$

PostgreSQL 10 High Performance

Save

Delves into advanced techniques for optimizing PostgreSQL performance. It covers topics such as query tuning, indexing strategies, and hardware considerations. It valuable resource for experienced administrators seeking to maximize the performance of their PostgreSQL databases. This book is particularly useful for understanding the nuances of query optimization.

PostgreSQL 10 High Performance - Third Edition:...

Paperback

Putting Knowledge to Work

Save

Provides practical solutions to common PostgreSQL administration tasks. It covers topics such as performance tuning, backup and recovery, and security. It serves as a useful reference for administrators looking to optimize their PostgreSQL deployments. The book offers recipes and step-by-step instructions that complement the theoretical knowledge gained in the course.

Putting Knowledge to Work

Paperback

Check price

Putting Knowledge to Work

Kindle Edition

Check price

PostgreSQL Database Administration on Win/Linux-Part 3 (PT)

What's inside

Learning objectives

Syllabus

Good to know

Save this course

Activities

Career center

Reading list

Share

Similar courses