The Azure PostgreSQL Workshop

Welcome to the Azure PostgreSQL Workshop — a hands-on, scenario-driven lab that takes you from zero to confidently operating Azure Database for PostgreSQL Flexible Server in production.

You will deploy infrastructure with Bicep, load realistic data, deliberately break things, observe the damage through monitoring, diagnose root causes, and fix them — the same cycle you follow in real operations.

This is a two-day workshop. We’ll move at a comfortable pace with plenty of time for questions, discussion, and troubleshooting. Whether you’re coming from an Oracle, SQL Server, or cloud-native background — please ask questions as we go. There are no silly questions, and the best workshops are the ones where everyone participates!

Who Is This For?

• Developers building applications on Azure PostgreSQL who want to understand the platform deeply
• Platform / DevOps engineers responsible for deploying, securing, and monitoring PostgreSQL on Azure
• DBAs migrating from Oracle, or on-premises PostgreSQL to Azure

Workshop Journey

Follow these 10 steps from infrastructure deployment to teardown:

Day 1 — Deploy, Connect, Monitor, Administer & Protect

Day 2 — Profile, Diagnose & Fix

What You Will Learn

Prerequisites

Before you begin, make sure you have the items below ready. The exact requirements depend on which deployment option you choose — see the comparison table at the bottom of this page.

Azure Subscription

You need an Azure subscription with Contributor access.

• Sign in at https://portal.azure.com
• Authenticate the Azure CLI: az login

Required Tools

Shell Environment

Your choice of shell depends on your deployment option:

• Option 1 (Enterprise): Use Azure Cloud Shell (Bash) to deploy. After deployment, you SSH into the jumpbox Linux VM which has psql, pg_dump, pg_restore, and other PostgreSQL 18 utilities pre-installed. All database work happens on the jumpbox.
• Option 2 (Simple) / Option 3 (BYOS): Use any terminal — Azure Cloud Shell, PowerShell, Bash, or Windows Terminal. You connect to PostgreSQL directly from your machine, so you need psql installed locally (or use the VS Code PostgreSQL extension or the Azure Portal Connect blade).

Deployment Options

The workshop provides three deployment options. Choose the one that best fits your scenario.

Option 1 — Enterprise Deployment (Recommended)

Deploy the complete hub-and-spoke architecture using Bicep. This is the default path used throughout the workshop.

What gets deployed:

• Hub VNet with a jumpbox Linux VM (Rocky Linux 9, PostgreSQL 18 client pre-installed)
• Spoke VNet with Azure Database for PostgreSQL Flexible Server (private access via delegated subnet)
• VNet peering between hub and spoke
• Private DNS zone (private.postgres.database.azure.com)
• NSG with SSH access
• Storage account (for diagnostic logs)

How to connect:

1. SSH into the jumpbox VM from Cloud Shell or your local terminal
2. Use psql directly on the jumpbox to reach PostgreSQL over the private network
3. (Optional) Set up an SSH tunnel to use GUI tools (VS Code PostgreSQL extension, pgAdmin) from your laptop — see Connecting to PostgreSQL for details

Best for: Enterprise-like scenarios with private networking, DNS resolution, and a jump-box access pattern.

Option 2 — Simple Deployment (Public Access)

Deploy only a PostgreSQL Flexible Server with public network access and a firewall rule for your IP. No VNet, no jumpbox, no DNS.

What gets deployed:

• Azure Database for PostgreSQL Flexible Server (public access, firewall whitelist)

How to connect: Directly from your machine using:

• psql (install locally or use Cloud Shell)
• VS Code PostgreSQL extension
• Azure Portal → PostgreSQL server → Connect blade

No SSH tunnel needed.

Best for: Quick start, simple labs, or when you already have a PostgreSQL client installed and want minimal setup.

See the Deploy with Bicep section for step-by-step instructions.

Option 3 — Bring Your Own Server

If you already have an Azure Database for PostgreSQL Flexible Server (or are attending an instructor-led session where infrastructure is pre-provisioned), skip the deployment section entirely and proceed to Connecting to PostgreSQL.

You will need:

• PostgreSQL server FQDN
• Admin username and password
• Jumpbox VM IP address (if applicable) and SSH credentials
• Network access (either public endpoint with your IP whitelisted, or SSH to the jumpbox)

Tips for Azure Cloud Shell

• Use code <filename> to open the built-in text editor
• Drag and drop files to upload them
• Use curl -o filename.ext https://url/filename.ext to download files directly

Next: Once your prerequisites are ready, head to Deploy Azure Database for PostgreSQL with Bicep to provision your environment.

Getting up and running

Deploy Azure Database for PostgreSQL with Bicep

In this section you will deploy the workshop environment using Bicep. Choose the option that matches your scenario.

Prerequisites

• Windows 10/11 with PowerShell, macOS, or Linux (or use Azure Cloud Shell)
• Azure CLI installed
• An Azure subscription with Contributor access

Option 1 — Enterprise Deployment

Deploys the full hub-and-spoke architecture: jumpbox VM, private VNet, PostgreSQL Flexible Server with private access, and a private DNS zone.

Step 1 — Download the Bicep templates

Open Azure Cloud Shell (Bash), or a local terminal with Azure CLI installed, and download the templates:

curl -O https://pg.azure-workshops.cloud/scripts/bicep.zip
unzip bicep.zip
cd bicep

PowerShell? Use Invoke-WebRequest instead:

Invoke-WebRequest -Uri https://pg.azure-workshops.cloud/scripts/bicep.zip -OutFile bicep.zip
Expand-Archive bicep.zip -DestinationPath .
cd bicep

Step 2 — Log in to Azure

az login

This opens a browser window. Sign in with your Azure account.

Verify you are on the correct subscription:

az account show --query "{name:name, id:id, state:state}" -o table

If the subscription shown is not the one you intend to use, list all available subscriptions and set the correct one:

az account list --query "[].{Name:name, ID:id, Default:isDefault}" -o table
az account set --subscription "<subscription-name-or-id>"

Step 3 — Create the resource group

az group create --name PG-Workshop --location uksouth

Step 4 — Deploy the Bicep template

az deployment group create --resource-group PG-Workshop --template-file main.bicep

You will be prompted for four values:

The deployment takes approximately 10–15 minutes. When it finishes, note the outputs — they contain the jumpbox public IP and the PostgreSQL FQDN.

Step 5 — Verify resources and collect connection details

Go to Resource Groups in the Azure Portal and click on PG-Workshop. You should see:

• Jumpbox VM (jumpbox)
• PostgreSQL Flexible Server
• Hub and Spoke virtual networks
• Private DNS zone
• Network security group
• Storage account

Keep these values accessible — you will use them in every subsequent section:

Step 6 — SSH into the jumpbox and verify connectivity

ssh <vmAdminUsername>@<jumpbox-public-ip>

Use the password you provided in Step 4. Once connected, verify the PostgreSQL client is installed:

psql --version

You should see psql (PostgreSQL) 18.x. Then test connectivity to the database:

psql -h <postgresql-fqdn> -U <pgAdminUsername> -d postgres

Enter the PostgreSQL password when prompted. If you see the postgres=> prompt, the deployment is working correctly.

Check the PostgreSQL server version:

SELECT version();

You should see output containing PostgreSQL 18.x. To exit psql:

\q

Next: Proceed to Connecting to PostgreSQL for detailed connection methods, SSH tunnels, and VS Code setup.

Option 2 — Simple Deployment (Public Network)

Deploys only a PostgreSQL Flexible Server with a public endpoint and a firewall rule for your IP. No jumpbox VM is created — you connect directly from your machine.

Step 1 — Download templates and navigate to the simple folder

If you haven’t already downloaded the templates, do so now (see Option 1 — Step 1). Then navigate to the simple folder:

cd bicep/simple

Step 2 — Log in and create the resource group

Skip this step if you already logged in and created the resource group above.

az login
az group create --name PG-Workshop --location uksouth

Step 3 — Get your public IP

On PowerShell (Windows):

(Invoke-WebRequest -Uri "https://ifconfig.me/ip").Content

On Bash (Linux/macOS/Cloud Shell):

curl -s ifconfig.me

Note the IP address returned.

Step 4 — Deploy

az deployment group create --resource-group PG-Workshop \
  --template-file main.bicep \
  --parameters clientIPAddress="<your-public-ip>"

You will be prompted for:

Step 5 — Connect from your local machine

The deployment output includes a ready-to-use psqlCommand. If you have psql installed locally:

psql "host=<server-fqdn> user=<administratorLogin> dbname=postgres sslmode=require"

If you do not have psql installed, you can connect from the Azure Portal: go to your PostgreSQL server → Connect blade.

Step 6 — Verify resources and collect connection details

Go to Resource Groups in the Azure Portal and click on PG-Workshop. You should see:

• PostgreSQL Flexible Server
• Firewall rule with your IP address

Keep these values accessible — you will use them in every subsequent section:

Once connected via psql, check the PostgreSQL server version:

SELECT version();

You should see output containing PostgreSQL 18.x. To exit psql:

\q

Next: Proceed to Connecting to PostgreSQL for detailed connection methods and VS Code setup.

Connecting to PostgreSQL

This section covers how to connect to your Azure Database for PostgreSQL Flexible Server, set up convenient environment variables, and store credentials securely. Since the database is deployed inside a VNet with private access, you need to go through the jumpbox VM to reach it.

You will need the following values from your deployment output:

• Jumpbox Public IP (vmPublicIp)
• PostgreSQL FQDN (postgreSqlFqdn)
• PostgreSQL Admin Username (postgreSqlUsername)
• VM Admin Username (vmUsername)

Method 1: SSH into the Jumpbox and use psql

The simplest approach — SSH into the jumpbox and connect directly.

# SSH into the jumpbox
ssh <vmUsername>@<jumpbox-ip>

Once on the jumpbox, connect to PostgreSQL:

psql -h <postgresql-fqdn> -U <pgadmin> -d postgres

You should see the PostgreSQL prompt. Verify with:

SELECT version();

Tip: After verifying your connection works, set up environment variables and .pgpass (see the Connection Best Practices section below) so you never have to type the full connection string again.

Method 2: SSH Tunnel (connect from your local machine)

Create an SSH tunnel to forward a local port through the jumpbox to the PostgreSQL server.

Step 1: Create the tunnel

Open a terminal on your local machine:

ssh -v -fN -L 5433:<postgresql-fqdn>:5432 <vmUsername>@<jumpbox-ip>

Flags explained:

• -v — verbose output so you can see the tunnel being established
• -f — run in background after authentication
• -N — don’t execute a remote command (tunnel only)
• -L 5433:<fqdn>:5432 — forward local port 5433 to the remote PostgreSQL port 5432

Step 2: Verify the tunnel is open

On Windows (PowerShell):

netstat -an | findstr 5433

On Linux/macOS:

lsof -i :5433

You should see the port in LISTEN state.

Step 3: Connect through the tunnel

psql -h localhost -p 5433 -U <pgadmin> -d postgres

To close the tunnel:

# Find the SSH process
ps aux | grep "ssh -.*5433"

# Kill it
kill <pid>

On Windows:

Get-Process ssh | Where-Object { $_.CommandLine -match "5433" } | Stop-Process

Method 3: VS Code PostgreSQL Extension

The VS Code PostgreSQL extension lets you run queries directly from the editor.

Step 1: Set up the SSH tunnel

You need the tunnel from Method 2 running first. Open a terminal and run:

ssh -fN -L 5433:<postgresql-fqdn>:5432 <vmUsername>@<jumpbox-ip>

Step 2: Install the PostgreSQL extension

1. Open VS Code
2. Go to Extensions (Ctrl+Shift+X)
3. Search for PostgreSQL (by Microsoft)
4. Click Install

Step 3: Create a new connection

1. Click the PostgreSQL icon in the left sidebar
2. Click + Create Connection Profile
3. Fill in:
   • Server name: localhost
   • Port: 5433
   • Database name: postgres
   • Authentication type: Password
   • User name: your PostgreSQL admin username
   • Password: your PostgreSQL admin password
   • Connection name: give it a friendly name (e.g., Workshop DB)
4. Click Connect

Step 4: Run queries

1. Right-click your connection and select New Query
2. Type your SQL:

SELECT version();

1. Press Ctrl+Shift+E or click Run to execute

You can also browse tables, views, and functions in the sidebar tree.

Method 4: VS Code PostgreSQL Extension (Simple Deployment)

If you deployed using bicep/simple/main.bicep, the server has public access enabled with a firewall rule for your IP. No SSH tunnel needed.

1. Open the PostgreSQL sidebar in VS Code
2. Click + Create Connection Profile
3. Fill in:
   • Server name: <postgresql-fqdn> (directly, no tunnel)
   • Port: 5432
   • Database name: postgres
   • User name: your admin username
   • Password: your admin password
4. Connect and query directly

Connection Best Practices

Once you have verified your connection works using one of the methods above, set up environment variables and a .pgpass file on the jumpbox. This avoids typing connection parameters repeatedly and keeps your password out of shell history.

Step 1 — Set libpq Environment Variables

libpq environment variables are recognized by psql, pg_dump, pg_restore, and all PostgreSQL client tools.

Create a file on the jumpbox:

cat > ~/.pg_azure << 'EOF'
export PGHOST=<postgresql-fqdn>
export PGUSER=<pgadmin>
export PGDATABASE=orders_demo
export PGSSLMODE=require
EOF

Or download the template and edit it:

curl -o ~/.pg_azure https://pg.azure-workshops.cloud/scripts/pg_azure

Then update the values in ~/.pg_azure with your actual hostname and username.

Source it in your current session:

source ~/.pg_azure

Tip: Add source ~/.pg_azure to your ~/.bashrc so the variables are set automatically on every login.

Notice that PGPASSWORD is intentionally omitted. Exporting your password as an environment variable is insecure — it is visible in /proc/<pid>/environ, in shell history, and to any process running as the same user. Use .pgpass instead.

Step 2 — Store Passwords Securely with .pgpass

The ~/.pgpass file provides password lookup for libpq clients without exposing the password in the environment. The format is:

hostname:port:database:username:password

Create the file:

echo "<postgresql-fqdn>:5432:*:<pgadmin>:<your-password>" > ~/.pgpass
chmod 600 ~/.pgpass

The * in the database field means this password applies to all databases on that server. The chmod 600 is required — PostgreSQL ignores .pgpass if the permissions are too open.

Now connect without any parameters:

psql

You should connect directly to orders_demo (from PGDATABASE) on the Flexible Server (from PGHOST) with no password prompt.

Step 3 — Getting the Connection String from the Azure Portal

The Azure Portal provides ready-made connection strings for various languages and tools.

1. Go to your PostgreSQL Flexible Server in the Azure Portal
2. In the left menu, click Connection strings
3. Copy the psql connection string

This is useful as a reference — but for day-to-day work during the workshop, the environment variables + .pgpass approach above is faster and more secure.

Troubleshooting

SSH tunnel fails to connect:

• Verify the jumpbox public IP is correct
• Check that NSG allows SSH (port 22) from your IP
• Ensure the jumpbox VM is running: az vm show -g apg-workshop-rg -n jumpbox --query powerState

psql: could not connect to server:

• Verify the tunnel is active: netstat -an | findstr 5433
• Check that you are using the correct port (5433 for tunnel, 5432 for direct)
• Verify the PostgreSQL FQDN resolves on the jumpbox: nslookup <postgresql-fqdn>

VS Code connection timeout:

• Make sure the SSH tunnel is running before creating the connection
• Check that the port matches (5433 for tunnel)
• Try disconnecting and reconnecting the profile

DNS resolution fails on the jumpbox:

• Verify BIND is running: systemctl status named
• Test resolution: dig <postgresql-fqdn>
• If BIND is not running, restart it: sudo systemctl restart named

.pgpass not working:

• Check permissions: ls -la ~/.pgpass — must be -rw------- (600)
• Verify the hostname matches exactly (including .postgres.database.azure.com)
• Ensure no trailing whitespace in the file: cat -A ~/.pgpass

psql: The PostgreSQL Command-Line Client

psql is the interactive terminal for PostgreSQL. You will use it throughout this workshop to run queries, explore schemas, import data, and administer the server. This section teaches you the essential skills for working with psql efficiently.

Prerequisite: You should have connected to PostgreSQL from the jumpbox in the previous section. If your environment variables and .pgpass are set up, simply run psql to connect.

Code block conventions used throughout this workshop:

• ` ```sh ` — run in the Linux shell on the jumpbox
• ` ```sql ` — SQL sent to the PostgreSQL server (type inside psql)
• ` ```psql ` — psql meta-commands (type inside psql, processed by the client — not sent to the server)

Backslash Meta-Commands

Anything you type in psql that begins with a backslash (\) is a meta-command — it is processed by the psql client itself, not sent to the PostgreSQL server.

Note: The meta-commands for exploring databases, schemas, tables, indexes, and roles (\l, \dt, \dn, \du, \d, etc.) are covered step-by-step in the Load Data section, where you will use them hands-on against real data. This section focuses on the psql session skills you need to work efficiently.

\conninfo — Always Verify Your Connection First

Before running any commands, confirm that you are connected to the right server, database, and user:

\conninfo

Example output:

You are connected to database "postgres" as user "pgadmin" on host "myserver.postgres.database.azure.com" at port "5432".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)

What each field tells you:

Run \conninfo any time you are unsure which server or database your session is on.

Display Modes

By default, psql displays query results as a horizontal table. This becomes unreadable when tables have many columns.

Toggle expanded (vertical) display:

\x auto

With \x auto, psql automatically switches to vertical display when the output is too wide for your terminal. Try it:

SELECT * FROM pg_stat_activity;

Without \x auto, this is a wall of text. With it, each row is displayed vertically.

Toggle query timing:

\timing

This shows how long each query takes. Enable it now — you will want it for every query in the workshop.

Getting Help

\?            -- list all backslash meta-commands
\h            -- list all SQL commands
\h CREATE TABLE  -- show syntax help for a specific SQL command

Watching a Query

\watch re-runs the last query at a set interval — useful for monitoring:

SELECT count(*) FROM pg_stat_activity;

\watch 2

This re-runs the query every 2 seconds. Press Ctrl+C to stop.

Command History

\s            -- print command history

Use Ctrl+R to search history interactively — type part of a previous command and psql will find it.

Inspecting Functions

You can view the source of any function:

\sf abs(bigint)

This prints the CREATE FUNCTION definition — useful for understanding built-in or custom functions.

Running SQL from a File

Instead of pasting SQL into psql, you can run a file:

\i /path/to/script.sql

Or from the command line:

psql -f /path/to/script.sql

This is how you will restore database dumps and run batch scripts later in the workshop.

Running a single command from the shell

If .pgpass and .pg_azure are set up, you can run one-off SQL directly from the shell with -c:

psql -c "CREATE DATABASE orders_demo;"

You will use this in the next section to create the workshop database before loading data.

Quick Reference

Navigation commands (\l, \dt, \dn, \d <table>, \du, etc.) are covered hands-on in the Load Data section.

Load Data — Restore the Sample Database

In this section you will restore a sample e-commerce database (orders_demo) with four tables and ~410K rows, then explore its schema to understand the data you will work with throughout the rest of the workshop.

Step 1 — SSH into the Jumpbox

Connect to the jumpbox VM using the public IP from your deployment output:

ssh <vmUsername>@<jumpbox-public-ip>

Replace <vmUsername> and <jumpbox-public-ip> with the values from your Bicep deployment output.

Step 2 — Connect to PostgreSQL with psql

From the jumpbox, connect to the default postgres database:

psql -h <postgresql-fqdn> -U <pgadmin> -d postgres

You should see the postgres=> prompt.

Step 3 — Explore the Server Before Loading Data

Before restoring any data, run the following meta-commands to understand what is already on the server. These are psql backslash commands — they are not SQL; they are interpreted by the psql client itself.

psql session skills (\timing, \x, \watch, \conninfo, \i, \?) are covered in the previous psql: The PostgreSQL Command-Line Client section. This step focuses on navigation commands that explore what is on the server.

Tip: Want to see the SQL behind any \ command? Run \set ECHO_HIDDEN on in psql — it will print the underlying query before each result. This is useful for learning and for reproducing meta-commands in other SQL clients like VS Code.

3.1 — List all databases

\l

What it does: Lists every database in the PostgreSQL cluster, including the owner, encoding, collation, and access privileges. You will see the default databases (postgres, azure_maintenance, azure_sys). After the restore in Step 4 you will see orders_demo here as well.

3.2 — List schemas

\dn

What it does: Lists all schemas in the current database. By default you will see public. Schemas are namespaces that let you organise tables, views, and functions within a single database.

3.3 — List all tables in all schemas

\dt *.*

What it does: The wildcard pattern *.* means “every table in every schema.” This shows you system catalog tables (in pg_catalog and information_schema) plus any user tables in public. Use this to get a quick inventory of what exists.

3.4 — List all views in all schemas

\dv *.*

What it does: Same idea as \dt but for views. Views are stored queries that act like virtual tables. You will see many built-in system views in pg_catalog and information_schema.

3.5 — List all indexes in all schemas

\di *.*

What it does: Lists every index across all schemas. Indexes speed up queries by providing fast lookup paths. Notice which tables have indexes and which do not — this will be relevant when you run the heavy queries in the next section.

3.6 — Additional useful meta-commands

Tip: Run \timing now so that every query you run from this point forward shows how long it took.

Step 4 — Download and Restore the Sample Database

4.1 — Exit psql

\q

4.2 — Download the dump file

The workshop uses a pre-built custom-format dump that contains four tables with realistic e-commerce data (~410K rows total).

curl -L -O "https://pg.azure-workshops.cloud/database/orders_demo.dump"

Verify the file downloaded correctly:

ls -lh orders_demo.dump

You should see a file of roughly 5–10 MB. If the file is missing or 0 bytes, check the URL and try again.

4.3 — Create the target database

psql -h <postgresql-fqdn> -U <pgadmin> -d postgres -c "CREATE DATABASE orders_demo;"

Tip: If you configured .pg_azure and .pgpass in the previous section, you can omit -h and -U and you won’t be prompted for a password:

psql -c "CREATE DATABASE orders_demo;"

4.4 — Restore the dump with pg_restore

pg_restore -h <postgresql-fqdn> -U <pgadmin> -d orders_demo --no-owner --no-privileges --verbose orders_demo.dump

With .pg_azure and .pgpass configured:

pg_restore -d orders_demo --no-owner --no-privileges --verbose orders_demo.dump

Flag reference:

• --no-owner — skip ownership assignment (avoids errors when the original owner doesn’t exist on this server)
• --no-privileges — skip privilege (GRANT/REVOKE) statements from the source
• --verbose — print progress as each object is restored

You should see output listing each table and index being created, followed by data loading via COPY. If you see errors about roles not existing, they are safe to ignore (that’s what --no-owner handles).

4.5 — Verify the restore succeeded

Connect to the new database and confirm the tables exist with data:

psql -h <postgresql-fqdn> -U <pgadmin> -d orders_demo -c "
SELECT 'customers' AS tbl, COUNT(*) FROM customers
UNION ALL SELECT 'products', COUNT(*) FROM products
UNION ALL SELECT 'orders', COUNT(*) FROM orders
UNION ALL SELECT 'order_items', COUNT(*) FROM order_items;"

Expected output:

    tbl     | count
------------+--------
 customers  |  10000
 products   |    500
 orders     | 100000
 order_items| 300000

If you see all four tables with the expected row counts, the restore was successful.

Step 5 — Explore the Restored Database

Connect to the new database interactively:

psql -h <postgresql-fqdn> -U <pgadmin> -d orders_demo

5.1 — Confirm the tables exist

\dt

You should see four tables:

5.2 — Inspect table structures

\d customers
\d products
\d orders
\d order_items

Pay attention to data types, primary keys, and whether any foreign key constraints exist.

5.3 — Check row counts

SELECT 'customers' AS table_name, COUNT(*) AS rows FROM customers
UNION ALL
SELECT 'products', COUNT(*) FROM products
UNION ALL
SELECT 'orders', COUNT(*) FROM orders
UNION ALL
SELECT 'order_items', COUNT(*) FROM order_items;

5.4 — Check table sizes

SELECT relname AS table_name,
       pg_size_pretty(pg_total_relation_size(oid)) AS total_size,
       pg_size_pretty(pg_relation_size(oid)) AS table_size,
       pg_size_pretty(pg_indexes_size(oid)) AS index_size
FROM pg_class
WHERE relname IN ('customers','products','orders','order_items')
ORDER BY pg_total_relation_size(oid) DESC;

5.5 — List indexes

\di

Note: You will see only primary-key indexes. There are no additional indexes on foreign keys or commonly queried columns — this is intentional. The demo queries in the next section rely on sequential scans to generate CPU load.

5.6 — Explore sample data

-- Peek at customers
SELECT * FROM customers LIMIT 5;

-- Product categories
SELECT category, COUNT(*) AS count FROM products GROUP BY category ORDER BY count DESC;

-- Order status distribution
SELECT status, COUNT(*) AS count FROM orders GROUP BY status ORDER BY count DESC;

-- Most recent orders
SELECT order_id, customer_id, order_date, total_amount, status
FROM orders ORDER BY order_date DESC LIMIT 10;

-- Average items per order
SELECT ROUND(AVG(item_count), 2) AS avg_items_per_order
FROM (SELECT order_id, COUNT(*) AS item_count FROM order_items GROUP BY order_id) sub;

5.7 — Check for missing indexes (useful diagnostic)

SELECT relname AS table,
       seq_scan, seq_tup_read,
       idx_scan, idx_tup_fetch,
       CASE WHEN seq_scan > 0
            THEN ROUND(seq_tup_read::numeric / seq_scan, 0)
            ELSE 0
       END AS avg_rows_per_seq_scan
FROM pg_stat_user_tables
ORDER BY seq_tup_read DESC;

This shows how many sequential scans vs. index scans each table has received. After running the workload queries in the next section, revisit this to see the impact.

Step 6 — Enable pg_stat_statements

pg_stat_statements tracks execution statistics for all SQL statements. It is required for Query Performance Insight in the Azure Portal and for the Monitoring and Index Tuning sections later in the workshop. Set it up now so it collects data from the start.

1. Go to Azure Portal → your PostgreSQL server → Server parameters
2. Search for shared_preload_libraries → ensure pg_stat_statements is checked
3. Search for pg_stat_statements.track → set to ALL
4. Click Save — this requires a server restart

After the restart, connect from the jumpbox and create the extension:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Verify it is working:

SELECT calls, query FROM pg_stat_statements LIMIT 5;

Run Demo Workload — Break Things on Purpose

In this section you will run a set of intentionally unoptimised, CPU-heavy queries against the orders_demo database you restored in the previous section. The goal is to generate realistic workload that you will observe in the Monitoring section and fix in the Index Tuning Lab.

Prerequisite: You should have the orders_demo database restored from the Load Data section and be connected from the jumpbox.

Connect to the database if you are not already:

psql -h <postgresql-fqdn> -U <pgadmin> -d orders_demo

Step 1 — Run CPU-Heavy Demo Queries

Important: Enable timing first so you can see how long each query takes:

\timing

These queries are intentionally unoptimised — they trigger full sequential scans, large sorts, and heavy computation. This is exactly the kind of workload you will observe in the next section — Monitoring PostgreSQL with Azure Portal.

Query 1 — Full Cross-Join Aggregation (extreme CPU)

SELECT c.city, c.country, p.category,
       COUNT(DISTINCT o.order_id) AS total_orders,
       SUM(oi.quantity * oi.unit_price * (1 - oi.discount/100)) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
GROUP BY c.city, c.country, p.category
ORDER BY revenue DESC;

Why it is heavy: This joins all four tables (10K × 100K × 300K × 500 rows of data in the pipeline) and groups by three columns. The COUNT(DISTINCT ...) forces a sort-based deduplication for every group. With no indexes on the join columns other than primary keys, the planner must do multiple sequential scans and hash joins, then a large sort for the ORDER BY.

Query 2 — Correlated Subquery (no index, sequential scans)

SELECT c.customer_id, c.first_name, c.last_name,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS order_count,
       (SELECT COALESCE(SUM(total_amount),0) FROM orders o WHERE o.customer_id = c.customer_id) AS lifetime_value
FROM customers c
ORDER BY lifetime_value DESC
LIMIT 100;

Why it is heavy: For each of the 10,000 customers, PostgreSQL executes two subqueries against the 100,000-row orders table. Without an index on orders.customer_id, each subquery triggers a full sequential scan — resulting in ~20,000 sequential scans of the orders table in total.

Query 3 — Window Functions Over Large Dataset (memory + CPU)

SELECT order_id, customer_id, order_date, total_amount,
       SUM(total_amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total_amount DESC) AS rank_by_amount,
       AVG(total_amount) OVER (PARTITION BY shipping_country ORDER BY order_date ROWS BETWEEN 100 PRECEDING AND CURRENT ROW) AS moving_avg
FROM orders;

Why it is heavy: Three separate window functions each require sorting the full 100,000-row orders table by different partition/order keys. The running_total computes a cumulative sum per customer; rank_by_amount assigns a row number per customer by descending amount; moving_avg computes a sliding 101-row average across shipping countries. PostgreSQL may need to materialise intermediate sort results to temp files if work_mem is limited.

Query 4 — Heavy Text Computation + Sort (CPU + temp disk)

SELECT c.email, md5(c.email || o.order_id::TEXT) AS hash_key,
       string_agg(p.product_name, ', ' ORDER BY oi.unit_price DESC) AS products_bought
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
GROUP BY c.email, o.order_id
ORDER BY hash_key;

Why it is heavy: The md5() function computes a hash for every row in the join result (~300,000 rows). string_agg(... ORDER BY ...) sorts product names within each group by descending price. The final ORDER BY hash_key sorts all ~100,000 result groups by a computed hash — the result is essentially random, which defeats any natural ordering and forces a full sort.

Query 5 — Repeated Sequential Scan Loop (sustained CPU for ~30s+)

DO $$
BEGIN
  FOR i IN 1..20 LOOP
    PERFORM COUNT(*) FROM orders o
    JOIN order_items oi ON oi.order_id = o.order_id
    WHERE md5(o.status || oi.quantity::TEXT) LIKE '00%';
  END LOOP;
END $$;

Why it is heavy: This PL/pgSQL anonymous block runs the same expensive query 20 times in a tight loop. Each iteration joins 100K orders with 300K order items, computes md5() on every joined row, and then filters by a text pattern. The LIKE '00%' filter on the hash cannot use any index, so every iteration is a full sequential scan + hash join + function evaluation. The result is sustained, constant CPU utilisation for tens of seconds.

Understanding the syntax:

Tip: Increase the loop count to 50 or 100 for longer sustained load:

DO $$
BEGIN
  FOR i IN 1..100 LOOP
    PERFORM COUNT(*) FROM orders o
    JOIN order_items oi ON oi.order_id = o.order_id
    WHERE md5(o.status || oi.quantity::TEXT) LIKE '00%';
  END LOOP;
END $$;

Query 6 — Large Temp-Table Sort + Distinct (IOPS + memory pressure)

SELECT DISTINCT ON (customer_id)
       customer_id, order_id, order_date, total_amount
FROM orders
ORDER BY customer_id, total_amount DESC, order_date DESC;

Why it is heavy: DISTINCT ON requires the result to be sorted by the grouping column (customer_id) and then by the tie-breaking columns (total_amount DESC, order_date DESC). This forces a full sort of all 100,000 orders. If the sort does not fit in work_mem, PostgreSQL spills to temp files on disk, creating I/O pressure.

Step 2 — Generate Maximum Load (Concurrent Execution)

For the best demonstration during monitoring exercises, run multiple queries at the same time from separate psql sessions.

Open three SSH sessions to the jumpbox, connect to orders_demo in each, then run:

Running these concurrently will show multiple active backends, high CPU utilisation, temp file usage, and sequential-scan-heavy workload in your monitoring dashboards.

Step 3 — Observe the Impact

After running the workload, check the damage:

Active queries

SELECT pid, state, query_start, LEFT(query, 80) AS query_snippet
FROM pg_stat_activity
WHERE datname = 'orders_demo' AND state != 'idle'
ORDER BY query_start;

Sequential scan statistics (revisit from the Load Data section)

SELECT relname AS table,
       seq_scan, seq_tup_read,
       idx_scan, idx_tup_fetch
FROM pg_stat_user_tables
ORDER BY seq_tup_read DESC;

How to read this output:

Rule of thumb: If seq_tup_read is orders of magnitude larger than idx_tup_fetch on a table, that table almost certainly needs an index on the columns used in WHERE/JOIN clauses. You will fix this in the Index Tuning Lab.

Temp file usage

SELECT datname, temp_files, pg_size_pretty(temp_bytes) AS temp_size
FROM pg_stat_database
WHERE datname = 'orders_demo';

How to read this output:

Total database size

SELECT pg_size_pretty(pg_database_size('orders_demo')) AS db_size;

These statistics feed directly into the next section — Monitoring PostgreSQL with Azure Portal. Leave them as-is — do not reset the stats yet.

Monitoring PostgreSQL with Azure Portal

In the previous section you ran six heavy queries against the orders_demo database and generated concurrent load. Now you will use the Azure Portal to see exactly what those queries did to your server — CPU spikes, memory pressure, IOPS saturation, temp file spills, and long-running sessions.

By the end of this section you will be able to:

• Navigate the built-in metrics for Azure Database for PostgreSQL Flexible Server
• Correlate a metric spike to a specific query or behaviour
• Use Query Performance Insight to find the most expensive queries
• Configure an alert rule so you get notified before problems escalate
• Use diagnostic settings to send logs to Log Analytics for deeper analysis

Prerequisite: You should have run the demo workload from the Run Demo Workload section. The metrics and query stats shown here are generated by that workload. If you skipped it, go back and run at least Queries 1, 3, and 5 concurrently before continuing.

Step 1 — Open the Metrics Blade

1. Go to the Azure Portal → your resource group → click your PostgreSQL Flexible Server
2. In the left menu, under Monitoring, click Metrics

You will see a chart area with a dropdown to select metrics. This is Azure Monitor Metrics Explorer — it works the same way for all Azure resources, but the available metrics are specific to PostgreSQL.

Step 2 — Observe CPU Impact

2.1 — Add the CPU metric

1. Click Add metric
2. Metric namespace: leave default
3. Metric: CPU percent
4. Aggregation: Max
5. Set the time range to Last 1 hour (top-right)

What to look for: You should see a clear spike corresponding to when you ran the concurrent workload (Queries 1, 3, and 5). If you ran Query 5 with a loop count of 50 or 100, you will see a sustained plateau at high CPU for the duration.

Link to the workload:

2.2 — Split by dimension (optional)

Click Apply splitting → split by None (Flexible Server doesn’t split CPU by backend, but this is useful for other metrics like connections).

Tip: Click the pin icon to pin this chart to a dashboard. When running workshops or demos, having a pre-built dashboard saves time.

Step 3 — Observe Memory Pressure

1. Click Add metric (or start a new chart)
2. Metric: Memory percent
3. Aggregation: Max

What to look for: Memory spikes correlate with:

• Query 3 (window functions): PostgreSQL materialises sort results in memory. If work_mem is sufficient, you see a memory spike. If not, it spills to disk (you will see that in Step 5 instead).
• Query 1 (hash joins): Each hash join builds an in-memory hash table.

Healthy range: < 75%. If you consistently see > 85%, the server SKU may be too small for the workload, or shared_buffers / work_mem are misconfigured.

Step 4 — Observe IOPS and Storage

Add these metrics to see storage behaviour:

Link to the workload:

Important: Azure Flexible Server has IOPS limits based on SKU and storage size. If your queries hit the IOPS ceiling, the server throttles I/O and queries slow down dramatically. You can see this by comparing Read IOPS against the provisioned IOPS limit shown in the Overview blade.

Step 5 — Observe Temp Files and Connections

These less obvious metrics reveal important performance details:

5.1 — Temp files

1. Metric: Temp Files Size (under the PostgreSQL-specific metrics)
2. Aggregation: Max

Temp files are created when PostgreSQL runs a sort or hash that exceeds work_mem. This is a direct signal that your queries are too heavy for the current work_mem setting.

Link to the workload:

• Query 3 (window functions: 3 sorts on 100K rows) — most likely temp file generator
• Query 4 (sort by computed hash) — random sort order forces spill
• Query 6 (DISTINCT ON with multi-column sort) — spills if table is larger than work_mem

What to do about it: Increasing work_mem reduces temp file usage but increases memory consumption per-session. The trade-off is:

Higher work_mem → fewer temp files → faster sorts → more RAM per connection
Lower work_mem  → more temp files → slower sorts → less RAM per connection

For 10 concurrent connections with work_mem = 64MB, PostgreSQL may use up to 640MB just for sorts.

5.2 — Active connections

1. Metric: Active Connections
2. Aggregation: Max

What to look for: When you ran 3 concurrent queries in Step 7 of the workload section, you should see active connections jump from 1 to 3 (or more). If connections spike beyond what you expect, it may indicate:

• Connection pooling is not configured (each app request opens a new connection)
• A connection leak (connections are opened but never closed)

Step 6 — Enable pg_stat_statements

pg_stat_statements is a standard PostgreSQL extension that collects per-query execution statistics (calls, time, rows). It works on any PostgreSQL installation and is the foundation for query performance analysis.

1. Go to Server parameters in the left menu
2. Allow the extension first:
   • Search for azure.extensions
   • Click the dropdown — this shows all extensions available on Flexible Server
   • Find and check pg_stat_statements
   • Click Save (this does not require a restart)
3. Now enable the extension in the server:
   • Search for shared_preload_libraries and ensure pg_stat_statements is checked
   • Search for pg_stat_statements.track and set it to ALL
4. Click Save — this requires a server restart

Already done? If you enabled pg_stat_statements in Step 6 of the Load Data section, skip to Step 7.

After the restart, connect from the jumpbox and create the extension:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Verify it’s working:

SELECT calls, total_exec_time, mean_exec_time, rows, LEFT(query, 100) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

You should see the demo workload queries at the top of the list.

Step 7 — Configure Diagnostic Settings and Query Store (Logs to Log Analytics)

While Metrics Explorer shows numbers, diagnostic settings capture detailed logs including individual query executions, autovacuum runs, and connection events. This is also a prerequisite for Query Performance Insight (Step 8).

This step requires both Azure Query Store (server-side data collection) and diagnostic settings (export to Log Analytics). Query Store is an Azure-specific feature — it is separate from the standard pg_stat_statements extension configured in Step 6.

7.1 — Enable Query Store

1. Go to Server parameters in the left menu
2. Search for pg_qs.query_capture_mode and set it to ALL (or TOP)
3. Search for pgms_wait_sampling.query_capture_mode and set it to ALL
4. Click Save — this requires a server restart

Without these parameters, the diagnostic settings below will export empty log categories — and Query Performance Insight (Step 8) will show no data.

7.2 — Create a Log Analytics workspace (if you don’t have one)

az monitor log-analytics workspace create \
  --resource-group <resource-group> \
  --workspace-name pg-workshop-logs \
  --location uksouth

7.3 — Enable diagnostic settings

1. In the PostgreSQL server blade, click Diagnostic settings (under Monitoring)
2. Click + Add diagnostic setting
3. Name: pg-logs
4. Check the log categories:
   • PostgreSQL Sessions — connection/disconnection events
   • PostgreSQL Query Store Runtime — query execution statistics
   • PostgreSQL Query Store Wait Statistics — what queries waited on
5. Check AllMetrics
6. Destination: Send to Log Analytics workspace → select pg-workshop-logs
7. Click Save

Logs take 5–10 minutes to start appearing in Log Analytics. Re-run a few demo workload queries to generate fresh data while you wait.

7.4 — Verify data is flowing

Before running any analytical queries, confirm that logs are arriving in your Log Analytics workspace. Go to your Log Analytics workspace → Logs and run:

AzureDiagnostics
| where ResourceProvider == "MICROSOFT.DBFORPOSTGRESQL"
| summarize count() by Category

You should see categories including PostgreSQLQueryStoreRuntimeStatistics, PostgreSQLQueryStoreWaitStatistics, and PostgreSQLSessions. If this returns no results, check that: (1) diagnostic settings in Step 7.2 are saved and pointing to this workspace, (2) Query Store is enabled in Step 7.1, (3) you’ve waited at least 5–10 minutes.

7.5 — Query the logs with KQL

Go to your Log Analytics workspace → Logs. Try these queries:

Slow queries (> 5 seconds):

AzureDiagnostics
| where ResourceProvider == "MICROSOFT.DBFORPOSTGRESQL"
| where Category == "PostgreSQLQueryStoreRuntimeStatistics"
| where mean_time_d > 5
| project TimeGenerated, db_name_s, query_id_d, calls_d, mean_time_d, total_time_d
| order by total_time_d desc
| take 20

Connection events:

AzureDiagnostics
| where ResourceProvider == "MICROSOFT.DBFORPOSTGRESQL"
| where Category == "PostgreSQLSessions"
| project TimeGenerated, event_s, user_s, client_ip_s, db_name_s
| order by TimeGenerated desc
| take 50

Wait events (what are queries waiting on):

AzureDiagnostics
| where ResourceProvider == "MICROSOFT.DBFORPOSTGRESQL"
| where Category == "PostgreSQLQueryStoreWaitStatistics"
| summarize total_wait_ms = sum(total_time_d * 1000) by event_s, query_id_d
| order by total_wait_ms desc
| take 20

No results? If the KQL queries return empty, verify: (1) pg_qs.query_capture_mode is set to ALL or TOP in Step 7.1, (2) diagnostic settings in Step 7.3 include the Query Store log categories, (3) you’ve waited at least 5–10 minutes after configuration.

Step 8 — Query Performance Insight

This is the most powerful built-in tool for finding slow queries without installing any extensions.

Prerequisites: Step 7 must be completed first. Query Performance Insight requires Query Store (pg_qs.query_capture_mode), Query Store Wait Sampling (pgms_wait_sampling.query_capture_mode), and a Log Analytics workspace with diagnostic settings configured. See the official prerequisites.

1. In the left menu, under Intelligent Performance, click Query Performance Insight
2. You will see tabs: Long running queries, Top queries by CPU, Top queries by IO

8.1 — Long running queries

This tab shows queries that took the longest wall-clock time. After the demo workload, you should see:

Note: Query Performance Insight normalises queries — it replaces literal values with $1, $2 placeholders so identical queries with different parameters are grouped together. The DO $$ ... $$ block appears as a single query.

8.2 — Top queries by CPU

Switch to the CPU tab. This shows queries ranked by total CPU time consumed.

Expected top entries after the workload:

8.3 — Top queries by IO

Switch to the IO tab. This ranks queries by blocks read from storage.

Expected top entries:

Tip: Click on any query bar to see the full query text and its execution timeline.

Step 9 — Create an Alert Rule

Set up an alert so you are notified when CPU exceeds a threshold — this simulates a production monitoring setup.

9.1 — Create the alert

1. In the PostgreSQL server blade → Alerts (under Monitoring)
2. Click + Create → Alert rule
3. Condition:
   • Signal: CPU percent
   • Operator: Greater than
   • Threshold: 80
   • Aggregation type: Maximum
   • Aggregation granularity: 5 minutes
   • Frequency of evaluation: 1 minute
4. Click Next: Actions

9.2 — Create an action group

1. Click + Create action group
2. Name: workshop-alerts
3. Notification type: Email/SMS/Push/Voice
4. Enter your email address
5. Click Review + create → Create

9.3 — Complete the alert rule

1. Alert rule name: High CPU Alert
2. Severity: 2 - Warning
3. Click Review + create → Create

9.4 — Test the alert

Go back to the jumpbox and re-run Query 5 with a higher loop count:

\c orders_demo

DO $$
BEGIN
  FOR i IN 1..100 LOOP
    PERFORM COUNT(*) FROM orders o
    JOIN order_items oi ON oi.order_id = o.order_id
    WHERE md5(o.status || oi.quantity::TEXT) LIKE '00%';
  END LOOP;
END $$;

Within a few minutes, you should receive an email alert that CPU exceeded 80%.

Clean up: After testing, you can disable the alert rule to avoid further notifications. Go to Alerts → Alert rules → click the rule → Disable.

Step 10 — Correlating Metrics to Queries (Summary)

This is the key skill — when you see a metric spike in the portal, how do you trace it back to a specific query?

Here is how each demo query maps to the metrics you observed:

Step 11 — Azure Monitor Workbooks (Optional — Instructor Demo)

Azure provides pre-built Workbooks for PostgreSQL that combine multiple metrics and logs into a single dashboard.

1. In the PostgreSQL blade → Workbooks (under Monitoring)
2. Browse the gallery — look for:
   • PostgreSQL Flexible Server Overview — CPU, memory, connections, IOPS in one view
   • Query Performance — visualises pg_stat_statements data over time
3. Click on a workbook to open it. You can filter by time range and server instance.

When to use Workbooks vs. Metrics Explorer:

• Metrics Explorer — ad-hoc investigation, “what is happening right now”
• Workbooks — pre-built dashboards for ongoing monitoring, good for handoff to ops teams
• Query Performance Insight — developer-focused, “which query is the problem”
• Log Analytics (KQL) — deepest level, cross-correlate logs with metrics, custom alerting

Step 12 — Recommended Server Parameters for Monitoring

Before leaving this section, verify these server parameters are enabled. They control what telemetry PostgreSQL and Azure collect:

1. Go to Server parameters in the portal
2. Search for and verify each parameter:

Azure-Specific Parameters (Flexible Server only)

These parameters are unique to Azure Database for PostgreSQL — Flexible Server and feed data into Azure Monitor. They do not exist in community PostgreSQL.

Standard PostgreSQL Parameters

These are community PostgreSQL parameters that work on any PostgreSQL installation. They control internal statistics collection and logging.

Note: Changing shared_preload_libraries requires a server restart. All other parameters above can be applied dynamically.

What You Learned

In this section you:

1. Observed CPU, memory, IOPS, and temp file metrics in Azure Metrics Explorer and correlated each spike to a specific demo query
2. Enabled pg_stat_statements to collect per-query execution statistics (standard PostgreSQL)
3. Configured Query Store and diagnostic settings to send PostgreSQL logs to Log Analytics and wrote KQL queries to analyse them
4. Used Query Performance Insight to identify the top queries by CPU consumption and I/O without needing SSH access to the server
5. Created an alert rule that sends a notification when CPU exceeds a threshold
6. Learned the triage workflow: Metric spike → timestamp → Query Performance Insight → EXPLAIN ANALYZE → fix → verify

These skills apply directly to production operations. The demo workload intentionally created the same problems you will see in real applications — missing indexes, sequential scans, temp file spills, and sustained CPU from poorly written queries. The next sections (Database Profiling, MVCC, Statistics & Query Planning) will teach you to diagnose and fix these problems from the PostgreSQL side.

Administration & Access Control

Managing PostgreSQL DB

Note: The Azure Portal is updated frequently. Screenshots in this section may look slightly different from what you see, but the functionality and steps are the same — you will be able to perform all tasks described here.

Managing Compute and Storage

Navigate to Compute + Storage to alter storage and compute settings. You can also change the backup retention period here.

Note: Increasing the compute size may incur additional costs. Only adjust if necessary.

Managing Server Parameters

PostgreSQL’s behaviour is controlled by hundreds of server parameters (also called GUCs — Grand Unified Configuration). These control everything from memory allocation (shared_buffers, work_mem) to query planning (random_page_cost), logging (log_min_duration_statement), replication, autovacuum thresholds, and more.

In a self-managed PostgreSQL installation, you would edit postgresql.conf directly. On Azure Database for PostgreSQL Flexible Server, you do not have access to configuration files. Instead, you manage parameters through the Azure Portal (Server parameters blade) or via the Azure CLI / REST API.

Changes made in the portal apply as the server-level default — they affect all databases, roles, and sessions unless overridden at a lower level.

Parameter types: Dynamic, Static, and Read-Only

Not all parameters behave the same way when you change them:

Tip: In the portal, the Server parameters blade shows a note next to each parameter indicating whether it is dynamic or requires a restart.

Scope levels

You can override the server-level default at narrower scopes. Each level inherits from the one above unless explicitly overridden:

To check the current effective value of any parameter in your session:

SHOW work_mem;

To see all non-default parameter values:

SELECT name, setting, source
FROM pg_settings
WHERE source != 'default'
ORDER BY name;

To enable PgBouncer, type pgbouncer in the search box and set its value to TRUE:

Click Save and wait for the deployment to complete successfully:

Once you see the success screen, access PostgreSQL through port 6432 on your VM:

psql -p 6432

Want to go deeper? For pool modes, pgbench load testing, and PgBouncer monitoring commands, see Connection Pooling with PgBouncer.

Applying Server Locks

Navigate to Locks:

Click +Add, enter a lock name of your choice, and select the lock type Delete:

If you attempt to delete the server, you should see an error similar to the following:

Roles and Permissions

In this section you will learn how PostgreSQL manages access control through its role system — creating roles, granting privileges, understanding inheritance, and applying least-privilege principles.

Prerequisite: You should have the orders_demo database restored from the Load Data section.

What You Will Build

By the end of this lab you will have built a complete access model for the orders_demo database with three different user profiles, each demonstrating a different security pattern.

Why this matters: In production, you should never give every user the admin credentials you created during deployment. Instead, create group roles that hold privileges, then assign users to those groups. This is the same principle as Active Directory groups or IAM roles — but implemented at the PostgreSQL level.

Key concepts you will practice:

1. Roles are the only access object — PostgreSQL has no separate “users” and “groups.” Everything is a role. CREATE USER is just an alias for CREATE ROLE ... LOGIN.
2. INHERIT vs NOINHERIT — controls whether a member automatically gets the parent role’s privileges (like a normal group) or must explicitly activate them (like sudo).
3. Privilege codes — the compact letter codes (r, a, w, d, D, x, t) that PostgreSQL uses to represent SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, and TRIGGER.
4. Default privileges — ensuring future tables get the right grants automatically, not just tables that exist today.
5. Schema isolation — revoking the overly permissive default on the public schema.

The access model is split into three paths — one per user profile:

1) app_team path (dev_user)

Value by step:

1. Establishes a controlled admin starting point for governance.
2. Centralizes write permissions in one reusable group role.
3. Connects a real user to the role with automatic inheritance.
4. Delivers immediate app productivity without manual role switching.

Real-world scenario: Application service accounts, backend developers — anyone who needs read/write access and should get it automatically on connect.

2) analyst_user path (readonly)

Value by step:

1. Keeps role creation in a single trusted admin workflow.
2. Encapsulates reporting permissions as SELECT-only.
3. Grants analysts safe default access through inheritance.
4. Prevents accidental writes while enabling BI/reporting use cases.

Real-world scenario: BI analysts, reporting dashboards, read replica users — anyone who should see data but never modify it.

3) contractor_user path (NOINHERIT)

Value by step:

1. Defines a privileged role, but does not expose it by default.
2. Associates contractor identity with explicit guardrails (NOINHERIT).
3. Starts each session in low-privilege mode to reduce blast radius.
4. Requires intentional elevation (SET ROLE) for controlled operations.
5. Enables temporary write capability with full operator intent.

Real-world scenario: External contractors, break-glass admin accounts, audited access — anyone who can have elevated access but must consciously activate it each time.

Understanding Roles in PostgreSQL

PostgreSQL has a single concept for managing access: the role. There are no separate “users” and “groups” — everything is a role. The CREATE USER and CREATE GROUP commands are just convenience aliases:

This is fundamentally different from Oracle (where users and roles are distinct objects) and SQL Server (where logins, users, and roles are separate layers).

Quick comparison for customers:

• PostgreSQL: one role object type; LOGIN and NOLOGIN define behavior; membership + INHERIT control access.
• Oracle: users and roles are separate objects; privileges can be direct or via role.
• SQL Server: login is server-level, user is database-level, roles are assigned per database.

Azure Flexible Server Role Hierarchy

On Azure Database for PostgreSQL Flexible Server, the admin user you created during deployment is not a superuser. Instead, it is a member of the azure_pg_admin role, which has most — but not all — superuser privileges.

Connect to the server from the jumpbox:

psql -h <postgresql-fqdn> -U <pgadmin> -d orders_demo

View the existing roles:

\du

You will see something like:

                                       List of roles
    Role name     |                         Attributes                         | Member of
------------------+------------------------------------------------------------+------------------
 azure_pg_admin   | Create role, Create DB, Bypass RLS, Replication            | {}
 <your-admin>     | Create role, Create DB                                     | {azure_pg_admin}
 azuresu          | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Key points:

• azuresu — the true superuser, managed by Azure. You cannot use this role.
• azure_pg_admin — the highest role available to you. Your admin user is a member of this role.
• <your-admin> — your admin user. It can create roles, create databases, and grant privileges, but it cannot load extensions that require superuser or access the file system.

Step 1 — Create Application Roles

A good practice is to create a group role (cannot login) that holds privileges, then add login roles (users) as members.

-- Create a group role for the application team
CREATE ROLE app_team NOLOGIN;

-- Create a read-only role
CREATE ROLE readonly NOLOGIN;

Now create two users and assign them to roles:

-- Developer: inherits app_team privileges automatically
CREATE ROLE dev_user LOGIN PASSWORD 'Workshop#Dev1' IN ROLE app_team INHERIT;

-- Analyst: inherits readonly privileges automatically
CREATE ROLE analyst_user LOGIN PASSWORD 'Workshop#Read1' IN ROLE readonly INHERIT;

-- Contractor: member of app_team but does NOT inherit privileges automatically
CREATE ROLE contractor_user LOGIN PASSWORD 'Workshop#Ext1' IN ROLE app_team NOINHERIT CONNECTION LIMIT 2;

Verify the roles:

\du

    Role name        |                 Attributes                      | Member of
---------------------+-------------------------------------------------+------------------
 analyst_user        |                                                 | {readonly}
 app_team            | Cannot login                                    | {}
 contractor_user     | No inheritance, 2 connections                   | {app_team}
 dev_user            |                                                 | {app_team}
 readonly            | Cannot login                                    | {}

Step 2 — Grant Privileges on the orders_demo Database

You should already be connected to the orders_demo database. If not:

\c orders_demo

Grant privileges to each group role:

-- app_team gets full access to all tables and sequences in public
GRANT USAGE ON SCHEMA public TO app_team;
GRANT ALL ON ALL TABLES IN SCHEMA public TO app_team;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO app_team;

-- readonly gets SELECT only
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

Why GRANT USAGE ON SCHEMA? In PostgreSQL, even if you have table-level privileges, you also need USAGE on the schema that contains the table. This is a common gotcha for people coming from Oracle or SQL Server where schema access works differently.

Step 3 — Test INHERIT vs NOINHERIT

This is the most important concept in this section. Use SET ROLE to impersonate each user and see the difference.

First, grant yourself the ability to switch to these roles:

-- Run as your admin user
GRANT dev_user TO <your-admin>;
GRANT analyst_user TO <your-admin>;
GRANT contractor_user TO <your-admin>;

Test dev_user (INHERIT):

SET ROLE dev_user;

-- This should work — dev_user inherits app_team privileges
SELECT customer_id, first_name, last_name, email FROM customers LIMIT 5;

-- This should also work — app_team has ALL privileges
INSERT INTO customers (first_name, last_name, email, city, country)
VALUES ('Test', 'User', 'test@workshop.dev', 'London', 'United Kingdom');

-- Switch back
RESET ROLE;

Test analyst_user (INHERIT, readonly):

SET ROLE analyst_user;

-- SELECT works — analyst_user inherits readonly privileges
SELECT status, COUNT(*) FROM orders GROUP BY status;

-- INSERT fails — readonly only has SELECT
INSERT INTO customers (first_name, last_name, email, city, country)
VALUES ('Bad', 'Insert', 'nope@fail.dev', 'London', 'United Kingdom');
-- ERROR: permission denied for table customers

RESET ROLE;

Test contractor_user (NOINHERIT):

SET ROLE contractor_user;

-- This FAILS — contractor_user does NOT inherit app_team privileges
SELECT * FROM customers LIMIT 1;
-- ERROR: permission denied for table customers

Why? contractor_user was created with NOINHERIT. Even though it is a member of app_team, it does not automatically receive app_team’s privileges. The user must explicitly activate the role:

-- Explicitly activate the parent role
SET ROLE app_team;

-- Now it works
SELECT * FROM customers LIMIT 5;

RESET ROLE;

When to use NOINHERIT: For users who should have access but must consciously “elevate” to use it — similar to sudo on Linux or runas on Windows. Useful for contractors, automated accounts, or break-glass scenarios.

Step 4 — View and Read Privilege Codes

Run the following to see all granted privileges:

RESET ROLE;

\dp

You will see output like:

                                  Access privileges
 Schema |    Name      | Type  |       Access privileges        | ...
--------+--------------+-------+--------------------------------+
 public | customers    | table | <admin>=arwdDxt/<admin>       +|
        |              |       | app_team=arwdDxt/<admin>      +|
        |              |       | readonly=r/<admin>             |
 public | orders       | table | <admin>=arwdDxt/<admin>       +|
        |              |       | app_team=arwdDxt/<admin>      +|
        |              |       | readonly=r/<admin>             |
 public | order_items  | table | <admin>=arwdDxt/<admin>       +|
        |              |       | app_team=arwdDxt/<admin>      +|
        |              |       | readonly=r/<admin>             |
 public | products     | table | <admin>=arwdDxt/<admin>       +|
        |              |       | app_team=arwdDxt/<admin>      +|
        |              |       | readonly=r/<admin>             |

Reading the privilege codes:

The format is role=privileges/grantor. So app_team=arwdDxt/<admin> means: app_team has SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, and TRIGGER on this table, granted by your admin user.

Step 5 — REVOKE Privileges

A realistic scenario: the orders table holds financial data and should be read-only for the application team. Remove INSERT/UPDATE/DELETE from app_team on orders, keeping only SELECT:

REVOKE INSERT, UPDATE, DELETE ON TABLE orders FROM app_team;

Verify:

SET ROLE dev_user;

-- SELECT still works
SELECT order_id, customer_id, total_amount, status FROM orders LIMIT 5;

-- INSERT now fails — orders is protected
INSERT INTO orders (customer_id, order_date, total_amount, status)
VALUES (1, now(), 99.99, 'pending');
-- ERROR: permission denied for table orders

-- But other tables still allow writes
INSERT INTO customers (first_name, last_name, email, city, country)
VALUES ('Another', 'Test', 'another@workshop.dev', 'Dublin', 'Ireland');

RESET ROLE;

Check the privilege codes again:

\dp orders

You should see app_team=rDxt/<admin> — the a, w, and d codes are gone.

Step 6 — Default Privileges for Future Tables

The GRANT ALL ON ALL TABLES command only affects tables that already exist. If you create a new table later, the grants are not applied automatically. This catches many people by surprise.

Fix this with ALTER DEFAULT PRIVILEGES:

-- Any future table created by your admin in the public schema
-- will automatically grant SELECT to readonly and ALL to app_team
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO readonly;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT ALL ON TABLES TO app_team;

Test it:

-- Create a new table
CREATE TABLE test_defaults (id serial, name text);

-- Check privileges — readonly and app_team should already have access

\dp test_defaults

You should see readonly=r/<admin> and app_team=arwdDxt/<admin> without any explicit GRANT.

Clean up:

DROP TABLE test_defaults;

Step 7 — Schema-Level Isolation

For a more realistic setup, create a separate schema for the application and restrict the public schema:

-- Create an application schema
CREATE SCHEMA app AUTHORIZATION app_team;

-- Revoke default public access (PostgreSQL grants USAGE + CREATE on public to everyone by default)
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

About the PUBLIC keyword: In PostgreSQL, PUBLIC (all-caps in GRANT/REVOKE context) means “every role.” By default, every role has USAGE and CREATE on the public schema — this is a well-known security concern. Revoking CREATE from PUBLIC is a recommended hardening step.

Step 8 — Verify Permissions Work End-to-End

Open a new psql session as dev_user to verify everything works without SET ROLE:

# On the jumpbox, open a new connection
psql -h <postgresql-fqdn> -U dev_user -d orders_demo

-- Should work (inherited from app_team)
SELECT COUNT(*) FROM customers;

-- Should fail (we revoked INSERT on orders from app_team in Step 5)
INSERT INTO orders (customer_id, order_date, total_amount, status)
VALUES (1, now(), 49.99, 'pending');
-- ERROR: permission denied for table orders

\q

Step 9 — Clean Up Roles

Switch back to your admin user and clean up:

psql -h <postgresql-fqdn> -U <pgadmin> -d orders_demo

-- Revoke privileges first (required before dropping roles)
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM app_team;
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM readonly;
REVOKE ALL ON ALL SEQUENCES IN SCHEMA public FROM app_team;
REVOKE USAGE ON SCHEMA public FROM app_team;
REVOKE USAGE ON SCHEMA public FROM readonly;

-- Re-grant CREATE on public schema (we revoked it in Step 7)
GRANT CREATE ON SCHEMA public TO PUBLIC;

-- Remove default privilege changes from Step 6
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  REVOKE SELECT ON TABLES FROM readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  REVOKE ALL ON TABLES FROM app_team;

-- Re-grant INSERT/UPDATE/DELETE on orders (we revoked in Step 5)
-- Not strictly needed since we're dropping the roles, but good practice
GRANT ALL ON ALL TABLES IN SCHEMA public TO <your-admin>;

-- Drop the schema we created
DROP SCHEMA IF EXISTS app;

-- Drop users first, then group roles
DROP ROLE IF EXISTS dev_user;
DROP ROLE IF EXISTS analyst_user;
DROP ROLE IF EXISTS contractor_user;
DROP ROLE IF EXISTS app_team;
DROP ROLE IF EXISTS readonly;

-- Remove the test rows we inserted during the lab
DELETE FROM customers WHERE email IN ('test@workshop.dev', 'another@workshop.dev');

Summary

Next: For row-level data filtering (restricting which rows a role can see, not just which tables), continue to the optional Row-Level Security section.

Row-Level Security (Optional)

This section is optional. Complete it at your own pace if time permits, or after the workshop as self-study. You can safely skip to Connection Pooling with PgBouncer.

Prerequisite: You should have completed the Roles and Permissions section. The roles (app_team, readonly, dev_user, analyst_user, contractor_user) must still exist. If you already ran the clean-up step, re-create them before proceeding.

Row-Level Security (RLS) limits which rows a role can see — not just which tables. This is essential for multi-tenant applications, compliance requirements, and data isolation scenarios where different users should see different subsets of the same table.

Why Not Use Views?

A common first instinct is to create a filtered view and grant access to the view instead of the base table:

-- The "view approach" — looks reasonable but has problems
CREATE VIEW shipped_orders AS
  SELECT * FROM orders WHERE status = 'shipped';

GRANT SELECT ON shipped_orders TO readonly;
REVOKE SELECT ON orders FROM readonly;

This works on the surface — analyst_user can query shipped_orders and sees only shipped rows. But it has serious weaknesses:

1. Views don’t enforce — they filter. If someone accidentally (or intentionally) re-grants SELECT ON orders to the role, the restriction evaporates. There is no enforcement at the engine level.

2. Views leak data through functions. A clever user can bypass the view by calling functions that access the underlying table:

-- If the view owner has access to the base table, the view
-- executes with the owner's privileges (SECURITY INVOKER is not the default)
-- A user with CREATE FUNCTION privilege could potentially:
CREATE FUNCTION leak() RETURNS SETOF orders AS $$
  SELECT * FROM orders;
$$ LANGUAGE sql SECURITY DEFINER;

3. Views multiply maintenance. Every filtering dimension (status, date range, country) requires a new view. Adding a column to the base table means updating every view.

4. Views can’t restrict writes. A SELECT-filtered view does nothing to stop INSERT, UPDATE, or DELETE on the base table if the role has those grants through another path.

Row-Level Security solves all of these — it adds an invisible, engine-enforced WHERE clause to every query, regardless of how the table is accessed.

Step 1 — Enable RLS on the Orders Table

Connect as your admin user:

psql -h <postgresql-fqdn> -U <pgadmin> -d orders_demo

-- Enable Row-Level Security on the orders table
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

Important: Table owners and members of azure_pg_admin bypass RLS by default. This is by design — the admin needs unrestricted access. To test RLS, you must switch to a non-owner role.

Step 2 — Create Policies

Policies define which rows each role can see. Multiple policies on the same table are additive (OR logic) — a row is visible if any matching policy allows it.

-- Policy 1: analyst_user can only see shipped orders
CREATE POLICY analyst_shipped_only ON orders
  FOR SELECT
  TO readonly
  USING (status = 'shipped');

-- Policy 2: app_team can see all orders (unrestricted)
CREATE POLICY app_team_full_access ON orders
  FOR ALL
  TO app_team
  USING (true)
  WITH CHECK (true);

Policy breakdown:

Step 3 — Test RLS

-- Test as analyst_user (member of readonly)
SET ROLE analyst_user;

-- Only shipped orders are visible
SELECT status, COUNT(*) FROM orders GROUP BY status;

Expected output — only one row:

 status  | count
---------+-------
 shipped | XXXXX
(1 row)

-- Test as dev_user (member of app_team) — sees everything
SET ROLE dev_user;
SELECT status, COUNT(*) FROM orders GROUP BY status;

Expected output — all statuses:

  status   | count
-----------+-------
 shipped   | XXXXX
 pending   | XXXXX
 delivered | XXXXX
 cancelled | XXXXX
(4 rows)

RESET ROLE;

Step 4 — More Policy Examples

Date-based policy

Analyst can only see orders from the last 90 days:

-- Drop the previous analyst policy first
DROP POLICY analyst_shipped_only ON orders;

-- Replace with a date-based policy
CREATE POLICY analyst_recent_only ON orders
  FOR SELECT
  TO readonly
  USING (order_date >= CURRENT_DATE - INTERVAL '90 days');

Country-based policy

A regional role sees only customers in their region:

-- Enable RLS on customers
ALTER TABLE customers ENABLE ROW LEVEL SECURITY;

-- Only UK customers visible to readonly
CREATE POLICY uk_customers_only ON customers
  FOR SELECT
  TO readonly
  USING (country = 'United Kingdom');

Session variable policy (most flexible)

The application sets a variable per session, and the policy reads it dynamically:

-- Drop the static policy
DROP POLICY IF EXISTS uk_customers_only ON customers;

-- Create a dynamic policy that reads a session variable
CREATE POLICY region_filter ON customers
  FOR SELECT
  TO readonly
  USING (country = current_setting('app.current_region', true));

How this works in practice: The application sets app.current_region after connecting (via SET or a connection init script), and the policy automatically filters rows to that region. No view maintenance needed.

Test it:

SET ROLE analyst_user;

-- Without the variable set, current_setting returns NULL → no rows match
SELECT COUNT(*) FROM customers;
-- Returns 0

-- Application sets the region
SET app.current_region = 'United Kingdom';
SELECT COUNT(*) FROM customers;
-- Returns only UK customers

-- Change region
SET app.current_region = 'Germany';
SELECT COUNT(*) FROM customers;
-- Returns only German customers

RESET ROLE;

Step 5 — What Happens With No Matching Policy?

If RLS is enabled and a role has no policy that matches, it sees zero rows — deny-by-default:

-- contractor_user has no policy on orders
SET ROLE contractor_user;
SET ROLE app_team;  -- elevate (NOINHERIT)
SELECT COUNT(*) FROM orders;
-- Returns the full count because app_team has a permissive policy

RESET ROLE;

RLS vs Views — When to Use Each

Recommendation: Use RLS when you need enforced data isolation. Use views for convenience when the role has no direct table access anyway.

Step 6 — Clean Up

RESET ROLE;

-- Drop policies
DROP POLICY IF EXISTS app_team_full_access ON orders;
DROP POLICY IF EXISTS analyst_recent_only ON orders;
DROP POLICY IF EXISTS analyst_shipped_only ON orders;
DROP POLICY IF EXISTS region_filter ON customers;
DROP POLICY IF EXISTS uk_customers_only ON customers;

-- Disable RLS
ALTER TABLE orders DISABLE ROW LEVEL SECURITY;
ALTER TABLE customers DISABLE ROW LEVEL SECURITY;

Note: If you are done with both this section and the Roles and Permissions section, return to Step 9 in the previous section to clean up all the roles.

Summary

Connection Pooling with PgBouncer (Optional)

This section is optional. Complete it at your own pace if time permits, or after the workshop as self-study.

PostgreSQL creates a new process for every client connection. Each process consumes 5–10 MB of memory and a slot in the connection table. With hundreds of application instances opening connections, this becomes a bottleneck — even if most connections are idle.

Azure Database for PostgreSQL Flexible Server includes a built-in PgBouncer connection pooler. This section shows you how to enable it and measure the difference.

Why Connection Pooling Matters

How it differs from Oracle and SQL Server:

• Oracle: Uses a thread-based model with shared server processes. Connection pooling is at the app level (e.g., Oracle Connection Pool in JDBC).
• SQL Server: Uses thread-per-request with lightweight worker threads (~512 KB each). Built-in connection pooling in ADO.NET.
• PostgreSQL: Process-per-connection (~5–10 MB each). Connection pooling is external — PgBouncer, PgPool-II, or the built-in PgBouncer in Azure.

Step 1 — Check Current Connection Usage

Connect to orders_demo:

psql -h <postgresql-fqdn> -U <pgadmin> -d orders_demo

Once connected, check the current connections:

-- Current connections
SELECT count(*) AS total_connections FROM pg_stat_activity;

-- Breakdown by state
SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state
ORDER BY count DESC;

-- Max allowed
SHOW max_connections;

Step 2 — Enable Built-in PgBouncer

1. Go to Azure Portal → your PostgreSQL server → Server parameters
2. Search for pgbouncer.enabled
3. Set to true
4. Configure these parameters:

1. Click Save

Pool modes explained:

Warning: In transaction mode, session-level features like SET, PREPARE, temporary tables, and LISTEN/NOTIFY do not persist across transactions. If your application relies on these, use session mode.

Step 3 — Create a PgBouncer-Compatible Login Role (MD5)

Azure-managed PgBouncer cannot use SCRAM-SHA-256 for client authentication. The built-in pooler has no mechanism to retrieve the SCRAM verifier from the server, so connections that use SCRAM passwords will fail with an authentication error on port 6432.

This is an architectural limitation of the Azure PgBouncer integration, not a protocol limitation — SCRAM itself works fine on direct connections (port 5432).

The workaround is to create a dedicated pool login role with an MD5 password:

Connect to the server on the direct port (5432) as the admin:

psql -h <postgresql-fqdn> -U <pgadmin> -d orders_demo

First, confirm the server’s current password encryption method:

SHOW password_encryption;

Expected output:

 password_encryption
---------------------
 scram-sha-256
(1 row)

This confirms the server defaults to SCRAM — which is what we want for all regular roles. Now temporarily switch to MD5 for the pool role:

-- Switch this session to MD5 password hashing
SET password_encryption = 'md5';

Verify the session change took effect:

SHOW password_encryption;

 password_encryption
---------------------
 md5
(1 row)

Now create the role (its password will be stored as MD5):

-- Create a dedicated PgBouncer login role
CREATE ROLE app_pool LOGIN PASSWORD 'MyPoolPassword123!';

-- Grant access to the tables in orders_demo
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_pool;

Revert the session back to SCRAM and confirm:

-- Revert session back to the default
SET password_encryption = 'scram-sha-256';

SHOW password_encryption;

 password_encryption
---------------------
 scram-sha-256
(1 row)

You can also verify that app_pool was stored with MD5 while your admin role remains SCRAM:

SELECT rolname,
       CASE WHEN rolpassword LIKE 'md5%' THEN 'md5'
            WHEN rolpassword LIKE 'SCRAM%' THEN 'scram-sha-256'
            ELSE 'unknown'
       END AS password_type
FROM pg_authid
WHERE rolname IN ('app_pool', current_user);

Expected output:

  rolname  | password_type
-----------+---------------
 <pgadmin> | scram-sha-256
 app_pool  | md5
(2 rows)

Why MD5 only for this role? The SET password_encryption change is session-scoped — it only affects the CREATE ROLE statement in the same session. Your admin account and all other roles remain SCRAM-protected. The app_pool role is used exclusively for pooled connections through port 6432.

Security note: Use a strong, unique password for app_pool. In production, store it in Azure Key Vault and rotate it regularly. Do not reuse the admin password.

Step 4 — Connect Through PgBouncer

PgBouncer listens on port 6432 (not the default 5432). Connect using the app_pool role you just created:

psql -h <postgresql-fqdn> -U app_pool -d orders_demo -p 6432

Enter the password MyPoolPassword123! when prompted.

Verify you’re going through PgBouncer:

SHOW server_version;  -- works: forwarded to PostgreSQL

Run a query to confirm:

SELECT count(*) FROM orders;

Step 5 — Load Test: With and Without PgBouncer

Use pgbench from the jumpbox to simulate concurrent connections. pgbench ships in the postgresql-contrib package, which is not installed by default on Rocky Linux. Install it from the PGDG repository:

sudo dnf -y module disable postgresql          # prevent AppStream from pulling PG13
sudo dnf install -y postgresql18-contrib       # includes pgbench, pg_stat_statements, etc.
pgbench --version                              # should show: pgbench (PostgreSQL) 18.x

Why module disable? Rocky Linux ships a postgresql AppStream module (usually PG13). Without disabling it, dnf may resolve to the older version and overwrite your PGDG psql binary.

Without PgBouncer (port 5432 — direct, SCRAM auth):

pgbench -h <postgresql-fqdn> -U <pgadmin> -d orders_demo -p 5432 \
  -c 50 -j 4 -T 30 -S

Flags:

• -c 50 — 50 concurrent client connections
• -j 4 — 4 worker threads
• -T 30 — run for 30 seconds
• -S — SELECT-only workload (read-only)

Note the TPS (transactions per second) and latency average.

With PgBouncer (port 6432 — pooled, MD5 app_pool role):

pgbench -h <postgresql-fqdn> -U app_pool -d orders_demo -p 6432 \
  -c 50 -j 4 -T 30 -S

Enter the app_pool password when prompted.

Compare:

Typical results:

• PgBouncer shows higher TPS and lower latency because connection setup is eliminated
• At higher concurrency (200+ clients), direct connections may hit max_connections and fail; PgBouncer queues them

Step 6 — Monitor PgBouncer via Server Parameters

PgBouncer statistics are available through the pgbouncer virtual database:

psql -h <postgresql-fqdn> -U app_pool -d pgbouncer -p 6432

SHOW POOLS;
SHOW STATS;
SHOW CLIENTS;
SHOW SERVERS;

Summary

Accessibility and Business Continuity

Logical Backup and Restore

PostgreSQL ships three logical backup utilities: pg_dump (single database), pg_dumpall (entire cluster), and pg_restore (restore non-plain formats). In this section you will practice all the common dump and restore patterns using the orders_demo database.

Note: If you have already completed the Index Tuning Lab, your dump will include the indexes you added. If not, only primary-key indexes will appear — that is expected.

Reminder: If your libpq environment variables are not set, run source ~/.pg_azure on the jumpbox first. All commands below run on the jumpbox Linux shell, not inside psql.

Step 1 — Plain-Text Dump

The simplest format. Output is a SQL script you can replay with psql.

pg_dump orders_demo > /tmp/orders_demo.plain.sql

Inspect the output:

less /tmp/orders_demo.plain.sql

You will see CREATE TABLE, COPY data blocks, and CREATE INDEX statements.

Schema only (no data):

pg_dump --schema-only orders_demo > /tmp/orders_demo_ddl.sql
less /tmp/orders_demo_ddl.sql

Data only:

pg_dump --data-only orders_demo > /tmp/orders_demo_data.sql
less /tmp/orders_demo_data.sql

INSERT statements instead of COPY:

pg_dump --data-only --inserts orders_demo > /tmp/orders_demo_inserts.sql
less /tmp/orders_demo_inserts.sql

When to use --inserts: COPY is much faster, but INSERT-based dumps are more portable (e.g., loading into a different RDBMS). Use COPY for PostgreSQL-to-PostgreSQL restores.

Single table:

pg_dump --table=customers orders_demo > /tmp/customers.sql
less /tmp/customers.sql

Step 2 — Custom Format Dump

Custom format (-Fc) compresses the data and allows selective restore with pg_restore:

pg_dump -Fc orders_demo -f /tmp/orders_demo.custom.dump
ls -lh /tmp/orders_demo.custom.dump

Compare the file size to the plain-text dump — custom format is significantly smaller.

Step 3 — Directory Format Dump

Directory format (-Fd) is the only format that supports parallel jobs:

pg_dump -Fd -j 4 orders_demo -f /tmp/orders_demo_dir
ls -la /tmp/orders_demo_dir/

The -j 4 flag uses 4 parallel workers. You will see one compressed file per table plus a toc.dat table of contents.

Step 4 — Restore to a Separate Test Database

Instead of dropping orders_demo, create a new database to practice the restore:

psql -d postgres -c "CREATE DATABASE orders_demo_restored;"

Restore from custom format:

pg_restore -d orders_demo_restored --no-owner --verbose /tmp/orders_demo.custom.dump

Verify:

psql -d orders_demo_restored -c "
SELECT 'customers' AS tbl, COUNT(*) FROM customers
UNION ALL SELECT 'products', COUNT(*) FROM products
UNION ALL SELECT 'orders', COUNT(*) FROM orders
UNION ALL SELECT 'order_items', COUNT(*) FROM order_items;"

Check the indexes that are present:

psql -d orders_demo_restored -c "\di"

Restore from directory format (parallel):

psql -d postgres -c "DROP DATABASE IF EXISTS orders_demo_restored;"
psql -d postgres -c "CREATE DATABASE orders_demo_restored;"
pg_restore -d orders_demo_restored -j 4 --no-owner --verbose /tmp/orders_demo_dir

Step 5 — Global Objects Dump

pg_dumpall is the only tool that can dump roles, tablespaces, and other cluster-wide objects that pg_dump cannot:

pg_dumpall > /tmp/whole_cluster.sql
less /tmp/whole_cluster.sql

For just the global objects (roles + tablespaces):

pg_dumpall -g --no-role-passwords > /tmp/globals.sql
less /tmp/globals.sql

The --no-role-passwords flag avoids errors on Azure Flexible Server where you cannot export passwords from managed roles.

Step 6 — Clean Up

Drop the test database:

psql -d postgres -c "DROP DATABASE IF EXISTS orders_demo_restored;"

The original orders_demo database is untouched and ready for subsequent sections.

Summary

Tip: For optimizing large migrations using dump and restore, see the Azure migration optimization guide.

Physical Backup and Point in Time Restore

Backup

When creating a server through the Azure portal, use the Compute tier tab to select either Burstable, General Purpose, or Memory Optimized for your server. This window is also where you set the Backup Retention Period—the number of days backups are stored.

The backup retention period determines how far back in time you can restore using point-in-time restore, as it depends on the available backups.

After changing the retention period, make sure to click Save.

When the deployment is finished, the retention period has been updated.

Point-in-Time Restore

Azure Database for PostgreSQL Flexible Server allows you to restore your server to a specific point in time, creating a new copy of the server. You can use this new server to recover data or redirect your client applications.

For example, if a table was accidentally dropped at noon, you can restore to just before noon and retrieve the missing table and data from the new server copy. Note: Point-in-time restore operates at the server level, not the database level.

To restore to a point in time:

1. In the Azure portal, select your Azure Database for PostgreSQL Flexible Server.
2. On the server’s Overview page, click Restore in the toolbar.

The new server created by point-in-time restore will have the same server admin login name and password as the original server at the selected restore time. You can change the password from the new server’s Overview page.

Important:
The new server created during a restore does not have the firewall rules or VNet service endpoints from the original server. You must set up these rules separately for the new server.

Logical Replication (Optional)

This section is optional. It requires deploying a second PostgreSQL Flexible Server. Complete it if time permits, or after the workshop as self-study.

Native Logical Replication

Logical replication uses the terms publisher and subscriber:

• The publisher is the PostgreSQL database you are sending data from.
• The subscriber is the PostgreSQL database you are sending data to.

Step 1 — Deploy a Second Flexible Server

The command below creates a Burstable-tier server in the same VNet. Review and adjust the parameters to match your environment before running:

• --resource-group — must match your workshop resource group
• --vnet / --subnet — must match your spoke VNet and a subnet delegated to PostgreSQL
• --admin-user / --admin-password — choose your own credentials
• --name — must be globally unique

# Review and adjust the values below before running
az postgres flexible-server create \
  --resource-group PG-Workshop \
  --vnet spoke-vnet \
  --subnet subnet-02 \
  --name replication-flex-<unique-suffix> \
  --admin-user replica \
  --admin-password '<strong-password>' \
  --sku-name Standard_B1ms \
  --tier Burstable \
  --storage-size 128 \
  --version 18 \
  --high-availability Disabled

While the replication server is being deployed, you can update the primary database parameters:

1. Go to the server parameters page in the Azure portal.
2. Set the server parameter wal_level to logical.
3. Update max_worker_processes to at least 16. Otherwise, you may encounter issues such as:
   WARNING: out of background worker slots.
4. Save the changes and restart the server to apply the wal_level change.
5. Confirm that your PostgreSQL instance allows network traffic from your connecting resource.
6. Grant the admin user replication permissions:

ALTER ROLE <adminname> WITH REPLICATION;

Replace <adminname> with the PostgreSQL user you created in the first section.

On the primary database, create the publication:

\c orders_demo

CREATE PUBLICATION customers_pub FOR TABLE customers;

Check if the database replica has finished deploying. The output should look like the following image:

Once the replica server is deployed, update the Private DNS to private.postgresql.database.azure.com:

Note the name of the PostgreSQL server: replication-flex-<Random Number>.postgres.database.azure.com.

Log in to the jumpbox and access the newly created database (replace <123> with your actual number):

psql -d postgres -U replica -h replication-flex-<123>.postgres.database.azure.com

When prompted, enter the password you chose during replica server creation.

Once connected, create the database and table. The table structure must match the published table on the primary:

CREATE DATABASE orders_demo;

\connect orders_demo

CREATE TABLE public.customers (
    customer_id  serial PRIMARY KEY,
    first_name   text NOT NULL,
    last_name    text NOT NULL,
    email        text NOT NULL,
    city         text,
    country      text,
    loyalty_points integer DEFAULT 0,
    created_at   timestamp DEFAULT now()
);

Now, create the subscription. Make sure to change the connection parameters to match your environment:

CREATE SUBSCRIPTION customers_sub
  CONNECTION 'host=<primary-private-ip> user=<pgadmin> dbname=orders_demo password=<your-password>'
  PUBLICATION customers_pub;

Replace <primary-private-ip>, <pgadmin>, and <your-password> with the values for your primary server.

Check the customers table. It should not be empty — the initial data sync copies all existing rows:

SELECT COUNT(*) FROM customers;
SELECT * FROM customers LIMIT 10;

HA/DR

High Availability

Azure Database for PostgreSQL Flexible Server offers high availability with automatic failover using zone-redundant server deployment. When deployed in a zone-redundant configuration, Flexible Server automatically provisions and manages a standby replica in a different availability zone. Using PostgreSQL streaming replication, data is replicated to the standby replica server in synchronous mode.

Key Concepts

Important: HA protects against infrastructure failures (VM crash, availability zone outage). It does not protect against data corruption or accidental DROP TABLE — for that, use Point-in-Time Restore (see previous section).

Tasks

Configure High Availability

1. Click the High Availability tab to configure high availability.

2. Check the box to enable high availability and save your changes. This will trigger a high availability deployment.

   

3. When prompted, confirm your selection.

   

4. After deployment is complete, you will receive a confirmation message.

   

Execute a Forced Failover

A forced failover simulates an unplanned outage — the standby is promoted without waiting for the primary to shut down cleanly. Use this to test your application’s reconnect behaviour.

• In the Azure portal, click the Forced Failover button to initiate a failover to the secondary server.

  

• You will see a message once failover is complete (typically 60–120 seconds).

  

• Verify that the primary and secondary zones have interchanged.

  

What to watch for: During failover, existing connections will drop. Applications should implement connection retry logic — most PostgreSQL drivers support this natively. The FQDN stays the same; DNS updates propagate within seconds.

Execute a Planned Failover

A planned failover is graceful — the primary finishes in-flight transactions, then hands over to the standby. Downtime is typically 30–60 seconds.

• In the Azure portal, click the Planned Failover button to initiate a planned failover to the secondary server.

  

• You will see a message once failover is complete.

  

Disable High Availability

1. Navigate to the High Availability tab.

   

2. Uncheck the availability option and click Save.

   

Patching and maintenance windows

Scheduled maintenance in Azure Database for PostgreSQL – Flexible server

Azure Database for PostgreSQL - Flexible server performs periodic maintenance to keep your managed database secure, stable, and up-to-date. During maintenance, the server gets new features, updates, and patches.

What happens during maintenance?

Best practice: Set the custom maintenance window to a low-traffic period (e.g., Saturday 02:00 UTC) so restarts have minimal impact.

Configuring a custom maintenance window

Configuring a custom maintenance window

Navigate to the maintenance tab in Azure portal for your flexible server.

Select a custom schedule from the options available in the drop down.

Click on save schedule to complete the configuration.

Using the Azure CLI

You can also configure the maintenance window via CLI:

az postgres flexible-server update \
  --resource-group PG-Workshop \
  --name <server-name> \
  --maintenance-window "Sat:02:00"

Security Management

Installing pgAudit Extension

Audit logging of database activities in Azure Database for PostgreSQL - Flexible Server is available through the PostgreSQL Audit extension: pgAudit. It provides detailed session and/or object audit logging.

Step 1 — Allow the Extension

1. In the Azure Portal, go to your PostgreSQL server → Server parameters
2. Search for azure.extensions
3. Enable pgaudit
4. Click Save

Step 2 — Add pgAudit to shared_preload_libraries

1. In Server parameters, search for shared_preload_libraries
2. Check pgaudit
3. Click Save and Restart

Step 3 — Create the Extension

Connect to your server from the jumpbox and enable the extension:

CREATE EXTENSION pgaudit;

Step 4 — Configure Audit Logging

1. In Server parameters, search for pgaudit.log
2. Change from NONE to ALL (or select specific categories: READ, WRITE, DDL, etc.)
3. Click Save

From this point, all database actions in your server are being audited.

Step 5 — Verify Audit Logging

Open a psql session and run some test commands:

CREATE TABLE audit_test(id int);
INSERT INTO audit_test SELECT generate_series(1, 100);
DROP TABLE audit_test;

Step 6 — View Audit Logs

Audit logs are sent to the Azure diagnostic logs pipeline. You can view them via:

Option A — Log Analytics (recommended):

1. Go to your PostgreSQL server → Diagnostic settings
2. Add a diagnostic setting that sends PostgreSQLLogs to a Log Analytics workspace
3. Click Save
4. After a few minutes, go to Logs in the left menu and run:

AzureDiagnostics
| where Category == "PostgreSQLLogs"
| where Message contains "AUDIT"
| project TimeGenerated, Message
| order by TimeGenerated desc
| take 50

Option B — Azure Portal Server Logs:

1. Go to your PostgreSQL server → Logs (under Monitoring)
2. Browse the recent log entries for AUDIT: prefixed messages

Note: There is a 1–2 minute delay before log entries appear in Log Analytics.

Summary

Day Two Operations

Database Profiling & Activity Analysis

Audience: This section is aimed at developers and DBAs more than DevOps / Platform engineers. It focuses on understanding what the database is doing internally — session activity, vacuum health, cache efficiency, I/O patterns, lock contention, and query-level statistics. If you are coming from Oracle or SQL Server, each section includes a comparison box showing the equivalent concept in those platforms.

After running the demo workload in the previous section, this is where you learn to diagnose what happened. The queries below are organised into two scripts:

• Script A — Health Check: Run this periodically or after any change. It gives you a full picture of the database state.
• Script B — Performance Triage: Run this when someone says “the database is slow.” It focuses on active sessions, locks, I/O pressure, and the costliest queries.

Both scripts are designed to be copy-pasted into psql on the jumpbox. Every query targets PostgreSQL system catalog views — no extensions required unless noted.

Prepare Your psql Session

Before running any profiling query, configure psql for a clean output:

\pset pager off
\timing on
\x off

Oracle comparison: In SQL*Plus you would use SET PAGESIZE 0; SET TIMING ON; SET LINESIZE 200;

SQL Server comparison: In SSMS, execution time appears in the status bar automatically. In sqlcmd, use -e flag.

Script A — Health Check

Run this to understand the overall state of the database. None of these queries modify data.

A1 — Server Identity & Version

SELECT
  version()            AS server_version,
  current_database()   AS database_name,
  current_user         AS current_role,
  inet_server_addr()   AS server_ip,
  inet_server_port()   AS server_port,
  now()                AS checked_at;

What you learn: Confirms which server, database, and role you are connected to. The version() output includes the PostgreSQL major/minor version and the OS it was compiled on — useful for verifying you are on PG 18.

Example output:

 server_version                        | database_name | current_role | server_ip   | server_port | checked_at
---------------------------------------+---------------+--------------+-------------+-------------+---------------------------
 PostgreSQL 18.1 on x86_64-pc-linux... | orders_demo   | pgadmin      | 10.1.2.4    | 5432        | 2026-03-19 14:30:00+00

Oracle: SELECT * FROM v$version; SELECT ora_database_name FROM dual; SELECT user FROM dual;

SQL Server: SELECT @@VERSION; SELECT DB_NAME(); SELECT SUSER_SNAME();

A2 — Non-System Schemas

SELECT nspname AS schema_name
FROM pg_namespace
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  AND nspname NOT LIKE 'pg_toast%'
  AND nspname NOT LIKE 'pg_temp_%'
ORDER BY 1;

What you learn: Lists only the user-created schemas, filtering out PostgreSQL internals. In the orders_demo database you will see just public. In production systems you might see app, staging, audit, etc.

Why it matters: In PostgreSQL, the search_path determines which schema is queried by default. Misconfigured search paths are a common source of “table not found” errors.

Oracle: Schemas and users are the same thing — SELECT username FROM all_users WHERE oracle_maintained = 'N';

SQL Server: SELECT name FROM sys.schemas WHERE schema_id > 4 AND schema_id < 16384;

A3 — Object Inventory by Schema & Type

SELECT
  n.nspname  AS schema_name,
  CASE c.relkind
    WHEN 'r' THEN 'table'
    WHEN 'p' THEN 'partitioned table'
    WHEN 'm' THEN 'materialised view'
    WHEN 'v' THEN 'view'
    WHEN 'S' THEN 'sequence'
    WHEN 'i' THEN 'index'
  END AS object_type,
  COUNT(*) AS object_count
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND n.nspname NOT LIKE 'pg_toast%'
  AND c.relkind IN ('r','p','m','v','S','i')
GROUP BY 1, 2
ORDER BY 1, 2;

What you learn: A quick summary of how many tables, partitioned tables, materialised views, views, sequences, and indexes exist in each user schema. This is the first thing to check when you connect to an unfamiliar database.

Example output for orders_demo:

 schema_name | object_type | object_count
-------------+-------------+--------------
 public      | index       |            4
 public      | sequence    |            4
 public      | table       |            4

Oracle: SELECT owner, object_type, COUNT(*) FROM all_objects WHERE owner NOT IN ('SYS','SYSTEM') GROUP BY owner, object_type;

SQL Server: SELECT s.name, o.type_desc, COUNT(*) FROM sys.objects o JOIN sys.schemas s ON o.schema_id = s.schema_id WHERE o.is_ms_shipped = 0 GROUP BY s.name, o.type_desc;

A4 — Largest Relations (Heap + Index + TOAST breakdown)

SELECT
  n.nspname  AS schema_name,
  c.relname  AS relation_name,
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'p' THEN 'partitioned' WHEN 'm' THEN 'matview' END AS type,
  pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,
  pg_size_pretty(pg_relation_size(c.oid))       AS heap_size,
  pg_size_pretty(pg_indexes_size(c.oid))        AS index_size,
  pg_size_pretty(
    pg_total_relation_size(c.oid)
    - pg_relation_size(c.oid)
    - pg_indexes_size(c.oid)
  ) AS toast_and_overhead
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','p','m')
  AND n.nspname NOT IN ('pg_catalog','information_schema')
ORDER BY pg_total_relation_size(c.oid) DESC
LIMIT 25;

What you learn: This goes beyond the basic size query from the previous section by breaking storage into three buckets:

• Heap — the actual table data on disk
• Index — all indexes attached to this table
• TOAST & overhead — large column values stored out-of-line (text, jsonb, bytea) plus free-space map and visibility map

If TOAST is large relative to heap, you likely have big text/JSON columns. If index size rivals heap size, you may have too many indexes.

Oracle: SELECT segment_name, segment_type, bytes/1024/1024 MB FROM dba_segments ORDER BY bytes DESC; — Oracle stores data, indexes, and LOBs as separate segments.

SQL Server: EXEC sp_spaceused 'tablename'; or query sys.dm_db_partition_stats. SQL Server separates in-row data, LOB data, and row-overflow data.

A5 — Row Estimates & Dead Tuple Pressure

SELECT
  schemaname,
  relname,
  n_live_tup,
  n_dead_tup,
  ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 25;

What you learn: n_live_tup is the estimated number of live rows (updated by ANALYZE). n_dead_tup is the count of rows that have been deleted or updated but not yet vacuumed — they still occupy disk space.

Why dead_pct matters:

• < 5% — healthy, autovacuum is keeping up
• 5–20% — worth watching, consider lowering the autovacuum threshold for this table
• > 20% — autovacuum is behind or blocked; investigate immediately

Key PostgreSQL concept — MVCC dead tuples: PostgreSQL uses Multi-Version Concurrency Control (MVCC). When you UPDATE a row, PG does not modify it in place — it creates a new version and marks the old one as dead. VACUUM later reclaims the space.

Oracle comparison: Oracle uses undo segments to store old row versions. Dead row versions live in undo, not in the table itself, so tables don’t bloat the same way. There is no VACUUM in Oracle.

SQL Server comparison: SQL Server uses tempdb-based row versioning (when READ_COMMITTED_SNAPSHOT is on) or lock-based isolation. Ghost records from deletes are cleaned by a background ghost cleanup task, but UPDATE is done in place — no dead tuple concept.

A6 — Vacuum & Analyze Health

SELECT
  schemaname,
  relname,
  n_live_tup,
  vacuum_count,
  autovacuum_count,
  last_vacuum,
  last_autovacuum,
  analyze_count,
  autoanalyze_count,
  last_analyze,
  last_autoanalyze
FROM pg_stat_user_tables
ORDER BY last_autovacuum ASC NULLS FIRST
LIMIT 25;

What you learn: Shows how frequently vacuum and analyze have run on each table, and when they last ran. Tables that have never been autovacuumed (NULL timestamps) or have very old timestamps need attention.

How to read this:

• vacuum_count / autovacuum_count — how many times the table has been vacuumed manually vs. automatically
• analyze_count / autoanalyze_count — how many times statistics have been gathered
• If last_autoanalyze is NULL or very old, the query planner is working with stale statistics — it will make bad execution plan choices

Practical tip: After restoring a dump, statistics are often empty. Run a manual analyze:

ANALYZE VERBOSE;

Oracle comparison: Oracle has automatic statistics gathering via the DBMS_STATS package and maintenance windows. There is no separate vacuum step — undo management handles old versions.

SQL Server comparison: SQL Server auto-updates statistics when ~20% of rows change (or lower with trace flag 2371). There is no vacuum equivalent — page splits and forwarding pointers are the closest analogy to bloat.

A7 — Read/Write Access Patterns

SELECT
  schemaname,
  relname,
  seq_scan,
  seq_tup_read,
  idx_scan,
  idx_tup_fetch,
  n_tup_ins   AS inserts,
  n_tup_upd   AS updates,
  n_tup_del   AS deletes,
  n_tup_hot_upd AS hot_updates,
  CASE WHEN (seq_scan + COALESCE(idx_scan, 0)) > 0
       THEN ROUND(100.0 * COALESCE(idx_scan, 0) / (seq_scan + COALESCE(idx_scan, 0)), 2)
       ELSE 0
  END AS idx_scan_pct
FROM pg_stat_user_tables
ORDER BY (seq_tup_read + COALESCE(idx_tup_fetch, 0) + n_tup_ins + n_tup_upd + n_tup_del) DESC
LIMIT 25;

What you learn: The complete read/write profile of every table. This single query answers:

• Is this table read-heavy or write-heavy? Compare seq_tup_read + idx_tup_fetch vs. inserts + updates + deletes
• Are queries using indexes? If idx_scan_pct is low and the table is large, you need indexes
• Are HOT updates working? hot_updates means the update didn’t need to touch any index. A high hot_updates to updates ratio is good — it means PostgreSQL is efficiently updating in-place

What are HOT updates? When you update a column that is not part of any index, PostgreSQL can do a Heap-Only Tuple (HOT) update — the new row version stays on the same page and no index entries need updating. This is significantly faster.

Oracle comparison: Query v$segment_statistics for logical reads, physical reads, and DML counts per segment. Oracle’s in-place update model means there’s no HOT equivalent.

SQL Server comparison: Query sys.dm_db_index_usage_stats for seeks, scans, lookups, and updates per index. SQL Server’s forwarding pointer in heap tables is the closest pain point to non-HOT updates.

A8 — Unused or Low-Value Indexes

SELECT
  s.schemaname,
  s.relname    AS table_name,
  s.indexrelname AS index_name,
  s.idx_scan,
  pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size,
  i.indexdef
FROM pg_stat_user_indexes s
JOIN pg_indexes i ON i.indexname = s.indexrelname AND i.schemaname = s.schemaname
WHERE s.idx_scan = 0
ORDER BY pg_relation_size(s.indexrelid) DESC
LIMIT 25;

What you learn: Indexes that have never been used since the last statistics reset. Every index has a cost:

• It consumes disk space
• Every INSERT/UPDATE/DELETE must update it
• It adds to vacuum workload

If an index has zero scans and takes significant space, it is a candidate for removal.

Caution: Always check pg_stat_database.stats_reset first — if stats were reset recently, a zero-scan count means nothing. Also check whether the index supports a UNIQUE or PK constraint before dropping.

-- When were stats last reset?
SELECT datname, stats_reset FROM pg_stat_database WHERE datname = current_database();

Oracle comparison: SELECT * FROM dba_index_usage; (12c+) or v$object_usage after ALTER INDEX ... MONITORING USAGE;

SQL Server comparison: SELECT * FROM sys.dm_db_index_usage_stats WHERE user_seeks = 0 AND user_scans = 0 AND user_lookups = 0;

A9 — Database Throughput, Cache Hit Ratio & Deadlocks

SELECT
  datname,
  numbackends,
  xact_commit,
  xact_rollback,
  ROUND(100.0 * xact_rollback / NULLIF(xact_commit + xact_rollback, 0), 2) AS rollback_pct,
  blks_read,
  blks_hit,
  ROUND(100.0 * blks_hit / NULLIF(blks_hit + blks_read, 0), 2) AS cache_hit_pct,
  tup_returned,
  tup_fetched,
  tup_inserted,
  tup_updated,
  tup_deleted,
  temp_files,
  pg_size_pretty(temp_bytes) AS temp_bytes,
  deadlocks,
  blk_read_time,
  blk_write_time,
  parallel_workers_to_launch,
  parallel_workers_launched,
  stats_reset
FROM pg_stat_database
WHERE datname = current_database();

What you learn: A single-row dashboard of your database’s lifetime activity:

Key concept — shared_buffers & cache hit ratio: PostgreSQL reads data into shared_buffers (a shared memory cache, typically 25% of RAM). The cache hit ratio measures what percentage of block reads were served from this cache rather than going to disk. On Azure Flexible Server, the default is usually well-tuned, but workloads with large sequential scans can blow the cache.

Oracle comparison: SELECT * FROM v$sysstat WHERE name LIKE '%buffer cache%'; — Oracle’s buffer cache hit ratio is conceptually identical. Oracle also has v$system_event for wait-based I/O analysis.

SQL Server comparison: SELECT * FROM sys.dm_os_performance_counters WHERE counter_name LIKE '%Buffer cache hit ratio%'; — same concept. SQL Server also exposes dm_exec_query_stats for query-level I/O.

A10 — WAL, Checkpoint & Background Writer

Note: \echo is a psql meta-command used here as a section label. The SQL queries below it run normally.