wallet: fix migration speed for postgres.
What the developer wrote
Authored by Rusty Russell
wallet: fix migration speed for postgres.
Testing a large db shows Postgres slowing down exponentially as
it inserts the channel_events. Rather than updating the index in the
db every time, do it at the end, for spectacular speedup:
```
lightningd-1 2025-10-08T05:39:44.333Z INFO lightningd: Creating database
lightningd-1 2025-10-08T05:39:47.581Z DEBUG lightningd: Transferring 6166 chain_events
lightningd-1 2025-10-08T05:39:48.455Z DEBUG lightningd: Transferring 1660043 channel_events
lightningd-1 2025-10-08T05:39:54.390Z INFO lightningd: Inserted 103100/1660043 channel_events
lightningd-1 2025-10-08T05:40:04.390Z INFO lightningd: Inserted 283280/1660043 channel_events
lightningd-1 2025-10-08T05:40:14.390Z INFO lightningd: Inserted 464065/1660043 channel_events
lightningd-1 2025-10-08T05:40:24.390Z INFO lightningd: Inserted 629559/1660043 channel_events
lightningd-1 2025-10-08T05:40:34.390Z INFO lightningd: Inserted 800659/1660043 channel_events
lightningd-1 2025-10-08T05:40:44.390Z INFO lightningd: Inserted 975433/1660043 channel_events
lightningd-1 2025-10-08T05:40:54.390Z INFO lightningd: Inserted 1134719/1660043 channel_events
lightningd-1 2025-10-08T05:41:04.390Z INFO lightningd: Inserted 1290549/1660043 channel_events
lightningd-1 2025-10-08T05:41:14.390Z INFO lightningd: Inserted 1443304/1660043 channel_events
lightningd-1 2025-10-08T05:41:24.390Z INFO lightningd: Inserted 1590013/1660043 channel_events
lightningd-1 2025-10-08T05:41:29.148Z INFO lightningd: bookkeeper migration complete: migrated 6166 chainmoves, 1660043 channelmoves, 132481 descriptions
```
Now we complete the entire migration in 1 minute 45 seconds.
Thanks to @michael1101 for reporting this.
Signed-off-by: Rusty Russell <rusty@rustcorp.com.au>
Changelog-Fixed: db: migration from v25.09 on a reasonable size account database could take almost infinite time.
What changed, and why it matters
This commit fixes a database migration performance bug in Core Lightning. When users upgraded from version 25.09, migrating large account databases to Postgres could take an extremely long time because an index was being updated after every single inserted record. The fix batches the index update until the end of the migration, reducing the time from potentially hours to under two minutes. It is a performance and reliability fix, not a security vulnerability.
No security action required. Treat as a normal bug-fix/performance patch. Users on large Postgres deployments should apply it to avoid excessively long upgrades.
Security signals we found
No security-relevant code change identified
Performance-only database migration optimization
No input parsing, authentication, cryptography, or network changes
Evidence from the diff
In wallet/account_migration.c, the migration loop previously called channel_mvt_index_created() for each channel_event, which internally updated the wait index in the database per insertion. On Postgres this caused exponential slowdown. The patch removes the per-row index update, instead assigning a simple incrementing local counter (num_channel_events) as the bind value and calling wait_index_increase() once after the loop to update WAIT_SUBSYSTEM_CHANNELMOVES/WAIT_INDEX_CREATED by the total count. This is a pure optimization with no change to the resulting data or schema.
Changed components
wallet/account_migration.cPostgres database migration path from v25.09Inspect captured patch +11 / −3
diff --git a/wallet/account_migration.c b/wallet/account_migration.c
index 65e72e14..e26d5211 100644
--- a/wallet/account_migration.c
+++ b/wallet/account_migration.c
@@ -366,6 +366,7 @@ void migrate_from_account_db(struct lightningd *ld, struct db *db)
struct db_stmt *stmt;
int version;
struct timemono prev;
+ u64 num_channel_events;
/* Initialize wait indices: we're going to use it to generate ids. */
load_indexes(db, ld->indexes);
@@ -507,11 +508,13 @@ void migrate_from_account_db(struct lightningd *ld, struct db *db)
log_debug(ld->log, "Transferring %zu channel_events",
tal_count(channel_events));
+
+ /* There can be lots of these, so do a single update at the end */
+ num_channel_events = 0;
for (size_t i = 0; i < tal_count(channel_events); i++) {
const struct channel_event *ev = channel_events[i];
struct mvt_account_id *account = tal(ev, struct mvt_account_id);
enum mvt_tag tag;
- u64 id;
/* We removed currency support, because the only way you could
* use it was to inject your own events, and nobody did that
@@ -536,8 +539,7 @@ void migrate_from_account_db(struct lightningd *ld, struct db *db)
" payment_group_id,"
" fees) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?);"));
set_mvt_account_id(account, NULL, ev->acct_name);
- id = channel_mvt_index_created(ld, db, account, ev->credit, ev->debit);
- db_bind_u64(stmt, id);
+ db_bind_u64(stmt, ++num_channel_events);
db_bind_mvt_account_id(stmt, db, account);
db_bind_credit_debit(stmt, ev->credit, ev->debit);
if (!mvt_tag_parse(ev->tag, strlen(ev->tag), &tag))
@@ -569,6 +571,12 @@ void migrate_from_account_db(struct lightningd *ld, struct db *db)
log_info(ld->log, "Inserted %zu/%zu channel_events", i, tal_count(channel_events));
}
+ wait_index_increase(ld, db,
+ WAIT_SUBSYSTEM_CHANNELMOVES,
+ WAIT_INDEX_CREATED,
+ num_channel_events,
+ NULL);
+
log_info(ld->log, "bookkeeper migration complete: migrated %zu chainmoves, %zu channelmoves, %zu descriptions",
tal_count(chain_events),
tal_count(channel_events),
Why this scored 20/100
Community notes
Notes can correct, qualify, or add evidence to the AI analysis. Every note shown here has been validated by a human moderator.
The AI analysis stands alone for now. Submit a note if you can add evidence or important context.