Short answer: yes. Prefect (both Server and Cloud) stores orchestration data in Postgres and ships with a set of indexes designed to make common operations fast: looking up a run by id, polling by state and schedule, and transitioning state. These are created automatically by Alembic migrations. What’s indexed (high level) - Primary key lookups -

flow_run.id

and

task_run.id

are primary keys (fast O(1) lookups) - Polling/listing by state - Indexes on

state_type

,

state_name

, and

state_timestamp

on both

flow_run

and

task_run

- Scheduler/polling by scheduled time - Index on

next_scheduled_start_time

(ASC) for both

flow_run

and

task_run

- Postgres partial index for scheduler:

(deployment_id, auto_scheduled, next_scheduled_start_time) WHERE state_type = 'SCHEDULED'

— lets the scheduler quickly find due scheduled runs - Sorting by “effective” start time - Expression indexes on

COALESCE(start_time, expected_start_time)

(ASC and DESC) for fast sorted listings in the UI/API - Fast “latest state” retrieval - Unique composite index on state tables: -

flow_run_state(flow_run_id, timestamp DESC) UNIQUE

-

task_run_state(task_run_id, timestamp DESC) UNIQUE

- Plus indexes on

type

,

name

, and

result_artifact_id

- Other useful indexes - `flow_run`:

deployment_id

,

work_queue_id/name

,

flow_id

,

parent_task_run_id

, etc. - Idempotency:

flow_run(flow_id, idempotency_key) UNIQUE

How these are used - Get a run by id - Query uses the primary key index - Example

SELECT * FROM flow_run WHERE id = $1;

- Transitioning a flow run’s state - Prefect 2.x reads the run row with a row-level lock to prevent concurrent transitions:

SELECT * FROM flow_run WHERE id = $1 FOR UPDATE;

- Inserts a new row in

flow_run_state

(benefits from the unique composite index) and updates

flow_run.state_id/state_type/state_name/state_timestamp

(which are indexed for later polling) - Polling runs by state/schedule - The scheduler uses the partial index to quickly find due scheduled runs:

SELECT ...
    FROM flow_run
    WHERE deployment_id = $1
      AND auto_scheduled = TRUE
      AND state_type = 'SCHEDULED'
      AND next_scheduled_start_time <= NOW()
    ORDER BY next_scheduled_start_time ASC

- Listing/filtering runs in the UI/API uses

state_type

,

state_timestamp

, and the coalesced start time index for fast filtering/sorting Source links (Prefect 2.x Orion; Cloud uses the same schema) - ORM models (tables and indexes): orm_models.py - Flow run state transition (FOR UPDATE lock shown in set_flow_run_state/read_flow_run): flow_runs.py - Scheduler partial index on scheduled runs: 2022-11-05 migration - Coalesced start time expression indexes: 2022-11-10 migration Notes - Prefect 3.x keeps this strategy; naming and a few details evolve, but the same core indexes (state filters, scheduled-time, coalesced start-time, and “latest state” unique composites) remain to support the same operations. - Prefect Cloud and Prefect Server (self-hosted) both run on Postgres and apply the same migrations and indexes. If you can share whether you’re on 2.x or 3.x today, I can point you to the exact table/index names in that version’s schema for your environment.

You’re spot on about the hot-row churn. In Prefect 2.x, each worker poll updates small, shared rows on every cycle — even when there’s no work — which creates exactly the WAL/index/lock pressure you’re seeing. What gets written each poll (citations) - Work queue:

last_polled

and sometimes

status

are updated on every

get_runs

call - Code: record_work_queue_polls (called from /work_queues/{id}/get_runs) - Column added by migration: add_last_polled to work_queue - Deployment:

last_polled

(and

status=READY

) are bumped when polls include that deployment’s queue - Code: mark_deployments_ready - Column added by migration: add_last_polled to deployment - Workers send heartbeats that update

worker.last_heartbeat_time/status

(less frequent, but still writes) - Code: worker_heartbeat Why it’s hot - 35 workers × 5s poll interval means ~7 polls/second. - Each poll updates: one work_queue row, and all deployments associated with that queue are touched via

mark_deployments_ready

. - These are tiny tables (7 queues, 39 deployments) → repeated updates on the same rows → WAL churn and autovacuum work, even when idle. Mitigation playbook (Prefect 2.x) 1) Increase the poll interval and heartbeat interval - Fewer polls = fewer writes. - Environment variables:

# How often workers query for work (increase this)
  export PREFECT_WORKER_QUERY_SECONDS=30

  # How often workers heartbeat (can increase as well)
  export PREFECT_WORKER_HEARTBEAT_SECONDS=60

- If you’re still using agents anywhere:

export PREFECT_AGENT_QUERY_INTERVAL=30

2) Reduce the number of polling workers - Each worker/pod generates writes even when there’s no work. Try fewer workers with the same execution capacity, and let task/flow parallelism happen inside work picked up rather than via more polling processes. 3) Avoid polling the same queues from many workers - If multiple workers poll the same

work_queue

, they all hammer the same rows. Where possible, run one worker per busy queue (or per work pool) and size that worker’s execution capacity instead of duplicating pollers. 4) Tame the Foreman service so statuses don’t flap - Foreman marks queues/deployments NOT_READY if

last_polled

is “old”, which can cause more status writes. - You can slow it down or relax timeouts:

# Slow the background status sweeps
  export PREFECT_API_SERVICES_FOREMAN_LOOP_SECONDS=30

  # Give more leeway before declaring NOT_READY
  export PREFECT_API_SERVICES_FOREMAN_DEPLOYMENT_LAST_POLLED_TIMEOUT_SECONDS=180
  export PREFECT_API_SERVICES_FOREMAN_WORK_QUEUE_LAST_POLLED_TIMEOUT_SECONDS=180

- You can also disable Foreman entirely if you don’t rely on these READY/NOT_READY statuses:

export PREFECT_API_SERVICES_FOREMAN_ENABLED=false

Note: even with Foreman off, the polling path will still update `last_polled`; this just prevents background status flips. 5) Database hygiene for hot small tables (Postgres) - Aggressive autovacuum on the hot tables reduces bloat/CPU from cleaning dead tuples: ``` ALTER TABLE deployment SET ( autovacuum_vacuum_scale_factor = 0.01, autovacuum_analyze_scale_factor = 0.02, autovacuum_vacuum_threshold = 50, autovacuum_analyze_threshold = 50, fillfactor = 80 );

ALTER TABLE work_queue SET ( autovacuum_vacuum_scale_factor = 0.01, autovacuum_analyze_scale_factor = 0.02, autovacuum_vacuum_threshold = 50, autovacuum_analyze_threshold = 50, fillfactor = 80 ); VACUUM (ANALYZE) deployment; VACUUM (ANALYZE) work_queue;

- Consider bumping instance IOPS and enabling WAL compression if available on your RDS flavor.

Quick verification queries

-- Current write pressure on hot tables SELECT relname, n_tup_ins, n_tup_upd, n_dead_tup, vacuum_count, autovacuum_count FROM pg_stat_user_tables WHERE relname IN ('deployment','work_queue') ORDER BY relname; -- See bloat/visibility map health SELECT relname, n_live_tup, n_dead_tup, (n_dead_tup::numeric / NULLIF(n_live_tup,0)) AS dead_ratio FROM pg_stat_user_tables WHERE relname IN ('deployment','work_queue'); ``` A few notes - The updates you observed are “by design” in Prefect 2.x:

last_polled

and

status

are the mechanism used by the API and Foreman to know if queues/deployments are healthy and being serviced. - Prefect 3.x retains similar semantics around worker polling; the mitigations above (longer intervals, fewer pollers, tuned Foreman) remain relevant when you upgrade. If you can share: - whether all 35 workers poll the same 7 queues, - your current values for

PREFECT_WORKER_QUERY_SECONDS

and

PREFECT_WORKER_HEARTBEAT_SECONDS

, I can help propose specific target settings (e.g., worker count vs. interval) to drop write QPS by an order of magnitude without hurting pickup latency.

You’re right—sorry for the confusion. I’ll stick to Prefect 3.x. In Prefect 3.x, the primary hot-row writes during idle polling come from: - Workers polling work queues - Updates

work_queue.last_polled

on each poll and may set

status=READY

if it was NOT_READY - Batched as a background task after the poll to reduce per-request overhead - Worker heartbeats - Upsert the worker row (

last_heartbeat_time

,

status

, and optionally

heartbeat_interval_seconds

) Notably, in 3.x, polling does not churn deployments on every cycle the way 2.x often did. The hot tables you’ll see written frequently are

work_queue

and

worker

. Relevant code paths (3.x server): - Work queue poll recording (batch update): models/work_queues.py - Worker heartbeat (upsert): models/workers.py - Foreman background service (marks resources READY/NOT_READY based on last_polled/heartbeats): services/foreman.py - Settings (3.x): Prefect settings How to reduce write pressure in 3.x - Increase polling and heartbeat intervals

# Fewer polls/heartbeats → fewer writes
  export PREFECT_WORKER_QUERY_SECONDS=30
  export PREFECT_WORKER_HEARTBEAT_SECONDS=60

- Reduce concurrent pollers against the same queues - Prefer fewer, larger workers per queue over many small workers all polling the same queue - Tame Foreman churn (if you use READY/NOT_READY status, give it a longer leash)

export PREFECT_API_SERVICES_FOREMAN_LOOP_SECONDS=30
  export PREFECT_API_SERVICES_FOREMAN_WORK_QUEUE_LAST_POLLED_TIMEOUT_SECONDS=180

If you don’t rely on these statuses at all, you can disable Foreman:

export PREFECT_API_SERVICES_FOREMAN_ENABLED=false

- Postgres hygiene for hot small tables - Lower autovacuum thresholds on

work_queue

and

worker

, and run targeted VACUUM to keep dead tuples down If you can share: - number of workers and how they’re mapped to queues - current values for

PREFECT_WORKER_QUERY_SECONDS

and

PREFECT_WORKER_HEARTBEAT_SECONDS

I’ll suggest concrete targets to cut write QPS substantially without impacting pickup latency.

Short answer: there isn’t a fixed “right” number; use fewer, larger workers and control total concurrency with each worker’s --limit. For 1,000–2,000 concurrent flow runs in Prefect 3.x, a solid starting point is: - Low-churn baseline (fewer pollers, less DB write pressure) - 4–8 workers with --limit 250–500 each - Prefetch 30–60s, poll every 10–15s - Example

export PREFECT_WORKER_QUERY_SECONDS=10
    export PREFECT_WORKER_PREFETCH_SECONDS=30
    export PREFECT_WORKER_HEARTBEAT_SECONDS=60
    prefect worker start -p my-pool --limit 500 --prefetch-seconds 30

- Pros: fewer hot-row updates (each worker polls once per interval) - Cons: when you need to pick up a huge burst immediately, it may take a couple of poll cycles to fully load all slots - Fast pickup of large bursts (more pollers) - ~10 workers with --limit 200 each, prefetch 60s, poll 5–10s - Pros: collectively can grab up to ~2,000 runs in one poll window (server returns up to ~200 per worker poll) - Cons: more polling = more writes to the work_queue table; mitigate by sharding across multiple work queues and assigning a small number of workers per queue General guidance - Prefer fewer workers with higher --limit to reduce polling/heartbeat write volume. - Shard across multiple work queues if you run many workers; don’t point 10+ workers at the same queue. - Tune pickup vs. database churn: - Increase PREFECT_WORKER_QUERY_SECONDS and PREFECT_WORKER_HEARTBEAT_SECONDS to reduce writes - Increase PREFECT_WORKER_PREFETCH_SECONDS to reduce how often you need to poll for the same scheduled window - The worker enforces concurrency with --limit; there is no “runs-per-poll” knob. Each poll returns up to the server’s per-call cap and the worker fills available slots. Key settings (3.x) - Poll interval: PREFECT_WORKER_QUERY_SECONDS (default ~10s) - Prefetch window: PREFECT_WORKER_PREFETCH_SECONDS (default ~10s) - Heartbeat: PREFECT_WORKER_HEARTBEAT_SECONDS (default ~30s) - Worker concurrency: --limit on prefect worker start - CLI help: run

prefect worker start --help

To tailor this precisely: - What worker type (Kubernetes, Docker, Process, ECS)? - Required pickup latency for bursts (seconds vs. minutes)? - How many work queues, and how are workers mapped to them? - Typical resource requests per flow-run pod/container (for cluster scheduling)? With that, I can recommend an exact worker/queue layout and interval settings to hit 1–2k concurrency while keeping DB write pressure in check.