A few common reasons we see for POST /task_runs/{id}/set_state “failures” (in Prefect 3.x): Orchestration decisions (not HTTP errors) - WAIT: Proposed state is delayed due to concurrency limits (tag or deployment). Response includes status WAIT and delay_seconds; no state is written yet. Typical when slots are full. - REJECT: Server replaces your proposed state (e.g., cache hit, retry scheduled, cache key too long). You’ll get a different state back and a reason. - ABORT: No state change; server refused the transition (e.g., zero concurrency limit to prevent deadlock; invalid transition due to parent flow being cancelled/failed; result/artifact persistence errors). HTTP-level errors - 401/403 Auth/config: Wrong API URL, missing/invalid credentials or auth middleware rejecting the call. - 404 Not Found: Task run ID is wrong or the run/parent flow run was deleted. - 422 Validation: Malformed request body (invalid state type, timestamps, or pydantic schema mismatch). - 409 Conflict: Database integrity/race (e.g., concurrent updates causing constraint errors). - 503 Service Unavailable: Transient DB/server issues (e.g., SQLite lock, Postgres connection/lock timeouts). Usually retryable. - Client-side timeouts / networking: httpx timeouts, proxy/DNS/connectivity problems. Large payloads may also be blocked by reverse proxies (413) before they reach the API. Result and payload issues - Large results/artifacts: If result persistence (artifact creation) fails, the server will ABORT the transition with a reason. Keep large payloads out of the state result or mark them unpersisted. - Cache key too long: Very long cache keys (e.g., > ~2000 chars) will be REJECTED with a reason. Concurrency and races - Concurrency slots: If no slots are available, you’ll get WAIT. If a limit is set to 0, you may see ABORT to prevent deadlock. - Duplicate/racing transitions: Multiple engines/workers trying to set state at once can produce 409 or orchestration rejections. What to check next - Look at the response body’s

status

(ACCEPT/REJECT/ABORT/WAIT) and

details.reason

for the exact cause. - Check Prefect server/worker logs around the time of the call for DB lock or artifact persistence errors. - Verify the task run actually exists (ID correct) and the parent flow isn’t already stopped/cancelled. - Review concurrency limits (tag/deployment) and current slot usage in the UI. - If behind a proxy, confirm request/response size limits and timeouts. Code references (for deeper debugging) - Task run set_state endpoint: server/api/task_runs.py - Orchestration result schemas (ACCEPT/REJECT/ABORT/WAIT): server/schemas/responses.py - Task orchestration logic and rules: - server/models/task_runs.py - server/orchestration/rules.py If you can share the exact HTTP status code and the response JSON (or the error/stack trace from your worker), I can pinpoint the likely cause and next steps.

Got it — a raw 500 means the server hit an unhandled exception while processing set_state. To pinpoint the cause, I’ll need a bit more detail: - Prefect version (client and server/Cloud) - Where you’re running the API (Cloud, Prefect Server, ephemeral server, Docker/K8s) and DB backend (SQLite/Postgres) - The transition you were proposing (e.g., to Completed/Failed/Retrying) and whether it’s consistent or intermittent - A snippet from the server logs at the exact time of the 500 Quick things to try/check now - Check server logs for the traceback (this is the fastest path to the root cause). If running locally, the stack trace will be in the Prefect API/uvicorn logs. On Docker/K8s, check the container/pod logs around the timestamp of the 500. - Ensure versions are compatible and up to date. If possible, upgrade to the latest Prefect 3.x on both client and server. - Try a minimal repro flow with a single task to see if all set_state calls fail or only certain transitions. - Enable debug logging while you test:

PREFECT_LOGGING_LEVEL=DEBUG

- If you’re on Prefect 2.x: there’s a known issue where using

exponential_backoff(...)

with

retries=0

can trigger a 500 during set_state. Workarounds: don’t use

exponential_backoff

when

retries=0

, or specify a list for

retry_delay_seconds

, or set

retries>=1

. Link: Issue #13794 Why 500s typically happen on set_state - Unhandled server exceptions during orchestration (e.g., a bug or unexpected edge case) - Serialization/artifact persistence errors that aren’t mapped to ABORT - Database errors not classified as retryable (these become 500 vs 503) - Version skew bugs (client/server API mismatches, especially across RCs) What to capture so I can diagnose precisely - The HTTP response body if you have it (Prefect sometimes returns an `exception_message`; the server logs will contain the full traceback) - Relevant server logs (stack trace) from the time of the error - Your task’s retry/cache settings if you’re using caching or custom retry logic If you can share the version info and a short server log snippet (or the task’s state you’re setting when this occurs), I’ll help you zero in on the exact cause and fix. For reference, the set_state endpoint and error handling live here: - server/api/task_runs.py - server/api/server.py

Thanks — since you’re on Prefect 2.14.21 (Docker, Postgres), a 500 during

task_runs/{id}/set_state

means the API hit an unhandled exception. Could you paste the actual log snippet? It didn’t come through. While we wait for that, here are the most common 2.x-specific culprits and quick checks: - retries=0 + exponential_backoff bug: In 2.x there’s a known issue where using

retry_delay_seconds=exponential_backoff(...)

with

retries=0

can cause a 500 when setting a terminal state. If you’re doing this, try: - Set

retries>=1

, or - Use a static list for

retry_delay_seconds

(e.g.,

[1]

) when

retries=0

, or - Conditional backoff:

retry_delay_seconds = None if retries == 0 else exponential_backoff(…)

Link: Issue #13794 - Client/server version skew: Make sure your worker/flow image and the API container are both 2.14.21 (or both upgraded). Mismatched versions can trigger server exceptions on set_state. - DB/migration issues: Ensure your Postgres migrations are up-to-date for the server image you’re running. A schema mismatch can surface as 500s on writes. - Concurrency/races causing unhandled DB errors: Under high parallelism, unhandled

IntegrityError

or lock/timeout conditions can bubble up as 500s in older 2.x releases. If you see Postgres errors like deadlocks/serialization failures in logs, try temporarily lowering concurrency and/or upgrade to a newer 2.x (or 3.x) where more of these are mapped to 409/503 instead of 500. - Large/unserializable result data: If a task tries to persist a very large or non-serializable result or artifact and the server path hits an unexpected error, it can 500. As a test, return a small primitive from the task (or disable result persistence) to see if the error disappears. Helpful next steps - Please paste the API container traceback around the 500. On Docker:

docker logs <your-prefect-api-container> --since=10m

Also include your task’s retry/backoff settings if you have them. - Turn on debug logs in the API container and rerun:

PREFECT_LOGGING_LEVEL=DEBUG

- Try a minimal repro flow with a single task that

return 1

to confirm whether all set_state calls fail or only specific transitions. If you share: - The exact stack trace from the API logs, - Whether you use

exponential_backoff(...)

with

retries=0

, - And whether client/server versions match, …I can pinpoint the root cause and propose a concrete fix. If an upgrade is possible, jumping to the latest 2.x (or 3.x) often resolves several set_state-related 500s.

Short answer: yes — a very large logs table can absolutely contribute to 500s, typically by creating long-running/locking queries or statement timeouts that bubble up as Internal Server Errors. While set_state itself doesn’t need to scan logs, heavy/bloated tables and indexes can slow inserts/reads elsewhere and cause lock/timeout pressure on the DB connection pool that the API depends on. What I’d check right away - Get the actual stack trace from the API container around the 500. That will tell us if it’s a Postgres timeout/lock/deadlock/integrity error vs something else. - Confirm client and API are both 2.14.21 (version skew can also throw 500s). - Inspect Postgres for contention and bloat: - Table/index sizes

SELECT
      relname AS table,
      pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
      pg_size_pretty(pg_relation_size(relid)) AS table_size,
      pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS index_size
    FROM pg_catalog.pg_statio_user_tables
    WHERE relname IN ('logs','task_run_states','task_runs','flow_run_states','flow_runs')
    ORDER BY pg_total_relation_size(relid) DESC;

- Row counts

SELECT relname, n_live_tup
    FROM pg_stat_user_tables
    WHERE relname='logs';

- Recent errors/timeouts/deadlocks in Postgres logs. Mitigations and next steps - Add/confirm retention for logs. If you don’t need 12M live rows: - Start with a conservative delete window (e.g., keep 30–90 days):

DELETE FROM logs
    WHERE timestamp < now() - interval '60 days';
    VACUUM (ANALYZE) logs;

Run in batches to avoid long locks (e.g., add LIMIT via a loop/job). - Consider partitioning logs by time (monthly partitions) so old partitions can be dropped quickly and queries stay fast. - Check indexes used by common filters (flow_run_id, task_run_id, timestamp). Missing or heavily bloated indexes will hurt. A REINDEX (off-peak) can help if there’s bloat. - Ensure autovacuum is keeping up. If not, tune autovacuum or run manual VACUUM/ANALYZE off-peak. - Reduce log volume where possible: - Lower logging level for noisy components. - If you are sending worker logs to the API, try temporarily disabling log shipping to the API to see if 500s disappear (helps confirm DB/log pressure as the cause). - Review DB and API timeouts. If your Postgres/Prefect API statement timeout is too low for current load, you’ll see intermittent 500s under contention. - If feasible, upgrade to a newer 2.x. Several DB and error-mapping fixes landed after 2.14.21, reducing cases where transient DB issues appear as 500s. Could you share: - The API container traceback at the time of a 500 - Whether you’re sending worker logs to the API - Approximate table/index sizes from the queries above With that, I can confirm if this is DB pressure from the logs table and suggest a concrete cleanup/partition plan.

Thanks — a 4GB logs table can definitely contribute to intermittent 500s on set_state in Prefect 2.14.21, especially under load. Heavy/continuous inserts into logs compete for the same DB connection pool the API uses for orchestration. If autovacuum can’t keep up or indexes are bloated, you’ll see slow queries, lock contention, and statement timeouts that bubble up as 500s. Quickest way to confirm - Temporarily disable sending logs to the API on your workers/flow containers and re-run. If 500s disappear, DB pressure from logs is the root cause.

PREFECT_LOGGING_TO_API_ENABLED=false
  PREFECT_LOGGING_LEVEL=WARNING

Apply this in your Docker environment for the worker/flow container(s), then redeploy/restart. Other tuning knobs (if you want to keep logs) - Slow down and/or reduce batch sizes so log writes are less frequent:

PREFECT_LOGGING_TO_API_ENABLED=true
  PREFECT_LOGGING_TO_API_BATCH_INTERVAL=5.0
  PREFECT_LOGGING_TO_API_BATCH_SIZE=2000000
  PREFECT_LOGGING_TO_API_MAX_LOG_SIZE=500000

- Increase API DB pool if it’s saturated:

PREFECT_SQLALCHEMY_POOL_SIZE=30
  PREFECT_SQLALCHEMY_MAX_OVERFLOW=15

Clean up and keep it healthy - Add/confirm index on timestamp (and the usual flow_run_id/task_run_id indexes):

CREATE INDEX CONCURRENTLY IF NOT EXISTS logs_timestamp_idx ON logs (timestamp);

- Delete old logs in batches (example keeps 60 days):

-- Run repeatedly/off-peak until 0 rows deleted
  WITH del AS (
    SELECT id
    FROM logs
    WHERE timestamp < now() - interval '60 days'
    ORDER BY id
    LIMIT 50000
  )
  DELETE FROM logs l
  USING del
  WHERE l.id = del.id;

- Then:

VACUUM (ANALYZE) logs;
  -- If indexes are very bloated:
  REINDEX TABLE CONCURRENTLY logs;

- Longer term: consider native time-based partitioning on logs so you can drop old partitions fast. Verify what Postgres is telling you - Look for lock/timeout/deadlock messages in Postgres logs around the 500 timestamps. - Check table and index sizes to spot bloat:

SELECT
    relname AS table,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    pg_size_pretty(pg_relation_size(relid)) AS table_size,
    pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS index_size
  FROM pg_catalog.pg_statio_user_tables
  WHERE relname IN ('logs','task_run_states','task_runs','flow_run_states','flow_runs')
  ORDER BY pg_total_relation_size(relid) DESC;

Notes - Disabling API logging only affects logs being written to the Prefect DB; you’ll still have worker/container stdout logs. - If you must stay on 2.14.21, the steps above usually resolve the pressure. If you can upgrade to a newer 2.x (or 3.x), there are improvements to DB error handling/mapping that reduce raw 500s under contention. If you can, try: 1) Set PREFECT_LOGGING_TO_API_ENABLED=false on workers and rerun a representative workload. 2) Share a snippet of the API container traceback from around a 500. 3) Post the row counts and the size query results above. I can then confirm cause and tailor a cleanup/partitioning plan. For reference to the settings in 2.x source: - Logging settings: https://github.com/PrefectHQ/prefect/blob/2.14.21/src/prefect/settings.py - API log handler batching: https://github.com/PrefectHQ/prefect/blob/2.14.21/src/prefect/logging/handlers.py

Got it — if Postgres is returning an error, that’s almost certainly the root cause of the 500s on set_state. Can you paste the exact Postgres error text (the full line with SQLSTATE/code) from your Postgres logs? That will tell us exactly which class of failure you’re hitting. Most common Postgres errors that surface as 500s on set_state (and quick fixes) - query_canceled / statement timeout (57014): Increase statement_timeout, reduce DB load, or slow log inserts (or disable API logging). - deadlock detected (40P01): Lower concurrency; ensure long-running transactions aren’t holding locks; consider shorter batches for deletes/vacuum jobs. - could not serialize access due to concurrent update (40001): Retryable conflict under high concurrency; lower parallelism; ensure indexes/ANALYZE are healthy so transactions are short. - lock not available (55P03): Similar to above; investigate blockers and long-held locks. - too many connections (53300) / remaining slots reserved: Increase Postgres max_connections or use PgBouncer; tune Prefect API pool:

PREFECT_SQLALCHEMY_POOL_SIZE=30
  PREFECT_SQLALCHEMY_MAX_OVERFLOW=15

- duplicate key / foreign key violations (23505/23503): Usually a race or schema mismatch; share the exact message to diagnose. - disk/full or out of memory (53100/53200): Free space, tune resources, or reduce log volume. Given your 4GB logs table, the fastest test to confirm DB pressure as the cause - Temporarily disable API log shipping on workers/flows:

PREFECT_LOGGING_TO_API_ENABLED=false
  PREFECT_LOGGING_LEVEL=WARNING

Redeploy/restart the worker containers. If 500s disappear, the logs table load is the trigger. Helpful diagnostics to run now - Get the exact Postgres error around the time of a 500:

docker logs <your-postgres-container> --since=10m

- Check for blocking and long-running queries:

SELECT pid, state, wait_event_type, wait_event, now()-query_start AS dur, query
  FROM pg_stat_activity
  WHERE datname = current_database()
  ORDER BY query_start;

- If you suspect locks/deadlocks, capture blockers:

SELECT bl.pid AS blocked_pid, kl.pid AS blocking_pid, a.query AS blocked_query, b.query AS blocking_query
  FROM pg_locks bl
  JOIN pg_stat_activity a ON a.pid = bl.pid
  JOIN pg_locks kl ON kl.locktype = bl.locktype
    AND kl.DATABASE IS NOT DISTINCT FROM bl.DATABASE
    AND kl.relation IS NOT DISTINCT FROM bl.relation
    AND kl.GRANTED
  JOIN pg_stat_activity b ON b.pid = kl.pid
  WHERE NOT bl.GRANTED;

If you’d like, paste: - The exact Postgres error message (with SQLSTATE/code) - Whether disabling API logging helped - Any findings from pg_stat_activity I’ll map that to a precise fix (timeouts vs locks vs connection pool vs conflicts) and provide a targeted mitigation plan.

Short answer: yes, you can increase timeouts. Which one to change depends on what’s timing out (DB vs HTTP). On Prefect 2.14.21 the key defaults are: - Server DB statement timeout (Prefect): PREFECT_API_DATABASE_TIMEOUT = 10s - Server DB connection timeout: PREFECT_API_DATABASE_CONNECTION_TIMEOUT = 5s - SDK/client HTTP timeout to the API: PREFECT_API_REQUEST_TIMEOUT = 60s - Uvicorn keep-alive (server): PREFECT_SERVER_API_KEEPALIVE_TIMEOUT = 5s If your Postgres error is “canceling statement due to statement timeout” (57014), increasing DB timeouts will help; if it’s lock/deadlock/too many connections, increasing timeouts may mask the symptom. Given your 4GB logs table, I’d also clean up/tune logs (as discussed), but here’s how to raise timeouts: Increase timeouts on the Prefect API container (Docker)

# Prefect API container (server)
PREFECT_API_DATABASE_TIMEOUT=30
PREFECT_API_DATABASE_CONNECTION_TIMEOUT=10
PREFECT_SQLALCHEMY_POOL_SIZE=15
PREFECT_SQLALCHEMY_MAX_OVERFLOW=30
PREFECT_SERVER_API_KEEPALIVE_TIMEOUT=30

Increase client HTTP timeout (workers/agents/flow images)

# Workers/agents/any SDK client
PREFECT_API_REQUEST_TIMEOUT=120

Example docker-compose snippet

services:
  prefect-server:
    image: prefecthq/prefect:2.14.21
    environment:
      PREFECT_API_DATABASE_CONNECTION_URL: <postgresql+asyncpg://prefect:pass@postgres:5432/prefect>
      PREFECT_API_DATABASE_TIMEOUT: "30"
      PREFECT_API_DATABASE_CONNECTION_TIMEOUT: "10"
      PREFECT_SQLALCHEMY_POOL_SIZE: "15"
      PREFECT_SQLALCHEMY_MAX_OVERFLOW: "30"
      PREFECT_SERVER_API_KEEPALIVE_TIMEOUT: "30"
    command: prefect server start --host 0.0.0.0

  worker:
    image: your-worker-image
    environment:
      PREFECT_API_URL: <http://prefect-server:4200/api>
      PREFECT_API_REQUEST_TIMEOUT: "120"

Postgres server-side timeouts (if you’re hitting statement/lock timeouts) - Prefer setting at DB or role level so all API sessions inherit it:

-- Set for the Prefect database
ALTER DATABASE prefect SET statement_timeout = '30s';
ALTER DATABASE prefect SET lock_timeout = '5s';
SELECT pg_reload_conf();

- You can also set per-role:

ALTER ROLE prefect SET statement_timeout = '30s';
ALTER ROLE prefect SET lock_timeout = '5s';

Notes and guidance - Increasing PREFECT_API_DATABASE_TIMEOUT gives Prefect API queries longer to finish before cancelling. Start with 20–30s; avoid “infinite” timeouts in production. - If your Postgres error is “too many connections”, raise Postgres max_connections or add PgBouncer and tune:

PREFECT_SQLALCHEMY_POOL_SIZE=20
  PREFECT_SQLALCHEMY_MAX_OVERFLOW=40

- If the error is a lock/deadlock, increasing timeouts won’t fix the root cause; reduce contention (disable or slow log writes, add indexes, batch deletes off-peak). - If requests from workers are timing out waiting on the API, raise PREFECT_API_REQUEST_TIMEOUT on the workers (e.g., 120s). If you can share the exact Postgres error text/code (e.g., 57014, 40P01, 55P03, 53300), I can recommend a precise timeout value and whether it’ll actually help versus needing query/index/retention changes.

Can you share the exact Postgres error text/code? That’ll tell us whether it’s a statement timeout, lock/deadlock, or something else. In the meantime, here’s a safe, battle‑tested way to clean a large logs table without taking Prefect down. Fastest mitigations before cleanup - Pause API log writes during maintenance (reduces lock pressure and 500s):

PREFECT_LOGGING_TO_API_ENABLED=false
  PREFECT_LOGGING_LEVEL=WARNING

- If your deletes are timing out, increase statement timeout only for your cleanup session (not globally):

-- in psql, for this session only
  SET statement_timeout = '15min';
  SET lock_timeout = '5s';

Prep: make sure you have helpful indexes

-- Speeds up time-based deletes and filters
CREATE INDEX CONCURRENTLY IF NOT EXISTS logs_timestamp_idx ON logs (timestamp);

-- These are usually already present, but confirm
CREATE INDEX CONCURRENTLY IF NOT EXISTS logs_flow_run_idx ON logs (flow_run_id);
CREATE INDEX CONCURRENTLY IF NOT EXISTS logs_task_run_idx ON logs (task_run_id);

Delete in small batches (avoids long locks and huge WAL)

DO $$
DECLARE
  rows_deleted BIGINT := 0;
BEGIN
  LOOP
    WITH del AS (
      SELECT id
      FROM logs
      WHERE timestamp < now() - interval '60 days'
      ORDER BY id
      LIMIT 50000
    )
    DELETE FROM logs l
    USING del
    WHERE l.id = del.id;

    GET DIAGNOSTICS rows_deleted = ROW_COUNT;
    EXIT WHEN rows_deleted = 0;

    PERFORM pg_sleep(0.1); -- small pause to reduce contention
  END LOOP;
END$$;

Then clean up bloat

VACUUM (ANALYZE) logs;

-- If indexes are very bloated, consider:
REINDEX TABLE CONCURRENTLY logs;
-- Avoid VACUUM FULL during business hours (it takes an exclusive lock)

If you’re still hitting errors, here’s what they usually mean and what to do - canceling statement due to statement timeout (57014): Raise the session’s statement_timeout (as above) and keep batches small (e.g., 10–50k). Run during off‑peak. - deadlock detected (40P01): Something else is writing to logs concurrently. Temporarily disable API logging, or run cleanup during a maintenance window. - could not obtain lock / lock timeout (55P03): Reduce batch size, pause API log shipping, or retry later. Check blockers:

SELECT bl.pid AS blocked_pid, kl.pid AS blocking_pid, a.query AS blocked_query, b.query AS blocking_query
  FROM pg_locks bl
  JOIN pg_stat_activity a ON a.pid = bl.pid
  JOIN pg_locks kl ON kl.locktype = bl.locktype
    AND kl.DATABASE IS NOT DISTINCT FROM bl.DATABASE
    AND kl.relation IS NOT DISTINCT FROM bl.relation
    AND kl.GRANTED
  JOIN pg_stat_activity b ON b.pid = kl.pid
  WHERE NOT bl.GRANTED;

- too many connections (53300): Increase Postgres max_connections or front with PgBouncer; also tune the API pool:

PREFECT_SQLALCHEMY_POOL_SIZE=20
  PREFECT_SQLALCHEMY_MAX_OVERFLOW=40

- replication/WAL pressure: Lower batch size and add small sleeps; schedule cleanup off‑peak. After cleanup, put protections in place - Keep logs from piling up: run a small daily job (same batched DELETE) to keep, say, 30–90 days. - Consider partitioning logs by month going forward so you can drop old partitions quickly. - Re-enable API log shipping only if needed. If DB pressure is a concern, keep:

PREFECT_LOGGING_TO_API_ENABLED=true
  PREFECT_LOGGING_TO_API_BATCH_INTERVAL=5.0
  PREFECT_LOGGING_TO_API_BATCH_SIZE=2000000
  PREFECT_LOGGING_TO_API_MAX_LOG_SIZE=500000

References (2.14.21) - Logging settings (including PREFECT_LOGGING_TO_API_*): settings.py - API log batching handler: logging/handlers.py - Logs ORM model: database/orm_models.py

If you can paste the exact Postgres error (including SQLSTATE), I’ll tailor the batch size/timeout and any additional steps precisely for your case.

That error is from PostgreSQL, not Prefect: the container ran out of shared memory while VACUUM/ANALYZE was allocating/resizing a dynamic shared memory segment. In Docker, /dev/shm defaults to 64MB, which is often too small for VACUUM/ANALYZE on big tables. Quick confirm - Inside the Postgres container:

df -h /dev/shm
  df -h

If /dev/shm is ~64M or nearly full, that’s the culprit. Also check overall disk free space. Workarounds right now - Run VACUUM with parallel maintenance disabled for this session (uses less shared memory):

psql ...
  SET max_parallel_maintenance_workers = 0;
  SET max_parallel_workers = 0;
  SET max_parallel_workers_per_gather = 0;
  VACUUM (ANALYZE, VERBOSE) logs;

- If you still hit timeouts, raise only this session’s timeouts while you vacuum:

SET statement_timeout = '15min';
  SET lock_timeout = '5s';

Permanent fix (recommended) - Increase the container’s shared memory size. Docker run:

docker run ... --shm-size=1g postgres:14

docker-compose:

services:
    postgres:
      image: postgres:14
      shm_size: 1gb
      # ... your existing env/volumes/ports

Kubernetes (mount a larger in‑memory /dev/shm):

volumeMounts:
  - mountPath: /dev/shm
    name: dshm
  volumes:
  - name: dshm
    emptyDir:
      medium: Memory
      sizeLimit: 1Gi

Then retry your maintenance: 1) Batch-delete old logs (small chunks), 2) VACUUM (ANALYZE) logs; 3) REINDEX TABLE CONCURRENTLY logs (if indexes are bloated). Also consider reducing log pressure going forward: - Temporarily disable API log shipping during cleanup:

PREFECT_LOGGING_TO_API_ENABLED=false
  PREFECT_LOGGING_LEVEL=WARNING

- Add a scheduled, batched cleanup to keep only N days of logs. If you can share your compose/K8s setup for Postgres, I can suggest the exact change to bump /dev/shm and the safest order of operations for the cleanup.

Short answer: not strictly required, but recommended after a large delete. - Autovacuum will eventually vacuum and analyze the table, but after bulk deletes it’s best to run VACUUM (ANALYZE) once to: - Reclaim dead tuples for reuse (note: this does not usually shrink the on-disk file) - Update planner statistics so queries don’t go sideways - You do not need to VACUUM after every small batch; run it once after your batched deletes finish (or periodically every N batches). - If you need to actually return disk space to the OS, VACUUM won’t do that; you need VACUUM FULL (exclusive lock, rewrite) or pg_repack (online, requires extension). Given your earlier /dev/shm error, run with parallelism disabled (or increase container shm):

-- In a psql session for just this run
SET max_parallel_maintenance_workers = 0;
SET max_parallel_workers = 0;
SET max_parallel_workers_per_gather = 0;

-- If long-running, raise just this session’s timeout
SET statement_timeout = '15min';
SET lock_timeout = '5s';

VACUUM (ANALYZE, VERBOSE) logs;

Optional follow-ups - If indexes are bloated after big deletes:

REINDEX TABLE CONCURRENTLY logs;

- If you’re tight on disk and must shrink immediately (off-peak):

VACUUM (ANALYZE) logs;         -- safe
-- OR, to shrink file size (disruptive):
VACUUM FULL (ANALYZE) logs;    -- exclusive lock during rewrite

Best practice going forward - Keep logs from piling up (daily batched deletes or partition by month). - Tune autovacuum just for the logs table so it triggers earlier:

ALTER TABLE logs SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold    = 5000,
  autovacuum_analyze_scale_factor= 0.02,
  autovacuum_analyze_threshold   = 5000
);

If you still hit shared memory errors, bump container /dev/shm (e.g., docker run --shm-size=1g) or keep parallel maintenance disabled for these runs.

Short answer: deleting old rows helps, but on its own it’s usually not enough. Inserts can still time out if the table/indexes are badly bloated or there’s heavy contention. Do cleanup + maintenance + guardrails. Recommended plan 1) Pause new inserts during maintenance (optional but helps):

PREFECT_LOGGING_TO_API_ENABLED=false
PREFECT_LOGGING_LEVEL=WARNING

2) Delete in batches to your target retention (e.g., keep 60–90 days):

DO $$
DECLARE rows_deleted BIGINT := 0;
BEGIN
  LOOP
    WITH del AS (
      SELECT id
      FROM logs
      WHERE timestamp < now() - interval '60 days'
      ORDER BY id
      LIMIT 50000
    )
    DELETE FROM logs l
    USING del
    WHERE l.id = del.id;

    GET DIAGNOSTICS rows_deleted = ROW_COUNT;
    EXIT WHEN rows_deleted = 0;
    PERFORM pg_sleep(0.1);
  END LOOP;
END$$;

3) Vacuum and refresh stats (required after large deletes):

-- Disable parallel maintenance if /dev/shm is small
SET max_parallel_maintenance_workers = 0;
SET max_parallel_workers = 0;
SET max_parallel_workers_per_gather = 0;

-- Give maintenance time to finish
SET statement_timeout = '15min';
SET lock_timeout = '5s';

VACUUM (ANALYZE, VERBOSE) logs;

4) Rebuild bloated indexes (this is what really speeds up inserts):

REINDEX TABLE CONCURRENTLY logs;
-- Or target biggest indexes first if needed:
-- REINDEX INDEX CONCURRENTLY logs_timestamp_idx;
-- REINDEX INDEX CONCURRENTLY logs_flow_run_idx;
-- REINDEX INDEX CONCURRENTLY logs_task_run_idx;

5) Prevent recurrence - Keep only N days with a daily batched job (same pattern as step 2). - Ensure helpful indexes exist but avoid extras that amplify every insert:

CREATE INDEX CONCURRENTLY IF NOT EXISTS logs_timestamp_idx ON logs (timestamp);
CREATE INDEX CONCURRENTLY IF NOT EXISTS logs_flow_run_idx ON logs (flow_run_id);
CREATE INDEX CONCURRENTLY IF NOT EXISTS logs_task_run_idx ON logs (task_run_id);

- Tune autovacuum just for the logs table so bloat doesn’t build up:

ALTER TABLE logs SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold    = 5000,
  autovacuum_analyze_scale_factor= 0.02,
  autovacuum_analyze_threshold   = 5000
);

- Reduce future log volume if possible:

PREFECT_LOGGING_TO_API_ENABLED=true
PREFECT_LOGGING_TO_API_BATCH_INTERVAL=5.0
PREFECT_LOGGING_TO_API_BATCH_SIZE=2000000
PREFECT_LOGGING_TO_API_MAX_LOG_SIZE=500000
PREFECT_LOGGING_LEVEL=WARNING

- Consider monthly partitioning of logs going forward; it keeps the active partition small and makes cleanup a fast DROP PARTITION. If timeouts persist after the above: - Share the exact Postgres error code/text (e.g., 57014/40P01/55P03/53300). - Temporarily increase the Prefect API DB statement timeout to ride through transient slowness while you clean up:

PREFECT_API_DATABASE_TIMEOUT=30

- Check for blockers:

SELECT bl.pid AS blocked_pid, kl.pid AS blocking_pid, a.query AS blocked_query, b.query AS blocking_query
FROM pg_locks bl
JOIN pg_stat_activity a ON a.pid = bl.pid
JOIN pg_locks kl ON kl.locktype = bl.locktype
  AND kl.DATABASE IS NOT DISTINCT FROM bl.DATABASE
  AND kl.relation IS NOT DISTINCT FROM bl.relation
  AND kl.GRANTED
JOIN pg_stat_activity b ON b.pid = kl.pid
WHERE NOT bl.GRANTED;

So: deleting old records is a key part of the fix, but you’ll typically need a VACUUM (ANALYZE) and a REINDEX to actually restore fast inserts, plus ongoing retention to keep it healthy.