https://prefect.io logo
Join Slack
Powered by
Hey, <@ULVA73B9P>, facing issues with timeout erro...
# ask-marvin
d
Hey, @Marvin, facing issues with timeout error on postgresql
Copy code
prefect-server-55f97569bd-67s8l     data, status, _ = await self.__do_execute(                                                                                                                                                                                 │
│ prefect-server-55f97569bd-67s8l                       ^^^^^^^^^^^^^^^^^^^^^^^^                                                                                                                                                                                 │
│ prefect-server-55f97569bd-67s8l   File "/usr/local/lib/python3.11/site-packages/asyncpg/prepared_stmt.py", line 256, in __do_execute                                                                                                                           │
│ prefect-server-55f97569bd-67s8l     return await executor(protocol)                                                                                                                                                                                            │
│ prefect-server-55f97569bd-67s8l            ^^^^^^^^^^^^^^^^^^^^^^^^                                                                                                                                                                                            │
│ prefect-server-55f97569bd-67s8l   File "asyncpg/protocol/protocol.pyx", line 206, in bind_execute                                                                                                                                                              │
│ prefect-server-55f97569bd-67s8l TimeoutError                                                                                                                                                                                                                   │
│ prefect-server-55f97569bd-67s8l 16:58:47.763 | ERROR   | prefect.server - Error running before-transition hook in rule <class 'prefect.server.orchestration.core_policy.SecureFlowConcurrencySlots'>: !TimeoutError()                                          │
│ prefect-server-55f97569bd-67s8l Traceback (most recent call last):                                                                                                                                                                                             │
│ prefect-server-55f97569bd-67s8l   File "/usr/local/lib/python3.11/site-packages/prefect/server/orchestration/rules.py", line 651, in __aenter__                                                                                                                │
│ prefect-server-55f97569bd-67s8l     await self.before_transition(*entry_context)                                                                                                                                                                               │
│ prefect-server-55f97569bd-67s8l   File "/usr/local/lib/python3.11/site-packages/prefect/server/orchestration/core_policy.py", line 582, in before_transition                                                                                                   │
│ prefect-server-55f97569bd-67s8l     acquired = await concurrency_limits_v2.bulk_increment_active_slots(                                                                                                                                                        │
│ prefect-server-55f97569bd-67s8l                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^                                                                                                                                                        │
│ prefect-server-55f97569bd-67s8l   File "/usr/local/lib/python3.11/site-packages/prefect/server/models/concurrency_limits_v2.py", line 208, in bulk_increment_active_slots                                                                                      │
│ prefect-server-55f97569bd-67s8l     result = await session.execute(query)                                                                                                                                                                                      │
│ prefect-server-55f97569bd-67s8l              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^                                                                                                                                                                                      │
│ prefect-server-55f97569bd-67s8l   File "/usr/local/lib/python3.11/site-packages/sqlalchemy/ext/asyncio/session.py", line 449, in execute                                                                                                                       │
│ prefect-server-55f97569bd-67s8l     result = await greenlet_spawn(
m
✅ thought for 171.3 seconds
Thanks for sharing the traceback — that’s helpful. Here’s what it’s telling us: - The Prefect API server is timing out while trying to increment flow concurrency slots (
SecureFlowConcurrencySlots
→ 
concurrency_limits_v2.bulk_increment_active_slots
). - This call runs on the API server and executes a simple UPDATE/SELECT against Postgres. An 
asyncpg.TimeoutError
here is almost always a client-side statement timeout or connection/pool pressure, sometimes compounded by DB lock contention. Quick fixes to try (Prefect 3.x) Set these environment variables on the API server pod/container (the one named like 
prefect-server-...
). Bump timeouts and right-size the pool:
Copy code
# Increase statement execution timeout (seconds)
PREFECT_SERVER_DATABASE_TIMEOUT=60

# Give a bit more time for initial connections
PREFECT_SERVER_DATABASE_CONNECTION_TIMEOUT=10

# Size the SQLAlchemy pool for your concurrency
PREFECT_SERVER_DATABASE_SQLALCHEMY_POOL_SIZE=20
PREFECT_SERVER_DATABASE_SQLALCHEMY_MAX_OVERFLOW=40

# How long to wait for a connection from the pool (seconds)
PREFECT_SERVER_DATABASE_SQLALCHEMY_POOL_TIMEOUT=60

# Recycle connections to avoid stale/slow ones (seconds)
PREFECT_SERVER_DATABASE_SQLALCHEMY_POOL_RECYCLE=600

# Optional: label connections in pg_stat_activity
PREFECT_SERVER_DATABASE_SQLALCHEMY_CONNECT_ARGS_APPLICATION_NAME=prefect-api
Notes - You can also use the 
PREFECT_API_DATABASE_*
prefixes; both are recognized by the API server in 3.x. - Apply these to the API server deployment (e.g., via your Helm values or Deployment env vars), then restart the pod. Why this helps - 
PREFECT_SERVER_DATABASE_TIMEOUT
is the client-side per-statement timeout used by asyncpg via SQLAlchemy; the default is ~10s which can be tight under load or when there’s lock contention. - Pool sizing and 
pool_timeout
reduce waits when many orchestration requests hit the DB at once. - 
pool_recycle=600
helps avoid stale connections and is a good operational default. Database checks (to rule out contention/under-provisioning) - Look for locks or long-running transactions when the timeouts occur: - 
pg_stat_activity
for blocking queries - 
pg_locks
for contention - Check DB CPU/IO saturation and connection limits - Enable 
log_min_duration_statement
temporarily to capture slow statements - Ensure autovacuum is healthy; bloated tables/indexes can slow updates Edge cases that can exacerbate timeouts - Heavy concurrency around the same concurrency limits (lots of runs contending for the same limit) can increase lock wait times. - Network latency between the API server and Postgres. - A global Postgres 
statement_timeout
set too low (server-side) — though that typically yields a QueryCanceled error, not an asyncpg TimeoutError. If you’re running in Kubernetes - Make sure these env vars are set on the API server pod. If you’re using a Helm chart, add them to the API container env. Restart the deployment after changes. - If you use PgBouncer, set it to transaction pooling and ensure its pool is large enough. Links - Settings reference (database, pool, timeouts): Prefect 3 settings reference - Self-hosted guidance and timeouts: Self-hosted Prefect guide - Deep dive on Postgres/asyncpg pooling and performance: “More Memory, More Problems” blog A few questions to tune this further - Prefect version (assuming 3.x) and how many concurrent flow runs are you orchestrating? - Postgres version and whether it’s managed (RDS, CloudSQL) or self-hosted? - Do you see any blocking queries or high CPU/IO on the DB when these errors occur? - Are timeouts isolated to concurrency slot checks or more general? If you share that, I can suggest specific pool/timeout values and any DB-side tweaks.
d
@Marvin if this is prefect 3 with backgroundServices as separate deployment. Should this be global env var?
Copy code
- name: PREFECT_SERVER_DATABASE_SQLALCHEMY_POOL_SIZE
          value: "100"
        - name: PREFECT_SERVER_DATABASE_CONNECTION_TIMEOUT
          value: "15"
        - name: PREFECT_API_SERVICES_LATE_RUNS_AFTER_SECONDS
          value: "1800"
        - name: PREFECT_SERVER_DATABASE_TIMEOUT
          value: "60"
        - name: PREFECT_SERVER_DATABASE_CONNECTION_TIMEOUT
          value: "10"
        - name: PREFECT_SERVER_DATABASE_SQLALCHEMY_POOL_SIZE
          value: "20"
        - name: PREFECT_SERVER_DATABASE_SQLALCHEMY_MAX_OVERFLOW
          value: "40"
        - name: PREFECT_SERVER_DATABASE_SQLALCHEMY_POOL_TIMEOUT
          value: "60"
        - name: PREFECT_SERVER_DATABASE_SQLALCHEMY_POOL_RECYCLE
          value: "600"
        - name: PREFECT_SERVER_DATABASE_SQLALCHEMY_CONNECT_ARGS_APPLICATION_NAME
          value: "prefect-api"
m
✅ thought for 516.9 seconds
Short answer: set these on every Prefect Server process that needs them. If your background services run as a separate deployment/pod, they need the same env vars too. There isn’t a cross-deployment “global” env var—each Deployment must receive the settings (use a shared ConfigMap/Secret or Helm values to DRY it). A few specifics based on your list: - Use the 
PREFECT_SERVER_DATABASE_*
prefix in Prefect 3.x for DB settings. You have some duplicates with conflicting values—pick one and remove the duplicates. - 
PREFECT_API_SERVICES_LATE_RUNS_AFTER_SECONDS
is a valid alias, but the canonical name is 
PREFECT_SERVER_SERVICES_LATE_RUNS_AFTER_SECONDS
. - 
PREFECT_SERVER_DATABASE_SQLALCHEMY_CONNECT_ARGS_APPLICATION_NAME
is the correct syntax for the application name. Cleaned example (choose your final numbers and apply to both API and background services deployments):
Copy code
- name: PREFECT_SERVER_DATABASE_SQLALCHEMY_POOL_SIZE
  value: "20"            # remove duplicates; choose one value
- name: PREFECT_SERVER_DATABASE_SQLALCHEMY_MAX_OVERFLOW
  value: "40"
- name: PREFECT_SERVER_DATABASE_SQLALCHEMY_POOL_TIMEOUT
  value: "60"
- name: PREFECT_SERVER_DATABASE_SQLALCHEMY_POOL_RECYCLE
  value: "600"
- name: PREFECT_SERVER_DATABASE_CONNECTION_TIMEOUT
  value: "10"
- name: PREFECT_SERVER_DATABASE_TIMEOUT
  value: "60"
- name: PREFECT_SERVER_SERVICES_LATE_RUNS_AFTER_SECONDS
  value: "1800"          # prefer SERVER_ prefix; API_ alias also works
- name: PREFECT_SERVER_DATABASE_SQLALCHEMY_CONNECT_ARGS_APPLICATION_NAME
  value: "prefect-api"
Why this applies to both: all server components (API and services) read the same settings model via environment variables; when they run as separate processes/pods they each need those env vars set. References (source): - Database settings model: server/database.py - Services settings (late runs): server/services.py - Engine configuration using these settings: server/database/configurations.py If you’re using Kubernetes, I recommend putting these into a ConfigMap/Secret and using 
envFrom
in both the API and background-services Deployments so they stay in sync. Would you like a quick example manifest for that?
33 Views
Open in Slack
PreviousNext
Powered by