Thanks for the details — this is a common pain point when you push lots of runs/subflows through a self‑hosted server. Even with low CPU/RAM, the UI can feel slow because it depends on DB queries and API pagination under heavy write/read load. Below are the highest‑impact, low‑risk optimizations you can apply up front. I’ll assume Prefect 3.x. Quick wins (apply these first) - Increase API DB connection pool size on the server

# On the Prefect server process
  PREFECT_SERVER_DATABASE_SQLALCHEMY_POOL_SIZE=20
  PREFECT_SERVER_DATABASE_SQLALCHEMY_MAX_OVERFLOW=30
  PREFECT_SERVER_DATABASE_TIMEOUT=30.0
  PREFECT_SERVER_DATABASE_CONNECTION_TIMEOUT=10.0

Code refs: server/database settings, database configuration - Use PgBouncer (recommended) - If you run PgBouncer in transaction mode, disable prepared statement caching in SQLAlchemy:

PREFECT_SERVER_DATABASE_SQLALCHEMY_CONNECT_ARGS__STATEMENT_CACHE_SIZE=0

- Point Prefect at PgBouncer and keep your Postgres tuned for fewer, longer-lived backend connections. - Start the Prefect API/UI without background services on the same process - This reduces DB/API contention during spikes:

prefect server start --no-services

Then run services (scheduler, late-runs, etc.) in a separate process/VM if needed. CLI help (verified):

prefect server start --help

Code refs: CLI server command, server services settings - Keep pagination sane and filter aggressively - The API enforces a max per-request limit (default 200). You can raise it modestly if your network is fast and DB is tuned, but avoid very large values:

PREFECT_SERVER_API_DEFAULT_LIMIT=300

Code ref: server API settings - In the UI, use tighter time windows and state filters, and avoid loading “all” runs when you have many subflows. Database-side checks - Ensure critical indexes exist (migrations usually create these, but verify)

-- Flow runs
  CREATE INDEX IF NOT EXISTS ix_flow_run__parent_flow_run_id ON flow_run(parent_flow_run_id);
  CREATE INDEX IF NOT EXISTS ix_flow_run__state_type ON flow_run(state_type);
  CREATE INDEX IF NOT EXISTS ix_flow_run__updated ON flow_run(updated DESC);
  CREATE INDEX IF NOT EXISTS ix_flow_run__start_time ON flow_run(start_time DESC);

  -- Task runs
  CREATE INDEX IF NOT EXISTS ix_task_run__flow_run_id ON task_run(flow_run_id);

Subflow-heavy UIs call parent/child lookups frequently; missing

parent_flow_run_id

and time/state indexes hurt. - Size Postgres for your concurrency and I/O - If using Cloud SQL, ensure adequate IOPS and

max_connections

for your pool + PgBouncer. - Useful baselines:

max_connections ≈ pool_size + (workers * concurrency) + overhead
    shared_buffers ~ 25% RAM
    work_mem 16–64MB (workload dependent)

- Enable pg_stat_statements and log slow queries temporarily to find hotspots. Reduce event/log write load (if heavy) - High-volume logs/events from Cloud Run can saturate DB writes and slow read queries used by the UI. - If acceptable, reduce client log volume or turn off logging to API on workers during spikes:

PREFECT_LOGGING_TO_API_ENABLED=false

Or tune batch sizes in logging settings. Code ref: logging settings Networking and API settings - If you front the API with a load balancer or proxy, bump keepalive to avoid churn: ``` PREFECT_SERVER_API_KEEPALIVE_TIMEOUT=65

Code ref: <https://github.com/PrefectHQ/prefect/blob/main/src/prefect/settings/models/server/api.py|server API settings>

Operational tips
- Confirm active settings quickly:

prefect config view --active-profile

- Temporary debugging (only while investigating):

PREFECT_SERVER_DATABASE_ECHO=true PREFECT_SERVER_LOGGING_LEVEL=DEBUG ``` Why this helps - UI slowness almost always comes from DB/API contention and large result sets, not CPU/RAM on the VM. - Bigger DB pools + PgBouncer prevent connection starvation. - Turning off background services on the API/UI process prevents periodic service jobs from competing with UI reads. - Indexes ensure subflow-heavy queries return fast. - Reasonable pagination and filters keep responses small and snappy. A few clarifying questions to tailor this further - Roughly how many flow runs and subflows are on screen when it feels sluggish? Which pages are slowest (Runs list, Flow run detail, Gantt, etc.)? - Postgres details: version, instance type, max_connections, and whether you already use PgBouncer? - Are you running the API/UI and services in the same process (i.e., without

--no-services

) today? - Any load balancer/proxy between your browser and the server VM? If you share those, I can recommend specific pool sizes and DB parameters (and help validate indexes for your query patterns).

Great question — here are the most relevant docs to follow for running PgBouncer on a GCP VM in front of a Cloud SQL for PostgreSQL instance, and wiring Prefect Server to it. Google Cloud docs (PgBouncer + Cloud SQL Postgres) - Use PgBouncer for connection pooling with Cloud SQL (overview, config, and deployment patterns) - Cloud SQL for PostgreSQL: Use PgBouncer for connection pooling - Connect from a Compute Engine VM - Connect from Compute Engine to Cloud SQL for PostgreSQL - Cloud SQL Auth Proxy (recommended if you want IAM auth and secure connectivity) - Connect using the Cloud SQL Auth Proxy - Private IP (alternative to the Auth Proxy if your VM is on the same VPC) - Configure Private IP for Cloud SQL PgBouncer references - Config and parameters - PgBouncer configuration - Notes about transaction pooling (no server‑side prepared statements) - PgBouncer FAQ Prefect docs (self‑hosted + DB settings) - Self‑hosted server (how to run Prefect Server on your VM) - Self-hosted Prefect Server - Server CLI reference - Prefect server CLI - Settings reference (how to pass DB URL and connect args) - Prefect settings Suggested wiring (VM with Auth Proxy + PgBouncer) 1) Run the Cloud SQL Auth Proxy on your VM (pointing at your instance) - Follow: Connect using the Cloud SQL Auth Proxy - Example:

cloud-sql-proxy --port 5432 --address 127.0.0.1 --verbose \
       --credentials-file /path/to/sa.json \
       PROJECT:REGION:INSTANCE

2) Install and configure PgBouncer on the VM - Follow: Use PgBouncer for connection pooling - Point PgBouncer’s

pgbouncer.ini

to the Auth Proxy as the server:

[databases]
     prefect = host=127.0.0.1 port=5432 dbname=prefect user=YOUR_DB_USER

     [pgbouncer]
     listen_addr = 127.0.0.1
     listen_port = 6432
     pool_mode = transaction
     max_client_conn = 1000
     default_pool_size = 25

- Make sure your DB user exists in Cloud SQL (PgBouncer does not create users). 3) Point Prefect Server at PgBouncer - Set environment variables on the Prefect server process:

PREFECT_SERVER_DATABASE_CONNECTION_URL="<postgresql+asyncpg://YOUR_DB_USER:YOUR_DB_PASS@127.0.0.1:6432/prefect>"

     # PgBouncer + transaction pool mode: disable server-prepared statements
     PREFECT_SERVER_DATABASE_SQLALCHEMY_CONNECT_ARGS__STATEMENT_CACHE_SIZE=0

     # Increase pool capacity and timeouts as needed
     PREFECT_SERVER_DATABASE_SQLALCHEMY_POOL_SIZE=20
     PREFECT_SERVER_DATABASE_SQLALCHEMY_MAX_OVERFLOW=30
     PREFECT_SERVER_DATABASE_TIMEOUT=30.0
     PREFECT_SERVER_DATABASE_CONNECTION_TIMEOUT=10.0

4) Start Prefect Server on the VM - Docs: Prefect server CLI - Example:

prefect server start --no-services

(Run background services separately if you want to keep UI/API responsive during spikes.) Gotchas to watch for - PgBouncer in transaction mode doesn’t support server‑side prepared statements — the Prefect setting

PREFECT_SERVER_DATABASE_SQLALCHEMY_CONNECT_ARGS__STATEMENT_CACHE_SIZE=0

avoids related errors and latency. - Ensure your Cloud SQL instance has enough

max_connections

and IOPS for your PgBouncer pool size.

- If you use Private IP instead of the Auth Proxy, follow Private IP and point PgBouncer directly at the Cloud SQL private address. If you share how you plan to connect (Auth Proxy vs Private IP) and your Cloud SQL edition/size, I can suggest concrete PgBouncer pool sizes and a minimal

pgbouncer.ini

tailored to your workload.

Totally understand—high‑volume runs can flood the

log

table. Here are practical ways to both reduce what gets written and automate cleanup. Assumes Prefect 3.x. Reduce what gets stored in Postgres - Prefer GCP/Cloud Run logs over DB logs (biggest win)

# Set on your workers/Cloud Run jobs
  PREFECT_LOGGING_TO_API_ENABLED=false

Your flow/task logs will still go to stdout and be captured by Cloud Logging, but won’t be stored in Prefect’s DB. - If you still need DB logs, lower the volume

# Fewer messages
  PREFECT_LOGGING_LEVEL=WARNING

  # Smaller messages and less throughput
  PREFECT_LOGGING_TO_API_MAX_LOG_SIZE=200000
  PREFECT_LOGGING_TO_API_BATCH_SIZE=1000000
  PREFECT_LOGGING_TO_API_BATCH_INTERVAL=5.0

Settings reference: Prefect settings and logging guide: Logging customization Automate cleanup (retention) Note: Prefect Server does not currently have built‑in log retention. You’ll need a scheduled cleanup job. Option A — Cloud SQL pg_cron (recommended on GCP) - Enable pg_cron and schedule a nightly delete:

-- Enable extension (once)
  CREATE EXTENSION IF NOT EXISTS pg_cron;

  -- Delete logs older than 30 days daily at 2 AM UTC
  SELECT cron.schedule(
    'cleanup_prefect_logs',
    '0 2 * * *',
    $$DELETE FROM log WHERE timestamp < NOW() - INTERVAL '30 days';$$
  );

- For very large tables, batch deletes to avoid long locks:

SELECT cron.schedule(
    'cleanup_prefect_logs_batched',
    '0 2 * * *',
    $$WITH to_delete AS (
         SELECT ctid FROM log
         WHERE timestamp < NOW() - INTERVAL '30 days'
         LIMIT 50000
       )
       DELETE FROM log WHERE ctid IN (SELECT ctid FROM to_delete);$$
  );

Docs: Cloud SQL: pg_cron Option B — Cron on your VM with psql - Create a script:

#!/bin/bash
  psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -c "DELETE FROM log WHERE timestamp < NOW() - INTERVAL '30 days';"

- Run nightly via cron:

0 2 * * * /usr/local/bin/cleanup_prefect_logs.sh >> /var/log/cleanup_prefect_logs.log 2>&1

Option C — A small Prefect “maintenance” flow - Schedule a daily flow that runs a parameterized DELETE against your Postgres using psycopg/asyncpg. This keeps everything inside Prefect if you prefer one control plane. Helpful queries - Table size and counts:

SELECT pg_size_pretty(pg_total_relation_size('log')) AS log_table_size;
  SELECT COUNT(*) FROM log;

- Fastest delete predicate:

DELETE FROM log WHERE timestamp < NOW() - INTERVAL '30 days';

The

timestamp

column is indexed in Prefect’s schema, so this predicate is efficient. Notes - Deleting old flow runs also removes their associated logs; if you already purge old runs, that will shrink the log table too. - After heavy deletes, let autovacuum reclaim space; avoid VACUUM FULL in business hours (it locks the table). If you share your preferred retention window (e.g., 7/30/90 days) and whether you’re on Cloud SQL, I can provide a ready‑to‑run pg_cron schedule for your setup.

Yes — in Prefect 3.x you can both control how many events are written and configure automatic trimming of the events table. Here’s what to use. Control event persistence and volume - Disable event persistence entirely (events won’t be written to Postgres)

PREFECT_SERVER_SERVICES_EVENT_PERSISTER_ENABLED=false

Notes: - Events will still be produced in-memory for streaming, but won’t be stored. - Event history is lost on server restart (no DB persistence by design). - Limit event payload size and related resource fanout

# Max serialized event size (default 1,500,000 bytes)
  PREFECT_SERVER_EVENTS_MAXIMUM_SIZE_BYTES=1500000

  # Per-event limits (defaults shown)
  PREFECT_SERVER_EVENTS_MAXIMUM_LABELS_PER_RESOURCE=500
  PREFECT_SERVER_EVENTS_MAXIMUM_RELATED_RESOURCES=100
  PREFECT_SERVER_EVENTS_MAXIMUM_EVENT_NAME_LENGTH=1024

Settings ref in code: ServerEventsSettings Automated cleanup (built in) - Prefect’s Event Persister includes automatic retention trimming; enable and tune it:

# Keep events for N days (default 7 days)
  PREFECT_SERVER_EVENTS_RETENTION_PERIOD="14 days"

  # Persister must be enabled for trimming to run
  PREFECT_SERVER_SERVICES_EVENT_PERSISTER_ENABLED=true

  # Throughput tuning for writes and deletes
  PREFECT_SERVER_SERVICES_EVENT_PERSISTER_BATCH_SIZE=100          # default 20
  PREFECT_SERVER_SERVICES_EVENT_PERSISTER_FLUSH_INTERVAL=10       # default 5s
  PREFECT_SERVER_SERVICES_EVENT_PERSISTER_BATCH_SIZE_DELETE=50000 # default 10000

- How it works: - A background job runs about every 15 minutes and deletes events older than the retention period, in batches (

batch_size_delete

) to avoid long locks. - This cleans both events and associated resources. Disable event console logging (noise reduction only) - This affects console/debug logging of events, not DB writes:

PREFECT_SERVER_SERVICES_EVENT_LOGGER_ENABLED=false

Recommended combinations - Minimal DB pressure

PREFECT_SERVER_SERVICES_EVENT_PERSISTER_ENABLED=false

No events stored; rely on logs/metrics elsewhere. - Keep short history, reduce table size

PREFECT_SERVER_EVENTS_RETENTION_PERIOD="3 days"
  PREFECT_SERVER_SERVICES_EVENT_PERSISTER_ENABLED=true
  PREFECT_SERVER_SERVICES_EVENT_PERSISTER_BATCH_SIZE_DELETE=50000

Lets you retain a short window for troubleshooting without growing indefinitely. Docs and references - Self-hosted server guide: Server CLI - Settings and profiles: Settings reference - Events concepts: Events concepts - Code references: - Events settings: ServerEventsSettings - Event persister service: EventPersister - Services settings: ServerServicesSettings If you share your current events table size and desired retention window, I can suggest concrete values for retention and delete batch size that fit your Cloud SQL tier.

Short answer - No — trimming/limiting events will not wipe out your artifacts. Artifacts are stored in separate tables and are unaffected by event retention or event persister settings. - Yes — the environment variables I suggested are valid for a self‑hosted Prefect Server running Prefect 3.6.1. Details - What gets deleted by event retention - The automatic retention job only trims event records (and their related resources) older than your retention window. - Affected tables:

event

,

event_resource

. - Unaffected: artifacts (

artifact

,

artifact_collection

), flow/task runs, states, logs, blocks, variables, etc. - Artifacts are separate - Artifacts live under the artifacts API and DB models and are not tied to the events retention logic. They remain until explicitly deleted via the artifacts API or manual cleanup. - Source modules: server/api/artifacts.py, ORM models (Artifact, ArtifactCollection) - Env vars you can use on Prefect 3.6.1 (self‑hosted) - Retention window (default 7 days)

PREFECT_SERVER_EVENTS_RETENTION_PERIOD="14 days"

- Control persistence (writing events to DB)

PREFECT_SERVER_SERVICES_EVENT_PERSISTER_ENABLED=true   # set false to stop writing events entirely
    PREFECT_SERVER_SERVICES_EVENT_PERSISTER_BATCH_SIZE=100
    PREFECT_SERVER_SERVICES_EVENT_PERSISTER_FLUSH_INTERVAL=10
    PREFECT_SERVER_SERVICES_EVENT_PERSISTER_BATCH_SIZE_DELETE=50000

- Event payload/volume limits

PREFECT_SERVER_EVENTS_MAXIMUM_SIZE_BYTES=1500000
    PREFECT_SERVER_EVENTS_MAXIMUM_LABELS_PER_RESOURCE=500
    PREFECT_SERVER_EVENTS_MAXIMUM_RELATED_RESOURCES=100
    PREFECT_SERVER_EVENTS_MAXIMUM_EVENT_NAME_LENGTH=1024

- Important behavior notes: - The built‑in trimming runs about every 15 minutes as part of the Event Persister service. - If you start the API with

--no-services

or set

PREFECT_SERVER_SERVICES_EVENT_PERSISTER_ENABLED=false

, events are not written and trimming will not run (there’s nothing to trim). References - Settings (events): ServerEventsSettings - Event Persister service: EventPersister - Services settings: ServerServicesSettings - Self‑hosted server CLI: Docs: Server CLI If you want to keep a small slice of event history, use a short retention period (e.g., 1–3 days) with the persister enabled. If you want to remove events entirely from DB, set

PREFECT_SERVER_SERVICES_EVENT_PERSISTER_ENABLED=false

.