sqldb: add composite indexes for v2 block-height horizon queries
What the developer wrote
Authored by Elle Mouton
sqldb: add composite indexes for v2 block-height horizon queries
Add composite indexes on graph_nodes and graph_channel_policies for
the upcoming v2 block-height-based horizon queries.
The v2 gossip protocol uses block heights instead of unix timestamps
for ordering node announcements and channel updates. The v2
NodeUpdatesInHorizon and ChanUpdatesInHorizon query paths will
filter on WHERE version = @v AND block_height >= start AND
block_height < end. Without these indexes, those queries would
require full table scans.
For nodes, the index is (version, block_height, pub_key). Including
pub_key covers the ORDER BY (block_height, pub_key) clause and
allows direct cursor seeks for pagination, avoiding an extra sort.
For channel policies, the index is (version, block_height). The
pagination cursor uses a CASE expression across two joined policy
rows so the index cannot cover the ORDER BY — the two leading
columns are sufficient for the range scan.
What changed, and why it matters
This commit adds database indexes to speed up new v2 gossip protocol queries. It is a performance optimization, not a security fix. There are no signals of a vulnerability, exploit, or security-relevant behavior change.
No security action required. Treat as a normal performance/schema migration.
Security signals we found
No strong security signals were identified.
Evidence from the diff
The patch extends an existing SQL migration (000009_graph_v2) to create two composite indexes: graph_node_block_height_idx on graph_nodes(version, block_height, pub_key) and graph_channel_policy_block_height_idx on graph_channel_policies(version, block_height). These support upcoming v2 block-height-based horizon queries and avoid full table scans. The down migration is updated to drop the indexes before dropping the columns. No code logic, permissions, or trust boundaries are changed.
Changed components
sqldb/sqlc/migrations/000009_graph_v2.up.sqlsqldb/sqlc/migrations/000009_graph_v2.down.sqlInspect captured patch +21 / −1
diff --git a/sqldb/sqlc/migrations/000009_graph_v2.down.sql b/sqldb/sqlc/migrations/000009_graph_v2.down.sql
index b566e40..ae045fa 100644
--- a/sqldb/sqlc/migrations/000009_graph_v2.down.sql
+++ b/sqldb/sqlc/migrations/000009_graph_v2.down.sql
@@ -1,3 +1,7 @@
+-- Drop v2 block-height indexes.
+DROP INDEX IF EXISTS graph_node_block_height_idx;
+DROP INDEX IF EXISTS graph_channel_policy_block_height_idx;
+
-- Remove the block_height column from graph_nodes
ALTER TABLE graph_nodes DROP COLUMN block_height;
diff --git a/sqldb/sqlc/migrations/000009_graph_v2.up.sql b/sqldb/sqlc/migrations/000009_graph_v2.up.sql
index 19a3e99..916de66 100644
--- a/sqldb/sqlc/migrations/000009_graph_v2.up.sql
+++ b/sqldb/sqlc/migrations/000009_graph_v2.up.sql
@@ -20,4 +20,20 @@ ALTER TABLE graph_channel_policies ADD COLUMN block_height BIGINT;
-- A bitfield describing the disabled flags for a v2 channel update.
ALTER TABLE graph_channel_policies ADD COLUMN disable_flags SMALLINT
- CHECK (disable_flags >= 0 AND disable_flags <= 255);
\ No newline at end of file
+ CHECK (disable_flags >= 0 AND disable_flags <= 255);
+
+-- Composite index for v2 node horizon queries. The query filters on
+-- (version, block_height) for the range scan and then paginates and orders by
+-- (block_height, pub_key). Including pub_key in the index lets the DB cover
+-- the ORDER BY without an extra sort and seek directly to the pagination
+-- cursor position.
+CREATE INDEX IF NOT EXISTS graph_node_block_height_idx
+ ON graph_nodes (version, block_height, pub_key);
+
+-- Index for v2 channel policy horizon queries which filter by gossip version
+-- and block-height range. The pagination cursor for channel queries uses a
+-- CASE expression across two joined policy rows (max of both block_heights),
+-- so the index cannot cover the ORDER BY — (version, block_height) is
+-- sufficient for the range scan.
+CREATE INDEX IF NOT EXISTS graph_channel_policy_block_height_idx
+ ON graph_channel_policies (version, block_height);
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.