Simplify FetchNonTerminalPayments by collapsing the selector down to
two branches: payments that are not failed and have no settled attempt,
and payments that still have unresolved attempts. This keeps the same
non-terminal semantics while making the query easier to reason about.
Also add a partial index on payments(id) where fail_reason IS NULL to
speed up the startup selector branch that scans payments without a
recorded failure reason.
FetchInFlightPayments no longer relies on the old
FetchAllInflightAttempts query surface once the non-terminal payment
query is in place.
Use make lint and the inflight recovery tests as evidence. The code
still passes once the old query and its generated bindings are removed.
Remove FetchAllInflightAttempts from payments.sql, regenerate the sqlc
bindings, and drop the matching SQLQueries interface method.
FetchInFlightPayments needs a dedicated SQL query that can return
non-terminal payments without relying on the unresolved-attempt scan.
The first version of that query fixed correctness, but the follow-up
selector measurements showed a UNION-based shape was materially
faster while returning the same payment set.
Use the inflight regression tests as evidence. The tests still fail on
SQL before the Go payment store is wired up, but this commit adds the
final SQL surface the later wiring commit depends on.
Add FetchNonTerminalPayments to the SQL query set, regenerate the sqlc
bindings, add the PaymentAndIntent adapters for the new row type, and
use the UNION-based candidate selection so the final query shape lands
in one commit.
The invoice filter queries (FetchPendingInvoices,
FilterInvoicesBySettleIndex, FilterInvoicesByAddIndex,
FilterInvoicesForward, FilterInvoicesReverse) all used LIMIT+OFFSET for
internal pagination. This causes SQLite to build an ephemeral temp
B-tree for every page to implement the OFFSET skip, making each
successive page O(offset+limit). On nodes with large invoice histories
this compounds into a significant CPU cost — profiling showed
FilterInvoicesReverse consuming 53% of total CPU, with _sqlite3BtreeInsert
and _balance_nonroot (2.4s combined) appearing inside the SELECT due to
the temp B-tree being built and rebalanced to skip rows.
Replace the OFFSET loop (queryWithLimit) with cursor-based pagination
across all four callers in sql_store.go:
- FetchPendingInvoices: add id_cursor param, advance cursor to
last_id + 1 each page.
- InvoicesSettledSince: add id_cursor param alongside the existing
settle_index lower bound, advance cursor to last_id + 1 each page.
- InvoicesAddedSince: cursor starts at idx+1, advances to last_id+1.
- QueryInvoices: forward cursor starts at IndexOffset+1 and advances
by +1; reverse cursor starts at IndexOffset-1 (or MaxInt64) and
advances by -1. Inclusive SQL bounds (>= / <=) are preserved so
query semantics and all existing callers are unchanged.
The queryWithLimit helper is removed as it has no remaining callers.
Each page now performs a single PK seek + forward scan of exactly
page_size rows with no temp sort structure, matching the cursor-based
pattern already used by the payments filter queries.
Split GetNodesByLastUpdateRange into two query variants: one for all
nodes and a new GetPublicNodesByLastUpdateRange for public-only nodes.
The public-only variant uses two separate EXISTS checks (one per
node_id column) instead of a single OR, allowing the planner to do
direct index probes on each channel node-id index.
Also upgrade the channel node-id indexes from single-column
(node_id_1) and (node_id_2) to composite (node_id_1, version) and
(node_id_2, version) to support version-aware public node checks
while preserving usefulness for node-centric lookups.
The v1 GetNodesByLastUpdateRange query was missing an explicit
`WHERE version = 1` filter and used a single-column index on
`graph_nodes(last_update)` which didn't match the full query ordering
shape `ORDER BY last_update, pub_key`, requiring an extra sort step.
Add `WHERE version = 1` to the query for correctness and replace the
index with a composite `(version, last_update, pub_key)` index that
covers the filter, range scan, and pagination ordering together.
Note: the migration 000009_graph_v2 file is edited directly (dropping
the old index and creating the new one) rather than adding a new
migration, since this migration has not been included in a release yet.
Add GetChannelsByPolicyBlockRange SQL query and wire it into
SQLStore.chanUpdatesInHorizonV2. This mirrors the existing v1
time-based query but filters on policy block_height instead of
last_update, using the same [start, end) exclusive-end semantics
and (max_block_height, channel_id) compound cursor pagination.
Also adds extractMaxBlockHeight helper (returns the max of both
policies' block heights for cursor tracking) and
buildChannelFromBlockRangeRow (structurally identical to the v1
variant but accepts the distinct sqlc-generated row type). The
extractChannelPolicies type-switch is extended with a case for the
new GetChannelsByPolicyBlockRangeRow type.
Add GetNodesByBlockHeightRange SQL query and wire it into
SQLStore.nodeUpdatesInHorizonV2. This mirrors the existing v1
time-based query but filters on (version, block_height) instead of
last_update, using the same [start, end) exclusive-end semantics
and (block_height, pub_key) compound cursor pagination.
The public-node filter for v2 checks for channels with a non-empty
channel announcement signature (c.signature), matching the v2
protocol's public channel indicator.
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.
BOLT 07 specifies that gossip_timestamp_filter range semantics are
"greater or equal to first_timestamp, and less than first_timestamp
plus timestamp_range", i.e. [start, end). Three of the four
implementations (KV ChanUpdatesInHorizon, KV NodeUpdatesInHorizon,
SQL NodeUpdatesInHorizon) were incorrectly using an inclusive end
time (<= instead of <). Only SQL ChanUpdatesInHorizon was correct.
This commit fixes the KV store's fetchNextChanUpdateBatch and
fetchNextNodeBatch to use >= (instead of >) for the end time break
condition, and < (instead of <=) for the hasMore check. It also
fixes the SQL GetNodesByLastUpdateRange query to use < instead of <=
on the end_time bound.
All godocs are updated to reference the BOLT 07 spec language and
explicitly document the [start, end) range semantics. New dedicated
tests (TestNodeUpdatesInHorizonExclusiveEnd and
TestChanUpdatesInHorizonExclusiveEnd) verify that items at exactly
the end time are excluded while items at the start time are included.
In this commit, we fix a gap that was introduced when the payment DB
commits were arranged for the main line. The `migration1` package
already had the updated `FilterPayments` query using `COALESCE` for
the index bounds and a separate `FilterPaymentsDesc` query for
reverse-ordered pagination, but the corresponding changes in
`sqldb/sqlc` and the main `sql_store.go` were accidentally left out.
The old `FilterPayments` used OR-based nullable params for the
`created_at` bounds (e.g., `p.created_at >= $3 OR $3 IS NULL`). On
Postgres, mixing nullable text fallbacks in a `COALESCE` with
timestamp columns causes a type mismatch error. The OR-based approach
also prevents the query planner from using the `created_at` index.
We fix this by providing non-nullable `time.Time` params from the Go
side, defaulting to epoch start and year 9999 when no filter is set.
We also drop the `Reverse` param from `FilterPaymentsParams` and
instead introduce a dedicated `FilterPaymentsDesc` query that orders
by `p.id DESC`. This avoids the conditional `CASE WHEN` ordering
trick, which some planners handle poorly.
Add a gossip version parameter to ChannelView in the Store interface,
KV and SQL implementations, and the ChannelGraph wrapper. The KVStore
guards v2 requests with ErrVersionNotSupportedForKVDB; the SQLStore
filters by the requested version.
Add three new SQL queries to support version-scoped channel lookups:
- GetPublicV1ChannelsBySCID: public v1 channels in a SCID range,
ordered by SCID.
- GetPublicV2ChannelsBySCID: public v2 channels in a SCID range,
ordered by SCID.
- ListChannelsPaginatedV2: paginate v2 channels by internal DB ID,
used by ChanUpdateRange.
Add TestVersionedDBs/channel_view to verify that v1 and v2 channel
views each return only their respective channels.
The previous query used an IN subquery that scanned all failed
resolutions across all payments (O(N) where N = total failed attempts
globally). Replace with a correlated EXISTS subquery that only checks
resolutions for the specific payment's attempts, making it O(k) where
k = attempts for this payment (typically 1-5).
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).
The GetInvoice query used an OR IS NULL pattern for each filter
parameter:
WHERE (i.hash = $1 OR $1 IS NULL)
AND (i.payment_addr = $2 OR $2 IS NULL)
SQLite's query planner decides on an execution plan at prepare time,
before seeing any parameter values. Because either condition can be
trivially true when its parameter is NULL, the planner conservatively
falls back to a full table scan rather than using the unique indexes on
hash and payment_addr. This caused every invoice lookup and update on
the hot path (HTLC settlement) to scan the entire invoices table.
Replace the single catch-all query with three dedicated queries, each
using a direct equality on a uniquely constrained column:
- GetInvoiceByHash: WHERE hash = $1
- GetInvoiceByAddr: WHERE payment_addr = $1 (new, covers AMP path)
- GetInvoiceBySetID: existing, unchanged
Update getInvoiceByRef to route to the appropriate query based on which
fields are present in the InvoiceRef. When both hash and payment address
are provided, we look up by hash and then verify the returned invoice's
payment address matches.
GetInvoice is now unused and removed from the SQLInvoiceQueries
interface.
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.
Older LND versions could create multiple payments for the same hash.
We need to preserve those historical records during KV→SQL migration,
but they don’t fit the normal payment schema because we enforce a
unique payment hash constraint. Introduce a lean payment_duplicates
table to store only the essential fields (identifier, amount,
timestamps, settle/fail data).
This keeps the primary payment records stable and makes the migration
deterministic even when duplicate records lack attempt info. The table
is intentionally minimal and can be dropped after migration if no
duplicate payments exist.
For now there is no logic in place which allows the noderunner to
fetch duplicate payments after the migration.
- Remove duplicate compile-time interface assertion for SQLStore.
- Fix the down migration to drop payment_intents before payments to
respect the foreign key dependency order. This was not a bug in the
first place bc we have the CASCADE when deleting payments.
The 000009 schema version slot is now taken by 000009_graph_v2_columns
which was merged ahead of the payments schema. Bump the payments schema
file number to 000010 to avoid the collision.
Moreover update the migration_dev.go file to reflect this change and
update the order of migration.
Previously a one(intent)-to-many(payment) relationship it is now
changed to a one-to-one relationship because a payment request
only can have 1 payment related to it. Looking into the future
with BOLT12 offers, the fetched invoice from the offer could be
stored here as well and the relationship would still hold.
All call sites have been migrated to the targeted replacement queries
in the previous commit. Remove FilterInvoices and its associated
params struct from the SQL source and regenerate.
The existing FilterInvoices query uses optional parameters via the
pattern `(col >= param OR param IS NULL)` for every filter. SQLite
cannot use indexes with this pattern because the OR prevents the query
planner from determining at plan time which rows satisfy the condition,
resulting in a full table scan regardless of the available indexes
(invoices_state_idx, invoices_settle_index_idx, and the primary-key
clustered index on id).
Additionally, the conditional ORDER BY:
ORDER BY CASE WHEN reverse = FALSE ... THEN id ELSE NULL END ASC,
CASE WHEN reverse = TRUE ... THEN id ELSE NULL END DESC
prevents the planner from using the index ordering and forces an
explicit sort.
Add five focused replacements, each with a plain sargable predicate and
a direct ORDER BY so the planner can always choose an index scan:
- FetchPendingInvoices: WHERE state IN (0, 3)
- FilterInvoicesBySettleIndex: WHERE settle_index >= $1
- FilterInvoicesByAddIndex: WHERE id >= $1
- FilterInvoicesForward: WHERE id >= $1 ... ORDER BY id ASC
- FilterInvoicesReverse: WHERE id <= $1 ... ORDER BY id DESC
FilterInvoicesForward and FilterInvoicesReverse accept non-nullable
timestamp parameters (created_after, created_before) so the planner
always sees plain range predicates on created_at. Callers supply
Go-side defaults when no date filter is needed, following the same
convention already used by the payments query.
FilterInvoices is kept in this commit so all existing call sites
continue to compile. It will be removed once all callers have been
migrated.
The ListChannelsWithPoliciesForCachePaginated query was missing the
policy version column, causing extractChannelPolicies to hardcode
lnwire.GossipVersion1 for that row type. Add cp1.version and
cp2.version to the query and use the fetched values instead.
Add a gossip version parameter to DisabledChannelIDs in the Store
interface and both implementations. Add a new version-filtered SQL
query and update the builder caller.
Update the UpsertChannelPolicy query to apply different staleness
checks based on gossip version. For v1 policies, continue checking
last_update timestamps. For v2 policies, check block_height instead,
using >= comparison to handle policies from the same block.
The version-specific WHERE clause ensures that policy updates are only
applied when they contain newer information according to the versioning
scheme appropriate for that gossip version.
Extend channel policy queries and structs to support v2-specific fields:
- Add BlockHeight field to track the block height for v2 policy updates.
- Add DisableFlags field for v2 channel disable messages.
Both fields are nullable (sql.NullInt64/Int16) to maintain backwards
compatibility with v1 channels. The fields are initialized as null in
updateChanEdgePolicy and threaded through all policy-related queries
(GetChannelBySCIDWithPolicies, ListChannelsByNodeID, UpsertEdgePolicy,
etc.) and the extractChannelPolicies helper.
This commit includes both the hand-written SQL query updates and the
corresponding sqlc-generated Go code.
This commit improves handling of missing channel signatures in the
database:
- Return nil from auth proof accessors instead of empty slices so that
missing signatures are stored as NULL in SQL.
- Update public channel checks to require signature length > 0, which
properly handles existing empty bytea values in the database.
- Add regression test covering empty v1 and v2 channel signatures to
prevent future issues.
Add SQL query to determine if a node has public v2 channels. Unlike
v1 which requires all four individual signatures, v2 channels are
considered public when the single aggregated signature is present.
Add SQL query to update the signature column for v2 channel auth proofs.
Unlike v1 which requires four separate signatures, v2 channels use a
single aggregated signature.
Add three new optional fields to the CreateChannel SQL query to
support v2 channel announcements:
- signature: single schnorr signature (replaces four ECDSA sigs)
- funding_pk_script: the funding output script
- merkle_root_hash: for taproot channels
These fields are NULL for v1 channels and populated for v2 channels.
