AI-200 Azure Database for PostgreSQL: Entra Connections, Schema Design, Data Types and Connection Pooling

To connect an app to Azure Database for PostgreSQL flexible server without a stored password, sign in as a Microsoft Entra principal: request an access token for the scope https://ossrdbms-aad.database.windows.net/.default and pass that token as the password. The server must have Microsoft Entra authentication enabled, and the identity must exist as a database role.

from azure.identity import DefaultAzureCredential
import psycopg

SCOPE = "https://ossrdbms-aad.database.windows.net/.default"
cred = DefaultAzureCredential()

conn = psycopg.connect(
    host="myserver.postgres.database.azure.com",
    dbname="app",
    user="ingest-func",              # the identity's name
    password=cred.get_token(SCOPE).token,
    sslmode="require",
)

The scope is the same for every server: it names the Azure open-source databases token audience, not your server's host name. A token issued for any other audience, even another Azure service's valid token, is rejected by PostgreSQL.

Creating the database role for an identity

Azure RBAC roles such as Contributor manage the server resource; they do not let anyone sign in to the database. A Microsoft Entra administrator of the server creates the role inside PostgreSQL, connected to the postgres database:

SELECT * FROM pgaadauth_create_principal('ingest-func', false, false);
-- arguments: role name, is administrator, is MFA-only

For a managed identity, the role name is the identity's name (for a system-assigned identity, the name of the app or resource), and the app signs in with that same name as the user and a token as the password. The role has no PostgreSQL password of its own (the token is the credential), and the user name in the connection must match the role name exactly. After the role exists, grant it normal PostgreSQL privileges (GRANT on schemas and tables) in the application database.

Connections are encrypted: require_secure_transport is on by default, so a client that sets sslmode=disable is refused rather than made faster.

Token lifetime and connection pools

An Entra access token is checked only when a connection signs in. Tokens are short-lived (minutes to an hour), so an established connection keeps working after its token expires, but any new connection opened with an old token fails with a password authentication error.

That matters for pools. A pool opens new connections throughout its life: when it grows, when it replaces a broken connection and when a connection reaches max_lifetime (one hour by default in psycopg_pool). If the token is fetched once at startup and passed as a fixed value, the pool works at first and then fails a growing share of new connections hours later, while older connections still succeed.

The fix is to fetch the token per connection attempt. In psycopg_pool 3.3 or later, kwargs (and conninfo) can be a callable that the pool calls every time it connects; the shared credential caches tokens and refreshes them before they expire.

from psycopg_pool import ConnectionPool

def conn_kwargs():
    return {"password": cred.get_token(SCOPE).token}

pool = ConnectionPool(
    conninfo=f"host={HOST} dbname=app user={USER} sslmode=require",
    kwargs=conn_kwargs,
    max_size=10,
)

On older psycopg_pool versions kwargs must be a dict, so upgrade before using this pattern. The rule to remember: whatever opens the connection must fetch the token, and in a pool that is the pool itself. In .NET, Npgsql's data source builder offers the same pattern with a periodic password provider that supplies a fresh token.

Send user input to PostgreSQL as bound parameters, never as part of the SQL text. In psycopg 3 the placeholders are %s (positional, with a tuple or list) and %(name)s (named, with a dict), and the values travel separately from the statement.

with conn.cursor() as cur:
    cur.execute(
        "SELECT id, title FROM docs WHERE tenant_id = %s",
        (tenant_id,),          # note the one-element tuple
    )
    rows = cur.fetchall()

Any string formatting that puts the value into the SQL text before execute() runs reopens SQL injection, however tidy it looks; the value must reach the driver as a separate argument. Placeholder syntax differs between drivers, so use the style your driver documents.

Adapting Python dicts to jsonb

psycopg does not guess how to send a Python dict, so passing one directly raises an error that it cannot adapt the type. Wrap it in Jsonb (or Json for a json column) from psycopg.types.json:

from psycopg.types.json import Jsonb

cur.execute(
    "INSERT INTO usage_events (request_id, details) VALUES (%s, %s)",
    (request_id, Jsonb(details)),
)

Let the wrapper serialise the value: Python's own text form of a dict uses single quotes and is not valid JSON.

Bulk loading with COPY instead of row-by-row INSERTs

For millions of rows, use PostgreSQL's COPY protocol, which streams rows to the server in one command. In psycopg 3 that is cursor.copy() with write_row():

with conn.cursor() as cur:
    with cur.copy(
        "COPY staging_pages (source_id, content) FROM STDIN"
    ) as copy:
        for source_id, content in records:
            copy.write_row((source_id, content))
conn.commit()

COPY is fast because it removes per-row overhead. Each separate INSERT costs a network round trip and a statement execution, and each separate commit waits for a durable log write; adding concurrency spreads that cost around but does not remove it. COPY gives the highest throughput without another service; executemany() with batched parameters is the middle ground for moderate volumes.

Pick the type that matches how the value is used: what you filter on, whether it must be exact, and whether it is a point in time. The table summarises the choices this topic tests.

Properties of the other types that look similar: timestamp (without time zone) stores no zone information, and timetz has no date part. real and double precision are binary floating point and approximate. money stores a fixed number of fractional digits set by the lc_monetary locale (usually two). hstore is an extension that holds only flat text key/value pairs. Plain text and bytea accept anything, so they neither validate nor query JSON.

timestamptz does not store the original zone: it converts input to UTC and displays it in the session's TimeZone, which is exactly what makes ordering and AT TIME ZONE conversions correct across zones. The p in numeric(p,s) is total digits and s is digits after the decimal point, so numeric(14,6) holds up to eight digits before it.

Embedding columns use the vector type from pgvector; vector storage, indexes and search are covered in the lesson on pgvector indexing and vector search.

Model each entity as a table with a primary key, and link child rows to parents with a foreign key whose ON DELETE action states what happens to children when a parent is deleted.

Generating keys

Identity columns are the SQL-standard replacement for serial. Choose ALWAYS when only the database may assign keys; UUIDs suit keys created by clients or merged across systems.

Foreign keys and delete behaviour

Add NOT NULL to the referencing column when every child must have a parent; a foreign key alone allows NULL. A CHECK constraint cannot express "this parent exists".

Index the referencing column yourself

PostgreSQL automatically indexes primary keys and unique constraints, so the parent side (projects.id) is already indexed. It does not index the referencing column (tasks.project_id). Without that index, every parent delete or update must scan the child table to find the rows to cascade or check, and joins from parent to children are slow. Create a B-tree index on each foreign key column you join or cascade on. Whatever enforces the relationship, finding the child rows is only fast with that index.

Use declarative range partitioning on the time column when a table grows fast and old rows are removed by age. Each partition holds one range (a day or a month), and expiring data becomes a metadata operation: detach or drop the oldest partition.

CREATE SEQUENCE audit_events_id_seq;

CREATE TABLE audit_events (
    id          bigint NOT NULL
                DEFAULT nextval('audit_events_id_seq'),
    occurred_at timestamptz NOT NULL,
    detail      jsonb,
    PRIMARY KEY (id, occurred_at)
) PARTITION BY RANGE (occurred_at);

CREATE TABLE audit_events_2026_11 PARTITION OF audit_events
    FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');

DROP TABLE audit_events_2026_08;   -- retention

The example uses a sequence default because identity columns on partitioned tables need PostgreSQL 17 or later; on 17+ you can write GENERATED ALWAYS AS IDENTITY instead.

Compared with a scheduled DELETE ... WHERE occurred_at < now() - interval '90 days', dropping a partition leaves no dead rows to vacuum (no bloat) and holds no long row-by-row locks. Queries that filter on occurred_at also benefit from partition pruning: the planner skips partitions outside the range. The partition scheme must match how data is removed: only a range on the time column lines whole partitions up with the retention window, while any scheme that spreads each period's rows across every partition leaves row-by-row deletes as the only way to expire them.

Keys on partitioned tables

A primary key or unique constraint on a partitioned table must include every partition key column, because each partition enforces uniqueness only within itself. ADD PRIMARY KEY (id) on a table partitioned by occurred_at fails; PRIMARY KEY (id, occurred_at) works and keeps range partitioning (and partition drops) intact. A constraint that exists only on individual partitions is not a key of the parent table.

Design B-tree indexes around the exact WHERE and ORDER BY of your hot queries: equality columns first, then the range or sort column, so the planner can jump to the matching rows, read them already ordered and stop at the LIMIT.

-- WHERE workspace_id = $1 AND state = $2
-- ORDER BY updated_at DESC LIMIT 50
CREATE INDEX ON orders (workspace_id, state, updated_at DESC);

Order matters. Once the index reaches a range or sort column, the columns after it can no longer narrow the search, so a leading range column makes the scan read every entry in the range. And when the index order does not match ORDER BY, rows do not come out pre-sorted: the server must find and sort every match before it can apply LIMIT, which also happens when separate single-column indexes are combined with a bitmap scan.

An expression index is used only when the query uses the same expression; a plain index on email does not serve lower(email). A partial index is tiny when its WHERE clause matches few rows. Because that clause already selects the rows, the key should be the column the query sorts or ranges on; a key that repeats the filtered value adds nothing. Confirm index use with EXPLAIN (ANALYZE, BUFFERS).

Index jsonb columns with GIN and choose the operator class by the operators your queries use.

-- containment filters only (WHERE specs @> '{"color":"red"}')
CREATE INDEX ON catalog_items USING gin (specs jsonb_path_ops);

-- containment AND key-exists (WHERE attrs ? 'discontinued')
CREATE INDEX ON products USING gin (attrs);   -- jsonb_ops

Operator support is fixed by the operator class: if a query uses an operator the class lacks, the planner cannot use that index at all, whatever the statistics say. When one index must serve both containment and key-exists, use the default jsonb_ops. An expression B-tree on one extracted key, such as (specs ->> 'brand'), serves comparisons on that single key only.

Substring search with pg_trgm

For LIKE or ILIKE with a leading wildcard ('%term%'), use the pg_trgm extension with a GIN trigram index. Each text index serves a different kind of match:

CREATE EXTENSION pg_trgm;
CREATE INDEX ON kb_articles USING gin (title gin_trgm_ops);

On Azure Database for PostgreSQL flexible server, an extension must first be allow-listed in the azure.extensions server parameter; otherwise CREATE EXTENSION fails. Only extensions that hook into server start-up also need shared_preload_libraries (and a restart); pg_trgm is not one of them.

Reuse connections instead of opening one per request. Every new PostgreSQL connection costs a TLS handshake, authentication and a new server process, which adds latency to the request and CPU on the server. Two layers solve this: an application-side pool in each process, and server-side pooling with the built-in PgBouncer.

Application pools

Create one pool per process at startup and borrow a connection per unit of work with the context manager:

pool = ConnectionPool(conninfo=..., min_size=1, max_size=5)

def handler(params):
    with pool.connection() as conn:
        return conn.execute(SQL, params).fetchall()

with pool.connection() always returns the connection, committing on success and rolling back on an exception. Code that borrows connections by hand must return them in a finally block. A connection that is never returned, for example because an exception skipped the return, stays checked out; once enough have leaked, requests wait for a free connection and time out even though the database is not busy. Raising pool limits hides a leak rather than fixing it, and the pool must be shared by the process: one created per request gives no reuse.

Sizing across scaled-out instances

The server sees instances × pool size connections, and that total must stay below max_connections minus the 15 connections Azure reserves for its own use. max_connections depends on the compute size, so check the limits page for your SKU. For example, if a server allowed 1,000 connections and an app scaled out to 50 instances, each with max_size=25, the pools could ask for 1,250; once the limit is reached, new connections fail with FATAL: sorry, too many clients already. Keep each instance's pool small (an instance that handles one request at a time needs very few connections) and put PgBouncer in front. Raising max_connections needs a server restart and costs memory per connection, and Microsoft recommends PgBouncer instead.

Built-in PgBouncer

Flexible server includes PgBouncer: set the server parameter pgbouncer.enabled to true (no restart needed) and point clients at port 6432 on the same host; port 5432 still goes directly to PostgreSQL. It is available on the General Purpose and Memory Optimized tiers only, not Burstable, so a Burstable server must be scaled to one of those tiers first. Its metrics are enabled separately with metrics.pgbouncer_diagnostics. Running your own PgBouncer in a sidecar or VM also works, but it is extra infrastructure to operate.

The built-in PgBouncer defaults to transaction pooling: a client holds a server connection only for the duration of one transaction, and the next transaction may run on a different server connection. Anything tied to the session therefore leaks or disappears.

Statement pooling is stricter still: it does not allow multi-statement transactions. No pool-size setting changes the core rule of transaction mode, which is that a client keeps a server connection for one transaction only.

A read replica is a separate, read-only flexible server that receives the primary's changes by asynchronous replication and has its own endpoint. Point reporting, analytics or other heavy read workloads that tolerate seconds of lag at the replica, and the primary keeps its capacity for writes and latency-sensitive reads.

Because replication is asynchronous, a read sent to the replica right after a write may not see that write yet. Keep read-your-own-writes paths (for example, showing an order confirmation right after checkout) on the primary and send only lag-tolerant reads to the replica. Replication to a read replica is always asynchronous; there is no synchronous option. Split traffic by how fresh each read must be, not by user: only a separate server adds capacity, since a second database on the same server still shares its CPU and memory. Running the app in the same Azure region as the server, over private networking, also cuts round-trip latency for every query.