Thanks for the detail — this is a classic symptom of database pressure in self‑hosted Prefect Server (seen in both 2.x and 3.x). The 500s you’re seeing typically come from: - Long‑running queries on large tables (logs/states/events) hitting the server’s statement timeout - Connection pool exhaustion when you scale API pods or workflow concurrency faster than Postgres can serve - Table/index bloat after months of inserts without regular pruning/vacuum Below is a concrete, 3‑phase plan you can run with on Prefect 2.x (all of it applies to 3.x as well), plus a couple of quick mitigations you can do today. Quick mitigations (do these first) - Temporarily dial down workflow concurrency until DB pressure drops - Increase DB timeouts and pool sizes on the API service and restart it:

# Recommended starting points for medium/large loads
  PREFECT_API_DATABASE_TIMEOUT=30
  PREFECT_API_DATABASE_CONNECTION_TIMEOUT=10
  PREFECT_SQLALCHEMY_POOL_SIZE=25
  PREFECT_SQLALCHEMY_MAX_OVERFLOW=10

- If you’ve scaled server replicas via HPA, remember every replica has its own pool. Ensure Postgres max_connections and instance resources can handle the aggregate. Phase 1 — Diagnose where the bloat and timeouts are coming from - Confirm you’re on latest DB schema (indices matter):

prefect server database upgrade -y

We verified this command exists:

prefect server database --help

- Get table sizes (you’ll usually find log, flow_run_state, task_run_state, events near the top):

SELECT
    schemaname, 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
  ORDER BY pg_total_relation_size(relid) DESC
  LIMIT 15;

- Look for slow queries and pool pressure in Postgres:

-- Log queries > 1s (requires server config / restart)
  SET log_min_duration_statement = 1000;

  -- Active connections
  SELECT datname, usename, state, count(*) 
  FROM pg_stat_activity 
  GROUP BY datname, usename, state
  ORDER BY count(*) DESC;

- Confirm critical indices exist:

-- Logs
  SELECT indexname, indexdef
  FROM pg_indexes
  WHERE tablename='log';

  -- States
  SELECT indexname, indexdef
  FROM pg_indexes
  WHERE tablename IN ('flow_run_state','task_run_state');

If you’re missing the composite log index, add it concurrently:

CREATE INDEX CONCURRENTLY IF NOT EXISTS ix_log__flow_run_id_timestamp
  ON log (flow_run_id, timestamp);

Phase 2 — Prune fast‑growing tables and VACUUM - Logs: Prefect does not auto‑prune logs. Delete by time window:

-- Keep 30 days (adjust as needed)
  DELETE FROM log WHERE timestamp < NOW() - INTERVAL '30 days';

- States: Keep only the most recent N per run and/or prune older than a window. Example keeps 5 most recent per flow_run older than 90d:

WITH old AS (
    SELECT id FROM (
      SELECT id, flow_run_id, timestamp,
             row_number() OVER (PARTITION BY flow_run_id ORDER BY timestamp DESC) AS rn
      FROM flow_run_state
    ) s
    WHERE rn > 5 AND timestamp < NOW() - INTERVAL '90 days'
  )
  DELETE FROM flow_run_state WHERE id IN (SELECT id FROM old);

Do the same for task_run_state:

WITH old AS (
    SELECT id FROM (
      SELECT id, task_run_id, timestamp,
             row_number() OVER (PARTITION BY task_run_id ORDER BY timestamp DESC) AS rn
      FROM task_run_state
    ) s
    WHERE rn > 5 AND timestamp < NOW() - INTERVAL '90 days'
  )
  DELETE FROM task_run_state WHERE id IN (SELECT id FROM old);

- Events: Prefect’s Event Persister trims by retention (default ~7 days) if enabled. If you need an immediate cut:

DELETE FROM events WHERE occurred < NOW() - INTERVAL '7 days';
  DELETE FROM event_resources WHERE occurred < NOW() - INTERVAL '7 days';

- Artifacts (optional, if you store many):

DELETE FROM artifact WHERE created < NOW() - INTERVAL '90 days';

- After big deletes, reclaim performance and space:

VACUUM (ANALYZE, VERBOSE) log;
  VACUUM (ANALYZE, VERBOSE) flow_run_state;
  VACUUM (ANALYZE, VERBOSE) task_run_state;

  -- If you need on-disk space back immediately, schedule downtime and run:
  VACUUM (FULL, ANALYZE, VERBOSE) log;

Note: VACUUM FULL locks tables — do it in a maintenance window. Phase 3 — Make it sustainable (retention + settings) - Set/confirm event retention and service settings:

# Enabled by default; trims events older than retention
  PREFECT_API_SERVICES_EVENT_PERSISTER_ENABLED=true
  # Typical retention
  PREFECT_EVENTS_RETENTION_PERIOD="7 days"
  # For high event volumes, consider:
  PREFECT_API_SERVICES_EVENT_PERSISTER_BATCH_SIZE=100
  PREFECT_API_SERVICES_EVENT_PERSISTER_FLUSH_INTERVAL=1

If you do not need events right now, you can disable to reduce DB write load:

PREFECT_API_SERVICES_EVENT_PERSISTER_ENABLED=false

- Put log retention on a schedule (there’s no built‑in auto‑cleanup for logs). You can run a small “DB housekeeping” job via cron or as a Prefect flow that executes the SQL above weekly. - Keep DB statistics fresh:

-- As a weekly job if autovacuum isn't enough
  ANALYZE log;
  ANALYZE flow_run_state;
  ANALYZE task_run_state;

- Tune server DB settings for your scale:

PREFECT_API_DATABASE_TIMEOUT=30         # 30–60 typical for large datasets
  PREFECT_API_DATABASE_CONNECTION_TIMEOUT=10
  PREFECT_SQLALCHEMY_POOL_SIZE=25         # Size per API pod
  PREFECT_SQLALCHEMY_MAX_OVERFLOW=10

If you use many API replicas, ensure Postgres max_connections and instance resources can handle sum(pool_size + overflow) across all replicas. If you introduce pgBouncer, coordinate pool behavior accordingly. What typically causes 500s under load - Statement timeouts for long queries (common on large log/state tables) - Pool acquisition timeouts (too many API requests vs pool size) - Table bloat causing seq scans or slow index scans - Missing indices (especially log(flow_run_id, timestamp)) Good to know - The Prefect 2.x and 3.x server share the same DB architecture and operational patterns; addressing the above in 2.x will carry over when you upgrade. - Staying current on migrations adds important indices. Use:

prefect server database upgrade -y

If you can share a few details, I can tailor the exact pruning SQL and settings: - Postgres version and instance size - Current DB size and top 10 tables by size (query above) - Whether events are enabled and approximate run/log volume per day - Number of Prefect server replicas and current values for the timeout/pool env vars - A sample of the slow query messages or Postgres logs (e.g., statement timeouts vs connection timeouts) I can also help you turn the pruning into a safe, idempotent weekly job and review index coverage specific to your workload.

Short answer: Not with the string “2d”. In Prefect 2.x,

PREFECT_EVENTS_RETENTION_PERIOD

is parsed as a Python timedelta using Pydantic v1. The supported input formats are: - ISO 8601 duration: e.g.,

P2D

(2 days),

PT5S

(5 seconds) - Django-style: e.g.,

2 days, 0:00:00

- Plain seconds as a number string: e.g.,

172800

(for 2 days) Examples that work: -

P2D

-

2 days, 0:00:00

-

172800

Examples that do not work: -

2d

-

2 days

(without the time part) So use

P2D

if you want a concise 2‑day retention.

Short answer: No.

PREFECT_EVENTS_RETENTION_PERIOD

only applies to the Events subsystem. It will trim rows in the events tables (e.g.,

events

,

event_resources

) older than the retention window. It does not touch: -

log

-

flow_run_state

/

task_run_state

-

flow_run

/

task_run

-

artifact

There is no built‑in automatic retention for logs or run/state tables in Prefect Server (2.x or 3.x). If you want a 2‑day retention for those, you’ll need to add your own pruning job (SQL, cron, or a small Prefect “housekeeping” flow). Examples:

-- Logs older than 2 days
DELETE FROM log
WHERE timestamp < NOW() - INTERVAL '2 days';

-- Flow run state history: keep the 5 most recent per run; prune the rest older than 2 days
WITH old AS (
  SELECT id FROM (
    SELECT id, flow_run_id, timestamp,
           row_number() OVER (PARTITION BY flow_run_id ORDER BY timestamp DESC) AS rn
    FROM flow_run_state
  ) s
  WHERE rn > 5 AND timestamp < NOW() - INTERVAL '2 days'
)
DELETE FROM flow_run_state WHERE id IN (SELECT id FROM old);

-- Do the same for task run state
WITH old AS (
  SELECT id FROM (
    SELECT id, task_run_id, timestamp,
           row_number() OVER (PARTITION BY task_run_id ORDER BY timestamp DESC) AS rn
    FROM task_run_state
  ) s
  WHERE rn > 5 AND timestamp < NOW() - INTERVAL '2 days'
)
DELETE FROM task_run_state WHERE id IN (SELECT id FROM old);

If you want the pruning to apply only to terminal/non‑terminal runs, we can tailor the WHERE clause to include or exclude runs in terminal states. Share your preference and I’ll provide the exact queries for your schema.

Great question. Short version: Prefect doesn’t auto‑delete logs/run history by default because retention is a policy decision with very different requirements across users, and deleting that data can break expectations around auditability, debugging, and historical analytics. Why Prefect does not auto‑prune logs/states/runs - Audit and compliance: Many teams need long‑term traceability of run history and state transitions. Silent deletion would be a breaking/surprising behavior. - Debugging and RCA: Historical logs and state transitions are often needed days/weeks later to diagnose issues or performance regressions. - Orchestration semantics: State history is a first‑class concept (e.g., transitions, retries, cancellations). Aggressive pruning can impact how users interpret runs in the UI or any downstream analysis that depends on full history. - One size does not fit all: Some orgs keep 90+ days, others keep 2–7 days, and some archive to cold storage. Prefect avoids destructive defaults and lets operators choose. - Operational risks: Automatic deletes can create heavy write load and table bloat during cleanup if done at the wrong time. It’s safer for operators to schedule pruning during maintenance windows and coordinate VACUUM/ANALYZE. Why events do have a default retention, but logs don’t - Events are high‑volume, ephemeral signals designed for near‑term observability. Prefect includes a default retention window for events because they behave more like a telemetry stream. - Logs are optional to store in the API at all. Many users forward logs to external systems and disable API log persistence entirely. That’s why there’s a hard switch:

# Stops logs from being written to the DB
  PREFECT_LOGGING_TO_API_ENABLED=false

This avoids surprise data loss while still giving you an easy way to cap DB growth. What we recommend instead - Pick an explicit retention policy per table and enforce it with a housekeeping job (cron, pg_cron, or a small Prefect flow). Run it off‑peak and follow it with VACUUM (and occasionally VACUUM FULL during a window). - Reduce DB write volume: - Disable API log persistence if you don’t need logs in the Prefect UI:

PREFECT_LOGGING_TO_API_ENABLED=false

- Or at least raise the level:

PREFECT_LOGGING_LEVEL=WARNING

- Keep events retention short if you don’t use them heavily:

PREFECT_EVENTS_RETENTION_PERIOD="P2D"  # ISO8601 for 2 days

- For heavy workloads, consider partitioning high‑growth tables (e.g., logs, *_state) by time and dropping whole partitions for painless retention and cleaner VACUUM behavior. If you share your target retention windows and whether you want to keep non‑terminal runs longer than terminal ones, I can give you exact SQL (and a safe, idempotent “housekeeping” Prefect flow) tailored to: - Keep N recent state records per run - Delete logs older than X days - Skip or retain non‑terminal runs - Run VACUUM/ANALYZE in the right order to minimize impact

I hear you—when the API’s background services are scanning large tables and autovacuum can’t keep up, it can feel like you’re pushing on a rope. If you can’t scale infra up or out, the fastest way to stabilize is to 1) stop the heaviest scanners, 2) slash write volume, and 3) prune + vacuum. Here’s a concrete “fastest path to green” you can apply on Prefect 2.x today. Step 1 — Stop or slow the background scanners (restart API after changing env) - If you do not rely on these features, disable them entirely:

# Schedules (auto-creating runs from deployment schedules)
  PREFECT_API_SERVICES_SCHEDULER_ENABLED=false

  # Mark ‘Late’ runs, pause expirations, and cancellation cleanup
  PREFECT_API_SERVICES_LATE_RUNS_ENABLED=false
  PREFECT_API_SERVICES_PAUSE_EXPIRATIONS_ENABLED=false
  PREFECT_API_SERVICES_CANCELLATION_CLEANUP_ENABLED=false

  # Foreman (marks worker/pools/queues readiness)
  PREFECT_API_SERVICES_FOREMAN_ENABLED=false

  # Telemetry
  PREFECT_SERVER_ANALYTICS_ENABLED=false

  # Events/Automations (if you’re not actively using them)
  PREFECT_EXPERIMENTAL_EVENTS=false

Trade-offs: - Scheduler off: deployment schedules won’t auto-create runs; ad‑hoc/API‑triggered runs still work. - LateRuns/Pauses/CancellationCleanup off: UI badges and automatic cleanups won’t run, but core execution continues. - Foreman off: readiness indicators may be stale; workers still poll normally. - If you need any of them, at least slow their loops so they scan far less often:

PREFECT_API_SERVICES_SCHEDULER_LOOP_SECONDS=300
  PREFECT_API_SERVICES_LATE_RUNS_LOOP_SECONDS=60
  PREFECT_API_SERVICES_CANCELLATION_CLEANUP_LOOP_SECONDS=120
  PREFECT_API_SERVICES_FOREMAN_LOOP_SECONDS=60

Step 2 — Cut write volume immediately - Turn off API log persistence (largest non-essential write source):

PREFECT_LOGGING_TO_API_ENABLED=false

If you must keep logs in the UI, at least reduce volume:

PREFECT_LOGGING_LEVEL=WARNING

- Keep events off or very short retention if you don’t need them:

PREFECT_EXPERIMENTAL_EVENTS=false
  # or, if you need events:
  PREFECT_EVENTS_RETENTION_PERIOD="P2D"   # ISO8601, 2 days

Step 3 — One-time cleanup and index/timeout sanity - Ensure you have all performance migrations (indexes):

prefect server database upgrade -y

- Prune fast-growing tables now (adjust window as needed):

-- Logs older than 2 days
  DELETE FROM log
  WHERE timestamp < NOW() - INTERVAL '2 days';

  -- Flow run state: keep 5 newest per run; prune the rest older than 2 days
  WITH old AS (
    SELECT id FROM (
      SELECT id, flow_run_id, timestamp,
             row_number() OVER (PARTITION BY flow_run_id ORDER BY timestamp DESC) AS rn
      FROM flow_run_state
    ) s
    WHERE rn > 5 AND timestamp < NOW() - INTERVAL '2 days'
  )
  DELETE FROM flow_run_state WHERE id IN (SELECT id FROM old);

  -- Task run state: same approach
  WITH old AS (
    SELECT id FROM (
      SELECT id, task_run_id, timestamp,
             row_number() OVER (PARTITION BY task_run_id ORDER BY timestamp DESC) AS rn
      FROM task_run_state
    ) s
    WHERE rn > 5 AND timestamp < NOW() - INTERVAL '2 days'
  )
  DELETE FROM task_run_state WHERE id IN (SELECT id FROM old);

- Reclaim performance:

VACUUM (ANALYZE, VERBOSE) log;
  VACUUM (ANALYZE, VERBOSE) flow_run_state;
  VACUUM (ANALYZE, VERBOSE) task_run_state;
  -- Use VACUUM FULL during a maintenance window only (it locks tables)

- Make sure critical composite index exists (helps log queries):

CREATE INDEX CONCURRENTLY IF NOT EXISTS ix_log__flow_run_id_timestamp
  ON log (flow_run_id, timestamp);

Step 4 — Keep the API from 500’ing while you stabilize - Give the API a bit more headroom on timeouts/pooling and then restart it:

PREFECT_API_DATABASE_TIMEOUT=30
  PREFECT_API_DATABASE_CONNECTION_TIMEOUT=10
  PREFECT_SQLALCHEMY_POOL_SIZE=25
  PREFECT_SQLALCHEMY_MAX_OVERFLOW=10

Note: if you run multiple API replicas, each has its own pool; ensure Postgres max_connections can handle the aggregate or run a single API replica until stable. Step 5 — Make it sustainable - Schedule weekly housekeeping (same DELETEs as above) and follow with ANALYZE. If you want, I can share a tiny Prefect “housekeeping” flow you can deploy. - Keep scanners disabled (or slowed) unless you need their features. - If you ever re-enable features, re-introduce them one at a time and watch DB load. Why this works without more infra - It removes the biggest read and write drivers (background scans, logs/events) at the source. - It shrinks table sizes fast so simple WHERE queries and indexes work again. - It avoids “fight the fire with more replicas” (which multiplies connection pools and scan frequency). If you share which features you actually need (schedules, late-run detection, automations/events, notifications), I’ll tailor the exact env toggles/intervals and a minimal pruning policy so you keep what you care about without the table scans bringing the DB down.

Here’s a detailed breakdown of the API “scanners” in Prefect 2.x—what they do, how they load the DB, and what you lose or keep if you turn them off. I’ve also included the key env vars to disable or slow each one and some practical guidance on when to keep vs. kill. Big picture best practice - If you run multiple API replicas, each replica runs these loops. To avoid multiplying table scans, designate ONE “control-plane” API pod with services enabled and disable them on all other replicas via env vars. Core scanners and their impact 1) Scheduler - What it does: Creates future flow runs from deployment schedules; periodically queries deployments and inserts scheduled runs. - DB load profile: High read + write under many deployments or a long scheduling horizon. Scans deployments and existing runs, inserts new flow_runs in batches. - User-visible value: Enables time-based scheduling (cron/RRule/interval). UI shows upcoming runs. - If ON under load: Can dominate DB time; duplicate scans from multiple API replicas are costly. - If OFF: Scheduled runs will not be auto-created. Ad‑hoc runs (manual/API) still work. You can replace with an external scheduler (e.g., cron hitting the API). - Tuning and toggles:

PREFECT_API_SERVICES_SCHEDULER_ENABLED=false
  PREFECT_API_SERVICES_SCHEDULER_LOOP_SECONDS=300
  # Narrow the scheduling horizon to reduce work:
  PREFECT_API_SERVICES_SCHEDULER_MIN_SCHEDULED_TIME=0
  PREFECT_API_SERVICES_SCHEDULER_MAX_SCHEDULED_TIME=3600
  PREFECT_API_SERVICES_SCHEDULER_DEPLOYMENT_BATCH_SIZE=50
  PREFECT_API_SERVICES_SCHEDULER_INSERT_BATCH_SIZE=200

2) MarkLateRuns (Late runs) - What it does: Marks scheduled runs as LATE when they miss their expected start by a small threshold. - DB load profile: Frequent reads on flow_run for SCHEDULED runs > threshold. Moderate cost that grows with the number of scheduled runs. - User-visible value: “Late” badges, and any automations/alerts relying on Late state. - If ON under load: Adds continuous read pressure; grows with queued scheduled runs. - If OFF: Nothing is marked Late; automations based on Late won’t fire; minimal correctness impact otherwise. - Tuning and toggles:

PREFECT_API_SERVICES_LATE_RUNS_ENABLED=false
  PREFECT_API_SERVICES_LATE_RUNS_LOOP_SECONDS=60
  PREFECT_API_SERVICES_LATE_RUNS_AFTER_SECONDS=60

3) PauseExpirations (Fail expired pauses) - What it does: Fails paused flow runs that exceed their pause timeout. - DB load profile: Scans for paused runs past expiration. Moderate. - User-visible value: Ensures time-based pause expirations are enforced. - If ON under load: Adds periodic queries but usually lighter than scheduler. - If OFF: Paused runs never auto-fail; operators must resume/fail manually; paused runs may linger in UI and can hold up any processes that look for terminalization. - Tuning and toggles:

PREFECT_API_SERVICES_PAUSE_EXPIRATIONS_ENABLED=false
  PREFECT_API_SERVICES_PAUSE_EXPIRATIONS_LOOP_SECONDS=60

4) CancellationCleanup - What it does: When a flow is cancelled, walks tasks/subflows to cancel/clean up related runs. - DB load profile: Moderate to high in environments with frequent cancellations or large fan‑outs (many task/subflow rows to update). - User-visible value: Ensures consistent cancellation propagation; prevents orphaned runs. - If ON under load: Adds periodic write-heavy work proportional to cancellation volume. - If OFF: Orphaned task/subflow runs may remain active/pending until they report; can cause resource leakage or confusing UI. - Tuning and toggles:

PREFECT_API_SERVICES_CANCELLATION_CLEANUP_ENABLED=false
  PREFECT_API_SERVICES_CANCELLATION_CLEANUP_LOOP_SECONDS=120

5) Foreman - What it does: Monitors worker heartbeats; sets readiness on work pools/queues/deployments based on last poll/heartbeat times. - DB load profile: Periodic scans/updates across workers, work_pools, work_queues, deployments. Moderate; grows with number of workers/pools/queues.

- User-visible value: Accurate readiness/health in the UI; can be used by some operators to gate operations. - If ON under load: Adds steady read/write churn; multiple replicas multiply it. - If OFF: Readiness/health becomes stale; workers still poll and execute work; functional impact is low, but UI signals become unreliable. - Tuning and toggles:

PREFECT_API_SERVICES_FOREMAN_ENABLED=false
  PREFECT_API_SERVICES_FOREMAN_LOOP_SECONDS=60

6) FlowRunNotifications - What it does: Delivers queued notifications for flow runs. - DB load profile: Very low; consumes from a small queue-like table/process. - User-visible value: Timely notifications. - If ON under load: Negligible DB cost; fine to leave enabled. - If OFF: No notifications sent. - Toggle:

PREFECT_API_SERVICES_FLOW_RUN_NOTIFICATIONS_ENABLED=false

7) Telemetry - What it does: Sends anonymous usage metrics (reads lightweight config). - DB load profile: Minimal. - User-visible value: None. - If ON/OFF: No impact on correctness; you can disable freely. - Toggle:

PREFECT_SERVER_ANALYTICS_ENABLED=false

Experimental / events-related services 8) EventPersister (if experimental events enabled) - What it does: Batches event writes to DB; trims old events by retention. - DB load profile: High write rate in event-heavy systems; periodic DELETE by occurred < threshold (uses index on occurred). - User-visible value: Event history, event-driven automations. - If ON under load: Can be a major write path; retention delete can spike I/O; autovacuum/ANALYZE needed. - If OFF: No events are stored; automations depending on events won’t run. - Tuning and toggles:

PREFECT_EXPERIMENTAL_EVENTS=false
  # Or keep on and tune:
  PREFECT_API_SERVICES_EVENT_PERSISTER_ENABLED=true
  PREFECT_API_SERVICES_EVENT_PERSISTER_BATCH_SIZE=100
  PREFECT_API_SERVICES_EVENT_PERSISTER_FLUSH_INTERVAL=1
  PREFECT_EVENTS_RETENTION_PERIOD="P2D"

9) Triggers: ReactiveTriggers and ProactiveTriggers (automations) - What they do: - Reactive: fires on incoming events (no scans). - Proactive: periodically evaluates non-event triggers (table scans). - DB load profile: ProactiveTriggers can add steady scans proportional to number/complexity of automations. - User-visible value: Automation rules. - If ON under load: Proactive triggers add repeated scans; Reactive has minimal DB impact. - If OFF: Automations won’t run (or only event-driven ones, if you keep Reactive via events). - Tuning and toggles:

PREFECT_API_SERVICES_TRIGGERS_ENABLED=false
  PREFECT_EVENTS_PROACTIVE_GRANULARITY=30
  PREFECT_EXPERIMENTAL_EVENTS=false

10) Actions, Event Logger, Event Stream Distributor - What they do: Execute actions, print events for debugging, stream events to WS clients. - DB load profile: Low to none (mostly bus consumers). - If OFF: Actions won’t run; logger/stream disabled—no DB relief needed unless you disable events altogether. Task scheduling timeouts (experimental) 11) TaskSchedulingTimeouts - What it does: Reschedules or fails task runs stuck in PENDING beyond a timeout. - DB load profile: Scans task_run for candidates. Moderate, scales with number of pending tasks. - User-visible value: Self-healing for stuck tasks. - If ON under load: Adds periodic scans; can help correctness in some edge cases. - If OFF: Stuck PENDING tasks may linger indefinitely until manual intervention. - Toggles:

PREFECT_EXPERIMENTAL_ENABLE_TASK_SCHEDULING=false
  PREFECT_TASK_SCHEDULING_PENDING_TASK_TIMEOUT=3600

What to keep vs. turn off (practical guidance) - Minimal-load, ad‑hoc triggering only (no schedules/automations): - Keep ON: FlowRunNotifications (optional), Telemetry OFF or ON (low cost) - Turn OFF: Scheduler, LateRuns, PauseExpirations, CancellationCleanup (optional), Foreman, Events/Triggers - You use schedules, but DB is stressed: - Keep Scheduler ON but slow it and narrow horizon

- Turn OFF: LateRuns, Foreman, PauseExpirations (if you can accept manual handling), Events/ProactiveTriggers - You rely on cancellations behaving consistently: - Keep CancellationCleanup ON (slow its loop if needed) - You rely on automations: - Prefer Reactive (events) over Proactive when possible; disable Proactive if it’s scanning too often Important multiplier: replicas - Each API replica runs all enabled services. If you must run multiple replicas, enable scanners on ONE “control-plane” pod only and set the env to disable on all others. This single change often cuts scan load by N× immediately. Complimentary moves that lower pressure further - Disable DB log persistence:

PREFECT_LOGGING_TO_API_ENABLED=false

- Prune and VACUUM high-growth tables (logs, *_state, events), then ANALYZE. - Ensure you’ve applied latest DB migrations (indexes):

prefect server database upgrade -y

If you share which features you actually depend on (schedules, pause expirations, cancellation semantics, automations), I’ll give you a minimal on/off matrix and env file for your pods so you cut the scans you don’t need while keeping expected behavior.

Got it. Here’s a clean list you can copy into your API deployment. The idea: disable all scanners/services on every replica, then enable them only on a single “master” API pod. Disable on all replicas (set these to false) - Core services - PREFECT_API_SERVICES_SCHEDULER_ENABLED=false - PREFECT_API_SERVICES_LATE_RUNS_ENABLED=false - PREFECT_API_SERVICES_PAUSE_EXPIRATIONS_ENABLED=false - PREFECT_API_SERVICES_CANCELLATION_CLEANUP_ENABLED=false - PREFECT_API_SERVICES_FOREMAN_ENABLED=false - PREFECT_API_SERVICES_FLOW_RUN_NOTIFICATIONS_ENABLED=false - PREFECT_SERVER_ANALYTICS_ENABLED=false - Experimental/events (if you aren’t using them, disable entirely) - PREFECT_EXPERIMENTAL_EVENTS=false - PREFECT_API_SERVICES_TRIGGERS_ENABLED=false - PREFECT_API_SERVICES_EVENT_PERSISTER_ENABLED=false - PREFECT_API_SERVICES_EVENT_LOGGER_ENABLED=false - PREFECT_API_EVENTS_STREAM_OUT_ENABLED=false - Experimental task scheduling (usually off by default; keep off) - PREFECT_EXPERIMENTAL_ENABLE_TASK_SCHEDULING=false Optional: also cut DB log writes on all replicas - PREFECT_LOGGING_TO_API_ENABLED=false - Or at least: PREFECT_LOGGING_LEVEL=WARNING Then pick ONE “master” API pod - Flip the booleans you actually need back to true on that one pod only. For most teams that means: - PREFECT_API_SERVICES_SCHEDULER_ENABLED=true # if you use deployment schedules - PREFECT_API_SERVICES_LATE_RUNS_ENABLED=true # optional - PREFECT_API_SERVICES_PAUSE_EXPIRATIONS_ENABLED=true # optional - PREFECT_API_SERVICES_CANCELLATION_CLEANUP_ENABLED=true # recommended if you cancel runs - PREFECT_API_SERVICES_FOREMAN_ENABLED=true # optional, for UI readiness/health - PREFECT_API_SERVICES_FLOW_RUN_NOTIFICATIONS_ENABLED=true # if you use notifications If you actively use events/automations, enable them only on the master - PREFECT_EXPERIMENTAL_EVENTS=true - PREFECT_API_SERVICES_TRIGGERS_ENABLED=true - PREFECT_API_SERVICES_EVENT_PERSISTER_ENABLED=true - (Keep PROACTIVE load reasonable: PREFECT_EVENTS_PROACTIVE_GRANULARITY=30) Sanity tips - Restart all API pods after changing env. - Keep “master” replica count = 1 (e.g., by label/selector) to avoid accidental duplicates. - Re-check DB pressure; if still high, slow loops on the master: - PREFECT_API_SERVICES_SCHEDULER_LOOP_SECONDS=300 - PREFECT_API_SERVICES_LATE_RUNS_LOOP_SECONDS=60 - PREFECT_API_SERVICES_CANCELLATION_CLEANUP_LOOP_SECONDS=120 - PREFECT_API_SERVICES_FOREMAN_LOOP_SECONDS=60 If you share which features you need (schedules, late badges, pause expirations, notifications, events/automations), I can generate a single env block for your “master” and a second block for “non-master” replicas you can paste into your manifests.

Short answer: Yes—run a single “control-plane” API pod and scale it vertically modestly. With your churn (≈1,000 concurrent jobs swapped every ~20 minutes → ~72k runs/day) the database is the bottleneck. Adding more API replicas multiplies background scanners and connection pools, which usually makes things worse unless you carefully disable services on all but one pod. Recommended approach for your workload - Use 1 API pod (master) as the control-plane. If you must run >1 replica for HA, disable all scanners/services on the others (see env blocks below). - Cut non-essential writes immediately: -

PREFECT_LOGGING_TO_API_ENABLED=false

-

PREFECT_EXPERIMENTAL_EVENTS=false

(unless you actively use automations) - Prune + vacuum high-growth tables (logs, flow_run_state, task_run_state) and ensure indices are in place, then keep a weekly housekeeping job running. Why a single API pod helps here - Eliminates N× table scans from services like the Scheduler, LateRuns, Foreman, etc. - Avoids multiplying connection pools across replicas (a common cause of 500s under DB pressure). - Keeps orchestration consistent; workers still poll normally. Concrete settings for the single master pod - DB headroom

PREFECT_API_DATABASE_TIMEOUT=30
  PREFECT_API_DATABASE_CONNECTION_TIMEOUT=10
  PREFECT_SQLALCHEMY_POOL_SIZE=25
  PREFECT_SQLALCHEMY_MAX_OVERFLOW=10

- Keep only what you need enabled (start conservative and re-enable as needed)

PREFECT_API_SERVICES_SCHEDULER_ENABLED=true                 # if you use deployment schedules
  PREFECT_API_SERVICES_LATE_RUNS_ENABLED=false                # optional; reduces scans
  PREFECT_API_SERVICES_PAUSE_EXPIRATIONS_ENABLED=false        # optional
  PREFECT_API_SERVICES_CANCELLATION_CLEANUP_ENABLED=true      # recommended if you cancel runs
  PREFECT_API_SERVICES_FOREMAN_ENABLED=false                  # optional; reduces scans
  PREFECT_API_SERVICES_FLOW_RUN_NOTIFICATIONS_ENABLED=true    # if you use notifications
  PREFECT_SERVER_ANALYTICS_ENABLED=false

  # Events/automations
  PREFECT_EXPERIMENTAL_EVENTS=false                           # keep off unless you need automations
  # If you must keep events: set short retention and batch aggressively
  # PREFECT_EXPERIMENTAL_EVENTS=true
  # PREFECT_API_SERVICES_EVENT_PERSISTER_ENABLED=true
  # PREFECT_API_SERVICES_TRIGGERS_ENABLED=true
  # PREFECT_EVENTS_RETENTION_PERIOD="P2D"
  # PREFECT_API_SERVICES_EVENT_PERSISTER_BATCH_SIZE=100
  # PREFECT_API_SERVICES_EVENT_PERSISTER_FLUSH_INTERVAL=1

- Cut log writes to DB:

PREFECT_LOGGING_TO_API_ENABLED=false
  # Or at least
  PREFECT_LOGGING_LEVEL=WARNING

If you keep multiple API replicas for HA - On all non-master replicas, set these to false:

PREFECT_API_SERVICES_SCHEDULER_ENABLED=false
  PREFECT_API_SERVICES_LATE_RUNS_ENABLED=false
  PREFECT_API_SERVICES_PAUSE_EXPIRATIONS_ENABLED=false
  PREFECT_API_SERVICES_CANCELLATION_CLEANUP_ENABLED=false
  PREFECT_API_SERVICES_FOREMAN_ENABLED=false
  PREFECT_API_SERVICES_FLOW_RUN_NOTIFICATIONS_ENABLED=false
  PREFECT_SERVER_ANALYTICS_ENABLED=false
  PREFECT_EXPERIMENTAL_EVENTS=false
  PREFECT_API_SERVICES_TRIGGERS_ENABLED=false
  PREFECT_API_SERVICES_EVENT_PERSISTER_ENABLED=false
  PREFECT_API_SERVICES_EVENT_LOGGER_ENABLED=false
  PREFECT_API_EVENTS_STREAM_OUT_ENABLED=false

- Keep

PREFECT_LOGGING_TO_API_ENABLED=false

on every replica. One-time cleanup (then schedule weekly) - Ensure DB is on latest schema:

prefect server database upgrade -y

- Prune and analyze (adjust windows as you prefer): ``` -- Logs older than 2 days DELETE FROM log WHERE timestamp < NOW() - INTERVAL '2 days'; -- Flow run state: keep 5 newest per run; prune rest older than 2 days WITH old AS ( SELECT id FROM ( SELECT id, flow_run_id, timestamp, row_number() OVER (PARTITION BY flow_run_id ORDER BY timestamp DESC) AS rn FROM flow_run_state ) s

WHERE rn > 5 AND timestamp < NOW() - INTERVAL '2 days' ) DELETE FROM flow_run_state WHERE id IN (SELECT id FROM old); -- Task run state: same pattern WITH old AS ( SELECT id FROM ( SELECT id, task_run_id, timestamp, row_number() OVER (PARTITION BY task_run_id ORDER BY timestamp DESC) AS rn FROM task_run_state ) s WHERE rn > 5 AND timestamp < NOW() - INTERVAL '2 days' ) DELETE FROM task_run_state WHERE id IN (SELECT id FROM old)); VACUUM (ANALYZE, VERBOSE) log; VACUUM (ANALYZE, VERBOSE) flow_run_state; VACUUM (ANALYZE, VERBOSE) task_run_state;

- Ensure critical log index exists:

CREATE INDEX CONCURRENTLY IF NOT EXISTS ix_log__flow_run_id_timestamp ON log (flow_run_id, timestamp); ``` A note on your scale - ~1,000 concurrent jobs refreshed every 20 minutes means sustained, heavy write/read load. The biggest wins will come from: - Eliminating API log persistence - Disabling events/automations (or keeping a very short retention) - Running only one set of background services - Keeping tables small via pruning + regular VACUUM/ANALYZE If you want, I can give you two ready-to-paste env blocks: - “Master API” (minimal services on) - “Non-master API” (all scanners off) Just confirm exactly which features you rely on (schedules, notifications, pause expirations, automations/events).

Short answer: Yes, Prefect 3.x improves database hygiene and background service behavior, but it’s not a magic wand. The biggest wins are built-in vacuum/cleanup for events and orphaned data, better batching, and more granular retention controls. Log retention by policy is still your call. What’s better in Prefect 3.x - Built-in database vacuum service - Enabled for events by default; can also prune old terminal flow runs and clean up orphaned rows (logs, artifacts) if you opt in. - Runs in small batches on a schedule to avoid long, blocking deletes. - Tunable retention and cadence. - Events retention is smarter - Still automatic, but with per-event-type overrides (e.g., trim heartbeats faster than other events). - Trimming now runs in the vacuum service instead of the inserter. - Indices and query paths - Additional/optimized indices for common server queries (events, scheduler, task/flow runs) reduce full-table scans in big installations. - Tuning knobs - Server-side database timeouts and SQLAlchemy pool controls are more explicit in 3.x. - Background services use a more resilient loop model with batching and backoff. What hasn’t changed - No automatic “log retention policy” for active runs. Prefect will clean up orphaned logs (e.g., when the flow run is purged), but if you want to age out logs by date, that’s still your retention job. - The same operational best practices apply: one “control‑plane” API pod with scanners on; scanners disabled on all other replicas; keep tables small with pruning; vacuum/analyze regularly. Recommended 3.x settings to get the benefits - Enable the vacuum service (events are on by default; add flow runs if you want automatic run aging)

# Enable events vacuum (default) and also flow run cleanup
  PREFECT_SERVER_SERVICES_DB_VACUUM_ENABLED="events,flow_runs"

  # How often to run vacuum (seconds) and how much to delete per batch
  PREFECT_SERVER_SERVICES_DB_VACUUM_LOOP_SECONDS=3600
  PREFECT_SERVER_SERVICES_DB_VACUUM_BATCH_SIZE=200

  # Retention window for flow_runs cleanup (seconds) — example: 30 days
  PREFECT_SERVER_SERVICES_DB_VACUUM_RETENTION_PERIOD=2592000

  # Example: trim flow-run heartbeats faster than other events (3 days)
  PREFECT_SERVER_SERVICES_DB_VACUUM_EVENT_RETENTION_OVERRIDES='{"prefect.flow-run.heartbeat": 259200}'

- If you want a very short window (e.g., 2 days) for a high‑churn cluster, set the retention accordingly. Note: logs tied to deleted runs get removed via orphan cleanup. - Keep API log writes low or off

PREFECT_LOGGING_TO_API_ENABLED=false
  # or at least
  PREFECT_LOGGING_LEVEL=WARNING

- Database timeouts and pooling (3.x names)

PREFECT_SERVER_DATABASE_TIMEOUT=30
  PREFECT_SERVER_DATABASE_CONNECTION_TIMEOUT=10
  PREFECT_SERVER_DATABASE_SQLALCHEMY_POOL_SIZE=25
  PREFECT_SERVER_DATABASE_SQLALCHEMY_MAX_OVERFLOW=10

- Same control-plane pattern - One API pod with scanners/services on; all others with them off to avoid N× scans/pools. What this means for you - Moving to 3.x will reduce manual work: events retention is cleaner, orphaned rows get handled automatically, and there are more guardrails for long deletes. - You’ll still want to: - Disable background scanners on non‑master replicas - Keep or implement a light pruning policy if you need hard log/run aging beyond the vacuum’s scope - Maintain regular VACUUM/ANALYZE on large tables If you want, I can map your current 2.x env to a 3.x starter profile that: - Runs a single master pod - Enables db vacuum for events + (optionally) flow runs - Keeps API logging off - Tunes pool/timeouts - Leaves other scanners set to your current usage pattern Just confirm: - Desired retention for flow runs (e.g., 2d, 7d, 30d) - Whether you want logs only via external logging (so we keep API logs off) - Which scanners you rely on (scheduler, cancellations, notifications, etc.)