sql: only create sql indices after initial load of data.
What the developer wrote
Authored by Rusty Russell
sql: only create sql indices after initial load of data.
This makes a big difference for large tables. Consider 1.6M channelmoves,
which took 82 seconds to populate, now takes 17 seconds:
Before:
plugin-sql: Time to call listchannelmoves: 10.380341485 seconds
plugin-sql: Time to refresh channelmoves: 82.311287310 seconds
After:
plugin-sql: Time to call listchannelmoves: 9.962815480 seconds
plugin-sql: Time to refresh channelmoves: 15.711549299 seconds
plugin-sql: Time to refresh + create indices for channelmoves: 17.100151235 seconds
tests/test_coinmoves.py::test_generate_coinmoves (50,000):
Time (from start to end of l2 node): 27 seconds
Worst latency: 16.0 seconds
Changelog-Changed: Plugins: `sql` initial load for tables is much faster (e.g 82 to 17 seconds for very large channelmoves table).
Signed-off-by: Rusty Russell <rusty@rustcorp.com.au>
What changed, and why it matters
This change is a performance optimization for the Core Lightning SQL plugin. It delays creating database indexes until after a table's data has been loaded for the first time, rather than creating indexes before loading. This makes initial data imports much faster (for example, one large table now loads in 17 seconds instead of 82 seconds). There is no security issue here.
No security action required. This is a routine performance improvement. Reviewers may optionally verify that indices_created is properly initialized and that init_indices handles sqlite3_exec errors correctly, which it does by calling plugin_err.
Security signals we found
No strong security signals were identified.
Evidence from the diff
The commit moves SQLite CREATE INDEX calls from plugin initialization (init_indices called once globally) to per-table completion (one_refresh_done). A new boolean field indices_created is added to struct table_desc to ensure indexes are created exactly once after the first refresh of each table. This avoids the overhead of maintaining indexes during bulk INSERT operations, significantly reducing initial load time. The change is purely a performance optimization and does not alter query semantics, access control, or data integrity.
Changed components
plugins/sql.cInspect captured patch +30 / −20
diff --git a/plugins/sql.c b/plugins/sql.c
index ca622c08..be7df11d 100644
--- a/plugins/sql.c
+++ b/plugins/sql.c
@@ -119,6 +119,8 @@ struct table_desc {
bool is_subobject;
/* Do we use created_index as primary key? Otherwise we create rowid. */
bool has_created_index;
+ /* Have we created our sql indexes yet? */
+ bool indices_created;
/* function to refresh it. */
struct command_result *(*refresh)(struct command *cmd,
const struct table_desc *td,
@@ -487,6 +489,28 @@ static struct command_result *refresh_complete(struct command *cmd,
return command_finished(cmd, ret);
}
+static void init_indices(struct plugin *plugin, const struct table_desc *td)
+{
+ for (size_t i = 0; i < ARRAY_SIZE(indices); i++) {
+ char *errmsg, *cmd;
+ int err;
+
+ if (!streq(indices[i].tablename, td->name))
+ continue;
+
+ cmd = tal_fmt(tmpctx, "CREATE INDEX %s_%zu_idx ON %s (%s",
+ indices[i].tablename, i,
+ indices[i].tablename,
+ indices[i].fields[0]);
+ if (indices[i].fields[1])
+ tal_append_fmt(&cmd, ", %s", indices[i].fields[1]);
+ tal_append_fmt(&cmd, ");");
+ err = sqlite3_exec(db, cmd, NULL, NULL, &errmsg);
+ if (err != SQLITE_OK)
+ plugin_err(plugin, "Failed '%s': %s", cmd, errmsg);
+ }
+}
+
/* Recursion */
static struct command_result *refresh_tables(struct command *cmd,
struct db_query *dbq);
@@ -502,6 +526,11 @@ static struct command_result *one_refresh_done(struct command *cmd,
assert(td->refreshing);
td->refreshing = false;
+ if (!td->indices_created) {
+ init_indices(cmd->plugin, td);
+ td->indices_created = 1;
+ }
+
/* Transfer refresh waiters onto local list */
list_head_init(&waiters);
list_append_list(&waiters, &td->refresh_waiters);
@@ -1524,6 +1553,7 @@ static struct table_desc *new_table_desc(const tal_t *ctx,
td->last_created_index = 0;
td->has_created_index = false;
td->refreshing = false;
+ td->indices_created = false;
list_head_init(&td->refresh_waiters);
/* Only top-levels have refresh functions */
@@ -1704,25 +1734,6 @@ static void init_tablemap(struct plugin *plugin)
}
}
-static void init_indices(struct plugin *plugin)
-{
- for (size_t i = 0; i < ARRAY_SIZE(indices); i++) {
- char *errmsg, *cmd;
- int err;
-
- cmd = tal_fmt(tmpctx, "CREATE INDEX %s_%zu_idx ON %s (%s",
- indices[i].tablename, i,
- indices[i].tablename,
- indices[i].fields[0]);
- if (indices[i].fields[1])
- tal_append_fmt(&cmd, ", %s", indices[i].fields[1]);
- tal_append_fmt(&cmd, ");");
- err = sqlite3_exec(db, cmd, NULL, NULL, &errmsg);
- if (err != SQLITE_OK)
- plugin_err(plugin, "Failed '%s': %s", cmd, errmsg);
- }
-}
-
static void memleak_mark_tablemap(struct plugin *p, struct htable *memtable)
{
memleak_ptr(memtable, dbfilename);
@@ -1735,7 +1746,6 @@ static const char *init(struct command *init_cmd,
struct plugin *plugin = init_cmd->plugin;
db = sqlite_setup(plugin);
init_tablemap(plugin);
- init_indices(plugin);
plugin_set_memleak_handler(plugin, memleak_mark_tablemap);
return NULL;
Why this scored 15/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.