Data Platforms in Azure, Part 4: Azure Database for PostgreSQL

In the last part of this series, we explored Azure Database for MySQL. Now let’s look at its close cousin: Azure Database for PostgreSQL. If you love PostgreSQL for its extensions, JSON support, and standards compliance, Azure gives you a managed version with built-in HA, scaling, and security.

---

What is Azure Database for PostgreSQL?

Azure Database for PostgreSQL is a fully managed relational database service built on the open-source PostgreSQL engine. Like MySQL on Azure, it handles patching, backups, and high availability for you.

There are three deployment models:

• Flexible Server (recommended): Fine-grained control, zone redundancy, stop/start, custom maintenance windows.
• Single Server (older, retiring): Still around, but not for new deployments.
• Hyperscale (Citus): Distributes tables across multiple nodes for massive horizontal scale-out.

Reference: Azure Database for PostgreSQL overview

---

Why PostgreSQL?

• Extensions: PostGIS for spatial data, pg_partman for partitioning, pgcrypto for encryption, and many more.
• Advanced data types: Arrays, JSONB, hstore.
• ACID compliance: Robust transactions and concurrency control.
• Hyperscale: Azure is one of the easiest ways to run Citus for multi-node PostgreSQL.

---

Getting Started

Step 1: Create a PostgreSQL Flexible Server

Portal steps:

1. Create a resource → Databases → Azure Database for PostgreSQL – Flexible Server.
2. Enter server name, resource group, region.
3. Pick compute + storage (start small: 2 vCores, 32 GB storage).
4. Enable zone redundancy if you want HA.

CLI example:

az postgres flexible-server create \
  --name pg-flex-demo \
  --resource-group rg-postgres-demo \
  --location westus3 \
  --admin-user pgadmin \
  --admin-password 'S3curePass!' \
  --sku-name Standard_B1ms \
  --tier Burstable \
  --storage-size 32 \
  --public-access 203.0.113.10

Step 2: Create a database

az postgres flexible-server db create \
  --resource-group rg-postgres-demo \
  --server-name pg-flex-demo \
  --database-name appdb

Step 3: Connect

From your machine:

psql "host=pg-flex-demo.postgres.database.azure.com \
      dbname=appdb \
      user=pgadmin@pg-flex-demo \
      password=S3curePass! \
      sslmode=require"

Schema and Data Example

Let’s create a table with JSONB to show PostgreSQL’s strengths:

CREATE TABLE matches (
  match_id SERIAL PRIMARY KEY,
  game VARCHAR(50) NOT NULL,
  played_at TIMESTAMPTZ DEFAULT now(),
  stats JSONB
);

INSERT INTO matches (game, stats) VALUES
('Fortnite', '{"kills": 5, "assists": 2, "damage": 430}'::jsonb),
('Valorant', '{"kills": 12, "deaths": 4, "headshots": 6}'::jsonb);

Query nested JSON fields:

SELECT game, stats->>'kills' AS kills
FROM matches
WHERE (stats->>'kills')::int > 10;

Hyperscale (Citus) Example

If you need scale beyond a single node, Hyperscale distributes tables across shards:

-- Enable Citus extension
CREATE EXTENSION IF NOT EXISTS citus;

-- Distribute a table by column
SELECT create_distributed_table('matches', 'match_id');

Now inserts are spread across worker nodes, and queries parallelize automatically.

Reference: Azure Database for PostgreSQL – Hyperscale (Citus)

---

Security and Access

• SSL enforced: All connections use SSL by default.
• Firewall rules: Restrict by IP or use private endpoints.
• Azure AD auth: Use managed identities for tighter integration.

---

Monitoring and Performance

• Use Query Store (pg_stat_statements) to capture slow queries.
• Enable auto vacuum and check pg_stat_activity regularly.
• For connection pooling, deploy PgBouncer alongside Flexible Server.

Example: top 5 queries by total runtime:

SELECT query, total_exec_time, calls
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

Reference: Monitor PostgreSQL performance in Azure

---

When to Use Azure PostgreSQL

Good fit:

• Applications that already use PostgreSQL on-prem or in the cloud.
• Apps that need JSON/relational hybrid models.
• Workloads that benefit from extensions (PostGIS, pg_partman, etc.).
• Large-scale analytics/HTAP with Hyperscale (Citus).

Avoid if:

• You need global low-latency NoSQL (use Cosmos DB).
• You want raw object storage for big data (use Data Lake).

---

Wrap-Up

Azure Database for PostgreSQL gives you the power of Postgres with managed backups, HA, and elastic scaling. You can run a small Flexible Server for dev/test, scale up for production, or go all-in with Hyperscale Citus for very large workloads.

In Part 5 of this series, we’ll step back and compare when to use Azure SQL vs. open-source databases (MySQL/Postgres) vs. Cosmos DB vs. Data Lake, tying together everything we’ve covered so far.

---

References

• Overview of Azure Database for PostgreSQL
• Flexible Server quickstart
• Hyperscale (Citus)
• Monitoring PostgreSQL in Azure