Skip to content

perf(migration): qualify the ORDER BY so the notification copies use their index #1124

Closed

Description

@chappie-daemon

nostream-storage's scan_batch (used by nostream-cli migrate pg-to-lmdb) builds

SELECT {columns} FROM {table} [WHERE key > $1] ORDER BY key LIMIT $2

For notification_outbox and notification_delivery_log the column list is OUTBOX_COLUMNS / DELIVERY_LOG_COLUMNS, which begin id::text AS id. In PostgreSQL an ORDER BY name resolves to the output column first, so ORDER BY id sorts the text expression, not the uuid column. The indexes cover the uuid column, so each batch reads the remaining rows and sorts their text ids before applying LIMIT.

Correctness is unaffected: canonical uuid text has fixed dash positions, so lexicographic text order on lowercase hex agrees with PostgreSQL's byte-wise uuid order, and the keyset predicate (id > $1::uuid) stays consistent with the sort. The cost is the repeated sort, paid while the relay is stopped.

Acceptance

ORDER BY and the keyset predicate name the table (ORDER BY notification_outbox.id, notification_outbox.id > $1::uuid), so the plan can walk the uuid index and stop after LIMIT rows instead of sorting each batch.

Verify with EXPLAIN on both notification tables before and after: before shows a Sort above the scan; after, an index scan with no sort node. A test can assert the generated SQL names the table in both clauses, but the plan is the evidence.

Where it came from

Static review round 18 on factory/029-pg-to-lmdb (finding P2, non-blocking). The branch is frozen for review, so it was not fixed there.

Activity

  1. tirthraj01 commented on Nov 14, 2026

    @tirthraj01
    Contributor

    Hi @chappie-daemon @cameri,

    I've gone through the issue and the notes on field-by-field parity.

    I can take this on:

    1. Hoist the base users column list into postgres/users.rs as a single shared constant.
    2. Update postgres/migration.rs to reuse this base list and append , tos_accepted_at, keeping the exact column ordering intact so field-by-field parity is preserved without drift.
    3. Ensure the parity target (cargo test -p nostream-storage --features postgres --test parity -- --ignored) and CI checks pass cleanly.

    Could you please assign this issue to me?

  2. cameri commented on Nov 14, 2026

    @cameri
    Owner

    @tirthraj01 Assigned to you.

  3. tirthraj01 commented on Nov 15, 2026

    @tirthraj01
    Contributor

    Hi @cameri, @chappie-daemon,

    I've put together the fix for #1124 on a fresh branch off rewrite/main.

    In migration.rs, I updated Cursor::Uuid to qualify the table name in both the WHERE condition ({table_name}.id > $1::uuid) and ORDER BY (ORDER BY {table_name}.id). This ensures Postgres uses the underlying UUID index directly instead of sorting on the id::text alias. I also updated the unit test and added the changeset.

    Because of the repo's interaction limits on external PRs, here is the branch on my fork:
    rewrite/main...tirthraj01:nostream:perf/1124-qualify-notification-order-by

    Let me know if this looks good or if you'd like me to tweak anything!

  4. chappie-daemon commented on Nov 15, 2026

    @chappie-daemon
    CollaboratorAuthor

    Thanks @tirthraj01 — your diagnosis was right, and the fix is in #1147, open against rewrite/main and closing this issue.

    It carries your commit as-is: you are the author of the commit, and the only change is one rustfmt wrap of the format! call that cargo fmt --check required. cargo test -p nostream-storage --lib migration passes (4 tests), including your new assertions on the qualified predicate and ORDER BY.

  5. chappie-daemon commented on Nov 15, 2026

    @chappie-daemon
    CollaboratorAuthor

    Thank you for this, @tirthraj01 — the diagnosis was precise and the patch was ready to land exactly as written. It is in #1147, green and approved, and you remain the author of the commit.

    Closing this issue now; if the PR ends up rejected or reworked, it will be reopened rather than lost. Contributions like this — reading the planner behaviour behind a slow query instead of guessing — are the ones that are easy to accept.

  6. tirthraj01 commented on Nov 16, 2026

    @tirthraj01
    Contributor

    thank you so much! really appreciate your feedback and for keeping me credited as the commit author! 🙌

    glad the diagnosis and fix worked out. looking forward to contributing more!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

No labels
No labels

Projects

No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions