Choosing Azure data services

Over a few months your agents have accumulated a resource group: a PostgreSQL server, two storage accounts, a queue, and — because someone asked about caching — a Redis instance nobody reads from. Each was a reasonable decision in isolation. The question this article answers is which store each kind of research data actually belongs in, and which operational edges will bite you once real traffic arrives: firewall rules, connection limits, and backups you have never tested.

Start with Postgres, and know when to leave

Azure Database for PostgreSQL Flexible Server is the right default for research data, because most research questions are relational: join responses to respondents to waves, filter by quota cell, aggregate by segment. You get transactions, foreign keys, real SQL, JSONB for the fields that genuinely have no schema, and pgvector for embeddings, in one managed service with automated backups.

The useful skill is recognising when something else earns its place:

When a file beats a database

An agent asked to “store the uploaded documents” will often write them into a BYTEA column, because that keeps everything in one place. It works, and then your 8 GB database becomes 400 GB, your nightly backup slows down, and your point-in-time restore takes hours — all for bytes you never query into.

The pattern that scales is both:

-- The file lives in Blob Storage; the row is what you can query.
create table documents (
  id            bigint generated always as identity primary key,
  project_id    bigint not null references projects(id),
  blob_url      text   not null,            -- no SAS token stored here
  sha256        text   not null,            -- so re-uploads are detectable
  bytes         bigint not null,
  status        text   not null default 'pending',
  extracted     jsonb,                      -- fields you will filter on
  created_at    timestamptz not null default now()
);
create index on documents (project_id, status);   -- the worker's query

Note blob_url holds the plain URL, not a URL with a SAS token appended. Tokens expire; storing one in a database guarantees a table full of dead links.

Blob Storage: containers, tiers and SAS URLs

A storage account holds containers, and containers hold blobs. The path within a container is just a string, so the folder structure you see in the portal is a convention, not a real hierarchy. Choose a prefix scheme you can list cheaply: project=1284/wave=3/raw/respondent-9001.pdf lets you enumerate one wave without scanning the account.

Access tiers trade storage cost against retrieval cost:

Two traps. First, the minimum retention is a billing rule, not a restriction: delete, overwrite or retier a blob early and you are charged, prorated, as if you had kept it — so a lifecycle rule that moves files to cool after seven days plus a cleanup job that deletes them after fourteen bills you for the missing sixteen days, every time. Second, archive is offline: you cannot read an archived blob at all until you rehydrate it, which can take up to 15 hours. Archive is for compliance copies, not for “we might want this next quarter”.

A SAS (shared access signature) is a token appended to a blob URL granting whoever holds it the encoded permissions until expiry — the standard way to let a browser download an export without proxying it through FastAPI. A user delegation SAS is signed with Microsoft Entra credentials and is the type Microsoft recommends; service and account SAS are signed with the storage account key.

# Agent version: signed with the account key, valid for a year, read/write.
from azure.storage.blob import generate_blob_sas, BlobSasPermissions
from datetime import datetime, timedelta, timezone

token = generate_blob_sas(
    account_name="iffresearch", container_name="exports", blob_name=name,
    account_key=ACCOUNT_KEY,                                  # the key is in the app
    permission=BlobSasPermissions(read=True, write=True),      # write, for a download?
    expiry=datetime.now(timezone.utc) + timedelta(days=365),   # a year
)

# Better: user delegation key from a managed identity, read-only, 15 minutes.
from azure.identity import DefaultAzureCredential
from azure.storage.blob import BlobServiceClient, generate_blob_sas, BlobSasPermissions
from datetime import datetime, timedelta, timezone

svc = BlobServiceClient("https://iffresearch.blob.core.windows.net",
                        credential=DefaultAzureCredential())
now = datetime.now(timezone.utc)
udk = svc.get_user_delegation_key(now, now + timedelta(hours=1))  # Entra-signed
token = generate_blob_sas(
    account_name="iffresearch", container_name="exports", blob_name=name,
    user_delegation_key=udk,                                   # no account key
    permission=BlobSasPermissions(read=True),
    expiry=now + timedelta(minutes=15),                        # near-term expiry
)

Anyone who obtains a SAS can use it, and Azure Storage cannot audit which SAS tokens you have issued. So: HTTPS only, read-only where possible, short expiry, and a plan for revocation — which for an account-key-signed SAS means regenerating the key.

Queues: Storage Queues or Service Bus

Both decouple a producer from a worker. They are not interchangeable.

For a fan-out of independent document jobs, Storage Queues are fine and cheap. The moment you need ordering per project, deduplication, transactions or a dead-letter queue, Service Bus saves you from writing all four badly. The failure mode to look for in agent-written Storage Queue workers is the missing poison-message check:

# Agent version: a message that always fails is retried forever.
while True:
    for msg in queue.receive_messages(max_messages=32, visibility_timeout=300):
        process(msg.content)          # raises for one bad message
        queue.delete_message(msg)     # never reached for that one

# Better: bound the retries and quarantine the message.
MAX_ATTEMPTS = 5
for msg in queue.receive_messages(max_messages=32, visibility_timeout=300):
    if msg.dequeue_count > MAX_ATTEMPTS:               # Azure counts this for you
        poison_queue.send_message(msg.content)         # your own dead-letter queue
        queue.delete_message(msg)
        continue
    try:
        process(msg.content)
        queue.delete_message(msg)
    except Exception:
        logger.exception("attempt %s failed", msg.dequeue_count)  # leave it to reappear

Without the dequeue_count check the lease expires, the message becomes visible again, and the worker retries it until someone notices the bill.

Cosmos DB and Redis: earn them

Cosmos DB is a distributed document database with single-digit-millisecond reads and multi-region writes. You pay in Request Units (RU/s), a normalised unit covering CPU, IOPS and memory: a point read of a 1 KB item by id and partition key costs 1 RU, queries cost more, and strong consistency roughly doubles read cost. Exceed your provisioned RU/s and Cosmos DB throttles with HTTP 429, so an app that worked last month can start failing purely because traffic grew. Provisioned RU/s also apply in every region the account replicates to, so the bill multiplies by region count.

It is the right answer for genuine scale and genuine document workloads with a good partition key. It is agent-driven overkill when the requirement was “store some JSON”, which JSONB in Postgres handles with joins and transactions included. The tell is cross-partition queries everywhere and a partition key chosen for convenience.

Redis is an in-memory store for the cache-aside pattern, session state, rate-limit counters and light queues. One piece of news matters more than the tiers: Microsoft has announced a retirement timeline for all Azure Cache for Redis SKUs and recommends moving to Azure Managed Redis, which runs Redis Enterprise across Memory Optimized, Balanced, Compute Optimized and Flash Optimized tiers. An agent that scaffolds a new Azure Cache for Redis instance today has created a migration for you.

The rule that keeps caches honest: a cache must be safe to delete entirely. If flushing it loses data, it is not a cache. And a cache with no TTL, behind a write path that forgets to invalidate, is a machine for serving stale data forever.

Networking: firewall rules and private endpoints

Flexible Server offers two networking models, chosen at creation:

• Public access keeps a publicly resolvable endpoint and allows only the source IP ranges you list as firewall rules. You can add a private endpoint alongside.
• Private access injects the server into a delegated subnet of your virtual network, with no public endpoint at all, reached on a private IP. It needs a private DNS zone to resolve the hostname.

Three things to know. Firewall rule changes take up to five minutes to take effect, so an immediate failure after adding your IP is not proof it is wrong. Rules must be IPv4. And the mode cannot be changed after creation, nor can you restore a backup across the public/private boundary — one of the few genuinely hard-to-undo decisions in the stack.

Then there is the checkbox. “Allow public access from any Azure service within Azure to this server” — in Bicep, a firewall rule with start and end IP 0.0.0.0 — does not mean “my Azure resources”. Microsoft’s documentation says plainly that it admits connections from other customers’ subscriptions, because many Azure services have no fixed outbound IP. With it on, your database password is the only remaining control. A legitimate stopgap while you get VNet integration working; not a destination.

// Agent version: the whole of Azure can reach your database.
resource anyAzure 'Microsoft.DBforPostgreSQL/flexibleServers/firewallRules@2024-08-01' = {
  parent: pg
  name: 'AllowAllAzureServices'
  properties: { startIpAddress: '0.0.0.0', endIpAddress: '0.0.0.0' }  // all Azure
}

// Better: the app's outbound addresses only, one rule per address.
resource appOutbound 'Microsoft.DBforPostgreSQL/flexibleServers/firewallRules@2024-08-01' = [
  for (ip, i) in split(api.properties.possibleOutboundIpAddresses, ','): {
    parent: pg
    name: 'api-outbound-${i}'
    properties: { startIpAddress: ip, endIpAddress: ip }
  }
]

Connection pooling from a scaled-out app

Postgres uses one operating-system process per connection, so idle connections cost real memory and a server struggles beyond a few thousand. Each of your app instances keeps its own pool, so the arithmetic is pool size times instances times worker processes: three App Service instances of four Uvicorn workers with a pool of 20 is 240 connections before a single request arrives. Scale to eight instances during a fielding push and you exhaust the server.

# Agent version: generous pool, no thought for how many copies will run.
engine = create_async_engine(DATABASE_URL, pool_size=20, max_overflow=10)

# Better: budget per instance, recycle, and go through PgBouncer on 6432.
from sqlalchemy.ext.asyncio import create_async_engine

engine = create_async_engine(
    DATABASE_URL,                 # host unchanged, port 6432 instead of 5432
    pool_size=5,                  # x workers x instances = your real total
    max_overflow=2,
    pool_pre_ping=True,           # drop connections killed by a failover
    pool_recycle=1800,            # avoid stale connections behind idle timeouts
)

The built-in PgBouncer does the multiplexing: many client connections onto few server connections. Turn it on with the pgbouncer.enabled server parameter — no restart needed — and change your connection string’s port from 5432 to 6432. Caveats before you do: it is not available on the Burstable compute tier; the default transaction pool mode breaks session-level features, and protocol-level prepared statements need pgbouncer.max_prepared_statements set above 0; and it is single-threaded, so extreme connection churn can make PgBouncer itself the bottleneck. In zone-redundant high availability it restarts on the promoted standby, so your connection string survives failover unchanged.

Backups you have actually tested

Flexible Server takes a daily snapshot plus continuous write-ahead-log archiving, giving point-in-time restore within a retention window of 7 days by default and 35 at most, with a recovery point objective of about five minutes. That is genuinely good. Three facts change how you plan around it:

1. A restore creates a new server. It never overwrites the original. Afterwards you repoint connection strings, and reapply firewall rules, private endpoints, server parameters and logins — none of which are copied.
2. You cannot export the backup files. They live in Microsoft-managed storage; you get restore, not download. If you want a portable copy, that is pg_dump on your own schedule, written to Blob Storage.
3. Geo-redundant backup is creation-time only. You cannot add it later, and geo-restore recovers only to the last replicated backup, not to an arbitrary point in time.

Microsoft’s own answer to “how do I know my backups work” is to perform periodic restores. That is the whole practice: once a quarter, restore to a new server, connect to it, run a couple of real queries, count some rows, then delete it. Write down how long it took. That number is your actual recovery time, and it is the only one you can quote with a straight face.

What to check in your own code

• Grep the schema for BYTEA and large text columns holding file contents — those probably belong in Blob Storage.
• Grep for stored SAS tokens: any database column or config value containing sig= or ?sv=.
• Grep SAS generation calls for account_key, and for expiry windows measured in days; switch to get_user_delegation_key and minutes.
• Grep your queue workers for dequeue_count or DequeueCount. If it is absent, you have no poison-message handling.
• Count your real connections: pool size times workers (check the Uvicorn or Gunicorn --workers flag) times the App Service instance count, against the server’s limit.
• Check the PostgreSQL Networking blade for a 0.0.0.0 rule or “Allow public access from any Azure service”, and for rules that are somebody’s home broadband.
• Check whether pgbouncer.enabled is true, and whether your connection string uses 6432 or 5432.
• Check the blob lifecycle policy against your cleanup jobs for early-deletion overlap, and that nothing in a hot path reads from the archive tier.
• Check the Backup and restore blade: what is the retention window, and when did anyone last actually restore?
• List your Redis resources: anything still on Azure Cache for Redis, and anything reading from a cache nobody writes to?