> For the complete AWA documentation index, see [`llms.txt`](../llms.txt).

# Deploying awa on managed Postgres

This page collects the operational gotchas and sizing data we learned running awa workers against Google Cloud SQL and AlloyDB in staging. Most of it applies to any managed Postgres (Amazon RDS / Aurora, Azure Database for PostgreSQL, etc.); GCP-specific advice is called out.

The reference data here was captured on a single 4-pod consumer × 4-pod producer fleet against a dedicated benchmarking database on each engine, with `enqueue_shards = 16` and the queue-storage direct COPY producer path. See [Benchmarking](../benchmarking/index.md) for methodology and the `awa-bench-driver` reports for raw numbers and EXPLAIN traces.

## Pick a Postgres version

**Use Postgres 18 if it's available.** On AlloyDB specifically we measured a hard cap at 4 vCPU on PG17 (~2 k jobs/s sustained, with `AccessShareLock` waiters queueing behind ring-rotation `ACCESS EXCLUSIVE` operations from the maintenance loop) that disappeared after an in-place upgrade to PG18 — the same 4 vCPU instance jumped to ~5 k jobs/s sustained, matching Cloud SQL PG18 at the same shape exactly.

Cloud SQL on PG17 is fine in our measurements; we did not see the same cap there. If you're on AlloyDB PG17 today and considering an upgrade for other reasons, awa throughput is one more reason to do it.

## Pick a vCPU size

The numbers below are _steady-state job completion_ and _burst enqueue_ on the queue-storage direct COPY producer path with `bench.noop` handlers (no per-job work). Real handlers reduce the consumer side proportionally; the burst-enqueue side is producer-bound and largely indifferent to handler cost.

| Engine | vCPU | PG | Sustained completed | 1 M spike enqueue | 1 M backlog drain |
| --- | --: | --: | --: | --: | --: |
| Cloud SQL | 1 | 17 | ~500 jobs/s | ~40,000 inserts/s | — |
| Cloud SQL | 4 | 18 | ~5,000 jobs/s | ~70,000 inserts/s | — |
| Cloud SQL | 16 | 18 | ~14,000 jobs/s | ~77,000 inserts/s | ~14,000 jobs/s |
| AlloyDB | 4 | 18 | ~5,200 jobs/s | ~52,000 inserts/s | ~9,000 jobs/s |

Reading the table:

- **Sustained completed** scales roughly linearly with vCPU on Cloud SQL PG18: 4 → 5 k/s, 16 → 14 k/s. AlloyDB PG18 4 vCPU matches Cloud SQL PG18 4 vCPU end-to-end at this scale.
- **Burst enqueue rate** saturates near 70–80 k/s on any 4 vCPU or larger instance — the bottleneck is the producer-side COPY pipeline and connection fan-out, not the DB CPU. Even a 1 vCPU Cloud SQL absorbed 1 M jobs at 40 k/s.
- **Backlog drain** matches the steady-state completion rate after awa-model v021 (PR #263) — heavy depth no longer collapses the dispatcher to ~10× slower than equilibrium.

Sizing rule of thumb: pick the vCPU count for your steady-state completion target, not the burst. Burst-only workloads can run on much smaller instances than their peak insertion rate would suggest.

## IAM and Cloud SQL connectivity

Migrations and custom queue-storage schema preparation need DDL-capable credentials. Ordinary workers can run with the [runtime database grants](../security/index.md) once `awa migrate` has materialized the default `awa` substrate, or once `awa storage prepare-queue-storage-schema` has materialized a custom queue-storage schema. If you rely on fresh-install auto-prepare from the first worker startup instead, that worker connection also needs the DDL privileges required by `prepare_schema()`.

> **0.7 design note (not yet shipped):** [ADR-042](../adr/042-caller-owned-finalization-transactions/index.md) uses one ordinary transaction, transaction-scoped advisory locks, and no session state, so its caller-owned finalization protocol is compatible with transaction-mode pgbouncer/pgcat and RDS Proxy. Queue `LISTEN` remains session-scoped: [ADR-033](../adr/033-per-key-execution-control/index.md) grant-close notifications require a direct/session-pooled listener or the #374 gated polling fallback. The sending `pg_notify` call itself remains transactional and pooler-safe.

### Cloud SQL with IAM authentication

If your runtime SA authenticates via Cloud SQL IAM (recommended over password-based auth in GCP), the SA needs `cloudsqlsuperuser` to call `CREATE SEQUENCE`, `CREATE TABLE`, and a handful of other DDL the queue-storage substrate uses. Granting the role is a one-shot manual step — Cloud SQL doesn't expose it through the IAM grant — so it typically lives next to your Terraform that creates the IAM user:

```sql
-- Run once per instance as a built-in superuser (e.g. `postgres`).
GRANT cloudsqlsuperuser TO "operator@PROJECT.iam";
```

If you skip this for the role that runs migrations / schema preparation, the setup job can fail with ownership or DDL permission errors before workers ever reach the queue-storage engine.

### AlloyDB

AlloyDB grants `alloydbsuperuser` to IAM SAs automatically — no manual GRANT step needed.

### Cloud SQL Auth Proxy / AlloyDB Auth Proxy as a native sidecar

Both Cloud SQL Auth Proxy 2.x and AlloyDB Auth Proxy 1.x work well as Kubernetes native sidecars (initContainer with `restartPolicy: Always`) — preferable to a regular container because the proxy starts before the main container and the lifecycle is tied to the pod. Example consumer-pod fragment:

```yaml
spec:
  serviceAccountName: awa-runtime
  initContainers:
    - name: auth-proxy
      image: gcr.io/cloud-sql-connectors/cloud-sql-proxy:2.13.0
      imagePullPolicy: IfNotPresent
      restartPolicy: Always # makes it a native sidecar
      args:
        - "--auto-iam-authn"
        - "--port=5432"
        - "PROJECT:REGION:INSTANCE"
      resources:
        requests: { cpu: 100m, memory: 128Mi }
        limits: { cpu: 500m, memory: 256Mi }
  containers:
    - name: worker
      image: ghcr.io/your-org/your-worker:tag
      env:
        - name: DATABASE_URL
          value: "postgres://operator%40PROJECT.iam@127.0.0.1:5432/your_db?sslmode=disable"
```

The same shape works for AlloyDB; swap the image to `gcr.io/alloydb-connectors/alloydb-auth-proxy:1.13.0` and the connection arg to `projects/PROJECT/locations/REGION/clusters/CLUSTER/instances/INSTANCE`.

## `enqueue_shards`: set this explicitly

The default `awa.queue_meta.enqueue_shards = 1` means every producer contends on a single enqueue-head row per `(queue, priority)`. With multiple concurrent producers this serialises the entire enqueue path.

**Recommendation: insert a `queue_meta` row for every high-volume queue and set `enqueue_shards` to at least 4 only when that queue can accept partitioned FIFO.** Keep `enqueue_shards = 1` for queues that require strict FIFO across the whole `(queue, priority)` lane. A 16-producer same-queue sweep in awa's own benchmark measured 1.0× / 1.60× / 2.75× / 3.69× at S=1/2/4/8 — that's about a 2.75× lift from S=1 to S=4 on a contended queue. The staging benchmark sweep at S=4/8/16/32 (a different, 4-producer setup with the producer not the bottleneck) showed no material difference between those values at 10 k offered.

```sql
INSERT INTO awa.queue_meta (queue, enqueue_shards)
VALUES ('your_queue', 4)
ON CONFLICT (queue)
DO UPDATE SET enqueue_shards = EXCLUDED.enqueue_shards;
```

Use an upsert — first-enqueue may create lane rows before any operator inserts a `queue_meta` row, so a plain `UPDATE` can quietly affect zero rows.

Going higher than 4 only helps when producer-side head-row contention is your bottleneck; raise it after measuring. Lowering it later is safe, but the FIFO contract changes — see [enqueue-head sharding](../configuration/index.md#sharding-the-enqueue-head-per-queue) and [ADR-025](../adr/025-sharded-enqueue-heads/index.md).

## Producer path: use the direct COPY entry point

For any high-volume producer running against managed Postgres (rather than a Docker-localhost benchmark) prefer the queue-storage native COPY path:

- **Rust:** `QueueStorage::enqueue_params_copy(pool, &jobs)`.
- **Python:** `client.enqueue_many_copy(jobs)`.

The compat-friendly `insert_many_copy_from_pool` / `client.insert_many_copy` path routes each row through the `awa.insert_job_compat()` SQL function once per row. On a real DB the per-row function-call cost (lane head update, NOTIFY, admin metadata) sums to ~100–150 ms per row on AlloyDB through the auth-proxy — fine when the goal is "one writer, strict compatibility", catastrophic when the goal is "burst 10⁶ jobs in seconds." The direct path runs at the inserts-per-second numbers in the sizing table above.

See [Producer path choice](../configuration/index.md#producer-path-choice) for the full surface comparison.

## MVCC discipline: long-running readers pin the whole database

Awa's queue storage keeps its hot path append-only and reclaims segments with `TRUNCATE`, but it is still a Postgres schema and is subject to the database-wide MVCC horizon. Any transaction holding an old snapshot — a `psql` session left `idle in transaction`, a BI tool with a long read transaction, `pg_dump`, a stuck migration — prevents vacuum and Awa's maintenance pruning from reclaiming row versions created after that snapshot, in every table in the database.

Under sustained load this degrades throughput, not correctness: jobs are not lost, but completion rate sags and backlog accumulates until the pinning transaction releases, after which the backlog drains. Every per-row Postgres queue (River, Oban, pg-boss, Graphile, pgmq) shares this failure mode. The long-horizon [benchmark scenario](../benchmarking/index.md) reproduces it on demand; [Troubleshooting](../troubleshooting/index.md#dead-tuples-growing-in-queue-storage) covers diagnosis on a live system.

Operational rules, in priority order:

### 1. Keep long-running analytical readers off the queue database

Reporting, BI dashboards, and ad-hoc analytical sessions belong on a separate database — a different instance, a read replica, or an ETL copy. The Awa admin UI and `queue_counts` reads are short bounded transactions and are fine against the primary; the thing to keep away is any client that holds a transaction (or repeatable-read snapshot) open for minutes.

### 2. Bound non-Awa sessions with timeouts

For roles that humans or reporting tools use against the same database:

```sql
ALTER ROLE analyst SET idle_in_transaction_session_timeout = '5min';
ALTER ROLE analyst SET statement_timeout = '5min';
```

Managed engines expose the same settings as instance flags if you would rather enforce a database-wide ceiling and exempt specific roles.

### 3. Alert on old transactions

The leading indicator is `pg_stat_activity.xact_start` age, not dead-tuple counts — by the time dead tuples are visible the horizon has been pinned for a while. A query suitable for a Grafana/postgres-exporter gauge:

```sql
SELECT COALESCE(max(extract(epoch FROM now() - xact_start)), 0) AS max_xact_age_seconds
FROM pg_stat_activity
WHERE datname = current_database()
  AND xact_start IS NOT NULL
  AND backend_type = 'client backend';
```

Alert when `max_xact_age_seconds` exceeds a few multiples of your normal longest job; `300` is a reasonable starting threshold for short-job workloads. The drill-down query for finding the offending session is in [Inspect long transactions](../troubleshooting/index.md#inspect-long-transactions).

### 4. Give autovacuum enough capacity

The queue-storage substrate ships aggressive per-table autovacuum storage parameters on its hot mutable tables (ring state, heads, leases, claims), so per-table thresholds are normally not yours to tune. What managed-Postgres defaults often starve is instance-wide vacuum capacity. If the [table churn query](../troubleshooting/index.md#inspect-table-churn) shows `autovacuum_count` flat while dead tuples climb on `leases%` or `attempt_state`, raise these flags:

- `autovacuum_max_workers` (default 3 is low for a busy queue database sharing the instance with application tables)
- `autovacuum_vacuum_cost_limit` / lower `autovacuum_vacuum_cost_delay` so workers actually keep up
- on Cloud SQL / AlloyDB these are instance flags (`gcloud sql instances patch --database-flags=...`); a restart may be required

Note that no autovacuum setting helps while a horizon is pinned — vacuum cannot reclaim what an open snapshot can still see. Rules 1–3 prevent the pin; rule 4 makes recovery fast after it releases.

## Things that broke for us in staging — worth pre-empting

These all eventually have fixes or workarounds, but each one cost hours the first time:

- **Concurrent worker startup on a fresh DB used to expose `prepare_schema` races.** The #264 fix serializes schema preparation under a transaction-scoped advisory lock and keeps the install body on that same connection. Still prefer running `awa migrate` as the explicit rollout step for the default `awa` substrate, or `awa storage prepare-queue-storage-schema` for custom queue-storage schemas, in production: it gives you one clear DDL owner, avoids first-request startup surprises, and lets workers use runtime-only grants.

- **PG18-on-AlloyDB upgrade preserves the schema and v021 indexes across the cut-over.** No re-bootstrap needed; just verify the upgrade completed cleanly with `gcloud alloydb clusters describe` showing `databaseVersion: POSTGRES_18` and run a smoke enqueue/claim.

- **Cloud SQL `must be owner of database` errors when cleaning up.** Test databases created by `partly_staging` (or whatever non-IAM user ran your migration job) can't be dropped by the IAM SA. Use the built-in `postgres` user via password from the `*-postgres-password` Secret Manager secret, or grant ownership over before dropping.

## Next

- [Configuration knobs](../configuration/index.md) — `enqueue_shards`, claimers, retention, etc.
- [PostgreSQL roles and privileges](../security/index.md) — the SQL grants the runtime expects.
- [Migration guide](../migrations/index.md) — schema version table and upgrade ordering, including v021's shard-aware lane indexes.
- [Deployment guide](../deployment/index.md) — generic deployment patterns (Docker, K8s, rolling deploys) not specific to managed Postgres.
- [Benchmarking notes](../benchmarking/index.md) — the in-repo benchmark harness and reference numbers.