`db_vacuum` on high-volume Postgres: events and flow_runs contend for one hardcoded connection, and row DELETE cannot promptly reduce disk usage

Describe the current behavior

Summary

On a high-volume self-hosted Prefect 3.7.8 + PostgreSQL 18.x deployment, official db_vacuum has two design limits we cannot change from settings. Both show up once the server writes hundreds of thousands of rows per hour on events / event_resources and related run tables:

1. events and flow_runs vacuum contend for a single hardcoded connection. The maintenance engine is pool_size=1 / max_overflow=0. Both schedulers share it, so they serialize and starve each other.
2. Row DELETE cannot promptly reduce disk usage. Dead tuples become reusable only after VACUUM; relation files still do not shrink unless VACUUM FULL / pg_repack / pg_squeeze.

Environment

• Prefect: 3.7.8 (self-hosted server / background services)
• Database: PostgreSQL 18.x
• PREFECT_SERVER_SERVICES_DB_VACUUM_ENABLED=events
• PREFECT_EVENTS_RETENTION_PERIOD=PT12H
• PREFECT_SERVER_SERVICES_DB_VACUUM_BATCH_SIZE=5000

Problem 1 — events and flow_runs contend for one hardcoded connection

_maintenance_database_config() in db_vacuum.py creates a dedicated Postgres engine and then:

# Opt out of the API statement timeout and keep a minimal pool, since
# vacuum tasks run sequentially on an hourly loop.
cached.timeout = None
cached.sqlalchemy_pool_size = 1
cached.sqlalchemy_max_overflow = 0

Tests lock this in (tests/server/services/test_db_vacuum.py):

assert config.sqlalchemy_pool_size == 1
assert config.sqlalchemy_max_overflow == 0

Problem 2 — Row DELETE cannot promptly reduce disk usage

Official cleanup is a time-window policy: delete rows older than retention_period by occurred / end_time.

On Postgres heap tables, DELETE only creates dead tuples. It does not promptly lower disk usage:

• space becomes reusable only after VACUUM (and even then the file often stays large)
• shrinking files needs VACUUM FULL / pg_repack / pg_squeeze, which is expensive and often impractical on TB-scale write-heavy tables So even after rows are gone, disk stays high for a long time. Operators must run a second reclaim step that Prefect does not own.

The retention model would map cleanly onto PARTITION BY RANGE (occurred) (and end_time for terminal flow_runs): DROP PARTITION is a metadata operation, is fast, and releases disk promptly.

Describe the proposed behavior

1. Give events and flow_runs vacuum separate capacity so they do not share one hardcoded connection (pool_size=1 / max_overflow=0). Either make the maintenance pool configurable, or give each vacuum type its own engine, and document the contention.
2. Document that official row DELETE does not promptly reduce Postgres disk usage: reuse needs VACUUM; shrinking files needs VACUUM FULL / pg_repack.
3. Longer term: optional time-range partitioning (or partition-drop retention) for events / event_resources (and optionally flow_run + children), so retention can drop old data and free disk promptly.

Example Use

No response

Additional context

No response