apoliakov · January 23, 2026 02:40
diff --git a/dbos_observability.sql b/dbos_observability.sql
 -- Queries for worker-centric DBOS Observability 
 -- Many of these are supported by the DBOS Conductor UI
 -- https://docs.dbos.dev/production/conductor
 -- For questions, reach out to [email protected]

 -- DBOS assigns each worker an `executor_id` when it starts. You can set the custom ID in DBOS config:
 -- https://docs.dbos.dev/python/reference/configuration
 -- This query returns all workflows that are currently running on each worker by executor_id

 -- 1. Query what executors are doing at any given time
 SELECT 
    executor_id, 
    workflow_uuid, 
    name, 
    status, 
    queue_name, 
    to_timestamp(created_at/1000) as created_at, 
    to_timestamp((CASE WHEN queue_name is null THEN created_at ELSE started_at_epoch_ms END)/1000) as started_at 
 FROM dbos.workflow_status 
 WHERE status = 'PENDING' 
 ORDER BY executor_id;

 -- 1.a. Grouped form. Return just the count of running workflows per worker:
 SELECT 
    executor_id, 
    count(*) as workflow_count
 FROM dbos.workflow_status 
 WHERE status = 'PENDING' 
 GROUP BY executor_id
 ORDER BY executor_id;


 -- 2. Tasks that are: ENQUEUED aka Backlog
 -- Conductor has a good UI for this under the Applications -> Queues tab
 -- DBOS Queues can have different names so you can optionally add `and queue_name = 'your_queue_name'` to the where clause
 -- This returns oldest workflows first
 SELECT 
    workflow_uuid, 
    name, 
    status, 
    queue_name, 
    to_timestamp(created_at/1000) as created_at
 FROM dbos.workflow_status 
 WHERE status = 'ENQUEUED' 
 ORDER BY created_at ASC;


 -- 3. Latency between enqueue and execution
 -- Here the latency_ms column is the delay between the time the workflow was created (enqueued) and the time it actually started execution
 -- Note that for workflows that have been interrupted and recovered, this captures the delay up to the most recent start time.
 -- This queries all workflows for the last 24 hours (where) and returns the top 100 highest latency workflows (limit)
 SELECT 
    workflow_uuid, 
    name, 
    status, 
    queue_name, 
    to_timestamp(created_at/1000) as created_at_ts,
    to_timestamp(started_at_epoch_ms/1000) as started_at_ts,
    started_at_epoch_ms - created_at as latency_ms
 FROM dbos.workflow_status 
 WHERE status <> 'ENQUEUED' and queue_name is not null 
    and started_at_epoch_ms >= (extract(epoch FROM (now() - interval '24 hours')) * 1000)
 ORDER BY latency_ms DESC
 LIMIT 100;


 -- 4. Timeline of workflows and tasks for a given worker for the last 24 hours
 -- This returns one row for each workflow and step. Each row is a range with a start_time and end_time. Ranges can overlap.
 -- It subsets for a specific executor_id: make sure to replace with yours.
 -- Workflow status and step results/errors are formatted in the "detail" column. Various other options are possible.
 WITH config AS (
    SELECT 'YOUR_EXECUTOR_ID' AS exec_id  --NOTE: replace with your executor_id
 ),
 time_range AS (
    SELECT extract(epoch FROM (now() - interval '24 hours')) * 1000 AS start_ms
 )
 SELECT 
    to_timestamp((CASE WHEN queue_name is null THEN created_at ELSE started_at_epoch_ms END) / 1000.0)  AS start_time,
    CASE WHEN status in ('SUCCESS', 'CANCELLED', 'ERROR')  THEN to_timestamp(updated_at / 1000.0) ELSE NULL END as end_time,
    'WORKFLOW' AS type,
    w.workflow_uuid,
    w.name AS function_name,
    'QUEUE: ' || coalesce(w.queue_name, 'None') || '; STATUS: ' || w.status AS detail
 FROM dbos.workflow_status w, config, time_range
 WHERE w.executor_id = config.exec_id
 AND w.created_at >= time_range.start_ms
 AND w.status <> 'ENQUEUED'

 UNION ALL

 SELECT 
    to_timestamp(o.started_at_epoch_ms  / 1000.0)  AS start_time,
    to_timestamp(o.completed_at_epoch_ms  / 1000.0) AS end_time,
    'STEP' AS type,
    o.workflow_uuid,
    o.function_name,
    'ID: ' || o.function_id  || '; ERROR: ' || coalesce(o.error, 'None') AS detail
 FROM dbos.operation_outputs o
 JOIN dbos.workflow_status w ON o.workflow_uuid = w.workflow_uuid
 JOIN config ON true
 JOIN time_range ON true
 WHERE w.executor_id = config.exec_id
 AND o.started_at_epoch_ms >= time_range.start_ms

 ORDER BY start_time ASC;

 -- For more info, see the schema at:
 -- https://docs.dbos.dev/explanations/system-tables
	-- Queries for worker-centric DBOS Observability
	-- Many of these are supported by the DBOS Conductor UI
	-- https://docs.dbos.dev/production/conductor
	-- For questions, reach out to [email protected]

	-- DBOS assigns each worker an `executor_id` when it starts. You can set the custom ID in DBOS config:
	-- https://docs.dbos.dev/python/reference/configuration
	-- This query returns all workflows that are currently running on each worker by executor_id

	-- 1. Query what executors are doing at any given time
	SELECT
	executor_id,
	workflow_uuid,
	name,
	status,
	queue_name,
	to_timestamp(created_at/1000) as created_at,
	to_timestamp((CASE WHEN queue_name is null THEN created_at ELSE started_at_epoch_ms END)/1000) as started_at
	FROM dbos.workflow_status
	WHERE status = 'PENDING'
	ORDER BY executor_id;

	-- 1.a. Grouped form. Return just the count of running workflows per worker:
	SELECT
	executor_id,
	count(*) as workflow_count
	FROM dbos.workflow_status
	WHERE status = 'PENDING'
	GROUP BY executor_id
	ORDER BY executor_id;


	-- 2. Tasks that are: ENQUEUED aka Backlog
	-- Conductor has a good UI for this under the Applications -> Queues tab
	-- DBOS Queues can have different names so you can optionally add `and queue_name = 'your_queue_name'` to the where clause
	-- This returns oldest workflows first
	SELECT
	workflow_uuid,
	name,
	status,
	queue_name,
	to_timestamp(created_at/1000) as created_at
	FROM dbos.workflow_status
	WHERE status = 'ENQUEUED'
	ORDER BY created_at ASC;


	-- 3. Latency between enqueue and execution
	-- Here the latency_ms column is the delay between the time the workflow was created (enqueued) and the time it actually started execution
	-- Note that for workflows that have been interrupted and recovered, this captures the delay up to the most recent start time.
	-- This queries all workflows for the last 24 hours (where) and returns the top 100 highest latency workflows (limit)
	SELECT
	workflow_uuid,
	name,
	status,
	queue_name,
	to_timestamp(created_at/1000) as created_at_ts,
	to_timestamp(started_at_epoch_ms/1000) as started_at_ts,
	started_at_epoch_ms - created_at as latency_ms
	FROM dbos.workflow_status
	WHERE status <> 'ENQUEUED' and queue_name is not null
	and started_at_epoch_ms >= (extract(epoch FROM (now() - interval '24 hours')) * 1000)
	ORDER BY latency_ms DESC
	LIMIT 100;


	-- 4. Timeline of workflows and tasks for a given worker for the last 24 hours
	-- This returns one row for each workflow and step. Each row is a range with a start_time and end_time. Ranges can overlap.
	-- It subsets for a specific executor_id: make sure to replace with yours.
	-- Workflow status and step results/errors are formatted in the "detail" column. Various other options are possible.
	WITH config AS (
	SELECT 'YOUR_EXECUTOR_ID' AS exec_id --NOTE: replace with your executor_id
	),
	time_range AS (
	SELECT extract(epoch FROM (now() - interval '24 hours')) * 1000 AS start_ms
	)
	SELECT
	to_timestamp((CASE WHEN queue_name is null THEN created_at ELSE started_at_epoch_ms END) / 1000.0) AS start_time,
	CASE WHEN status in ('SUCCESS', 'CANCELLED', 'ERROR') THEN to_timestamp(updated_at / 1000.0) ELSE NULL END as end_time,
	'WORKFLOW' AS type,
	w.workflow_uuid,
	w.name AS function_name,
	'QUEUE: ' \|\| coalesce(w.queue_name, 'None') \|\| '; STATUS: ' \|\| w.status AS detail
	FROM dbos.workflow_status w, config, time_range
	WHERE w.executor_id = config.exec_id
	AND w.created_at >= time_range.start_ms
	AND w.status <> 'ENQUEUED'

	UNION ALL

	SELECT
	to_timestamp(o.started_at_epoch_ms / 1000.0) AS start_time,
	to_timestamp(o.completed_at_epoch_ms / 1000.0) AS end_time,
	'STEP' AS type,
	o.workflow_uuid,
	o.function_name,
	'ID: ' \|\| o.function_id \|\| '; ERROR: ' \|\| coalesce(o.error, 'None') AS detail
	FROM dbos.operation_outputs o
	JOIN dbos.workflow_status w ON o.workflow_uuid = w.workflow_uuid
	JOIN config ON true
	JOIN time_range ON true
	WHERE w.executor_id = config.exec_id
	AND o.started_at_epoch_ms >= time_range.start_ms

	ORDER BY start_time ASC;

	-- For more info, see the schema at:
	-- https://docs.dbos.dev/explanations/system-tables
No results found