https://prefect.io logo
Join Slack
Powered by
<@ULVA73B9P> I'm running on kubernetes a self host...
# ask-marvin
n
@Marvin I'm running on kubernetes a self hosted Prefect server on version 3.6.12, with valkey deployed instead of redis and db on AWS RDS. The server is slowing down, and I noticed that the API requests taking a lot of time are 
resource:"POST /api/work_pools/*/get_scheduled_flow_runs"
, despite having one single deployment which has a scheduled run every 10 minutes (all other flows are run on demand, not automatically scheduled). What is happening?
m
✅ thought for 147.0 seconds
What’s most likely happening is: this endpoint is worker polling, and in 3.6.12 it can be expensive even when you barely have any scheduled deployments. Short version - 
POST /api/work_pools/*/get_scheduled_flow_runs
is the endpoint workers hit to ask “is there anything for me to run?” - It is called repeatedly, typically every ~30 seconds per worker. - The cost is not driven mainly by your one 10-minute schedule. - The cost is often driven by: - total size of the 
flow_run
table - number of work queues in the pool - concurrency accounting queries - locking / polling behavior - DB latency on RDS - Valkey is not really helping this endpoint; this is primarily a database query path. So even if only one deployment is scheduled every 10 minutes, the server may still spend a lot of time answering “nothing to do right now”. What that endpoint is for In Prefect 3.x, workers poll their work pool for scheduled work. That endpoint: - checks the work pool / work queues - looks for 
SCHEDULED
flow runs that are ready - applies queue/pool concurrency logic - locks selected rows to avoid double-pickup - returns runs to the worker If you have multiple workers, they all do this repeatedly. If you have, say, 10 workers, that can easily mean ~20 requests/minute to the same endpoint even when there is no real work. Why it can be slow despite only one scheduled deployment The important detail is: the query does not just say “give me the next run for this one deployment”. It operates at the work-pool / work-queue level and has to evaluate eligible scheduled runs plus concurrency state. From the implementation in 3.6.x, the expensive parts are roughly: 1. Workers poll continuously - default polling interval is around 30s - each worker repeats the same query - many requests can pile up on the API / DB 2. The DB query can depend on total historical flow-run volume - if your 
flow_run
table is large, checking for runnable scheduled work becomes slower - this can happen even if most runs are on-demand 3. Concurrency accounting is part of the query path - the endpoint checks running/pending counts for queue/pool limits - that means reading more than just scheduled rows 4. Locking is involved - it uses row-locking semantics to safely assign runs - with multiple workers polling concurrently, lock overhead increases 5. Multiple work queues can make it worse - if your work pool has many queues, the endpoint may evaluate each queue - even if only one queue/deployment really matters 6. RDS latency amplifies the cost - each poll is a real DB round trip - repeated small-but-complex queries over networked Postgres add up 7. Valkey is not the main factor here - this endpoint is not being accelerated by Redis/Valkey-style caching in the way you might hope - so replacing Redis with Valkey is probably not the root cause here Why this matches your symptom exactly Your symptom is:
only one deployment is scheduled every 10 minutes, but 
get_scheduled_flow_runs
is slow
That strongly suggests the slowness is polling overhead, not schedule volume. In other words: - on-demand flows still create rows in 
flow_run
- those rows contribute to table size / query planning / concurrency scans - workers keep polling even when no scheduled work is available - the API spends time proving “nothing is ready” Most likely root causes in your setup In order of likelihood: 1. Large 
flow_run
table in Postgres - especially if you retain lots of historical runs - on-demand runs still count here 2. Too many workers polling the same pool - common in Kubernetes if multiple worker replicas are running 3. Many work queues in the pool - even unused queues can matter 4. DB indexing / planner mismatch - the endpoint benefits heavily from the right indexes and fresh planner stats 5. RDS resource pressure
- CPU, IOPS, connection count, or slow storage What I would check first 1. How many workers are polling this work pool? - If you have several replicas, this may be mostly self-inflicted polling load. 2. How large is 
flow_run
? - row count and table size matter a lot. 3. How many work queues exist in that work pool? - not just active ones. 4. Run 
EXPLAIN ANALYZE
on the underlying query path - this is the fastest way to confirm whether you’re hitting scans / bad plans / lock waits. 5. Check RDS metrics - CPU - read IOPS - DB connections - lock waits - slow queries Practical mitigations 1. Reduce worker polling pressure If you have more workers than you need, scale them down. Also consider increasing the worker query interval if low-latency pickup is not critical. The setting to look at is 
PREFECT_WORKER_QUERY_SECONDS
. That reduces how often workers call this endpoint. 2. Clean up old flow runs If you keep a lot of historical flow runs, this can materially affect performance. Archiving/pruning old rows can help a lot. 3. Review work queues If the pool has lots of queues, remove unused ones or simplify the pool structure. 4. Inspect database indexes and query plans This is probably the biggest technical lever. In particular, you want to verify whether Postgres is doing sequential scans or repeated scans over 
flow_run
. 5. Check concurrency settings If you have queue or pool concurrency limits configured, they may be forcing extra counting work every poll. 6. Look for lock contention If many workers poll simultaneously, they may be contending on the same scheduling rows. Important note on Valkey Your use of Valkey instead of Redis is probably not the main explanation for this particular endpoint being slow. This path is mostly about Postgres work, not cache hits. So I would focus first on: - worker count - work queue count - 
flow_run
size - query plans on RDS If you want, I can help you narrow it down quickly. If you can share: - number of worker replicas - number of work queues in the pool - approximate 
flow_run
row count - whether pool/queue concurrency limits are set - one slow query trace or 
EXPLAIN ANALYZE
…I can help you determine whether this is primarily: - polling frequency - queue explosion - table bloat - missing index / bad query plan - lock contention Relevant docs: - Work pools - Workers - Deployments
n
"POST /api/flow_runs/paginate"
returned 500 and took 10+ seconds @Marvin
m
✅ thought for 138.8 seconds
That’s a strong signal that this is not just the worker polling endpoint — your database/API is likely under broader pressure. 
POST /api/flow_runs/paginate
is typically used by the UI/API to list flow runs. If that is taking 10s and returning 500, the likely problem is one of these: Most likely causes 1. Large 
flow_run
table - even on-demand runs grow this table - listing/paginating flow runs gets slower as history grows 2. RDS is slow / overloaded - CPU, IOPS, connection count, lock waits 3. DB connection pool exhaustion in the Prefect server - this endpoint does both a 
read
query and a 
count
query - in 3.6.12 they are performed concurrently - under load, that can amplify connection pressure 4. Bad query plan / stale stats / missing useful indexes - especially if Postgres is doing sequential scans on 
flow_run
5. A UI filter is making it worse - some filters/sorts are much more expensive than others Why this matters together with 
get_scheduled_flow_runs
 Seeing both: - 
POST /api/work_pools/*/get_scheduled_flow_runs
slow - 
POST /api/flow_runs/paginate
slow/500 usually means the bottleneck is the database layer, not just scheduling. So the emerging picture is: - worker polling is continuously hitting Postgres - flow-run listing is also hitting Postgres - RDS or the server DB pool is struggling What I’d check immediately 1. Size of 
flow_run
 Run this on Postgres:
Copy code
sql
SELECT count(*) FROM flow_run;

SELECT
  pg_size_pretty(pg_total_relation_size('flow_run')) AS flow_run_total_size;
2. Active queries / waits
Copy code
sql
SELECT pid, state, wait_event_type, wait_event, query_start, query
FROM pg_stat_activity
WHERE datname = current_database()
ORDER BY query_start ASC;
Look for: - many active Prefect queries - lock waits - long-running 
count(*)
/ paginate queries 3. Table/index health
Copy code
sql
ANALYZE flow_run;
ANALYZE task_run;
ANALYZE deployment;
ANALYZE work_queue;
ANALYZE work_pool;
And check whether autovacuum is keeping up. 4. Server logs around the 500 The 500 is important because it may tell us whether this is: - SQL timeout - connection pool timeout - serialization failure - DB error If you can grab the traceback from the Prefect server pod logs, that would narrow this down quickly. 5. RDS metrics In CloudWatch, check around the same timestamp: - CPUUtilization - DatabaseConnections - ReadIOPS / WriteIOPS - FreeableMemory - DiskQueueDepth Likely practical fixes Reduce polling load - if you have many worker replicas, scale them down - increase 
PREFECT_WORKER_QUERY_SECONDS
if acceptable Prune old flow-run history - if 
flow_run
is large, retention cleanup can help a lot Tune DB pool on the Prefect server If your server is under concurrency pressure, increasing SQLAlchemy pool settings may help. Common env vars to review: - 
PREFECT_SERVER_DATABASE_SQLALCHEMY_POOL_SIZE
- 
PREFECT_SERVER_DATABASE_SQLALCHEMY_MAX_OVERFLOW
Update planner stats / vacuum - make sure autovacuum is healthy - run manual 
ANALYZE
if stats are stale Inspect expensive UI queries - avoid very broad “all flow runs ever” views if the table is huge - filters on task runs / certain sorts can be much slower than plain recent runs My current best guess Based on the two symptoms together, my best guess is: your 
flow_run
table has grown enough that routine Prefect API queries are now expensive, and worker polling is adding constant background DB load; on top of that, RDS and/or the API DB pool is saturating, causing 10s+ latency and occasional 500s. If you want, send me: - the 
flow_run
row count - number of worker replicas - number of work queues - the actual 500 traceback from server logs - whether RDS CPU / connections spike during these calls and I can help you pinpoint whether the first thing to fix is: - worker polling - DB pool sizing - retention cleanup - RDS sizing - query/index tuning Relevant docs:
n
@Marvin I have 162259 flow runs, and 
flow_run_total_size
is 2101MB
m
✅ thought for 16.8 seconds
That’s not enormous, but 
162,259
rows / 
~2.1 GB
is surprisingly bloated for 
flow_run
alone. That combination suggests one or more of: - table bloat / dead tuples - large JSON payloads on rows - large associated TOAST storage - indexes taking substantial space - autovacuum not keeping up So I would shift suspicion from “too many rows” to “the table is unhealthy or queries are inefficient for its shape.” Why this is suspicious 
162k
rows should not normally make basic pagination take 10+ seconds on a healthy Postgres/RDS instance. Even self-hosted Prefect with moderate load should usually handle that fine. So if 
POST /api/flow_runs/paginate
is taking 10s and 500ing at only 
162k
rows, likely causes become: 1. DB bloat / stale stats 2. RDS underprovisioned or saturated 3. many concurrent worker polls / API requests 4. connection pool exhaustion in Prefect server 5. slow query plan due to missing or poor index usage 6. lock contention What to check next 1. How much of that 2.1 GB is table vs indexes Run:
Copy code
sql
SELECT
  pg_size_pretty(pg_relation_size('flow_run')) AS table_size,
  pg_size_pretty(pg_indexes_size('flow_run')) AS indexes_size,
  pg_size_pretty(pg_total_relation_size('flow_run')) AS total_size;
This will tell us whether: - the table itself is huge - the indexes are huge - or both 2. Check dead tuples / autovacuum Run:
Copy code
sql
SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'flow_run';
If 
n_dead_tup
is high or autovacuum/analyze is old, that’s a big clue. 3. Check actual bloat-ish indicators on flow_run and task_run Also inspect 
task_run
, because UI queries may involve it too:
Copy code
sql
SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
WHERE relname IN ('flow_run', 'task_run')
ORDER BY pg_total_relation_size(relid) DESC;
4. Check worker count How many worker replicas are polling this work pool? Even with modest row counts, many workers can create constant DB churn. 5. Check the Prefect server traceback for the 500 This is still very important. The traceback can distinguish between: - SQLAlchemy connection pool timeout - DB statement timeout - lock timeout - serialization issue - other DB exception What I suspect now Given your numbers, my updated best guess is: the main issue is probably not raw row count, but a combination of polling load + database bloat/stats/pool contention. 
162k
rows should generally be manageable. 
2.1 GB
for that row count is what stands out. Low-risk things you can do now Run analyze
Copy code
sql
ANALYZE flow_run;
ANALYZE task_run;
ANALYZE deployment;
ANALYZE work_queue;
ANALYZE work_pool;
Check vacuum health If autovacuum is lagging, that can absolutely cause bad plans and slow pagination. Reduce worker polling pressure temporarily If you have multiple workers: - scale down replicas if possible - increase 
PREFECT_WORKER_QUERY_SECONDS
Look at RDS metrics during the slow calls Especially: - CPU - free memory - disk queue depth - read IOPS - DB connections Best next step Please run these two queries and send the output:
Copy code
sql
SELECT
  pg_size_pretty(pg_relation_size('flow_run')) AS table_size,
  pg_size_pretty(pg_indexes_size('flow_run')) AS indexes_size,
  pg_size_pretty(pg_total_relation_size('flow_run')) AS total_size;
Copy code
sql
SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE relname IN ('flow_run', 'task_run');
If you also know: - number of worker replicas - RDS instance class - whether autovacuum is enabled I can help you narrow this down pretty quickly.
n
first query:
Copy code
┌────────────┬──────────────┬────────────┐
│ table_size │ indexes_size │ total_size │
│  varchar   │   varchar    │  varchar   │
├────────────┼──────────────┼────────────┤
│ 132 MB     │ 216 MB       │ 2102 MB    │
└────────────┴──────────────┴────────────┘
second query:
Copy code
┌──────────┬────────────┬────────────┬──────────────────────────┬───────────────────────────────┬───────────────────────────────┬───────────────────────────────┐
│ relname  │ n_live_tup │ n_dead_tup │       last_vacuum        │        last_autovacuum        │         last_analyze          │       last_autoanalyze        │
│ varchar  │   int64    │   int64    │ timestamp with time zone │   timestamp with time zone    │   timestamp with time zone    │   timestamp with time zone    │
├──────────┼────────────┼────────────┼──────────────────────────┼───────────────────────────────┼───────────────────────────────┼───────────────────────────────┤
│ flow_run │     162295 │      16181 │ NULL                     │ 2026-07-16 22:02:37.486414+02 │ 2026-07-21 15:05:51.984192+02 │ 2026-07-21 14:24:00.865962+02 │
└──────────┴────────────┴────────────┴──────────────────────────┴───────────────────────────────┴───────────────────────────────┴───────────────────────────────┘
third query:
Copy code
┌──────────┬────────────┬────────────┬────────────┐
│ relname  │ n_live_tup │ n_dead_tup │ total_size │
│ varchar  │   int64    │   int64    │  varchar   │
├──────────┼────────────┼────────────┼────────────┤
│ flow_run │     162296 │      16185 │ 2102 MB    │
│ task_run │     427609 │      33068 │ 751 MB     │
└──────────┴────────────┴────────────┴────────────┘
@Marvin
m
✅ thought for 17.9 seconds
Thanks — this is very helpful. The good news is: this does not look like catastrophic bloat on 
flow_run
. What your numbers say For `flow_run`: - 
~162k
live rows - 
~16k
dead rows - table heap only 
132 MB
- indexes 
216 MB
That all looks pretty normal. The suspicious part is this: - 
total_size = 2102 MB
- but 
table + indexes = 132 MB + 216 MB = 348 MB
That means roughly 1.7 GB is elsewhere, almost certainly TOAST storage. In Postgres, TOAST is where large variable-width values get stored out-of-line — typically big JSON/text payloads. So the picture is now: Likely interpretation - 
flow_run
itself is not huge - indexes are not huge - autovacuum/analyze seem active - dead tuples are not extreme - but many 
flow_run
rows likely contain large serialized payloads / metadata / parameters / state details That means the slowness is less likely to be “table bloat” and more likely to be one of: 1. pagination is reading/serializing large 
flow_run
rows 2. some query path is forcing access to toasted columns 3. connection pool / concurrency pressure from workers 4. RDS instance is underpowered for repeated API polling + UI queries 5. lock/contention issues This is the key insight now At 
162k
rows, 
POST /api/flow_runs/paginate
should usually be fast. If it is 10+ seconds and sometimes 500s, I would now prioritize: 1. Prefect server / DB connection pool saturation 2. worker polling load 3. RDS resource bottleneck 4. large row serialization costs / TOAST fetches more than raw row count or bloat. Why TOAST matters here The endpoint returns flow run objects, and if Prefect is selecting ORM rows with columns that contain large JSON/text fields, Postgres may need to fetch big toasted values, and then Python/Pydantic has to serialize them. So even a page of 100 rows can become surprisingly expensive if each row carries bulky metadata/state payloads. What I would check next 1. How many worker replicas do you have? This is still one of the most important missing facts. If you have many workers polling every 30s, that alone can create enough DB/API pressure to make the UI pagination endpoint intermittently fail. 2. What RDS instance type are you using? A small burstable instance can absolutely struggle here. If you’re on something like: - 
db.t3.micro
- 
db.t3.small
- 
db.t3.medium
then I would be very suspicious. 3. Check whether the 500 is a connection-pool timeout Please inspect the Prefect server pod logs around one of these failures. I’d specifically look for: - 
sqlalchemy.exc.TimeoutError
- pool timeout / queue pool exhausted - statement timeout - connection refused / too many connections If you see a SQLAlchemy pool timeout, that would explain both the slow paginate and the worker endpoint slowness. 4. Measure TOAST size explicitly Run this:
Copy code
sql
SELECT
  c.relname AS table_name,
  c.reltoastrelid::regclass AS toast_table,
  pg_size_pretty(pg_total_relation_size(c.reltoastrelid)) AS toast_size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname = 'flow_run';
That should confirm whether most of the 2.1 GB is TOAST. 5. Check average / max sizes of likely large columns I don’t want to guess the exact column names in your schema without seeing them, but likely candidates are JSON/text columns related to: - parameters - context - state - empirical policy - labels / tags / metadata If you want, I can help you build a query to inspect the largest columns safely. My current best guess Given everything you shared, my current ranking is: 1. Too much concurrent polling / API load for your DB/server sizing 2. Large toasted 
flow_run
payloads making pagination serialization expensive 3. DB connection pool pressure in Prefect server 4. RDS instance constraints 5. less likely: plain table bloat What I’d do right now, in order Immediate 1. Check number of worker replicas 2. Check RDS instance class
3. Check Prefect server logs for the 500 traceback 4. Confirm TOAST size Mitigation 1. Reduce worker count if oversized 2. Increase 
PREFECT_WORKER_QUERY_SECONDS
3. Increase Prefect server DB pool if it’s small 4. Consider pruning old flow runs / task runs if you keep a lot of verbose metadata 5. If RDS is burstable/small, move to a larger non-burstable class One more very useful query Please run this too:
Copy code
sql
SELECT
  c.relname,
  c.reltoastrelid::regclass AS toast_table,
  pg_size_pretty(pg_total_relation_size(c.reltoastrelid)) AS toast_size
FROM pg_class c
WHERE c.relname IN ('flow_run', 'task_run');
And if possible, tell me: - how many worker replicas are running - your RDS instance type - the exact error/traceback from the 500 in server logs Those three items should let us get from “strong guess” to “probable root cause.”
n
@Marvin I have three workpools, two replicas each. I have 800+ max_connections, so this is not the issue. The result of the query is
Copy code
┌──────────┬─────────────────────────┬────────────┐
│ relname  │       toast_table       │ toast_size │
│ varchar  │         varchar         │  varchar   │
├──────────┼─────────────────────────┼────────────┤
│ flow_run │ pg_toast.pg_toast_16543 │ 1754 MB    │
│ task_run │ pg_toast.pg_toast_16601 │ 8192 bytes │
└──────────┴─────────────────────────┴────────────┘
m
✅ thought for 18.8 seconds
That’s the smoking gun. The problem is very likely 
flow_run
row size / TOASTed payloads, not row count. You have: - only 
~162k
flow_run
rows - heap table only 
132 MB
- indexes only 
216 MB
- but 
flow_run
TOAST is 1.75 GB That means the average 
flow_run
row is carrying a lot of large out-of-line data. And with 6 worker replicas total polling across 3 work pools, you also have steady background API pressure: - 6 workers - polling roughly every 30s by default - so about 12 work-pool polling requests/minute even before UI traffic That by itself is not crazy, but when the DB has to touch / serialize heavy 
flow_run
rows, it can absolutely turn into the slowdown you’re seeing. What this means concretely 
POST /api/flow_runs/paginate
returning 10s+ / 500 strongly suggests the API is reading and serializing 
flow_run
rows that include large toasted columns. And because 
get_scheduled_flow_runs
also interacts with 
flow_run
, the large-row nature of that table can contribute there too, especially under repeated polling. Important nuance You’re right that 
max_connections=800+
means the database is unlikely failing due to PostgreSQL connection limits. But I would still not completely rule out the Prefect API’s SQLAlchemy pool being too small. Those are different things: - Postgres 
max_connections
= how many DB sessions the server can accept - Prefect SQLAlchemy pool size = how many connections the Prefect API process is willing to hold/reuse Still, with the TOAST result you shared, the bigger issue now looks like fat 
flow_run
records. Why 
flow_run
gets huge In Prefect, large 
flow_run
rows usually come from one or more of: - large parameters - large state payloads / state details - large serialized results or messages associated to the run metadata - large context variables - excessive tags / labels / metadata blobs Even if task results are persisted elsewhere, some run metadata can still make the row large. Why this causes your exact symptoms 1. 
paginate
gets slow The UI/API lists flow runs and serializes them into responses. If each run contains heavy toasted fields: - more disk reads - more TOAST fetches - more network traffic between RDS and API - more Python deserialization/serialization overhead 2. intermittent 500s If the query is just slow enough, or the API process gets backed up, you can see: - timeouts - worker starvation - request cancellation - pool waits inside the app - generic 500s 3. worker polling also slows down Even if 
get_scheduled_flow_runs
is not selecting every giant field, it still operates on the same table and contends with the same DB load. What I’d investigate next: which 
flow_run
columns are huge This is now the highest-value next step. Run this to inspect average / max sizes for likely large columns on `flow_run`:
Copy code
sql
SELECT
  avg(pg_column_size(parameters)) AS avg_parameters,
  max(pg_column_size(parameters)) AS max_parameters,
  avg(pg_column_size(context)) AS avg_context,
  max(pg_column_size(context)) AS max_context,
  avg(pg_column_size(state)) AS avg_state,
  max(pg_column_size(state)) AS max_state,
  avg(pg_column_size(empirical_policy)) AS avg_empirical_policy,
  max(pg_column_size(empirical_policy)) AS max_empirical_policy
FROM flow_run;
If some of these columns don’t exist in your exact schema, we can adjust, but this is the right kind of check. If you want to first discover the columns:
Copy code
sql
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'flow_run'
ORDER BY ordinal_position;
Also: find the biggest rows This is very useful:
Copy code
sql
SELECT
  id,
  name,
  pg_column_size(flow_run) AS row_size
FROM flow_run
ORDER BY row_size DESC
LIMIT 20;
That will show whether you have a subset of pathological runs causing most of the TOAST usage. What to do if large parameters/state are the cause 1. Stop sending huge parameters to flows if possible
If you pass large dicts / payloads into flow parameters, that can inflate 
flow_run
. Better pattern: - store big payloads in S3 / blob storage / DB - pass a reference/URI/id into the flow 2. Avoid storing large objects in flow state/metadata If you’re returning or attaching very large objects, inspect whether they’re being represented in state details or metadata. 3. Prune old runs If you have historical rows with giant payloads, cleanup will help. 4. Consider a maintenance rebuild if needed If you eventually delete a lot of oversized historical rows, you may need vacuum maintenance to reclaim space. 5. Reduce worker poll frequency This won’t solve the root cause, but it can lower pressure: - increase 
PREFECT_WORKER_QUERY_SECONDS
I would also still inspect the API traceback Because the exact 500 will tell us whether the immediate failure mode is: - SQLAlchemy pool timeout - statement timeout - cancelled query - serialization issue My current conclusion Based on all the evidence: the most likely root cause is oversized 
flow_run
rows caused by large toasted columns, with worker polling and general API traffic amplifying the impact. This fits all of your data much better than: - too many rows - severe bloat - raw PostgreSQL max connection limits If you want, send me the output of either:
Copy code
sql
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'flow_run'
ORDER BY ordinal_position;
or, even better, the column-size query above, and I can help identify exactly which field is blowing up 
flow_run
.
n
@Marvin Running
Copy code
SELECT
           avg(pg_column_size(parameters)) AS avg_parameters,
           max(pg_column_size(parameters)) AS max_parameters,
           avg(pg_column_size(context)) AS avg_context,
           max(pg_column_size(context)) AS max_context,
           avg(pg_column_size(empirical_policy)) AS avg_empirical_policy,
           max(pg_column_size(empirical_policy)) AS max_empirical_policy
         FROM flow_run;
returns
Copy code
┌───────────────────┬────────────────┬─────────────┬─────────────┬──────────────────────┬──────────────────────┐
│  avg_parameters   │ max_parameters │ avg_context │ max_context │ avg_empirical_policy │ max_empirical_policy │
│      double       │     int32      │   double    │    int32    │        double        │        int32         │
├───────────────────┼────────────────┼─────────────┼─────────────┼──────────────────────┼──────────────────────┤
│ 10237.47510665936 │        2157105 │         5.0 │           5 │   173.75663812156395 │                  254 │
└───────────────────┴────────────────┴─────────────┴─────────────┴──────────────────────┴──────────────────────┘
Running
Copy code
SELECT
           id,
           name,
           pg_column_size(flow_run) AS row_size
         FROM flow_run
         ORDER BY row_size DESC;
returns
Copy code
┌──────────────────────────────────────┬─────────────────────┬──────────┐
│                  id                  │        name         │ row_size │
│                 uuid                 │       varchar       │  int32   │
├──────────────────────────────────────┼─────────────────────┼──────────┤
│ 1bb776cd-099e-48a1-838b-1863ceb9b2c1 │ refreshing-parrot   │  2157954 │
│ 40bce496-23d5-4695-b1a8-2189e9fa9011 │ spicy-mosquito      │  2157942 │
│ ee389e29-58ee-4c74-bb12-16b25d194b75 │ outstanding-harrier │  2097861 │
│ f81d5af7-64d5-4ea1-b456-093a7828d8de │ enthusiastic-sambar │  2097861 │
│ 5437aa63-db96-4227-a6cf-117884a1b71c │ interesting-frog    │  2097861 │
│ 961ec163-c4ea-4392-b647-df1bd327c3b1 │ purple-falcon       │  2097853 │
│ 92a5e01d-8749-4d15-8ae3-c431f364258b │ celadon-lorikeet    │  2097853 │
│ 36540e05-830a-4ad0-93f1-e5393e2eca00 │ malachite-toucan    │  2097853 │
│ cd42b3b1-636f-4a12-9c45-ee66ccae0af6 │ tidy-piculet        │  2097845 │
│ 08dc43fa-806b-4b74-ac59-aa3a76c1ef94 │ daffodil-newt       │  2097845 │
│ 0ec7132d-60f2-4230-893e-23b23e5c3d31 │ modest-bumblebee    │  2097829 │
│ abfe1eeb-417d-43e1-9866-e76cc6c6bbfb │ axiomatic-scallop   │  2097829 │
│ 992e425e-243a-4676-92df-7f488e6fadb0 │ rampant-alligator   │  2097829 │
│ ce920a92-ff49-4f7b-96c3-595fbc24dd37 │ cinnamon-jaguarundi │  2097829 │
│ fd1f4231-515e-4d36-a56d-a59ed65d99ad │ outrageous-marmot   │  2097829 │
│ 87bfd8a9-ecd1-47b7-b516-038a0f1c03a6 │ bold-guan           │  2097821 │
│ 479eba4e-509f-4641-bf49-6789aedc831f │ pastoral-bug        │  2097821 │
│ 1db24325-80ed-46cc-b5b0-06d0aff46ce6 │ blue-hog            │  2097821 │
│ 4e773f00-bd0a-4c12-8e9e-f136c63a936b │ ambitious-crow      │  2097821 │
│ ebe16af6-9312-473e-af30-31b11db7eff3 │ shiny-swallow       │  2097821 │
└──────────────────────────────────────┴─────────────────────┴──────────┘
m
✅ thought for 22.9 seconds
Yep — that’s it. Root cause confirmed: your 
flow_run.parameters
column is massively inflating 
flow_run
. Your query shows: - 
avg(parameters)
≈ 10 KB per run - 
max(parameters)
≈ 2,157,105 bytes (~2.1 MB) - 
context
is negligible - 
empirical_policy
is negligible And your largest rows are all ~`2.1 MB`, which lines up almost perfectly with 
parameters
. So the big issue is: you have some flow runs with extremely large parameter payloads, and on average your parameters are also pretty large. That explains the 
1.75 GB
TOAST footprint almost perfectly. What’s happening operationally When Prefect stores a flow run, it persists the run parameters in 
flow_run.parameters
. If your flows are triggered with large payloads — for example: - big JSON blobs - large nested dicts/lists - full documents / records - base64 content - serialized datasets / batches — then each flow run row becomes heavy. That affects: - UI pagination over flow runs - API serialization of flow-run responses - DB IO on reads - overall contention on 
flow_run
Even though only some runs are ~2 MB, your average parameter size is already ~10 KB, which is quite high for a metadata table. So the answer to “what is happening?” is now pretty concrete: 1. Your 
flow_run
table is storing very large parameter payloads 2. Those parameters are being TOASTed out-of-line in Postgres 3. The API endpoints that list / inspect flow runs are paying the cost to read/serialize those heavy rows 4. Worker polling adds constant background DB pressure on top 5. That combination is causing slow endpoints and occasional 500s This is the most important fix direction Stop passing large payloads as Prefect flow parameters. Instead: - store the payload in external storage - S3 - database table - object storage - file - pass only a small reference: - S3 URI - primary key - object ID - path For example, instead of:
Copy code
python
my_flow(huge_payload=really_large_dict)
prefer:
Copy code
python
my_flow(payload_s3_uri="<s3://bucket/path/to/payload.json>")
Then the flow loads the payload inside execution. Why this matters so much 
parameters
are part of run metadata, and that metadata is frequently queried by the API/UI. It’s not like an opaque blob stored somewhere never read again — it sits on a hot table. So large parameters turn a control-plane table into a data-plane storage table, which hurts performance. What I would do next 1. Identify which flows/deployments are producing these huge parameters Run:
Copy code
sql
SELECT
  deployment_id,
  count(*) AS runs,
  avg(pg_column_size(parameters)) AS avg_param_size,
  max(pg_column_size(parameters)) AS max_param_size
FROM flow_run
GROUP BY deployment_id
ORDER BY max_param_size DESC
LIMIT 20;
If you want names too:
Copy code
sql
SELECT
  d.name AS deployment_name,
  count(*) AS runs,
  avg(pg_column_size(fr.parameters)) AS avg_param_size,
  max(pg_column_size(fr.parameters)) AS max_param_size
FROM flow_run fr
LEFT JOIN deployment d ON fr.deployment_id = d.id
GROUP BY d.name
ORDER BY max_param_size DESC
LIMIT 20;
That will show which deployment(s) are creating the biggest rows. 2. Identify the worst individual runs You already have IDs; if needed, inspect one of the bad rows:
Copy code
sql
SELECT
  id,
  name,
  pg_column_size(parameters) AS parameter_size,
  left(parameters::text, 1000) AS parameter_preview
FROM flow_run
ORDER BY pg_column_size(parameters) DESC
LIMIT 5;
Be careful: this may dump sensitive data, so use it cautiously. 3. Change the calling pattern Wherever these flows are being triggered, stop embedding large payloads directly in parameters. 4. Clean up historical oversized runs If you have many historical giant rows, you may want to delete old runs according to your retention policy. That won’t immediately shrink on-disk size unless Postgres reclaims it over time / with maintenance, but it will reduce active working set and future growth.
5. Expect improvement in 
paginate
after new runs get smaller Once new runs stop carrying huge parameters, the UI/API should improve substantially. Old rows can still affect broad queries until cleaned up, but the trend will improve. How this relates to your scheduled-run endpoint This also helps explain why 
get_scheduled_flow_runs
was showing up: - it shares the same 
flow_run
table - even if it doesn’t need all parameter payloads directly, it still operates in the same database environment - heavy 
flow_run
rows increase overall IO/cache pressure - broad API slowness spreads So the earlier observation was real, but the deeper root cause seems to be oversized flow-run records, especially 
parameters
. Bottom line The issue is not number of runs, not major bloat, and probably not Valkey. It’s that 
flow_run.parameters
is being used to store large payloads, including some ~2 MB parameter sets, and that is slowing Prefect’s API/control-plane queries. If you want, I can help with either of these next: 1. writing the SQL to find exactly which deployment/function is generating the oversized parameters, or 2. suggesting a safe migration pattern for moving large flow inputs out of Prefect parameters and into S3 / external storage.
n
@Marvin How do I run this
Copy code
SELECT
  d.name AS deployment_name,
  count(*) AS runs,
  avg(pg_column_size(fr.parameters)) AS avg_param_size,
  max(pg_column_size(fr.parameters)) AS max_param_size
FROM flow_run fr
LEFT JOIN deployment d ON fr.deployment_id = d.id
GROUP BY d.name
ORDER BY max_param_size DESC
LIMIT 20;
but also showing the flow name (not the run, the flow itself)
m
✅ thought for 25.9 seconds
Join 
flow
as well and group by both flow and deployment. Use:
Copy code
sql
SELECT
  f.name AS flow_name,
  d.name AS deployment_name,
  count(*) AS runs,
  avg(pg_column_size(fr.parameters)) AS avg_param_size,
  max(pg_column_size(fr.parameters)) AS max_param_size
FROM flow_run fr
LEFT JOIN deployment d ON fr.deployment_id = d.id
LEFT JOIN flow f ON fr.flow_id = f.id
GROUP BY f.name, d.name
ORDER BY max_param_size DESC
LIMIT 20;
If you want it a bit more robust in case names collide, group by IDs too:
Copy code
sql
SELECT
  f.id AS flow_id,
  f.name AS flow_name,
  d.id AS deployment_id,
  d.name AS deployment_name,
  count(*) AS runs,
  avg(pg_column_size(fr.parameters)) AS avg_param_size,
  max(pg_column_size(fr.parameters)) AS max_param_size
FROM flow_run fr
LEFT JOIN deployment d ON fr.deployment_id = d.id
LEFT JOIN flow f ON fr.flow_id = f.id
GROUP BY f.id, f.name, d.id, d.name
ORDER BY max_param_size DESC
LIMIT 20;
If you want, I can also give you a version that shows: - only the largest offenders - a human-readable size column - or the top individual runs for each flow/deployment.
Open in Slack
PreviousNext
Powered by