Enabling efficient paginated listing of df.instances for external clients

[erdemtuna]

Hi everyone,

We've started to build Durable Pipelines in in the VS Code PostgreSQL extension. We hit a few rough edges in df.instances that we'd love your help smoothing. They all share one root cause: external clients (like us) can't paginate df.instances efficiently today.

The first durable surface our users will see in the MVP is a paginated Instances list sourced straight from df.instances. We identified three independent gaps surfaced. They land cleanly in any order, so we've ranked them by impact for convenience, but whichever you pick up first unblocks us.

Where we got stuck

There's no index on df.instances.created_at. The canonical list API df.list_instances has no pagination or total count support, and it omits timestamps. The random 8 character id can't serve as a proxy for chronological ordering either. So any client that wants a paginated, timestamped Instances list in chronological order has two options: (a) sort the full table on every page request, or (b) fetch a bounded window in one batch and paginate on the client as a workaround.

Root cause: three independent gaps

Gap 1: df.instances has no index for time ordering

Per src/lib.rs:190, 193, the only df.* indexes today are:

CREATE INDEX IF NOT EXISTS idx_instances_status ON df.instances(status);
CREATE INDEX IF NOT EXISTS idx_nodes_instance ON df.nodes(instance_id);

Notably absent: any index supporting ORDER BY created_at or completed_at. Every chronological query against df.instances requires a full table sort.

We considered using id as a proxy for chronological order to lean on the PK index. But short_id() (src/types.rs:91-101) keeps only the trailing 8 hex characters of a fresh UUIDv4. Fully random, no time component. So ORDER BY id is arbitrary, not chronological. No proxy is possible.

Gap 2: df.list_instances lacks pagination and total count

Per src/monitoring.rs:17-107, df.list_instances(status_filter, limit_count):

• Runs SELECT id, label FROM df.instances ORDER BY created_at DESC LIMIT $N (gated by Gap 1).
• Caps at 10 000 rows.
• Has no OFFSET and no total count return value.
• Enriches each row with a client.get_instance_info(id) call per row.

There's no canonical API a downstream client can call to render "Page X of Y".

Gap 3: df.list_instances omits timestamps

The function returns (instance_id, label, function_name, status, execution_count, output). It does not return created_at, completed_at, or duration. Any UI that wants to show "when did this run?" has to run a second query against df.instances to enrich. That's PK keyed, so it's cheap, but it's still an extra round trip on every list call. And the data is right there in the function.

Our current workaround

In MVP, we plan to:

1. Call df.list_instances(NULL, 200) to fetch a bounded recent window (still gated by Gap 1, but only one sort per list call rather than one per page click).
2. Enrich via SELECT id, created_at, completed_at FROM df.instances WHERE id = ANY($ids). PK keyed, fast (workaround for Gap 3).
3. Paginate on the client through the 200 row window (Prev / current page / Next, page size 20).
4. Surface a banner when the window cap is hit: "Showing the 200 most recent instances. Extended pagination ships in a later release."

Functional, but it caps user visible history at 200 rows and still incurs a full table sort on every list operation. We want to swap in real server side pagination as soon as the upstream supports it.

Proposed changes

1. Add a (created_at DESC, id) index on df.instances

CREATE INDEX IF NOT EXISTS idx_instances_created_at_desc_id
    ON df.instances (created_at DESC, id);

Smallest change with the largest payoff. Unblocks efficient ORDER BY created_at DESC for every downstream client, including the existing df.list_instances.

2. Add a paginated server side list function

Just a starting point to react to, we're flexible on the exact shape (keyset vs offset, total_count snapshot vs has_more, etc.):

df.list_instances_paginated(
    status_filter text DEFAULT NULL,
    limit_count   int  DEFAULT 100,
    after_cursor  text DEFAULT NULL
) RETURNS TABLE (
    instance_id     text,
    label           text,
    function_name   text,
    status          text,
    execution_count bigint,
    output          text,
    created_at      timestamptz,
    completed_at    timestamptz,
    total_count     bigint,
    next_cursor     text
);

Happy to align on whatever idiom fits the extension best.

3. Return created_at and completed_at from df.list_instances

The smallest of the three. Even if (2) is out of scope, this alone would eliminate our enrichment query.

---

If any of these are welcome but lower priority on your side, give us a shout. We're happy to send PRs.

[pinodeca]

Posted by GitHub Copilot using Claude Opus 4.7.

Evaluation of this issue's proposals

What this issue gets right

• Gap 1 (no index) is correct: there is no index supporting ORDER BY created_at on df.instances (src/lib.rs L174–L194).
• Gap 2 (no pagination, silent 10K cap) is correct.
• Gap 3 (no timestamps) is correct and the cheapest win.

Where the proposal is incomplete or sub-optimal

It misses the real performance bug. In src/monitoring.rs L88–L101:

for (id, label) in &user_instances {
    if let Ok(info) = client.get_instance_info(id).await { ... }
}

For every row returned, list_instances makes a separate duroxide round trip to fetch function_name, execution_count, output. That's N+1 — at limit=10 000 it is 10 000 sequential SQL queries via the async client, plus tokio runtime spin-up per call. Adding a created_at index and a _paginated variant turns each "page click" from one bad sort into 200 round trips. The index is necessary but the listing function will still be slow.

By contrast, df.metrics() (which this issue uses as the ground-truth count) is one aggregate call and returns instantly — that's why it can see all 17K rows.

total_count is a footgun under RLS. COUNT(*) over the RLS-filtered df.instances is O(rows). Exposing it as part of every list call invites UIs that render "Page X of Y" and re-issue a full count on every page change. At ~17K rows it is fine; at 10M rows it is a self-DoS.

Two separate functions duplicate API surface. Shipping df.list_instances and df.list_instances_paginated permanently forks the contract (status + label filtering, output columns, RLS interaction must be kept in sync). The existing function already lacks half of what we want — extending it with optional cursor params is backward compatible (new params get defaulted) and avoids a second function to maintain, document, test, and gate via B1 upgrade tests.

Cursor shape is left "tbd." "Keyset vs offset, total_count vs has_more" is the entire engineering decision; the rest is plumbing.

The status index isn't used for ordered scans. idx_instances_status is a single-column index. Filtered-and-ordered queries (WHERE status = $1 ORDER BY created_at DESC) need a composite to avoid a sort step, so adding only (created_at DESC, id) leaves the common "list running" path on a sort.

Silent cap behavior isn't really addressed. The issue talks about pagination but doesn't say what happens when a caller passes limit_count := 100000 on day one. Today: silent truncation. The fix needs an explicit policy.

---

Counter-proposal

Make these changes in priority order; each is independently shippable.

P0 — Stop lying to the caller

In df.list_instances, replace let limit_count = limit_count.min(10000); with pgrx::error!() when limit_count > MAX_LIMIT. A loud error beats silent truncation. Make MAX_LIMIT a GUC (pg_durable.list_instances_max_limit, default 1000) so operators can tune it.

This is a ~5-line patch and resolves the correctness aspect of the original report immediately, independent of the larger pagination work.

P1 — Fix the N+1 (the actual scaling bug)

Two options, in increasing order of work:

a. Bulk method in duroxide-pg-opt: add Client::get_instance_info_batch(ids: &[&str]) that issues one WHERE instance_id = ANY($1) against duroxide's instances/executions tables. Replaces the per-row await loop with two queries total (one SPI, one async). This is the smallest change that makes listing scale; ship it before any pagination work or the paginated variant inherits the same N+1.

b. Mirror the hot columns into df.instances (function_name, latest_execution_id, output) and have the BGW update them on terminal status transitions, which it already touches. Then list_instances is a single SPI query, no async runtime, no duroxide round trip, no tokio::runtime::Builder per call. Larger refactor, but it makes list_instances indistinguishable in cost from any other indexed SQL view, and removes the runtime-creation overhead currently amortized across every call.

Recommend (a) now, (b) on the roadmap.

P2 — Indexes that match the access patterns

CREATE INDEX IF NOT EXISTS idx_instances_created_at_id
    ON df.instances (created_at DESC, id);

-- Replace the single-column status index with a composite
DROP INDEX IF EXISTS df.idx_instances_status;
CREATE INDEX IF NOT EXISTS idx_instances_status_created_at_id
    ON df.instances (status, created_at DESC, id);

The composite still satisfies the lookups the old index served (planner does index scan with leading-column equality). Skip the partial-on-completed_at index until there's a workload that needs it.

Add to a new sql/pg_durable--<prev>--<next>.sql upgrade script and make sure the .so works against the pre-upgrade schema (B1 contract per docs/upgrade-testing.md) — the SQL the Rust code emits doesn't reference the index by name, so the planner just picks whatever exists.

P3 — Extend df.list_instances in place (no second function)

df.list_instances(
    status_filter    text        DEFAULT NULL,
    limit_count      int         DEFAULT 100,
    label_filter     text        DEFAULT NULL,   -- closes #87
    after_created_at timestamptz DEFAULT NULL,   -- keyset cursor
    after_id         text        DEFAULT NULL
) RETURNS TABLE (
    instance_id     text,
    label           text,
    function_name   text,
    status          text,
    execution_count bigint,
    output          text,
    created_at      timestamptz,
    completed_at    timestamptz,
    has_more        boolean
)

Design notes:

• Keyset, not offset. Offset pagination re-scans skipped rows and is broken by concurrent inserts; keyset on (created_at DESC, id) is stable and uses the new index directly. Predicate: WHERE (created_at, id) < ($after_created_at, $after_id).
• has_more, not total_count. Implementation: fetch limit + 1, set has_more = true if you got the extra, drop it. Cost: zero beyond the page itself. Clients that genuinely need a total can call df.metrics() (cheap aggregate) or a new df.count_instances(status_filter, label_filter) if there's demand — keep it as an opt-in.
• No opaque base64 cursor. Two named columns (after_created_at, after_id) are debuggable from psql, don't need stable encoding, and survive schema evolution. Clients are well-typed.
• Backward compatible. All new params default; existing callers keep working. No second function to maintain or upgrade-test.
• Closes Add a filter_label parameter to list_instances function #87 in the same change — label_filter is one AND label = $X clause.

P4 — Document what df.metrics() does and doesn't filter

The original repro uses df.metrics() as ground truth, but it currently doesn't filter by RLS (it's a duroxide aggregate over all tenants). That's a separate latent issue worth verifying before users build dashboards on it, but it's out of scope of this issue.

---

Why this is better than the proposal as filed

Concern	This issue as filed	Counter-proposal
Silent 10K truncation	Implicitly fixed by pagination on the new function; old function unchanged	Fixed explicitly in old function (P0), even before pagination ships
N+1 duroxide round trips	Not addressed	P1 fixes the actual scaling cliff
Status-filtered pagination	Single-column status index can't satisfy ordered scans efficiently	Composite (status, created_at DESC, id)
API surface	Two functions, forked contract	One function, extended with defaulted params
Cost of "Page 47 of 873"	total_count invites it	has_more makes it ergonomically harder; explicit opt-in count if needed
Label filter (#87)	Separate work	Folded in for ~one line
Cursor format	"tbd"	Typed (timestamptz, text) — debuggable, no encoding spec