Explore PostgreSQL architecture

PostgreSQL is an open-source relational database system that is available as an Azure service. In this module, you learn about the PostgreSQL architecture, and how to manage different aspects of Azure Database for PostgreSQL.

• Describe Azure Database for PostgreSQL
• Understand PostgreSQL architecture
• Understand PostgreSQL shared memory
• Exercise – Explore Azure Database for PostgreSQL

Understand client-server communication in PostgreSQL

PostgreSQL is a client-server system, which allows many clients to connect to a central server. In this module, you learn how PostgreSQL manages connections from clients, and look at some common PostgreSQL client tools.

• Understand connection handling
• Understand the PostgreSQL client tools
• Exercise - Explore PostgreSQL with client tools

Understand PostgreSQL query processing

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.

• Identify the query processing components
• Understand the EXPLAIN statement
• Exercise - Execute the EXPLAIN statement

Secure Azure Database for PostgreSQL

Azure Database for PostgreSQL includes comprehensive security features including encryption, authentication, and granting permissions to database users. In this module, you learn about the security features of Azure Database for PostgreSQL.

• 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

Procedures and functions in PostgreSQL

PostgreSQL supports stored procedures and functions to make SQL queries reusable. In this module, you learn how to create and run stored procedures and functions.

• 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

Understand write-ahead logging

Azure Database for PostgreSQL is an ACID compliant database service. Write-ahead logging ensures changes are both atomic and durable. In this module, you learn how Azure Database for PostgreSQL implements write-ahead logging.

• Describe write ahead logging
• Describe replication and logical decoding
• Exercise - List table changes with logical decoding

Configure and manage Azure Database for PostgreSQL

Azure Database for PostgreSQL employs system catalogs and views to manage metadata efficiently. Understanding these can significantly enhance database performance and streamline the administration of PostgreSQL systems.

• Understand the system catalogs and system views
• Investigate metadata with system catalogs and system views
• Explain the purpose of vacuum and autovacuum
• Configure autovacuum server parameters
• Exercise - Configure system parameters and explore metadata with system catalogs and views

Understand concurrency in PostgreSQL

Azure Database for PostgreSQL is a multi-user relational database solution. The increase in users brings a risk of conflicts and so it is important to understand the concurrency systems that are in place.

• Understand multi-version concurrency control (MVCC) and snapshots
• Describe isolation levels
• Understand locking
• Exercise - Understand locking

Tune queries in Azure Database for PostgreSQL

Database management systems (DBMSs) need systems to understand the data and the queries that are running against this data. It's important to understand how performance tuning works and how you can assess performance metrics.

• Understand statistics
• Assess query performance with the Query Store
• Exercise - Assess query performance using Query Store

Migrate to Azure Database for PostgreSQL flexible server

Azure Database for PostgreSQL Flexible Server supports data migration from PostgreSQL servers. This module covers online and offline migration tools and methods, helping you choose the right approach for your scenario.

• Compare online and offline migrations
• Identify migration tool options
• Analyze the decision criteria for tool selection and migration model
• Exercise – Offline PostgreSQL database migration
• Exercise – Online PostgreSQL database migration