[epic]: graph db: SQL implementation & migration

[ellemouton]

This is the tracking issue for the graph db SQL-ization project. It will be the go-to reference to see how the project is progressing.

I'll start with a check-list for easy reference & then will add any details & context lower down behind some drop-down tags.

High Level Project Check List

• Prepare the code (exported methods and tests) such that everything is DB agnostic (ie, hide db specific types and behaviour) in preparation for having things ready to be run against a different DB backend.
• Introduce an abstract graph DB interface & let the ChannelGraph use this interface instead of a raw pointer.
• Introduce SQL schema, queries & CRUD
• add itest jobs that runs the suite against nodes using the new SQLStore backend for the graph store.
• Implement the kvdb -> SQL migration for the above stores.
• Once there is enough confidence in the code & migration, add the SQL impl & migrations to the main prod build

Lower Level Check Lists

1. Prep graph DB code to be ready for new db backend

• Clean-up the graph db tests so that they are ready to be run against a SQL DB impl of the graph store.
  This includes removing any kvdb types and replacing them with more abstract types.
  - graph/db: clean up test code in preparation for DB abtraction #9694
  - graph+lnwire: start validating that extra lnwire msg bytes are valid TLV #9787
  - multi: various test preparations for different graph store impl #9800
  - graph/db: final test preparation before SQL impl #9823
• Ensure that any methods/flows currently not covered by unit tests are properly covered.
  - graph/db: add test coverage for all V1Store methods  #9796
• Prepare the exported graph db methods such that they dont reveal any bbolt specific types
  - graph/db: remove various kvdb parameters from exported methods #9695
  - batch: dont expose kvdb.RwTx in batch.SchedulerOptions #9790
• graph/db: init SQLStore caches and batch schedulers #9853

2. Abstract graph DB interface

• graph/db: introduce a V1Store interface #9791

3. Introduce SQL schema, queries & CRUD

We'll define the schemas, queries and implement the crud required for each of the following data strucutres:

• nodes: sqldb+graph/db: add node related tables and implement some node CRUD #9866
• channels: sqldb+graph/db: add channel tables and implement some channel CRUD #9869
• channel updates: graph/db+sqldb: channel policy SQL schemas, queries and upsert CRUD #9887
• zombies: [13] graph/db: SQL-ize the zombie index #9937
• source nodes sqldb+graph/db: add node related tables and implement some node CRUD #9866
• closed SCIDs: [16] graph/db: SQL closed SCIDs table and last few methods #9971
• prune log: [15] graph/db: SQL prune log #9939

Note that this will be done in an incremental fashion s.t we are implementing the SQL impl of the new abstract graph store interface method by method. Here are all the methods that will be converted:

• AddLightningNode
• AddrsForNode
• ForEachSourceNodeChannel
• ForEachNodeChannel
• ForEachNodeDirectedChannel
• ForEachNode
• ForEachNodeCacheable
• LookupAlias
• DeleteLightningNode
• NodeUpdatesInHorizon
• FetchLightningNode
• HasLightningNode
• IsPublicNode
• GraphSession
• ForEachChannel
• DisabledChannelIDs
• AddChannelEdge
• HasChannelEdge
• DeleteChannelEdges
• AddEdgeProof
• ChannelID
• HighestChanID
• ChanUpdatesInHorizon
• FilterKnownChanIDs
• FilterChannelRange
• FetchChanInfos
• FetchChannelEdgesByOutpoint
• FetchChannelEdgesByID
• ChannelView
• MarkEdgeZombie
• MarkEdgeLive
• IsZombieEdge
• NumZombies
• PutClosedScid
• IsClosedScid
• UpdateEdgePolicy
• SourceNode
• SetSourceNode
• PruneTip
• PruneGraphNodes
• PruneGraph
• DisconnectBlockAtHeight

Once all the above is complete we can:

• run the full unit test suite against the SQL graph impls: [17] multi: run all graph unit tests against SQL backends & run itest suite against SQL graph backend #9972
• run the full itest suite against the SQL graph impls: [17] multi: run all graph unit tests against SQL backends & run itest suite against SQL graph backend #9972

4. Migration

• Migration function

  • nodes: [graph mig 1]: graph/db: migrate graph nodes from kvdb to SQL  #10036
  • channels: [graph mig 2]: graph/db: migrate graph channels and policies from kvdb to SQL #10037
  • channel polices: [graph mig 2]: graph/db: migrate graph channels and policies from kvdb to SQL #10037
  • zombies, closed scids, prune log: [graph mig 3]: graph/db: migrate zombies, closed SCIDs, prune log from kvdb to SQL #10038

• itest for the upgrade.: lnd+itest: plug in graph SQL migration under test tag & add itest #10071

• plug the migration in to the code behind a dev feature flag: lnd+itest: plug in graph SQL migration under test tag & add itest #10071

• Random/Rapid Unit tests for nodes/channels/channel policies: graph/db: add graph SQL migration rapid unit test #10073

5. Testing/performance improvements & benchmarking

Planned Schemas:

Nodes

NOTE:

• the block_height column here is just to show how v2 data will fit into the picture. Our initial V1 impl will not include this column

(interact with the schema here)

Channels

NOTES:

1. the merkle_root_hash and schnorr_sig columns are only for demonstrating how future gossip V2 data will fit in to the picture. They wont be included in the initial schema.
2. The nodes table here is not complete - it is just to show how channels link to nodes. See "Nodes" section above for details on the nodes table.

(interact here)

Channel Policies

1. the block_height and disabled_flags columns are just to show how v2 data will fit in. (might split disabled flags into own table? need way to easily query "get all disabled channels in v2")
2. The nodes and channels tables here are not complete - it is just to show how channel_policies link to channels and to nodes. See "Nodes" & "Channels" sections above for details on the nodes and `channels tables.

Other Graph Tables

Extra checklist not to forget (pls don't edit):

• use Slices for queries where possible: graph/db: use /*SLICE:<field_name>*/ to optimise various graph queries #10081
• NB: introduce DNS addr type before. At read time, see if any opaque addrs contain DNS type addrs & store as such (waiting on discovery+lnwire: add support for DNS host name in NodeAnnouncement msg #9455)
• re-usable NewReadTx/NewWriteTx in sqldb packages: sqldb: re-usable TxOptions and NoOpReset #9873
• re-usable sqldb.NoReset: sqldb: re-usable TxOptions and NoOpReset #9873
• ensure that node_addr normalisation is ok as is - check performance & where full node read is used.
• test if forEachNodeDirectedChannel performance needs improvement
• 0c94f73#r2142544520
• [10] graph/db+sqldb: implement ForEachSourceNodeChannel #9931 (comment)
• graph: fix nil pointer deference in ForEachChannelCacheable #9951 (comment)
• [13] graph/db: SQL-ize the zombie index #9937 (comment)
• standardize errors: [14] graph/db: implement more SQLStore methods #9938 (comment)
• ensure migration retry safety via a unit test. Ie, ensure no reset functionality is needed. See discussion here: [graph mig 3]: graph/db: migrate zombies, closed SCIDs, prune log from kvdb to SQL #10038 (comment)

Other side quests:

• graph/db: only fetch required info for graph cache population #9923
• multi: explicitly define InboundFees in ChannelUpdate and ChannelEdgePolicy #9897