Querying Jobs

Silo includes a built-in SQL query engine powered by Apache DataFusion. You can run SQL queries to inspect jobs, monitor queue health, and debug issues — from the TypeScript client, the siloctl CLI, or the web UI.

Running Queries

From the TypeScript Client

// Rows are automatically deserialized from MessagePack — no manual decoding needed.
const result = await client.query(
  "SELECT id, status_kind, priority FROM jobs WHERE tenant = 'customer-123' LIMIT 10",
  "customer-123"  // tenant for shard routing
);

for (const row of result.rows) {
  console.log(row);  // { id: "job-abc", status_kind: "Running", priority: 10 }
}

// You can pass a generic type parameter for typed rows:
interface JobRow {
  id: string;
  status_kind: string;
  count: number;
}

const typed = await client.query<JobRow>(
  "SELECT id, status_kind, COUNT(*) as count FROM jobs GROUP BY id, status_kind",
  "customer-123"
);

for (const row of typed.rows) {
  console.log(row.id, row.status_kind, row.count);  // fully typed
}

The second argument is the tenant used for routing the query to the correct shard. If you’re not using multi-tenancy, you can omit it.

You can also pass positional bind parameters as a third argument. Placeholders use $1, $2, … and are matched by position.

const tenant = "customer-123";
const minPriority = 10;

const result = await client.query(
  "SELECT id, priority FROM jobs WHERE tenant = $1 AND priority >= $2 ORDER BY priority ASC LIMIT 25",
  tenant,
  [tenant, minPriority]
);

Supported bind parameter types in the TypeScript client are boolean, number, string, Uint8Array, and null.

The result includes:

• columns — schema information (name and dataType for each column)
• rows — deserialized JavaScript objects, one per row
• rowCount — total number of rows returned

From siloctl

# Query a specific shard
siloctl query <shard-id> "SELECT id, status_kind FROM jobs LIMIT 10"

# With a tenant
siloctl -t customer-123 query <shard-id> "SELECT id, status_kind FROM jobs WHERE tenant = 'customer-123'"

# JSON output for scripting
siloctl --json query <shard-id> "SELECT id, status_kind FROM jobs WHERE status_kind = 'Failed'"

Finding shard IDs

Use siloctl cluster info to see all shards and their owners. If you know the tenant, the client’s getShardForTenant() method returns the shard ID.

Tables

Silo exposes six tables: jobs, tasks, floating_refresh_tasks, queues, tenant_counts, and queue_counts.

jobs Table

The primary table containing all job data.

Column	Type	Description
shard_id	String	UUID of the shard storing this job
tenant	String	Tenant identifier
id	String	Job ID
priority	UInt8	Priority (0 = highest, 99 = lowest)
enqueue_time_ms	Int64	Unix timestamp in milliseconds when the job was enqueued
payload	String (nullable)	JSON-encoded job payload
status_kind	String (nullable)	Current job status (see Status Values)
status_changed_at_ms	Int64 (nullable)	Unix timestamp in milliseconds of last status change
task_group	String	Task group the job belongs to
current_attempt	UInt32 (nullable)	Current attempt number (1-indexed). Present for Waiting/Scheduled jobs, null for Running and terminal statuses
next_attempt_starts_after_ms	Int64 (nullable)	Unix timestamp in milliseconds when the next attempt is scheduled to start. Present for Waiting/Scheduled jobs, null otherwise
metadata	Map<String, String> (nullable)	Key-value metadata attached to the job

Each tenant’s jobs are also indexed by enqueue_time_ms, newest first. A tenant-scoped query that projects only shard_id, tenant, id, and enqueue_time_ms is served from that index once the shard’s index backfill has completed (enqueue_time_index_backfill.enabled = true in the database config starts the backfill), and its rows arrive in enqueue_time_ms DESC, id ASC order. Silo declares that ordering to the query planner, so an ORDER BY enqueue_time_ms DESC, id ASC on such a query runs without a sort, and a LIMIT reads only OFFSET + LIMIT index keys rather than the whole tenant. See Listing Jobs and Scan strategy priority.

tasks Table

Exposes the internal task execution queue for debugging purposes. Tasks are the ephemeral work items that drive job execution — they are created when jobs are enqueued and deleted when workers lease them.

Debugging only

This table scans the raw task queue storage. Performance may be poor for large datasets unless queries include a task_group filter, which enables an efficient key prefix scan matching the task broker’s access pattern. For best results, always filter by task_group and optionally narrow by start_time_ms.

Column	Type	Description
shard_id	String	UUID of the shard storing this task
tenant	String	Tenant identifier
task_group	String	Task group (worker routing key)
start_time_ms	Int64	Scheduled execution time in milliseconds
priority	UInt8	Task priority (0 = highest)
job_id	String	The job this task belongs to
attempt	UInt32	Attempt number
variant_type	String	Task type: RunAttempt, RequestTicket, CheckRateLimit, RefreshFloatingLimit

floating_refresh_tasks Table

Lists the pending floating limit refreshes. Each row is a refresh task waiting in a shard’s refresh index for the next worker that polls its task group; the row is removed when a worker leases it. A backed-off retry carries the time before which it will not be handed out.

Column	Type	Description
shard_id	String	UUID of the shard holding the pending refresh
task_group	String	Task group whose workers receive the refresh
tenant	String	Tenant identifier
queue_key	String	The floating concurrency queue being refreshed
task_id	String	Task ID the worker leases and reports on
not_before_ms	Int64 (nullable)	Earliest time the refresh is handed out, set on a retry after a reported failure; null means claimable now
current_max_concurrency	UInt32	The queue’s max concurrency when the refresh was scheduled
last_refreshed_at_ms	Int64	Unix timestamp in milliseconds of the queue’s last successful refresh

Filtering by task_group, and by tenant together with task_group, narrows the scan to a key prefix.

queues Table

Shows the current state of concurrency queues — which jobs hold concurrency tickets and which are waiting.

Column	Type	Description
shard_id	String	UUID of the shard
tenant	String	Tenant identifier
queue_name	String	Name of the concurrency queue (the limit key)
entry_type	String	"holder" (has a ticket) or "requester" (waiting for one)
task_id	String	Task or request ID
job_id	String (nullable)	Job ID (present for requesters)
priority	UInt8 (nullable)	Job priority (present for requesters)
timestamp_ms	Int64	When the ticket was granted or the request was made

tenant_counts Table

Pre-computed per-tenant, per-status job counts. Backed by merge-operator counters maintained on every status transition, making this table very fast for tenant-level status aggregation (sub-millisecond vs full scan).

Column	Type	Description
shard_id	String	UUID of the shard
tenant	String	Tenant identifier
status_kind	String	Job status kind (Scheduled, Running, Succeeded, etc.)
cnt	Int64	Number of jobs with this tenant and status

tenant_counts is a sparse aggregation. It returns one row per (tenant, status_kind) pair with a positive count. Statuses with zero jobs are omitted rather than returned as cnt = 0, so a missing row should be interpreted as zero.

For example, if a tenant has 12 Scheduled jobs and no Running jobs, tenant_counts returns a Scheduled row and no Running row.

queue_counts Table

Pre-computed per-queue concurrency counters. Fast for checking queue utilization without scanning individual holder/requester entries.

Column	Type	Description
shard_id	String	UUID of the shard
tenant	String	Tenant identifier
queue_name	String	Concurrency queue name
holders	Int64	Number of concurrency slots currently held
requesters	Int64	Number of tasks waiting for a slot

Status Values

The status_kind column has the following values:

Status	Meaning
Waiting	Ready to run — the job’s start time has arrived and it’s in the task queue
Scheduled	Scheduled for the future — the job’s start time hasn’t arrived yet, or it’s waiting for a retry backoff
Running	Currently being processed by a worker
Succeeded	Completed successfully
Failed	Failed after exhausting all retries
Cancelled	Cancelled by the user

Waiting vs Scheduled

Both Waiting and Scheduled are derived from the same internal status. The difference is timing: Waiting means the job’s start_time is at or before now (it’s ready to run), while Scheduled means start_time is in the future (it’s not ready yet). A job that’s mid-retry-backoff shows as Scheduled because its next attempt is scheduled for the future.

Use current_attempt to distinguish between freshly enqueued jobs (current_attempt = 1) and jobs retrying after a failure (current_attempt > 1). The next_attempt_starts_after_ms column shows exactly when the next attempt will become eligible to run.

Example Queries

Listing Jobs

The newest-first listing is cheapest when it projects only columns the enqueue-time index carries. This shape reads exactly LIMIT index keys on a shard whose index backfill has completed, with no sort and no job-record reads; fetch the other columns for the page afterwards by id:

-- Newest 50 jobs for a tenant, served from the enqueue-time index
SELECT id, enqueue_time_ms
FROM jobs
WHERE tenant = 'customer-123'
ORDER BY enqueue_time_ms DESC, id ASC
LIMIT 50

-- The next page: reads 100 index keys, returns the last 50
SELECT id, enqueue_time_ms
FROM jobs
WHERE tenant = 'customer-123'
ORDER BY enqueue_time_ms DESC, id ASC
LIMIT 50 OFFSET 50

The tiebreak must be id ASC and the projection must stay within shard_id, tenant, id, and enqueue_time_ms. Projecting or filtering on any column outside those four, or adding an indexed filter (id, status_kind, metadata), moves the query to a path that reads status or job records, and the ORDER BY becomes a sort over every row scanned:

-- Reads each job's record to project status and task group, then sorts
SELECT id, status_kind, priority, task_group
FROM jobs
WHERE tenant = 'customer-123'
ORDER BY enqueue_time_ms DESC
LIMIT 50

-- Jobs in a specific task group
SELECT id, status_kind, enqueue_time_ms
FROM jobs
WHERE tenant = 'customer-123' AND task_group = 'emails'
ORDER BY enqueue_time_ms DESC

Filtering by Status

-- All currently running jobs
SELECT id, task_group, priority
FROM jobs
WHERE tenant = 'customer-123' AND status_kind = 'Running'

-- Failed jobs
SELECT id, task_group, status_changed_at_ms
FROM jobs
WHERE tenant = 'customer-123' AND status_kind = 'Failed'
ORDER BY status_changed_at_ms DESC

-- Jobs waiting to be picked up
SELECT id, priority, task_group
FROM jobs
WHERE tenant = 'customer-123' AND status_kind = 'Waiting'
ORDER BY priority ASC

-- Future-scheduled jobs (including retry backoffs)
SELECT id, task_group
FROM jobs
WHERE tenant = 'customer-123' AND status_kind = 'Scheduled'

-- New future-scheduled jobs (never attempted)
SELECT id, task_group, next_attempt_starts_after_ms
FROM jobs
WHERE tenant = 'customer-123' AND status_kind = 'Scheduled' AND current_attempt = 1

-- Jobs waiting to retry after a failure (attempted at least once)
SELECT id, task_group, current_attempt, next_attempt_starts_after_ms
FROM jobs
WHERE tenant = 'customer-123' AND status_kind = 'Scheduled' AND current_attempt > 1

Aggregations

-- Count jobs by status
SELECT status_kind, COUNT(*) as count
FROM jobs
WHERE tenant = 'customer-123'
GROUP BY status_kind

-- Count by task group and status
SELECT task_group, status_kind, COUNT(*) as count
FROM jobs
WHERE tenant = 'customer-123'
GROUP BY task_group, status_kind
ORDER BY task_group, status_kind

-- Average priority by task group
SELECT task_group, AVG(CAST(priority AS DOUBLE)) as avg_priority
FROM jobs
WHERE tenant = 'customer-123'
GROUP BY task_group

-- Fast per-tenant status counts using the pre-computed sparse counter table
SELECT status_kind, cnt
FROM tenant_counts
WHERE tenant = 'customer-123'
ORDER BY status_kind

Querying Metadata

Jobs can have arbitrary key-value metadata. Querying metadata uses DataFusion’s map functions.

Exact match — find jobs where a metadata key equals a specific value:

SELECT id, status_kind
FROM jobs
WHERE tenant = 'customer-123'
  AND array_contains(element_at(metadata, 'env'), 'production')

Prefix match — find jobs where a metadata value starts with a prefix:

SELECT id, status_kind
FROM jobs
WHERE tenant = 'customer-123'
  AND starts_with(array_any_value(element_at(metadata, 'region')), 'us-')

You can also use LIKE for prefix matching:

SELECT id, status_kind
FROM jobs
WHERE tenant = 'customer-123'
  AND array_any_value(element_at(metadata, 'region')) LIKE 'us-%'

Combining metadata filters with status:

SELECT id, status_kind, priority
FROM jobs
WHERE tenant = 'customer-123'
  AND array_contains(element_at(metadata, 'env'), 'production')
  AND status_kind = 'Running'

Why array_contains?

The metadata column is a Map type. element_at(metadata, 'key') returns a list (since maps can theoretically have duplicate keys), so you use array_contains() to check for a value in the list, or array_any_value() to unwrap it to a scalar for comparisons and LIKE.

Inspecting Concurrency Queues

-- See all holders and requesters for a queue
SELECT queue_name, entry_type, task_id, job_id, timestamp_ms
FROM queues
WHERE tenant = 'customer-123' AND queue_name = 'user:456'

-- Count holders vs requesters per queue
SELECT queue_name, entry_type, COUNT(*) as count
FROM queues
WHERE tenant = 'customer-123'
GROUP BY queue_name, entry_type

-- Find queues with waiting requesters (indicates jobs are blocked on concurrency)
SELECT queue_name, COUNT(*) as waiting
FROM queues
WHERE tenant = 'customer-123' AND entry_type = 'requester'
GROUP BY queue_name
ORDER BY waiting DESC

Debugging the Task Queue

-- Count pending tasks per task group
SELECT task_group, COUNT(*) as count
FROM tasks
GROUP BY task_group
ORDER BY count DESC

-- Inspect tasks in a specific group within a time window (recommended pattern)
SELECT job_id, start_time_ms, priority, variant_type
FROM tasks
WHERE task_group = 'emails'
  AND start_time_ms >= 1700000000000
  AND start_time_ms <= 1700001000000
ORDER BY start_time_ms

-- Find stuck internal tasks (non-RunAttempt tasks indicate concurrency/rate-limit waits)
SELECT task_group, variant_type, COUNT(*) as count
FROM tasks
GROUP BY task_group, variant_type
ORDER BY count DESC

Looking Up a Specific Job

-- Get full details for a job by ID
SELECT *
FROM jobs
WHERE tenant = 'customer-123' AND id = 'job-abc-123'

Writing Performant Queries

Silo uses predicate pushdown to select the most efficient scan strategy based on your WHERE clause. Understanding how this works helps you write fast queries.

Always filter by tenant

When multi-tenancy is enabled, always include a WHERE tenant = '...' condition. Without it, Silo must scan all tenants on the shard, which is much slower.

-- Fast: scans only one tenant's data
SELECT id FROM jobs WHERE tenant = 'customer-123' AND status_kind = 'Running'

-- Slow: scans all tenants on the shard
SELECT id FROM jobs WHERE status_kind = 'Running'

Scan strategy priority

Silo picks the best scan strategy from your filters in this priority order:

1. Exact ID lookup — WHERE id = '...' — direct key lookup, fastest
2. Metadata exact match — WHERE array_contains(element_at(metadata, 'key'), 'value') — uses the metadata index
3. Metadata prefix — WHERE starts_with(array_any_value(element_at(metadata, 'key')), 'prefix') — uses the metadata index with prefix scan
4. Status filter — WHERE status_kind = '...' — uses the status/time index
5. Full scan — no indexed filter — scans all jobs for the tenant

Only one index-backed filter is used per query. If you combine filters (e.g., status + metadata), the highest-priority one drives the scan and the rest are applied as post-filters.

A full scan is not always a walk over job records. When the query has a tenant filter, no other indexed filter, and a projection limited to shard_id, tenant, id, and enqueue_time_ms, Silo walks the tenant’s enqueue-time index instead, newest first, on any shard whose index backfill has completed. EXPLAIN names the path on the SiloExecutionPlan line (path=EnqueueTimeIndex); the same shape on a shard still being backfilled shows path=StatusIndex or path=FullScanJoin and returns the same rows.

-- Uses ExactId scan (priority 1), even though status filter is also present
SELECT * FROM jobs WHERE tenant = 'customer-123' AND id = 'job-abc' AND status_kind = 'Running'

-- Uses MetadataExact scan (priority 2)
SELECT * FROM jobs WHERE tenant = 'customer-123' AND array_contains(element_at(metadata, 'env'), 'prod')

-- Uses Status scan (priority 4)
SELECT * FROM jobs WHERE tenant = 'customer-123' AND status_kind = 'Failed'

Tasks table: always filter by task_group

The tasks table performs best when filtered by task_group, which uses a key prefix scan matching the task broker’s internal access pattern. Adding a start_time_ms range narrows the scan further with early termination.

-- Fast: scans only one task group's key range
SELECT * FROM tasks WHERE task_group = 'emails' LIMIT 100

-- Faster: scans a narrow time window within the group
SELECT * FROM tasks
WHERE task_group = 'emails'
  AND start_time_ms >= 1700000000000
  AND start_time_ms <= 1700001000000

-- Slow: full scan across all task groups
SELECT * FROM tasks

The floating_refresh_tasks table follows the same rule: filter by task_group, and add tenant to narrow the prefix further.

-- Pending refreshes one tenant's workers in a group will receive
SELECT queue_key, task_id, not_before_ms FROM floating_refresh_tasks
WHERE task_group = 'emails' AND tenant = 'customer-123'

Use LIMIT

Always add LIMIT to exploratory queries. Without it, Silo will scan and return all matching rows.

-- Good: bounded result set
SELECT id, status_kind FROM jobs WHERE tenant = 'customer-123' LIMIT 100

-- Risky with large tenants: returns everything
SELECT id, status_kind FROM jobs WHERE tenant = 'customer-123'

Cross-shard queries

The TypeScript client routes queries to a single shard based on the tenant. If you need to query across all shards (e.g., for cluster-wide aggregations), use siloctl to query each shard, or use the cluster query engine if available.

# Query each shard separately
siloctl query <shard-0> "SELECT COUNT(*) as count FROM jobs"
siloctl query <shard-1> "SELECT COUNT(*) as count FROM jobs"

Supported SQL

Silo’s query engine supports standard SQL via Apache DataFusion:

• SELECT with column projection and *
• WHERE with AND, OR, comparisons (=, !=, >, <, >=, <=), LIKE, IN
• ORDER BY (single or multiple columns, ASC/DESC)
• LIMIT and OFFSET
• GROUP BY with aggregate functions (COUNT, SUM, AVG, MIN, MAX)
• CAST for type conversions
• CASE expressions
• Arithmetic expressions
• Map functions: element_at(), array_contains(), array_any_value(), starts_with()

Read-only

Queries are read-only. You cannot use INSERT, UPDATE, DELETE, or any other data-modifying SQL. To modify jobs, use the client API or siloctl commands.

Next Steps

• Use siloctl for command-line query access and common workflows
• Set up observability for continuous monitoring alongside ad-hoc queries
• Learn about concurrency limits to understand the queues table