sqldb: optimise payment index layout in new migration 13
What the developer wrote
Authored by ziggie
sqldb: optimise payment index layout in new migration 13
Since migration 10 is already merged into master it cannot be edited.
Add a new migration (000013_payments_index_improvements) that carries
forward two index improvements:
- Drop idx_htlc_attempt_index on payment_htlc_attempts(attempt_index)
and idx_route_hops_htlc_attempt_index on
payment_route_hops(htlc_attempt_index). Both are redundant with
existing UNIQUE constraints and only add write/maintenance overhead.
- Add idx_htlc_payment_id_attempt_time on
payment_htlc_attempts(payment_id, attempt_time) to optimise batched
attempt reads that filter by payment_id and order by attempt_time
(FetchHtlcAttemptsForPayments).
- Add idx_htlc_resolutions_type_attempt_index on
payment_htlc_attempt_resolutions(resolution_type, attempt_index) to
optimise the failed-attempt cleanup path that filters by
resolution_type before joining on attempt_index
(DeleteFailedAttempts).
What changed, and why it matters
This commit is a routine database performance tuning change. It removes two indexes that duplicate existing uniqueness constraints and adds two new composite indexes to speed up common payment-related queries. There is no security fix or vulnerability here.
No security action required. Treat as a normal performance optimization; verify migration applies cleanly in staging and that query plans improve for FetchHtlcAttemptsForPayments and DeleteFailedAttempts.
Security signals we found
No strong security signals were identified.
Evidence from the diff
The commit introduces migration 000013_payments_index_improvements. The up migration drops idx_htlc_attempt_index and idx_route_hops_htlc_attempt_index because they are redundant with existing UNIQUE constraints (which already create implicit indexes). It adds idx_htlc_payment_id_attempt_time on payment_htlc_attempts(payment_id, attempt_time) and idx_htlc_resolutions_type_attempt_index on payment_htlc_attempt_resolutions(resolution_type, attempt_index). The down migration reverses these changes. This is purely an optimization with no semantic or security changes to query results.
Changed components
sqldb migrationspayment_htlc_attempts table indexingpayment_route_hops table indexingpayment_htlc_attempt_resolutions table indexingInspect captured patch +51 / −0
diff --git a/sqldb/migrations_dev.go b/sqldb/migrations_dev.go
index dca5365..729160d 100644
--- a/sqldb/migrations_dev.go
+++ b/sqldb/migrations_dev.go
@@ -27,4 +27,9 @@ var migrationAdditions = []MigrationConfig{
Version: 15,
SchemaVersion: 12,
},
+ {
+ Name: "000013_payments_index_improvements",
+ Version: 16,
+ SchemaVersion: 13,
+ },
}
diff --git a/sqldb/sqlc/migrations/000013_payments_index_improvements.down.sql b/sqldb/sqlc/migrations/000013_payments_index_improvements.down.sql
new file mode 100644
index 0000000..29fd14f
--- /dev/null
+++ b/sqldb/sqlc/migrations/000013_payments_index_improvements.down.sql
@@ -0,0 +1,10 @@
+-- Restore the composite indexes removed in the up migration.
+DROP INDEX IF EXISTS idx_htlc_payment_id_attempt_time;
+DROP INDEX IF EXISTS idx_htlc_resolutions_type_attempt_index;
+
+-- Restore the redundant indexes that were dropped in the up migration.
+CREATE INDEX IF NOT EXISTS idx_htlc_attempt_index
+ON payment_htlc_attempts(attempt_index);
+
+CREATE INDEX IF NOT EXISTS idx_route_hops_htlc_attempt_index
+ON payment_route_hops(htlc_attempt_index);
diff --git a/sqldb/sqlc/migrations/000013_payments_index_improvements.up.sql b/sqldb/sqlc/migrations/000013_payments_index_improvements.up.sql
new file mode 100644
index 0000000..d23ab33
--- /dev/null
+++ b/sqldb/sqlc/migrations/000013_payments_index_improvements.up.sql
@@ -0,0 +1,36 @@
+-- ─────────────────────────────────────────────
+-- Remove redundant indexes from the payments schema.
+-- ─────────────────────────────────────────────
+-- Drop two explicit indexes that duplicate indexes already created by UNIQUE
+-- constraints:
+--
+-- - payment_htlc_attempts(attempt_index) duplicates UNIQUE(attempt_index)
+-- - payment_route_hops(htlc_attempt_index) duplicates
+-- UNIQUE(htlc_attempt_index, hop_index)
+--
+-- This reduces write/index maintenance overhead without changing query
+-- capabilities, since the UNIQUE-backed autoindexes already satisfy these
+-- lookups via exact and leftmost-prefix matching.
+-- ─────────────────────────────────────────────
+
+DROP INDEX IF EXISTS idx_htlc_attempt_index;
+DROP INDEX IF EXISTS idx_route_hops_htlc_attempt_index;
+
+-- ─────────────────────────────────────────────
+-- Add composite indexes for hot payment query paths.
+-- ─────────────────────────────────────────────
+-- Add two composite indexes to better match high-frequency query patterns
+-- observed in the payment lifecycle.
+-- ─────────────────────────────────────────────
+
+-- Composite index for batched attempt fetches that filter by payment_id and
+-- order by attempt_time. This matches FetchHtlcAttemptsForPayments:
+-- WHERE payment_id IN (...) ORDER BY payment_id, attempt_time.
+CREATE INDEX IF NOT EXISTS idx_htlc_payment_id_attempt_time
+ON payment_htlc_attempts(payment_id, attempt_time);
+
+-- Composite index for delete paths that first filter failed resolutions by
+-- resolution_type and then join/delete by attempt_index. This matches
+-- DeleteFailedAttempts.
+CREATE INDEX IF NOT EXISTS idx_htlc_resolutions_type_attempt_index
+ON payment_htlc_attempt_resolutions(resolution_type, attempt_index);
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.