https://prefect.io logo
Join Slack
Powered by
<@ULVA73B9P> I am setting up a db cleanup process ...
# marvin-ai
l
@Marvin I am setting up a db cleanup process and i followed this flow as described in the documentation. According to the docs using the API for deleting old flow runs will handle all related objects like logs or task runs. However after running the flow successfully with 30 days retention period i see rows from more than 3 months ago in the log and taks_run tables. Why are those not cleaned up?
m
✅ thought for 364.4 seconds
Great question — and thanks for linking the doc. A few key details explain what you’re seeing. How Prefect 3.x deletes related data - Task runs: Deleting a flow run via the API performs a hard delete on the flow run and uses database foreign keys with ON DELETE CASCADE to remove its task runs and their states immediately. - Logs: Logs do not have a database foreign key cascade. When a flow run is deleted via the API, Prefect enqueues a background “docket” task to delete that run’s logs. This is asynchronous. Why you may still see old rows 1) Those task runs belong to flow runs that were not deleted by your retention filter - The example flow in the docs filters by start_time (not created), terminal states, and often excludes subflow runs (child flow runs). Any run that: - never started (no start_time), - is not in a terminal state, - or is a subflow run that wasn’t included, will not be deleted — and its task_run and log rows will remain. - Link: Database maintenance - Data retention 2) Your database schema may be missing the ON DELETE CASCADE constraint - If the corresponding flow_run rows are already gone but task_run rows remain, that usually means your DB constraints do not match current Prefect 3.x schema (e.g., older install or incomplete migrations). In 3.x, 
task_run.flow_run_id
should cascade on delete. 3) Log cleanup is asynchronous (or may be disabled) - The API enqueues per-run log deletion tasks. If your API server wasn’t running, or if those docket tasks failed, you’ll still see old log rows. - Additionally, logs that are not associated with a flow_run_id (rare, but possible) are not removed by flow-run deletion and require the vacuum service or a custom cleanup. 4) Subflows are special - Deleting a parent’s task run does not delete child flow runs. Those child runs (and their task runs/logs) will persist until you explicitly delete them based on your policy. How to diagnose quickly 1) Pick one old task_run row and check its flow run:
Copy code
-- Find the parent flow run
SELECT flow_run_id FROM task_run WHERE id = '<task_run_id>';

-- See if that flow run still exists and why it wasn’t deleted
SELECT id, state_type, start_time, end_time, parent_task_run_id
FROM flow_run
WHERE id = '<flow_run_id>';
- If the flow_run still exists: your filter likely excluded it (e.g., non-terminal, start_time within 30 days, or subflow). - If the flow_run does not exist: your DB likely lacks the ON DELETE CASCADE on task_run.flow_run_id (see next step). 2) Verify foreign key cascade on task_run.flow_run_id (Postgres):
Copy code
SELECT
  tc.constraint_name,
  rc.delete_rule
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.referential_constraints AS rc ON tc.constraint_name = rc.constraint_name
WHERE
  tc.constraint_type = 'FOREIGN KEY'
  AND tc.table_name = 'task_run'
  AND kcu.column_name = 'flow_run_id';
- Expected: delete_rule = CASCADE. If not, you’ll need to upgrade/apply migrations so the schema matches Prefect 3.x. 3) Confirm log deletion ran - Check your API server logs for entries related to “delete_flow_run_logs” after your retention flow. If the server wasn’t running, the log cleanup wouldn’t have executed. - Optional: enable the built-in database vacuum service to catch orphaned logs/artifacts over time: - PREFECT_SERVER_SERVICES_DB_VACUUM_ENABLED=flow_runs,events - PREFECT_SERVER_SERVICES_DB_VACUUM_RETENTION_PERIOD=... (for flow runs) See: Database maintenance References - Data retention guide: Database maintenance - Data retention
- REST bulk delete endpoint: Bulk delete flow runs If you can share the exact filters from your retention flow (e.g., whether you filtered by start_time and whether you excluded subflows), I can help pinpoint which category your remaining rows fall into and suggest precise queries or config changes.
l
@Marvin I found a lot of error logs in my prefect-server-background-services pod. What does this traceback mean?
Copy code
2026-03-25T14:30:24.619+02:00    result = conn.execute(
2026-03-25T14:30:24.619+02:00             ^^^^^^^^^^^^^
2026-03-25T14:30:24.619+02:00  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1419, in execute
2026-03-25T14:30:24.619+02:00    return meth(
2026-03-25T14:30:24.619+02:00           ^^^^^
2026-03-25T14:30:24.619+02:00  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/sql/elements.py", line 527, in _execute_on_connection
2026-03-25T14:30:24.619+02:00    return connection._execute_clauseelement(
2026-03-25T14:30:24.619+02:00           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
2026-03-25T14:30:24.619+02:00  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1641, in _execute_clauseelement
2026-03-25T14:30:24.619+02:00    ret = self._execute_context(
2026-03-25T14:30:24.619+02:00          ^^^^^^^^^^^^^^^^^^^^^^
2026-03-25T14:30:24.619+02:00  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1846, in _execute_context
2026-03-25T14:30:24.619+02:00    return self._exec_single_context(
2026-03-25T14:30:24.619+02:00           ^^^^^^^^^^^^^^^^^^^^^^^^^^
2026-03-25T14:30:24.619+02:00  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1986, in _exec_single_context
2026-03-25T14:30:24.619+02:00    self._handle_dbapi_exception(
2026-03-25T14:30:24.619+02:00  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2366, in _handle_dbapi_exception
2026-03-25T14:30:24.619+02:00    raise exc_info[1].with_traceback(exc_info[2])
2026-03-25T14:30:24.619+02:00  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
2026-03-25T14:30:24.619+02:00    self.dialect.do_execute(
2026-03-25T14:30:24.619+02:00  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 952, in do_execute
2026-03-25T14:30:24.619+02:00    cursor.execute(statement, parameters)
2026-03-25T14:30:24.619+02:00  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 585, in execute
2026-03-25T14:30:24.619+02:00    self._adapt_connection.await_(
2026-03-25T14:30:24.619+02:00  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 132, in await_only
2026-03-25T14:30:24.619+02:00    return current.parent.switch(awaitable)  # type: ignore[no-any-return,attr-defined] # noqa: E501
2026-03-25T14:30:24.619+02:00           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
2026-03-25T14:30:24.619+02:00  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 196, in greenlet_spawn
2026-03-25T14:30:24.619+02:00    value = await result
2026-03-25T14:30:24.619+02:00            ^^^^^^^^^^^^
2026-03-25T14:30:24.619+02:00  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 563, in _prepare_and_execute
2026-03-25T14:30:24.619+02:00    self._handle_exception(error)
2026-03-25T14:30:24.619+02:00  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 513, in _handle_exception
2026-03-25T14:30:24.619+02:00    self._adapt_connection._handle_exception(error)
2026-03-25T14:30:24.619+02:00  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 799, in _handle_exception
2026-03-25T14:30:24.619+02:00    raise error
2026-03-25T14:30:24.619+02:00  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 550, in _prepare_and_execute
2026-03-25T14:30:24.619+02:00    self._rows = deque(await prepared_stmt.fetch(*parameters))
2026-03-25T14:30:24.619+02:00                       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
2026-03-25T14:30:24.619+02:00  File "/usr/local/lib/python3.11/site-packages/asyncpg/prepared_stmt.py", line 177, in fetch
2026-03-25T14:30:24.619+02:00    data = await self.__bind_execute(args, 0, timeout)
2026-03-25T14:30:24.619+02:00           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
2026-03-25T14:30:24.619+02:00  File "/usr/local/lib/python3.11/site-packages/asyncpg/prepared_stmt.py", line 268, in __bind_execute
2026-03-25T14:30:24.619+02:00    data, status, _ = await self.__do_execute(
2026-03-25T14:30:24.619+02:00                      ^^^^^^^^^^^^^^^^^^^^^^^^
2026-03-25T14:30:24.619+02:00  File "/usr/local/lib/python3.11/site-packages/asyncpg/prepared_stmt.py", line 257, in __do_execute
2026-03-25T14:30:24.619+02:00    return await executor(protocol)
2026-03-25T14:30:24.619+02:00           ^^^^^^^^^^^^^^^^^^^^^^^^
2026-03-25T14:30:24.619+02:00  File "asyncpg/protocol/protocol.pyx", line 205, in bind_execute
2026-03-25T14:30:24.619+02:00TimeoutError
m
Your message was too long, here's your message at the allowed limit: I found a lot of error logs in my prefect-server-background-services pod What does this traceback mean? ```2026-03-25T14 30 24 619+02 00 result = conn execute( 2026-03-25T14 30 24 619+02 00 ^^^^^^^^^^^^^ 2026-03-25T14 30 24 619+02 00 File /usr/local/lib/python3 11/site-packages/sqlalchemy/engine/base py line 1419 in execute 2026-03-25T14 30 24 619+02 00 return meth( 2026-03-25T14 30 24 619+02 00 ^^^^^ 2026-03-25T14 30 24 619+02 00 File /usr/local/lib/python3 11/site-packages/sqlalchemy/sql/elements py line 527 in _execute_on_connection 2026-03-25T14 30 24 619+02 00 return connection _execute_clauseelement( 2026-03-25T14 30 24 619+02 00 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 2026-03-25T14 30 24 619+02 00 File /usr/local/lib/python3 11/site-packages/sqlalchemy/engine/base py line 1641 in _execute_clauseelement 2026-03-25T14 30 24 619+02 00 ret = self _execute_context( 2026-03-25T14 30 24 619+02 00 ^^^^^^^^^^^^^^^^^^^^^^ 2026-03-25T14 30 24 619+02 00 File /usr/local/lib/python3 11/site-packages/sqlalchemy/engine/base py line 1846 in _execute_context 2026-03-25T14 30 24 619+02 00 return self _exec_single_context( 2026-03-25T14 30 24 619+02 00 ^^^^^^^^^^^^^^^^^^^^^^^^^^ 2026-03-25T14 30 24 619+02 00 File /usr/local/lib/python3 11/site-packages/sqlalchemy/engine/base py line 1986 in _exec_single_context 2026-03-25T14 30 24 619+02 00 self _handle_dbapi_exception( 2026-03-25T14 30 24 619+02 00 File /usr/local/lib/python3 11/site-packages/sqlalchemy/engine/base py line 2366 in _handle_dbapi_exception 2026-03-25T14 30 24 619+02 00 raise exc_info[1] with_traceback(exc_info[2]) 2026-03-25T14 30 24 619+02 00 File /usr/local/lib/python3 11/site-packages/sqlalchemy/engine/base py line 1967 in _exec_single_context 2026-03-25T14 30 24 619+02 00 self dialect do_execute( 2026-03-25T14 30 24 619+02 00 File /usr/local/lib/python3 11/site-packages/sqlalchemy/engine/default py line 952 in do_execute 2026-03-25T14 30 24 619+02 00 cursor execute(statement parameters) 2026-03-25T14 30 24 619+02 00 File /usr/local/lib/python3 11/site-packages/sqlalchemy/dialects/postgresql/asyncpg py line 585 in execute 2026-03-25T14 30 24 619+02 00 self _adapt_connection await_( 2026-03-25T14 30 24 619+02 00 File /usr/local/lib/python3 11/site-packages/sqlalchemy/util/_concurrency_py3k py line 132 in await_only 2026-03-25T14 30 24 619+02 00 return current parent switch(awaitable) # type ignore[no-any-return attr-defined] # noqa E501 2026-03-25T14 30 24 619+02 00 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 2026-03-25T14 30 24 619+02 00 File /usr/local/lib/python3 11/site-packages/sqlalchemy/util/_concurrency_py3k py line 196 in greenlet_spawn 2026-03-25T14 30 24 619+02 00 value = await result 2026-03-25T14 30 24 619+02 00 ^^^^^^^^^^^^ 2026-03-25T14 30 24 619+02 00 File /usr/local/lib/python3 11/site-packages/sqlalchemy/dialects/postgresql/asyncpg py line 563 in _prepare_and_execute 2026-03-25T14 30 24 619+02 00 self _handle_exception(error) 2026-03-25T14 30 24 619+02 00 File /usr/local/lib/python3 11/site-packages/sqlalchemy/dialects/postgresql/asyncpg py line 513 in _handle_exception 2026-03-25T14 30 24 619+02 00 self _adapt_connection _handle_exception(error) 2026-03-25T14 30 24 619+02 00 File /usr/local/lib/python3 11/site-packages/sqlalchemy/dialects/postgresql/asyncpg py line 799 in _handle_exception 2026-03-25T14 30 24 619+02 00 raise error 2026-03-25T14 30 24 619+02 00 File /usr/local/lib/python3 11/site-packages/sqlalchemy/dialects/postgresql/asyncpg py line 550 in _prepare_and_execute 2026-03-25T14 30 24 619+02 00 self _rows = deque(await prepared_stmt fetch(*parameters)) 2026-03-25T14 30 24 619+02 00 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 2026-03-25T14 30 24 619+02 00 File /usr/local/lib/python3 11/site-packages/asyncpg/prepared_stmt py line 177 in fetch 2026-03-25T14 30 24 619+02 00 data = await self __bind_execute(args 0 timeout) 2026-03-25T14 30 24 619+02 00 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 20
26-03-25T14 30 24 619+02 00 File /usr/local/lib/python3 11/site-packages/asyncpg/prepared_stmt py line 268 in __bind_execute 2026-03-25T14 30 24 619+02 00 data status _ = await self __do_execute( 2026-03-25T14 30 24 619+02 00 ^^^^^^^^^^^^^^^^^^^^^^^^ 2026-03-25T14 30 24 619+02 00 File /usr/local/lib/python3 11/site-packages/asyncpg/prepared_stmt py line 257 in __do_execute 2026-03-25T14 30 24 619+02 00 return await executor(protocol) 2026-03-25T14 30 24 619+02 00 ^^^^^^^^^^^^^^^^^^^^^^^^ 2026-03-25T14 30 24 619+02 00 File asyncpg/protocol/protocol pyx line 205 in bind_execute 2026-03-25T14 30
l
@Marvin What does this mean?
Copy code
result: Result[Any] = compile_state_cls.orm_execute_statement(
                          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/bulk_persistence.py", line 2033, in orm_execute_statement
    return super().orm_execute_statement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/context.py", line 306, in orm_execute_statement
    result = conn.execute(
             ^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1419, in execute
    return meth(
           ^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/sql/elements.py", line 527, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1641, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1846, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1986, in _exec_single_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2366, in _handle_dbapi_exception
    raise exc_info[1].with_traceback(exc_info[2])
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 952, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 585, in execute
    self._adapt_connection.await_(
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 132, in await_only
    return current.parent.switch(awaitable)  # type: ignore[no-any-return,attr-defined] # noqa: E501
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 196, in greenlet_spawn
    value = await result
            ^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 563, in _prepare_and_execute
    self._handle_exception(error)
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 513, in _handle_exception
    self._adapt_connection._handle_exception(error)
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 799, in _handle_exception
    raise error
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 550, in _prepare_and_execute
    self._rows = deque(await prepared_stmt.fetch(*parameters))
                       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/asyncpg/prepared_stmt.py", line 177, in fetch
    data = await self.__bind_execute(args, 0, timeout)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/asyncpg/prepared_stmt.py", line 268, in __bind_execute
    data, status, _ = await self.__do_execute(
                      ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/asyncpg/prepared_stmt.py", line 257, in __do_execute
    return await executor(protocol)
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "asyncpg/protocol/protocol.pyx", line 205, in bind_execute
TimeoutError
m
✅ thought for 176.9 seconds
Short version: your database query took longer than the allowed timeout and asyncpg/SQLAlchemy aborted it. The TimeoutError is coming from the PostgreSQL driver while executing a prepared statement. Why this happens - Long-running SQL (e.g., deleting/reading lots of rows during retention) - Missing/inefficient indexes or bloat causing slow scans - Locks blocking your statement - Timeout settings too low for the workload How to fix - Increase Prefect’s DB timeouts and retry:
Copy code
# e.g., bump statement timeout to 5 minutes and connection timeout to 30s
  export PREFECT_API_DATABASE_TIMEOUT=300
  export PREFECT_API_DATABASE_CONNECTION_TIMEOUT=30
  # then restart your Prefect server/API
Docs: Settings reference - Ensure you’re deleting in batches (the docs flow does this). For very large datasets, use smaller batches and brief sleeps between them. - Check for locks or slow plans: - Look at Postgres logs for statement_timeout or blocking locks - Run EXPLAIN ANALYZE on the slow query (if you have it) - Verify expected indexes exist (Prefect migrations create indexes on columns like flow_run.start_time/state_type/parent_task_run_id, task_run.flow_run_id, logs.flow_run_id/timestamp). If your schema is old, upgrade migrations:
Copy code
PREFECT_API_DATABASE_TIMEOUT=600 prefect server database upgrade -y
- If this happened during log cleanup after deletions: that delete can touch a lot of rows. Increasing 
PREFECT_API_DATABASE_TIMEOUT
usually resolves it; you can also enable the built-in vacuum service to clean orphans in the background: Database maintenance If you can share when this fired (e.g., during your retention flow’s read vs. delete step) and your DB size, I can suggest tighter, query-specific tuning.
7 Views
Open in Slack
PreviousNext
Powered by