- Module 1: PostgreSQL is an open-source relational database system that is now available as a service in Azure. In this module you will learn about the PostgreSQL architecture, and how to manage different aspects of PostgreSQL in Azure Database for PostgreSQL.
After completing this module, you'll be able to:
- Describe the architecture of PostgreSQL.
- Describe the options for creating an Azure Database for PostgresQL server
- Understand PostgreSQL shared memory
- Module 2: PostgreSQL is a client-server system, which allows many clients to connect to a central server. In this module you will learn how PostgreSQL manages connections from clients, as well as looking at some common PostgreSQL client tools.
After completing this module, you'll be able to:
- Understand connection handling.
- Understand the PostgreSQL client tools.
- Module 3: The Azure Database for PostgreSQL server receives SQL queries and returns the relevant results. Each SQL query goes through several stages in order to return the correct results as quickly as possible. In this module you will learn about the different stages of processing a query, as well as the EXPLAIN statement.
After completing this module, you'll be able to:
- Identify the query processing components.
- Understand EXPLAIN.
- Module 4: Azure Database for PostgreSQL includes comprehensive security features including encryption in transit and at rest, authentication, and granting permissions to database users. In this module you will learn about the security features of Azure Database for PostgreSQL, and how to configure them.
After completing this module, you'll be able to:
- Describe the security features of Azure Database for PostgreSQL.
- Create database users and grant permissions.
- Understand encryption in Azure Database for PostgreSQL.
- Module 5: PostgreSQL supports stored procedures and functions to make SQL queries reusable. In this module you will learn how to create and run stored procedures and functions.
After completing this module, you'll be able to:
- Create a stored procedure in Azure Database for PostgreSQL.
- Call a stored procedure in Azure Database for PostgreSQL.
- Create and use a function in Azure Database for PostrgreSQL.
- Module 6: Azure Database for PostgreSQL is an ACID compliant database service. Write ahead logging ensures changes are both atomic and durable (the A and D in ACID). Changes are first written to the log before they are committed to the database. In this module you learn how Azure Database for PostgreSQL implements write ahead logging, and how the log can be used for replication and logical decoding.
After completing this module, you'll be able to:
- Describe write-ahead logging.
- Describe replication and logical decoding.
- Module 7: Relational databases can store vast quantities of data, but they also need to hold information about the structure of that data. For an operational database management system (DBMS) information about the structure of tables, and all other objects, security, and concurrency, amongst many other settings and metrics, is required. This information is know as metadata and is stored in system catalogs in Azure Database for PostgreSQL. In addition to directly accessing system catalogs, you can access system views which present the data from system catalogs in more understandable or useful ways.
After completing this module, you'll be able to:
- Describe system catalogs and system views in Azure Database for PostgreSQL.
- Investigate metadata in Azure Database for PostgreSQL.
- Module 8: Azure Database for PostgreSQL is a multi-user relational database solution. The ability to support many concurrent users enables PostgreSQL databases to scale out and enable applications that support many users and locations at the same time. The increase in users brings a risk of conflicts and so it is important to understand the concurrency systems that are in place in Azure Database for PostgreSQL to manage concurrency and conflicts. In this module we will look at both isolation levels and locking in Azure Database for PostgreSQL.
After completing this module, you'll be able to:
- Describe Multi-Version Content Control and snapshots in Azure Database for PostgreSQL.
- Describe isolation levels in Azure Database for PostgreSQL.
- Describe locking in Azure Database for PostgreSQL.
- Module 9: Azure Database for PostgreSQL uses vacuum and autovacuum to remove rows that have been marked as soft-deleted after an update or deletion.
After completing this module, you'll be able to:
- Explain the purpose of vacuum and autovacuum.
- Configure autovacuum server parameters.
- Module 10: Relational databases store many different types of data for many different types of applications. Some databases have thousands of users and some have just one user. Some tables have tens of rows while others have millions of rows. Some data is unique while other data is duplicated. All of these variables mean that database management systems (DBMSs) need systems to understand the data and the queries that are running against this data to ensure optimum performance. When you are performance tuning a database, it is important to understand how performance tuning works and how you can assess performance metrics.
After completing this module, you'll be able to:
- Describe statistics in Azure Database for PostgreSQL.
- Assess query performance with the Query Store.
Overview
Syllabus
- Module 1: Module 1: Explore PostgreSQL architecture
- Introduction
- Describe Azure Database for PostgreSQL
- Understand PostgreSQL architecture
- Understand PostgreSQL shared memory
- Exercise – Explore Azure Database for PostgreSQL
- Knowledge check
- Summary
- Module 2: Module 2: Understand client-server communication in PostgreSQL
- Introduction
- Understand connection handling
- Understand the PostgreSQL client tools
- Exercise: Explore PostgreSQL with Azure Data Studio
- Knowledge check
- Summary
- Module 3: Module 3: Understand PostgreSQL query processing
- Introduction
- Identify the query processing components
- Understand EXPLAIN
- Exercise: Practice EXPLAIN
- Knowledge check
- Summary
- Module 4: Module 4: Secure Azure Database for PostgreSQL
- Introduction
- Describe Azure Database for PostgreSQL security
- Describe built-in Azure Database for PostgreSQL server roles
- Grant permissions in Azure SQL Database for PostgreSQL
- Understand encryption in Azure Database for PostgreSQL
- Exercise: Configure permissions in Azure Database for PostgreSQL
- Knowledge check
- Summary
- Module 5: Module 5: Procedures and functions in PostgreSQL
- Introduction
- Create a stored procedure in Azure Database for PostgreSQL
- Call a stored procedure in Azure Database for PostgreSQL
- Create and use a function in Azure Database for PostgreSQL
- Exercise: Create a stored procedure in Azure Database for PostgreSQL
- Knowledge check
- Summary
- Module 6: Module 6: Understand write-ahead logging
- Introduction
- Describe write ahead logging
- Describe replication and logical decoding
- Exercise: List table changes with logical decoding
- Knowledge check
- Summary
- Module 7: Module 7: Use system catalogs and system views in PostgreSQL
- Introduction
- An overview of system catalogs and system views
- Investigate metadata with system catalogs and system views
- Exercise: Investigate metadata with system catalogs and system views
- Knowledge check
- Summary
- Module 8: Module 8: Understand concurrency in PostgreSQL
- Introduction
- Understand multi-version concurrency control (MVCC) and snapshots
- Describe isolation levels
- Understand locking
- Exercise: Understand locking
- Knowledge check
- Summary
- Module 9: Module 9: Optimize vacuum in Azure Database for PostgreSQL
- Introduction
- Explain the purpose of vacuum and autovacuum
- Configure autovacuum server parameters
- Exercise: Configure autovacuum server parameters
- Knowledge check
- Summary
- Module 10: Module 10: Tune queries in Azure Database for PostgreSQL
- Introduction
- Understand statistics
- Assess query performance with the Query Store
- Exercise: Assess query performance using Query Store
- Knowledge check
- Summary
