UI repaints continuously: `/queues` runs 16 full table scans, `/tasks` polls in a loop, and the skeleton row count doesn't match the data

**Repo:** apalis-dev/apalis-board · **Version:** `1.0.0-rc.8` (`apalis-board-api` + the shipped `dist/` bundle), Postgres backend via `apalis-postgres 1.0.0-rc.8`

The board flickers continuously in normal use — worse while jobs are running, which is when you have it open. Three separate causes, measured.

---

### 1. `list_queues.sql` runs 16 full aggregate scans per request

`apalis-postgres/queries/backend/list_queues.sql` is ~17 `SELECT … GROUP BY job_type` branches `UNION ALL`'d together, each an independent aggregate over the whole of `apalis.jobs`, plus two correlated subqueries per job type. `EXPLAIN ANALYZE` on 5,395 rows:

```
Seq Scan on jobs ×16
Execution Time: 24.487 ms
```

Linear in table size — at 50k rows that's ~250 ms, and the UI polls this endpoint continuously.

I tried to index it and **it gets worse**: a covering index on `(job_type, status, run_at, done_at)` took it to 29.9 ms, converting only 6 of 16 branches to index-only scans. Thirteen of the aggregates have no selective predicate, so they genuinely must read every row. There is no index-shaped fix.

A single pass computing all the counters with `FILTER (WHERE …)` or `SUM(CASE …)` in one `GROUP BY job_type` would return the same JSON from one scan instead of sixteen.

### 2. `list_jobs.sql` has no index to use

```sql
SELECT * FROM apalis.jobs
WHERE status = $1 AND job_type = $2
ORDER BY done_at DESC, run_at DESC
LIMIT $3 OFFSET $4
```

The schema creates only single-column indexes (`status`, `job_type`, `id`, `lock_by`), so this always filters then sorts the whole matching set for 15 rows:

```
before Seq Scan (5,222 rows) -> top-N heapsort 2.624 ms
after Index Scan, no sort node 0.116 ms
```

with

```sql
CREATE INDEX CONCURRENTLY IF NOT EXISTS jobs_status_type_recent_idx
ON apalis.jobs (status, job_type, done_at DESC, run_at DESC);
```

`DESC` matters — `done_at` is NULL for unfinished tasks, so `ORDER BY done_at DESC` is NULLS FIRST and only a matching index ordering avoids the sort. `list_all_jobs.sql` needs the same without `job_type`. Happy to open a PR against `apalis-postgres` for these two.

### 3. The UI polls in a loop and its skeleton doesn't match the data

From the browser's network panel: **67 requests in 39.5 s**, cycling `tasks?page=1`, `page=2`, `page=3` continuously.

And on the Queues page the loading skeleton renders **7 placeholder rows** while the real result is 3 — so every refetch takes the table 3 rows → 10 rows → 3 rows. That's the visible flash, and it's a layout jump rather than a subtle transition because the fallback is `text-center` while the content is a table.

This looks like `` around a polled `Resource` — which re-shows its fallback on *every* refetch. `` keeps the previous content visible while refetching, which is what a live dashboard wants. Sizing the skeleton to the last known row count (or reserving no rows at all) would remove the remaining jump.

---

### Also, minor

`ServeUI` sets `Cache-Control: immutable` correctly, but nothing in the crate compresses. The embedded bundle is **2.2 MB of wasm served raw**; brotli takes it to 664 KB (3.4×). Since `index.html` ships an empty `` and the whole UI renders from that bundle, every cold load is a blank page until it arrives. A `CompressionLayer` in the shipped router would help — with `text/event-stream` excluded, since compressing `/api/v1/events` buffers it and the System Status indicator reads that stream.

Of the three, **#3 is the one users see.** #1 and #2 are what make it slow enough to notice.

Start with apalis-postgres/queries/backend/list_queues.sql, list_jobs.sql, and list_all_jobs.sql, then trace the /queues and /tasks polling resources and the ServeUI router. Check the reported query plans and browser request loop first. Done means fewer full scans and refetches, stable skeleton layout, and compressed UI responses without buffering /api/v1/events.

索引モデルが issue の本文から書いたものです。