< Marvin> tell me how to clean events from db Prefect Community #marvin-ai

Join Slack

<@ULVA73B9P> tell me how to clean events from db

# marvin-ai

jusstol

07/10/2026, 8:55 AM

@Marvin tell me how to clean events from db

Marvin

07/10/2026, 8:55 AM

✅ thought for 89.3 seconds

Marvin

07/10/2026, 8:56 AM

If you're on a self-hosted Prefect Server, the good news is: events already have built-in retention cleanup in Prefect 3.x. Recommended approach: use Prefect's built-in event retention, not manual SQL unless you need one-time emergency cleanup. ### 1) Set event retention Prefect uses

PREFECT_SERVER_EVENTS_RETENTION_PERIOD

. Example: keep only 2 days of events

Copy code

bash
export PREFECT_SERVER_EVENTS_RETENTION_PERIOD="2d"

You can also use seconds:

Copy code

bash
export PREFECT_SERVER_EVENTS_RETENTION_PERIOD="172800"

### 2) Make sure DB vacuum cleanup is enabled The cleanup runs through the DB vacuum service.

Copy code

bash
export PREFECT_SERVER_SERVICES_DB_VACUUM_ENABLED="events"
export PREFECT_SERVER_SERVICES_DB_VACUUM_LOOP_SECONDS="3600"
export PREFECT_SERVER_SERVICES_DB_VACUUM_BATCH_SIZE="200"

That means: - cleanup runs every hour - deletes old events in batches - removes from both

events

and

event_resources

### 3) Restart Prefect server/services After changing env vars, restart your Prefect server so the settings take effect. ### 4) Check what’s in the DB Useful SQL:

Copy code

sql
SELECT count(*), min(occurred), max(occurred)
FROM events;

SELECT count(*), min(occurred), max(occurred)
FROM event_resources;

To see how many old rows would be removed:

Copy code

sql
SELECT count(*)
FROM events
WHERE occurred < now() - interval '2 days';

### 5) If you need to manually purge old events once Delete

event_resources

first, then

events

Copy code

sql
DELETE FROM event_resources
WHERE occurred < now() - interval '2 days';

DELETE FROM events
WHERE occurred < now() - interval '2 days';

Afterward, reclaim space:

Copy code

sql
VACUUM ANALYZE event_resources;
VACUUM ANALYZE events;

### 6) Safer manual cleanup for large tables Since your DB is fairly large, batch deletes are safer than one huge delete:

Copy code

sql
DELETE FROM event_resources
WHERE id IN (
  SELECT id
  FROM event_resources
  WHERE occurred < now() - interval '2 days'
  LIMIT 10000
);

DELETE FROM events
WHERE id IN (
  SELECT id
  FROM events
  WHERE occurred < now() - interval '2 days'
  LIMIT 10000
);

Run those repeatedly until

rows are deleted, then:

Copy code

sql
VACUUM ANALYZE event_resources;
VACUUM ANALYZE events;

### Important notes - Retention is based on the

occurred

timestamp. - There is no separate Prefect CLI command to purge events manually. - Prefect’s built-in vacuum deletes old

event_resources

and

events

automatically. - If disk space does not shrink after deletes, that’s a PostgreSQL vacuum/reclaim issue, not a Prefect issue. ### Docs See Prefect’s database maintenance docs: Database maintenance If you want, I can also give you: - a one-time SQL cleanup script for events - or a full retention plan for

events

log

, and flow run tables based on your 28GB DB.

jusstol

07/10/2026, 9:48 AM

now tell me how to clean up old flow runs

jusstol

07/10/2026, 9:49 AM

@Marvin now tell me how to clean up old flow runs

Marvin

07/10/2026, 9:49 AM

✅ thought for 91.7 seconds

Marvin

07/10/2026, 9:50 AM

For old flow runs, the best option in Prefect 3.x is to enable the built-in DB vacuum service for

flow_runs

. By default, Prefect mainly cleans events automatically. Flow run cleanup must be explicitly enabled. ## Recommended setup

Copy code

bash
export PREFECT_SERVER_SERVICES_DB_VACUUM_ENABLED="events,flow_runs"
export PREFECT_SERVER_SERVICES_DB_VACUUM_RETENTION_PERIOD="2592000"   # 30 days
export PREFECT_EVENTS_RETENTION_PERIOD="604800"                       # 7 days
export PREFECT_SERVER_SERVICES_DB_VACUUM_LOOP_SECONDS="3600"
export PREFECT_SERVER_SERVICES_DB_VACUUM_BATCH_SIZE="200"

This will make Prefect periodically clean up: - old terminal flow runs - orphaned logs - orphaned artifacts - stale artifact collections - old events ## What flow runs are eligible Cleanup is based on: -

end_time

older than retention period - terminal states only: -

COMPLETED

FAILED

CANCELLED

CRASHED

So active/running flow runs are not touched. ## Important detail Flow run retention uses:

PREFECT_SERVER_SERVICES_DB_VACUUM_RETENTION_PERIOD

Default is 90 days. ## Check what would be deleted

Copy code

sql
SELECT count(*) AS flow_runs_to_delete
FROM flow_run
WHERE end_time < now() - interval '30 days'
  AND state_type IN ('COMPLETED', 'FAILED', 'CANCELLED', 'CRASHED');

You can also inspect oldest runs:

Copy code

sql
SELECT id, name, state_type, end_time
FROM flow_run
WHERE end_time < now() - interval '30 days'
  AND state_type IN ('COMPLETED', 'FAILED', 'CANCELLED', 'CRASHED')
ORDER BY end_time
LIMIT 20;

## One-time manual cleanup If you want to manually delete old flow runs, Prefect does support deleting flow runs through the API/client, which is safer than raw SQL because it respects app-level cleanup behavior. If you need SQL, start with a preview and backup first.

Copy code

sql
DELETE FROM flow_run
WHERE end_time < now() - interval '30 days'
  AND state_type IN ('COMPLETED', 'FAILED', 'CANCELLED', 'CRASHED');

## Caution on direct SQL Direct SQL may leave related rows/orphans depending on table relationships and how much historical data you remove at once. Since your database is already fairly large, I would strongly prefer enabling built-in vacuum over hand-deleting

flow_run

rows. If you do manual deletion: - do it in batches - run

VACUUM ANALYZE

after - keep the Prefect vacuum service enabled so orphan cleanup can catch leftovers Example batch delete:

Copy code

sql
DELETE FROM flow_run
WHERE id IN (
  SELECT id
  FROM flow_run
  WHERE end_time < now() - interval '30 days'
    AND state_type IN ('COMPLETED', 'FAILED', 'CANCELLED', 'CRASHED')
  LIMIT 1000
);

Then:

Copy code

sql
VACUUM ANALYZE flow_run;

## Practical recommendation for your DB Given your 28GB self-hosted Postgres setup, I’d suggest: - `events`: 1–3 days - `flow_runs`: 14–30 days - enable

events,flow_runs

vacuum immediately ## Docs Prefect database maintenance: Database maintenance If you want, I can next give you a complete retention recipe for: -

events

flow_run

log

flow_run_state

with exact env vars and SQL checks for each.

jusstol

07/10/2026, 9:58 AM

@Marvin i have a problem with my prefect server. i try to delete a flow run through the prefect ui, and when i check prefect server logs i see :

Copy code

File "/usr/local/lib/python3.11/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 558, in _prepare_and_execut
    self._handle_exception(error)
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 508, in _handle_exception
    self._adapt_connection._handle_exception(error)
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 794, in _handle_exception
    raise error
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 545, in _prepare_and_execut
    self._rows = deque(await prepared_stmt.fetch(*parameters))
                       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/asyncpg/prepared_stmt.py", line 176, in fetch
    data = await self.__bind_execute(args, 0, timeout)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/asyncpg/prepared_stmt.py", line 267, in __bind_execute
    data, status, _ = await self.__do_execute(
                      ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/asyncpg/prepared_stmt.py", line 256, in __do_execute
    return await executor(protocol)
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "asyncpg/protocol/protocol.pyx", line 206, in bind_execute
TimeoutError

but i have no problem connecting to prefects postgresql database manually. how can i debug this ?

Marvin

07/10/2026, 9:58 AM

✅ thought for 123.8 seconds

Marvin

07/10/2026, 10:00 AM

Yes — this looks much more like a query execution timeout during delete than a connectivity problem. Because you can connect to Postgres manually, the likely issue is: - Prefect can reach Postgres - but deleting that flow run triggers a large cascading delete - and the DB operation exceeds Prefect's asyncpg/SQLAlchemy timeout The stack ending in

asyncpg ... TimeoutError

during

bind_execute

is consistent with that. ## Why deleting a flow run can be slow When Prefect deletes a flow run, it is not just removing one row. Deleting a

flow_run

can cascade into related records like: -

flow_run_state

task_run

task_run_state

flow_run_input

Also, logs/artifacts may involve additional cleanup behavior. So if that run has lots of task runs / state history, deletion may take longer than Prefect's DB statement timeout. ## First thing to check: Prefect DB timeout Prefect server uses a database timeout setting. In practice, a common default is around 10 seconds. Try increasing:

Copy code

bash
export PREFECT_SERVER_DATABASE_TIMEOUT=60

Then restart your Prefect server. If you're using

PREFECT_API_DATABASE_TIMEOUT

in your setup, check that too, but for a server deployment I'd start with:

Copy code

bash
export PREFECT_SERVER_DATABASE_TIMEOUT=60

You may also want:

Copy code

bash
export PREFECT_SERVER_DATABASE_CONNECTION_TIMEOUT=10
export PREFECT_SERVER_DATABASE_SQLALCHEMY_POOL_TIMEOUT=60
export PREFECT_SERVER_DATABASE_SQLALCHEMY_POOL_SIZE=10
export PREFECT_SERVER_DATABASE_SQLALCHEMY_MAX_OVERFLOW=20

## How to debug it properly ### 1) Find out how big that flow run is Run this against Postgres for the problematic flow run ID:

Copy code

sql
SELECT COUNT(*) AS task_runs
FROM task_run
WHERE flow_run_id = '<FLOW_RUN_ID>';

SELECT COUNT(*) AS flow_run_states
FROM flow_run_state
WHERE flow_run_id = '<FLOW_RUN_ID>';

SELECT COUNT(*) AS task_run_states
FROM task_run_state
WHERE task_run_id IN (
  SELECT id
  FROM task_run
  WHERE flow_run_id = '<FLOW_RUN_ID>'
);

If those counts are large, that strongly supports the timeout diagnosis. ### 2) Watch active Postgres queries while you click delete In Postgres:

Copy code

sql
SELECT pid, now() - query_start AS runtime, state, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY query_start;

If you see a long-running

DELETE

against Prefect tables, that’s your culprit. ### 3) Check for locks Sometimes the delete is blocked, not just slow.

Copy code

sql
SELECT blocked.pid AS blocked_pid,
       blocked.query AS blocked_query,
       blocking.pid AS blocking_pid,
       blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks ON blocked.pid = blocked_locks.pid
JOIN pg_locks blocking_locks
  ON blocked_locks.locktype = blocking_locks.locktype
 AND blocked_locks.database IS NOT DISTINCT FROM blocking_locks.database
 AND blocked_locks.relation IS NOT DISTINCT FROM blocking_locks.relation
 AND blocked_locks.page IS NOT DISTINCT FROM blocking_locks.page
 AND blocked_locks.tuple IS NOT DISTINCT FROM blocking_locks.tuple
 AND blocked_locks.virtualxid IS NOT DISTINCT FROM blocking_locks.virtualxid
 AND blocked_locks.transactionid IS NOT DISTINCT FROM blocking_locks.transactionid
 AND blocked_locks.classid IS NOT DISTINCT FROM blocking_locks.classid
 AND blocked_locks.objid IS NOT DISTINCT FROM blocking_locks.objid
 AND blocked_locks.objsubid IS NOT DISTINCT FROM blocking_locks.objsubid
 AND blocked_locks.pid <> blocking_locks.pid
JOIN pg_stat_activity blocking ON blocking.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

### 4) Check table sizes Given your DB size, the biggest related tables matter.

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', 'flow_run_state', 'task_run', 'task_run_state', 'log', 'events', 'event_resources')
ORDER BY pg_total_relation_size(relid) DESC;

Marvin

07/10/2026, 10:00 AM

### 5) Enable SQL logging temporarily You can turn on DB echo for more visibility:

Copy code

bash
export PREFECT_SERVER_DATABASE_ECHO=true

Then restart the server and retry. This can help confirm which statement is timing out. ## Likely fixes ### Option A: Increase timeout Fastest test:

Copy code

bash
export PREFECT_SERVER_DATABASE_TIMEOUT=60

If needed, try

. ### Option B: Clean up old data first If your related tables are bloated, deleting one run can become expensive. Since you're self-hosted and already looking at retention, enabling automated cleanup can help overall DB performance:

Copy code

bash
export PREFECT_SERVER_SERVICES_DB_VACUUM_ENABLED="events,flow_runs"
export PREFECT_SERVER_SERVICES_DB_VACUUM_RETENTION_PERIOD="2592000"
export PREFECT_EVENTS_RETENTION_PERIOD="172800"

### Option C: Delete problematic runs during low activity If locks/contention are involved, trying during a quiet window can help. ### Option D: Manual deletion only if necessary If the UI/API keeps timing out, you can try deleting directly in SQL, but I’d treat that as a careful maintenance operation, because it bypasses Prefect’s application-layer behavior. If you go that route, back up first and inspect dependencies carefully. ## My strongest guess Given your environment and the error: - not a basic DB connectivity issue - most likely a large or blocked cascading delete - first remediation to try is increase
PREFECT_SERVER_DATABASE_TIMEOUT
to 60s and inspect row counts for that specific run ## Useful docs Database maintenance: Database maintenance If you want, I can help you with a step-by-step triage plan next: 1. get the flow run ID 2. run the exact SQL checks 3. interpret the results 4. decide whether to raise timeout, vacuum, or do manual cleanup

4 Views

Open in Slack

Previous Next