Database performance
Query-driven indexing, PostgreSQL regression tests, and operational diagnosis.
Index audit
The September 2026 audit covers all 19 application tables. Primary and unique indexes count toward lookup coverage. Small configuration tables do not need an index for every optional sort or JSON field.
| Table | Access paths and decision |
|---|---|
organizations | ID and unique slug cover tenant reads and scheduler enumeration. No extra index for the small global name list. |
agent_profiles | Organization/key, kind uniqueness and department/status cover provisioning and the bounded five-profile registry. |
integrations | Retain organization/key/provider/source/status/kind; add organization/name/ID for the connection list. |
organization_metric_definitions | Composite registry primary key and organization/active index cover point updates and reviewed definitions. |
organization_metric_current | Composite series primary key and organization/observed index cover current projections. |
organization_entity_current | Composite entity primary key and organization/observed index cover current metadata. |
organization_observation_segments | Retain object uniqueness and organization/dataset observed/window range indexes. Historical bytes remain in Parquet. |
platform_fx_rates_current | Base/quote primary key serves point lookups and the bounded shared rate projection. |
chats | Extend organization/activity and organization/status/activity with ID for ordered pagination. Keep title trigram search. |
messages | Extend chat/time with the source-before-reply expression and ID. Keep active assistant uniqueness, reply lookup and text search. |
tasks | Add organization/update-time/ID for the work list and organization/status/ID for polling. Retain identifier, dedupe, source, parent and schedule indexes. Put owner and root first in their existing indexes to support foreign-key checks. |
task_dependencies | Add organization/task/prerequisite for tenant reads and cascading tenant removal; retain forward/reverse status indexes and edge uniqueness. |
run_requests | Add organization/status/ID for polling and organization/update-time/ID for history. Put profile first in the existing ownership index; retain task, dedupe, active request and due indexes. |
runs | Add organization/start-time/ID for history and organization/status/ID for recovery. Retain request/attempt uniqueness, parent and active lease expiry indexes. |
run_logs | Existing run/sequence primary key and organization/run/sequence index cover append, history and cascade. |
human_inbox_items | Extend status/time with ID; add organization/time/ID and organization/kind/time/ID for Inbox and Notifications, plus author foreign-key lookup. Retain task, run and idempotency indexes. |
durability | Retain organization/namespace/key, kind and expiry; reorder source-run index to source-run/organization for run deletion and scoped reads. |
schedules | Add organization/next-run/ID and creator foreign-key lookup; retain key uniqueness, enabled due index and profile/status/origin access. |
actions | Add organization/status/ID and profile foreign-key lookup; retain pending availability, task, run and idempotency indexes. |
Every declared foreign key now has a valid non-partial index whose leading columns contain the foreign key. This includes tables used by cascade, restrict and set-null operations. JSON provenance references are not foreign keys; deletion still requires application-level reference checks.
Scheduler time predicates now run in SQL for tasks, schedules and run requests. Exhausted retries are excluded before hydration. Failed-run recovery loads only the latest attempts of still-claimed requests; historical failures remain available in public history. Mutation paths still revalidate current state, task versions and leases transactionally.
Reproduce validation
Use an isolated disposable pgvector PostgreSQL database, then provide its URL explicitly:
TEST_DATABASE_URL=postgresql://postgres:oblive-test@127.0.0.1:5432/oblive_test bun test apps/backend/tests/database
TEST_DATABASE_URL=postgresql://postgres:oblive-test@127.0.0.1:5432/oblive_test bun test apps/backend/tests/repositories/work-list-pagination.postgres.test.tsThese suites migrate the database, create tenant-scoped fixtures, and remove those fixtures. They
never fall back to the application’s DATABASE_URL. CI runs both suites against PostgreSQL 17 with
pgvector in addition to ordinary unit tests.
The plan suite creates 30,000 tasks, chats, transcript messages and inbox entries per fixture. It
runs ANALYZE and EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) with normal planner settings. On the local
isolated database, 51-row pages used their ordered indexes without a full-history sort:
| Query | Execution time | Cached blocks |
|---|---|---|
| Recent tasks | 0.061 ms | 7 |
| Open chats | 0.081 ms | 54 |
| Transcript | 0.025 ms | 5 |
| Notifications | 0.067 ms | 56 |
These are warm synthetic database measurements, not end-to-end page latency or a production speedup claim. Tests assert bounded block reads and absence of a full sort, not machine-dependent timing. Pagination tests cover every task/inbox ordering, equal values, nulls and PostgreSQL microseconds.
Diagnose a slow local page
Confirm the latest migrations are applied before measuring. Correlate the page request with backend
transaction durations and the actual SQL. Inspect EXPLAIN (ANALYZE, BUFFERS) on a representative
read, tenant cardinality, pool waits and storage calls. A sequential scan on a tiny table is often
cheaper than an index scan. Do not disable sequential scans to make a plan look faster.
Optional sort combinations may still sort their filtered rows. Add indexes when measured traffic justifies the additional write and storage cost; do not add blanket JSON indexes. Bounded Context object-store reads and optional-panel isolation are separate fixes for page latency and recovery.
The index migration uses the repository’s transactional migration runner. Rebuilding indexes takes write locks: schedule its application on large live databases in a maintenance window and measure migration duration against a representative copy first. This work does not apply changes to a deployed database or establish the cause of an untraced production page failure.