sqldb: drop redundant and unused invoice indexes
What the developer wrote
Authored by ziggie
sqldb: drop redundant and unused invoice indexes
Remove four indexes from the invoices table that either duplicate existing
UNIQUE constraint indexes or are never used in WHERE clauses:
- invoices_hash_idx: redundant, UNIQUE constraint on hash already creates
an implicit index
- invoices_payment_addr_idx: redundant, UNIQUE constraint on payment_addr
already creates an implicit index
- invoices_preimage_idx: unused, preimage is NULL on all new invoices and
is never used as a query filter
- invoices_settled_at_idx: unused, settled_at is NULL on all pending
invoices and is never used as a query filter (settle_index is used
instead)
Dropping these reduces B-tree working set size, which improves page cache
utilization as the invoice table grows.
What changed, and why it matters
This commit removes four database indexes from LND's invoice table. Two are unnecessary because the same columns already have unique-constraint indexes. The other two are reportedly never used in queries. The stated goal is to reduce memory and storage overhead, not to fix a security issue. There is no indication this change introduces a vulnerability.
No security action required. Treat as a routine performance/maintenance migration. Operators should verify the migration runs cleanly and monitor query performance, though the dropped indexes are described as redundant or unused.
Security signals we found
No strong security signals were identified.
Evidence from the diff
The patch adds a new SQL migration (version 12, schema version 15) that drops four indexes from the invoices table: invoices_hash_idx, invoices_payment_addr_idx, invoices_preimage_idx, and invoices_settled_at_idx. The first two duplicate implicit indexes created by UNIQUE constraints on hash and payment_addr. The latter two are described as unused because preimage is NULL on new invoices and settled_at is not used as a query filter. A corresponding down-migration recreates the indexes. No application code, query logic, or access controls are changed.
Changed components
sqldb/sqlc/migrations/000012_drop_redundant_invoice_indexes.up.sqlsqldb/sqlc/migrations/000012_drop_redundant_invoice_indexes.down.sqlsqldb/migrations_dev.goInspect captured patch +25 / −0
diff --git a/sqldb/migrations_dev.go b/sqldb/migrations_dev.go
index 7b02b15..dca5365 100644
--- a/sqldb/migrations_dev.go
+++ b/sqldb/migrations_dev.go
@@ -22,4 +22,9 @@ var migrationAdditions = []MigrationConfig{
// schema. This is optional and can be disabled by the
// user if necessary.
},
+ {
+ Name: "000012_drop_redundant_invoice_indexes",
+ Version: 15,
+ SchemaVersion: 12,
+ },
}
diff --git a/sqldb/sqlc/migrations/000012_drop_redundant_invoice_indexes.down.sql b/sqldb/sqlc/migrations/000012_drop_redundant_invoice_indexes.down.sql
new file mode 100644
index 0000000..92e8599
--- /dev/null
+++ b/sqldb/sqlc/migrations/000012_drop_redundant_invoice_indexes.down.sql
@@ -0,0 +1,4 @@
+CREATE INDEX IF NOT EXISTS invoices_hash_idx ON invoices(hash);
+CREATE INDEX IF NOT EXISTS invoices_payment_addr_idx ON invoices(payment_addr);
+CREATE INDEX IF NOT EXISTS invoices_preimage_idx ON invoices(preimage);
+CREATE INDEX IF NOT EXISTS invoices_settled_at_idx ON invoices(settled_at);
diff --git a/sqldb/sqlc/migrations/000012_drop_redundant_invoice_indexes.up.sql b/sqldb/sqlc/migrations/000012_drop_redundant_invoice_indexes.up.sql
new file mode 100644
index 0000000..fa6a0e2
--- /dev/null
+++ b/sqldb/sqlc/migrations/000012_drop_redundant_invoice_indexes.up.sql
@@ -0,0 +1,16 @@
+-- invoices_hash_idx is redundant: The UNIQUE constraint on invoices(hash)
+-- already creates an implicit index.
+DROP INDEX IF EXISTS invoices_hash_idx;
+
+-- invoices_payment_addr_idx is redundant: The UNIQUE constraint on
+-- invoices(payment_addr) already creates an implicit index.
+DROP INDEX IF EXISTS invoices_payment_addr_idx;
+
+-- invoices_preimage_idx is useless: There are no queries that filter on
+-- preimage so there is no need to index it.
+DROP INDEX IF EXISTS invoices_preimage_idx;
+
+-- invoices_settled_at_idx is useless: settled_at is NULL for all pending
+-- invoices and is never used as a filter in any query (settle_index is used
+-- instead).
+DROP INDEX IF EXISTS invoices_settled_at_idx;
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.