Build AI application backends with Azure Database for PostgreSQL

radio_button_uncheckedshort_text1. Start from the persistent-memory requirement

Consider an AI research agent that must resume conversations, preserve multi-step task state, and retrieve context for thousands of concurrent users with sub-second latency. Its database needs relational integrity for users, sessions, messages, and checkpoints, but also flexible metadata for model settings and tool output. PostgreSQL fits this mixed requirement through transactions, SQL, JSONB, indexes, and an extension ecosystem.

Running the engine yourself would also make the application team responsible for servers, patching, backups, failover, capacity, and security. Azure Database for PostgreSQL moves those operational duties to a managed service while preserving compatibility with community PostgreSQL and its tools.

• Explain the service architecture and compute choices.
• Connect with Microsoft Entra ID and TLS.
• Model tables, relationships, constraints, JSONB, and indexes.
• Use PostgreSQL-specific query patterns for agent data.
• Integrate Python safely with psycopg and reusable connections.

Topic summary

The module turns PostgreSQL into durable agent memory while Azure manages the database platform around it.

radio_button_uncheckedshort_text2. Understand the managed PostgreSQL architecture

Azure Database for PostgreSQL runs the community database engine as a fully managed relational service. Compute and storage are separate: the engine uses Linux-based compute while database files remain on Azure-managed storage with locally redundant copies. This layout lets compute and storage evolve independently and gives the service a durable foundation.

You still control database configuration, maintenance windows, high-availability options, extensions, and capacity. Microsoft handles hardware provisioning, platform patching, backup orchestration, and the managed high-availability infrastructure.

Topic summary

The service separates compute from durable managed storage and delegates platform operations to Microsoft.

radio_button_uncheckedshort_text3. Choose a compute tier and managed recovery features

A tier change is possible after deployment and normally involves a brief restart. Begin from measured CPU, memory, connection, and latency needs rather than selecting the largest tier by default.

Automated backups combine snapshots and transaction logs. The default retention is seven days and can be extended to 35 days. The service uses zone-redundant backup storage where supported and locally redundant storage elsewhere, encrypts backups with AES-256, and supports platform-managed or customer-managed keys. Point-in-time restore creates a new server at a selected second within the retention window, which is useful after accidental writes or for historical testing.

Topic summary

Pick compute from workload behavior, and align the seven-to-35-day backup window with the recovery requirement.

radio_button_uncheckedshort_text4. Plan extensions and connection pooling early

Extensions add types, functions, operators, and index methods without changing PostgreSQL core. AI solutions commonly evaluate pgvector for embeddings and nearest-neighbor search, pg_trgm for fuzzy text matching and autocomplete, hstore for lightweight key-value attributes, and PostGIS for geospatial workloads. Confirm availability before the architecture depends on an extension, then include extension upgrades in maintenance planning.

Built-in PgBouncer keeps reusable server connections and multiplexes short-lived client connections over that pool. It is especially useful when every inference request stores messages or loads context. The built-in feature is available on General Purpose and Memory Optimized, not Burstable, and listens on port 6432 rather than the direct PostgreSQL port 5432.

az postgres flexible-server parameter set \
  --resource-group rg-ai-agent \
  --server-name pg-ai-agent \
  --name pgbouncer.enabled \
  --value true

# Direct PostgreSQL: 5432
# Built-in PgBouncer: 6432

Topic summary

Validate extensions at design time, and use built-in PgBouncer on supported production tiers when connection churn is high.

radio_button_uncheckedshort_text5. Assemble a complete PostgreSQL connection

A flexible-server endpoint follows <server>.postgres.database.azure.com. The name resolves to a public address when public networking is enabled or to a private address with virtual-network integration. A client needs the host, database, username, credential, port, and SSL mode.

Libraries may accept a URI, keyword-value string, or individual parameters. Keep connection configuration outside source code and never log secrets or access tokens.

Topic summary

A correct connection binds endpoint, port, database, identity, credential, and TLS policy into one testable configuration.

radio_button_uncheckedshort_text6. Prefer Microsoft Entra authentication where possible

Microsoft Entra authentication replaces stored database passwords with OAuth 2.0 access tokens. It centralizes identity governance, supports managed identities for Azure-hosted applications, produces Entra sign-in audit trails, and limits exposure because tokens expire. Configure an Entra administrator on the server, then request a token for the PostgreSQL resource.

from azure.identity import DefaultAzureCredential

credential = DefaultAzureCredential()
access_token = credential.get_token(
    "https://ossrdbms-aad.database.windows.net/.default"
)

# Pass access_token.token as the PostgreSQL password.

DefaultAzureCredential can use managed identity in Azure and developer credentials such as Azure CLI locally. The token is passed as the PostgreSQL password. Native PostgreSQL authentication remains useful for legacy systems, identities outside the tenant, or disconnected development, but passwords should live in Azure Key Vault, rotate regularly, be randomly generated, and belong to least-privileged roles.

Topic summary

Microsoft Entra tokens and managed identities remove long-lived application passwords; native credentials require deliberate secret management.

radio_button_uncheckedshort_text7. Enforce TLS and understand network reachability

Azure Database for PostgreSQL requires encrypted transport and supports TLS 1.2 and 1.3. The client sslmode determines whether encryption and certificates are validated. disable is rejected; allow and prefer do not verify the server; require encrypts without certificate verification; verify-ca validates the certificate chain; verify-full also verifies that the certificate hostname matches the server.

Production applications should use verify-full and trust the relevant DigiCert or Microsoft root certificates. If verification fails, repair the trust store rather than weakening sslmode.

Public access exposes a public endpoint protected by firewall rules, so a developer IP must be allowed. Private access gives the server a private address in a virtual network and requires a client in the same VNet, a peered network, or a connected on-premises network through VPN or ExpressRoute. Authentication cannot compensate for a blocked network path.

Topic summary

Use verify-full for authenticated encryption and troubleshoot identity, TLS, DNS, routing, and firewall layers independently.

radio_button_uncheckedshort_text8. Organize servers, databases, and schemas

One server can host multiple databases; each connection targets one database, and queries do not directly join objects across databases. Inside a database, schemas provide namespaces for tables, functions, and other objects. The public schema is the default when no schema is specified.

Choose separate databases for strong isolation, independent restore needs, or applications that must not see each other. Choose separate schemas when related domains still need foreign keys and cross-namespace joins, for logical tenant separation, or for simpler permission boundaries. A single database and public schema are sufficient for many modest AI applications.

Topic summary

Databases create strong isolation; schemas organize related objects inside a database without preventing joins and relationships.

radio_button_uncheckedshort_text9. Model agent memory with types and constraints

CREATE TABLE agent_conversations (
  id BIGSERIAL PRIMARY KEY,
  session_id UUID NOT NULL UNIQUE,
  user_id VARCHAR(255) NOT NULL,
  started_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ended_at TIMESTAMPTZ,
  metadata JSONB NOT NULL DEFAULT '{}'::jsonb
);

CREATE TABLE agent_messages (
  id BIGSERIAL PRIMARY KEY,
  conversation_id BIGINT NOT NULL
    REFERENCES agent_conversations(id) ON DELETE CASCADE,
  role VARCHAR(20) NOT NULL
    CHECK (role IN ('user', 'assistant', 'system')),
  content TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX ix_agent_messages_context
  ON agent_messages(conversation_id, created_at, id);

Every table needs a stable primary key. SERIAL and BIGSERIAL generate sequential 32-bit and 64-bit values; UUID with gen_random_uuid() supports identifiers created outside the database or merged across systems. BIGSERIAL is simple and index-friendly for high-volume centralized inserts.

NOT NULL blocks missing values, DEFAULT supplies an omitted value, CHECK limits a domain such as task status, and UNIQUE prevents duplicates. These rules protect integrity even when more than one application writes to the database.

Topic summary

Use strict relational columns for stable facts, JSONB for controlled variability, and constraints as the final integrity boundary.

radio_button_uncheckedshort_text10. Define relationships, indexes, and safe schema changes

A foreign key represents one-to-many relationships such as one conversation with many messages. RESTRICT prevents deletion while dependent rows remain; CASCADE propagates deletion; SET NULL and SET DEFAULT preserve the dependent row with a changed reference. Use cascading deletes only when deleting the parent should unquestionably remove every child. Many-to-many relationships use a junction table whose composite primary key prevents duplicate pairs.

PostgreSQL automatically indexes primary keys and unique constraints, but it does not automatically create every foreign-key or query index. B-tree is the default for equality, ranges, joins, and sorting. Composite column order matters: an index on (conversation_id, created_at) serves the leading conversation filter, not a query that filters only created_at. Every index also consumes storage and makes writes more expensive.

ALTER TABLE evolves an existing structure, and DROP TABLE permanently removes it. Most PostgreSQL DDL is transactional, so related changes can run inside BEGIN and either COMMIT together or ROLLBACK on failure. Some changes acquire strong locks; schedule and test them before production. Be especially cautious with DROP ... CASCADE.

Topic summary

Relationships enforce ownership, indexes follow actual access patterns, and transactional DDL reduces partial schema changes.

radio_button_uncheckedshort_text11. Respect SQL execution order and PostgreSQL filters

A SELECT alias does not exist when WHERE, GROUP BY, or HAVING runs. Repeat the expression, or place it in a subquery or CTE; ORDER BY can use the alias because it runs later. PostgreSQL also provides ILIKE for case-insensitive patterns, NULLS FIRST or NULLS LAST to control null ordering, and COALESCE to select the first non-null value.

Topic summary

Logical execution order explains alias visibility, while ILIKE, explicit null ordering, and COALESCE simplify application queries.

radio_button_uncheckedshort_text12. Query JSONB and paginate without deep OFFSET scans

The -> operator returns a JSON value and ->> returns text. #> and #>> navigate nested paths. The ? operator tests whether a key exists, while @> tests containment. GIN indexes can accelerate containment and existence predicates on large JSONB collections. Functions such as jsonb_array_elements_text expand arrays for filtering and aggregation.

OFFSET pagination becomes progressively slower because the engine scans and discards every skipped row. Keyset pagination remembers the last sortable values and filters from that cursor. Include a unique tie-breaker such as id with the timestamp so that equal timestamps neither duplicate nor omit rows.

SELECT id, session_id, metadata->>'model' AS model, started_at
FROM agent_conversations
WHERE user_id = $1
  AND metadata @> $2::jsonb
  AND (started_at, id) < ($3, $4)
ORDER BY started_at DESC, id DESC
LIMIT 20;

Topic summary

Use JSONB operators for flexible metadata and keyset pagination for stable performance deep into large result sets.

radio_button_uncheckedshort_text13. Compose reusable and recursive queries with CTEs

A Common Table Expression names a temporary result available only to the statement. It can split a complex query into readable stages, such as recent sessions followed by message aggregates.

WITH recent_sessions AS (
  SELECT id, user_id, started_at
  FROM agent_conversations
  WHERE started_at >= CURRENT_DATE - INTERVAL '7 days'
), message_totals AS (
  SELECT conversation_id, COUNT(*) AS message_count
  FROM agent_messages
  GROUP BY conversation_id
)
SELECT r.user_id, r.started_at,
       COALESCE(m.message_count, 0) AS message_count
FROM recent_sessions r
LEFT JOIN message_totals m ON m.conversation_id = r.id;

WITH RECURSIVE handles task trees, organization structures, or threaded messages by combining an anchor with a recursive branch. Always include a reliable termination condition or depth ceiling so cycles cannot run indefinitely. For conversation threads, join each message to its parent and carry a depth value through the recursion.

Topic summary

CTEs make multi-stage SQL auditable; recursive CTEs traverse hierarchies safely when they include a termination rule.

radio_button_uncheckedshort_text14. Reduce round trips with RETURNING and idempotent upserts

RETURNING retrieves generated IDs, timestamps, and changed values from INSERT, UPDATE, or DELETE without a second query. It is ideal when a new conversation ID is immediately required to insert messages.

INSERT ... ON CONFLICT handles a unique-key collision. DO NOTHING safely ignores a duplicate, while DO UPDATE uses the EXCLUDED pseudo-table to merge the proposed values into the existing row. A conditional WHERE can avoid an unnecessary update. This is a natural fit for checkpoints, user preferences, and other idempotent agent operations.

INSERT INTO task_checkpoints (task_id, step_number, state)
VALUES ($1, $2, $3::jsonb)
ON CONFLICT (task_id, step_number)
DO UPDATE SET state = EXCLUDED.state,
              updated_at = CURRENT_TIMESTAMP
RETURNING id, updated_at;

Topic summary

RETURNING saves a query round trip, and ON CONFLICT converts duplicate-prone writes into deliberate idempotent behavior.

radio_button_uncheckedshort_text15. Integrate Python applications safely with psycopg 3

psycopg 3 is the modern PostgreSQL adapter for Python, with synchronous and asynchronous APIs, PostgreSQL feature support, and optional pooling. The binary extra is the easiest development installation; production builds that target a specific libpq can compile against its development headers.

import psycopg
from psycopg_pool import ConnectionPool

pool = ConnectionPool(conninfo, min_size=1, max_size=10)

def load_context(conversation_id: int):
    with pool.connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute(
                """SELECT role, content, created_at
                   FROM agent_messages
                   WHERE conversation_id = %s
                   ORDER BY created_at""",
                (conversation_id,),
            )
            return cursor.fetchall()

Context managers close cursors and connections even when an exception occurs. Always pass values through positional %s or named placeholders; never concatenate user input into SQL. Use fetchone for one expected row, fetchall only for small result sets, and iterate over the cursor for large results.

conn = psycopg.connect(
    conninfo,
    connect_timeout=10,
    options="-c statement_timeout=30000"
)

Topic summary

psycopg context managers, parameters, result streaming, timeouts, and pooling create a safer application boundary.

radio_button_uncheckedshort_text16. Handle failures and optimize database traffic

Retry transient OperationalError failures from network interruptions, restarts, or temporary contention with exponential backoff. Do not retry syntax errors or data violations unchanged. Handle UniqueViolation, ForeignKeyViolation, and CheckViolation with meaningful feedback and roll back the failed transaction. DeadlockDetected and LockNotAvailable can be retried after rollback; acquire locks in a consistent order to reduce recurrence.

A connection leak eventually exhausts the pool, so return every borrowed connection. Set connection and statement timeouts from the workload latency budget. Batch hundreds or a few thousand rows with executemany; for much larger loads, COPY usually eliminates far more network round trips.

with cursor.copy(
    "COPY agent_messages (conversation_id, role, content) FROM STDIN"
) as copy:
    for message in messages:
        copy.write_row(message)

Prepared statements reuse parse and planning work for repeated parameterized queries. Pooling avoids the network handshake, authentication, and server allocation required for every new connection. Tune pool size against application concurrency and the server connection limit instead of making it arbitrarily large.

Topic summary

Classify errors before retrying, clean up every connection, and reduce round trips through pooling, batches, prepared statements, and COPY.

radio_button_uncheckedshort_text17. Guided lab: build an agent-tool backend

The source exercise uses about 30 minutes to build a PostgreSQL backend that an AI agent can call as a tool. It persists conversation context and task state so work survives process restarts and interrupted sessions.

1. Prepare an Azure subscription with deployment permissions, Visual Studio Code, the latest Azure CLI, Python 3.12 or later, and psql.
2. Download the starter project and parameterize its deployment settings.
3. Deploy an Azure Database for PostgreSQL flexible server with Microsoft Entra authentication.
4. Create conversations, messages, and task-checkpoint tables with relationships and constraints.
5. Implement Python functions for writing messages, saving checkpoints, and loading context.
6. Run the supplied workflow test and inspect stored state with SQL.
7. Interrupt and resume a task to prove that memory is durable across sessions.
8. Remove the disposable Azure resources when the validation is complete.

Topic summary

The lab proves that secure PostgreSQL tables and Python tools can preserve conversation and checkpoint state across agent runs.

radio_button_uncheckedshort_text18. Assessment review and final checklist

1. Variable conversation metadata: JSONB supports different nested structures and remains queryable and indexable.
2. An inserted row needs its generated ID immediately: RETURNING produces it in the same statement.
3. Insert or update a user preference without duplicates: INSERT ... ON CONFLICT DO UPDATE.
4. Many short-lived Python connections: use ConnectionPool so callers borrow and return reusable connections.
5. Task status must belong to a fixed set: enforce it with CHECK (status IN (...)).

The distractors belong to other products or enforce a different rule: VARCHAR(MAX), OUTPUT, LAST_INSERT_ID(), and ON DUPLICATE KEY UPDATE are not PostgreSQL answers; UNIQUE does not limit the allowed status values; NOT NULL DEFAULT supplies a value but does not reject another invalid value; one global connection is fragile and cannot safely serve arbitrary concurrency.

• Match compute and pooling to measured concurrency.
• Use Microsoft Entra ID, verify-full TLS, and least privilege.
• Keep stable facts relational and controlled variability in JSONB.
• Index filters, joins, and sorts without indexing everything.
• Prefer keyset pagination, RETURNING, and ON CONFLICT for efficient state operations.
• Use parameterized SQL, timeouts, rollback, targeted retries, and connection pools.

Official references

1. Azure Database for PostgreSQL documentation
2. Azure Database for PostgreSQL service overview
3. Connect and query with Python
4. Microsoft Entra authentication for Azure Database for PostgreSQL
5. PostgreSQL data types
6. psycopg 3 documentation

Topic summary

A production-ready AI backend combines managed operations, secure identity and transport, intentional schema design, efficient PostgreSQL SQL, and disciplined client behavior.