PostgreSQL in Azure: Azure Database for PostgreSQL

What you get with the managed service

When you run PostgreSQL on an Azure VM, you manage everything: OS updates, PostgreSQL version upgrades, WAL archiving, replication configuration, and monitoring. Azure Database for PostgreSQL Flexible Server handles all of that:

• Automated daily backups with point-in-time restore up to 35 days back
• Automatic minor version updates (you control when they apply via maintenance windows)
• Built-in high availability with automatic failover
• Integrated monitoring via Azure Monitor
• Built-in PgBouncer connection pooler
• Microsoft Entra ID (Azure AD) authentication support

The trade-off is reduced control. You cannot install arbitrary system-level extensions, modify postgresql.conf directly, or run custom PostgreSQL builds. For standard application workloads, this is rarely a problem.

Creating a PostgreSQL Flexible Server

# Create a PostgreSQL server with public access
az postgres flexible-server create \
  --resource-group my-storage-rg \
  --name my-postgres-demo \
  --location eastus \
  --admin-user pgadmin \
  --admin-password "P@ssw0rd!PG123" \
  --sku-name Standard_D2ds_v4 \
  --tier GeneralPurpose \
  --version 16 \
  --storage-size 32 \
  --public-access 0.0.0.0

# Add your IP to the firewall
MY_IP=$(curl -s ifconfig.me)
az postgres flexible-server firewall-rule create \
  --resource-group my-storage-rg \
  --name my-postgres-demo \
  --rule-name AllowMyIP \
  --start-ip-address $MY_IP \
  --end-ip-address $MY_IP

# Create the server with VNet integration (production approach)
az postgres flexible-server create \
  --resource-group my-storage-rg \
  --name my-postgres-private \
  --location eastus \
  --admin-user pgadmin \
  --admin-password "P@ssw0rd!PG123" \
  --sku-name Standard_D4ds_v4 \
  --tier GeneralPurpose \
  --version 16 \
  --vnet my-app-vnet \
  --subnet postgres-subnet \
  --private-dns-zone my-postgres-private.private.postgres.database.azure.com

Connecting with psql

The hostname follows the pattern: <server-name>.postgres.database.azure.com.

# Connect with psql
psql \
  --host my-postgres-demo.postgres.database.azure.com \
  --port 5432 \
  --username pgadmin \
  --dbname postgres \
  --set=sslmode=require

# Or using a connection URI
psql "postgresql://pgadmin:P%40ssw0rd%21PG123@my-postgres-demo.postgres.database.azure.com:5432/postgres?sslmode=require"

SSL is required. The sslmode=require parameter enforces an encrypted connection. For strict certificate verification, use sslmode=verify-full with the root certificate downloaded from Microsoft.

Creating databases and running queries

-- Create a database with the correct locale
CREATE DATABASE myappdb
    WITH OWNER = pgadmin
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.UTF-8'
    LC_CTYPE = 'en_US.UTF-8';

-- Connect to it
\c myappdb

-- Create a dedicated application user
CREATE USER appuser WITH PASSWORD 'AppUserSecure!Pass456';
GRANT CONNECT ON DATABASE myappdb TO appuser;
GRANT USAGE ON SCHEMA public TO appuser;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO appuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO appuser;

-- Create a table with a UUID primary key
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    customer_email TEXT NOT NULL,
    total_amount NUMERIC(12, 2) NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create an index for common query patterns
CREATE INDEX idx_orders_status ON orders (status);
CREATE INDEX idx_orders_customer ON orders (customer_email);
CREATE INDEX idx_orders_created ON orders (created_at DESC);

-- Insert and query
INSERT INTO orders (customer_email, total_amount)
VALUES ('alice@example.com', 99.50),
       ('bob@example.com', 249.00);

SELECT
    status,
    COUNT(*) AS count,
    SUM(total_amount) AS total
FROM orders
GROUP BY status
ORDER BY count DESC;

Enabling and using extensions

Extensions in Azure PostgreSQL are enabled via server parameters rather than apt install. First allowlist the extension in the server parameter, then create it in your database.

# Allow an extension via server parameter (example: pgvector for AI workloads)
az postgres flexible-server parameter set \
  --resource-group my-storage-rg \
  --server-name my-postgres-demo \
  --name azure.extensions \
  --value pgvector,uuid-ossp,pg_stat_statements

Then in psql, create the extension:

-- Install extensions in the target database
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS pgvector;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Use pgvector for similarity search (useful for AI/embedding workloads)
CREATE TABLE document_embeddings (
    id SERIAL PRIMARY KEY,
    document_text TEXT,
    embedding VECTOR(1536)  -- 1536 dimensions for OpenAI text-embedding-3-small
);

-- Find the 5 most similar documents to a query embedding
SELECT document_text,
       1 - (embedding <=> '[0.1, 0.2, ...]'::vector) AS similarity
FROM document_embeddings
ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector
LIMIT 5;

-- Find slow queries using pg_stat_statements
SELECT
    calls,
    mean_exec_time::numeric(10,2) AS avg_ms,
    max_exec_time::numeric(10,2) AS max_ms,
    left(query, 80) AS query_snippet
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Enabling PgBouncer connection pooling

PostgreSQL opens a new process for every connection, which is expensive. Applications that open and close connections frequently (web applications, serverless functions) can overwhelm a PostgreSQL server. PgBouncer sits in front of PostgreSQL and maintains a pool of connections, multiplexing many client connections across fewer server connections.

# Enable PgBouncer on the server
az postgres flexible-server parameter set \
  --resource-group my-storage-rg \
  --server-name my-postgres-demo \
  --name pgbouncer.enabled \
  --value true

# Optionally configure pool size
az postgres flexible-server parameter set \
  --resource-group my-storage-rg \
  --server-name my-postgres-demo \
  --name pgbouncer.default_pool_size \
  --value 50

Connect to PgBouncer on port 6432 instead of 5432:

psql "postgresql://pgadmin:password@my-postgres-demo.postgres.database.azure.com:6432/myappdb?sslmode=require"