Conservative improvements to syncing by gilescope · Pull Request #1365 · midnightntwrk/midnight-node

gilescope · 2026-04-20T07:27:47Z

Overview

Back-ports four conservative performance fixes to the cNIGHT db-sync observation path onto release/node-0.22.5. Bundles upstream work from #934 plus follow-ups into the release branch.

The four commits:

Add cache for multi asset id to avoid excessive joins (Add cache for multi asset id to avoid excessive joins #934) — resolves (policy, asset_name) → multi_asset.ident once via MultiAssetCache, eliminating the JOIN multi_asset from every subsequent observation query. Also includes a change file (changes/changed/cache-multi-asset-id.md) and tidies candidate-data-source decoding.
Increase performance of cNight db-sync queries — pre-queries coarse tx / tx_out / ma_tx_out id bounds for the block-range window, then constrains the four observation queries (registration / deregistration / asset-create / asset-spend) by primary-key range. Postgres can then prune rows before the expensive joins. Old compile-time sqlx cache entries are replaced.
Add tx_in indexes as well — adds the tx.id bounding to the tx_in-keyed queries (spend / deregistration) so they benefit from the same pruning.

Net effect: significantly lower postgres CPU / query latency during cNIGHT observation, no behavioural change.

🗹 TODO before merging

Ready

📌 Submission Checklist

Changes are backward-compatible (or flagged if breaking)
Pull request description explains why the change is needed
Self-reviewed the diff
I have included a change file, or skipped for this reason: changes/changed/cache-multi-asset-id.md covers Add cache for multi asset id to avoid excessive joins #934; the other three commits are perf fixes to the same path with no user-visible surface change.
If the changes introduce a new feature, I have bumped the node minor version
Update documentation (if relevant)
Updated AGENTS.md if build commands, architecture, or workflows changed
No new todos introduced

🧪 Testing Evidence

Observed reduced db-sync query times on a live cNIGHT observation workload.

Additional tests are provided (if possible)

🔱 Fork Strategy

Node Runtime Update
Node Client Update
Other:
N/A

Links

Upstream: #934

@gilescope

* Add cache for multi asset id to avoid excessive joins * change file * fix npm audit * Apply suggestion from @gilescope --------- Co-authored-by: Squirrel <giles.cope@shielded.io> Signed-off-by: Giles Cope <gilescope@gmail.com>

…y_hash Needed because cherry-pick landed in a branch whose .sqlx prepared cache does not include this query (cached key changed after trailing-whitespace normalisation). Using the runtime form avoids the compile-time DB check entirely and matches the pattern used elsewhere in #934. Signed-off-by: Giles Cope <gilescope@gmail.com>

First query for coarse bounds of tx, tx_out and ma_tx_out tables and use them in the 'business' queries to reduce size of tables before joins are made. Signed-off-by: Giles Cope <gilescope@gmail.com>

Signed-off-by: Giles Cope <gilescope@gmail.com>

Convert the runtime query_as form back to the compile-time macro form for all queries in cnight_observation.rs. This restores static query verification against the db-sync schema via the .sqlx prepared cache. Signed-off-by: Oscar Bailey <79094698+ozgb@users.noreply.github.com>

Resolves high/moderate severity vulnerabilities in axios, brace-expansion, follow-redirects, lodash, picomatch, yaml, vite, and effect. Signed-off-by: Giles Cope <gilescope@gmail.com>

Signed-off-by: Giles Cope <gilescope@gmail.com>

cosmir17

LGTM, thanks for pushing through this one. The perf gains look solid.

…y args Replaces the seven repeated arguments (start, end, limit, offset, low_bound, high_bound + per-call locals) on the four cNight observation query helpers with a single `PagedQuery<'a>` struct. Squashed from PR #1365 fixup commits b9497e8 + b146dc2 + b1ffce6 (originally landed as "fix: cargo clippy" / "Apply suggestion"). Also drops the now-unused `QueryBounds` import in the data_source module.

…y args Replaces the seven repeated arguments (start, end, limit, offset, low_bound, high_bound + per-call locals) on the four cNight observation query helpers with a single `PagedQuery<'a>` struct. Squashed from PR #1365 fixup commits b9497e8 + b146dc2 + b1ffce6 (originally landed as "fix: cargo clippy" / "Apply suggestion"). Also drops the now-unused `QueryBounds` import in the data_source module. Signed-off-by: Giles Cope <gilescope@gmail.com>

* Increase performance of cNight db-sync queries. First query for coarse bounds of tx, tx_out and ma_tx_out tables and use them in the 'business' queries to reduce size of tables before joins are made. Signed-off-by: Giles Cope <gilescope@gmail.com> * Add tx_in indexes as well Signed-off-by: Giles Cope <gilescope@gmail.com> * refactor: use sqlx::query_as! macro for cNight observation queries Convert the runtime query_as form back to the compile-time macro form for all queries in cnight_observation.rs. This restores static query verification against the db-sync schema via the .sqlx prepared cache. Signed-off-by: Oscar Bailey <79094698+ozgb@users.noreply.github.com> Signed-off-by: Giles Cope <gilescope@gmail.com> * chore: replace sqlx::query_as! macro with runtime form in get_block_by_hash Needed because cherry-pick landed in a branch whose .sqlx prepared cache does not include this query (cached key changed after trailing-whitespace normalisation). Using the runtime form avoids the compile-time DB check entirely and matches the pattern used elsewhere in #934. Signed-off-by: Giles Cope <gilescope@gmail.com> * feat: add changefile Signed-off-by: Giles Cope <gilescope@gmail.com> * fix: should be debug not warn Signed-off-by: Giles Cope <gilescope@gmail.com> * refactor: introduce PagedQuery to consolidate cNight observation query args Replaces the seven repeated arguments (start, end, limit, offset, low_bound, high_bound + per-call locals) on the four cNight observation query helpers with a single `PagedQuery<'a>` struct. Squashed from PR #1365 fixup commits b9497e8 + b146dc2 + b1ffce6 (originally landed as "fix: cargo clippy" / "Apply suggestion"). Also drops the now-unused `QueryBounds` import in the data_source module. Signed-off-by: Giles Cope <gilescope@gmail.com> * fix: clippy needless borrow in get_block_by_hash bind Signed-off-by: Giles Cope <gilescope@gmail.com> * fix: fixed compilation for try-runtime feature (#1427) Signed-off-by: Tomasz Bartos <tomasz.bartos@shielded.io> Signed-off-by: Giles Cope <gilescope@gmail.com> --------- Signed-off-by: Giles Cope <gilescope@gmail.com> Signed-off-by: Oscar Bailey <79094698+ozgb@users.noreply.github.com> Signed-off-by: Tomasz Bartos <tomasz.bartos@shielded.io> Co-authored-by: Lech Głowiak <lech.glowiak@iohk.io> Co-authored-by: Lech Głowiak <lech.glowiak@shielded.io> Co-authored-by: Oscar Bailey <79094698+ozgb@users.noreply.github.com> Co-authored-by: Klapeyron <11329616+Klapeyron@users.noreply.github.com>

justinfrevert and others added 4 commits April 20, 2026 08:19

Increase performance of cNight db-sync queries.

e2f5d80

First query for coarse bounds of tx, tx_out and ma_tx_out tables and use them in the 'business' queries to reduce size of tables before joins are made. Signed-off-by: Giles Cope <gilescope@gmail.com>

Add tx_in indexes as well

6f852d9

Signed-off-by: Giles Cope <gilescope@gmail.com>

gilescope marked this pull request as ready for review April 20, 2026 07:30

gilescope requested a review from a team as a code owner April 20, 2026 07:30

feat: add changefile

36a80b4

Signed-off-by: Giles Cope <gilescope@gmail.com>

gilescope mentioned this pull request Apr 20, 2026

baked sync #1366

Closed

15 tasks

gilescope and others added 2 commits April 20, 2026 08:41

fix: cargo fmt

42f8875

Signed-off-by: Giles Cope <gilescope@gmail.com>

ozgb force-pushed the giles-934-on-0.22.5 branch 2 times, most recently from ed988f1 to d9d1c78 Compare April 20, 2026 09:31

fix: npm audit fix for local-environment and toolkit-js

c2b1c97

Resolves high/moderate severity vulnerabilities in axios, brace-expansion, follow-redirects, lodash, picomatch, yaml, vite, and effect. Signed-off-by: Giles Cope <gilescope@gmail.com>

gilescope added the skip-changes-check-jira label Apr 20, 2026

gilescope added 2 commits April 20, 2026 11:34

fix: cargo clippy

b9497e8

Signed-off-by: Giles Cope <gilescope@gmail.com>

fix: cargo clippy

b146dc2

Signed-off-by: Giles Cope <gilescope@gmail.com>

ozgb previously approved these changes Apr 20, 2026

View reviewed changes

gilescope enabled auto-merge April 20, 2026 10:47

fix: lock subxt

eb3665b

Signed-off-by: Giles Cope <gilescope@gmail.com>

gilescope dismissed ozgb’s stale review via eb3665b April 20, 2026 12:34

fix: update to same as main

e29df26

Signed-off-by: Giles Cope <gilescope@gmail.com>

gilescope requested a review from ozgb April 20, 2026 17:40

cosmir17 previously approved these changes Apr 20, 2026

View reviewed changes

Comment thread primitives/mainchain-follower/src/data_source/cnight_observation.rs Outdated

Comment thread primitives/mainchain-follower/src/data_source/cnight_observation.rs

gilescope commented Apr 20, 2026

View reviewed changes

Comment thread primitives/mainchain-follower/src/data_source/cnight_observation.rs Outdated

Apply suggestion from @gilescope

b1ffce6

gilescope dismissed cosmir17’s stale review via b1ffce6 April 20, 2026 22:13

cosmir17 mentioned this pull request Apr 20, 2026

chore(ci): add DCO config to allow individual remediation commits #1370

Merged

cosmir17 approved these changes Apr 20, 2026

View reviewed changes

justinfrevert approved these changes Apr 21, 2026

View reviewed changes

gilescope merged commit ac89a6b into release/node-0.22.5 Apr 21, 2026
30 of 31 checks passed

gilescope deleted the giles-934-on-0.22.5 branch April 21, 2026 06:53

gilescope mentioned this pull request Apr 27, 2026

upstream of 0.22.5 changes #1429

Merged

15 tasks

gilescope mentioned this pull request Apr 27, 2026

upstream of 0.22.5 changes (#1429) #1430

Merged

15 tasks

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Conservative improvements to syncing#1365

Conservative improvements to syncing#1365
gilescope merged 13 commits into
release/node-0.22.5from
giles-934-on-0.22.5

gilescope commented Apr 20, 2026 •

edited by ozgb

Loading

Uh oh!

cosmir17 left a comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

5 participants

Conversation

gilescope commented Apr 20, 2026 • edited by ozgb Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Overview

🗹 TODO before merging

📌 Submission Checklist

🧪 Testing Evidence

🔱 Fork Strategy

Links

Uh oh!

cosmir17 left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

5 participants

gilescope commented Apr 20, 2026 •

edited by ozgb

Loading