[feature]: Bring FwdLog into SQL

[ziggie1984]

Currently running the following RPC calls in against the PG KV-DB might suffer some performance issues:

RPC Affected:

FeeReport

ForwardingHistory

We should move the fwd-log to native sql as well.

[warioishere]

Bigger public node here migrated from bbolt to PG

lncli feereport

real    0m38.882s
user    0m0.020s
sys     0m0.014s

but forwardinghistory is instant for me

lncli fwdinghistory

real    0m0.051s
user    0m0.018s
sys     0m0.011s

[ziggie1984]

Yes thats understandable the feeReport rpc call is very inefficient currently it scans the DB 3 times for different timescales. However the forwardinghistory will give you only 100 event by default, if you try to query everything or at least 1 month of data you will also see the bottleneck there.

[warioishere]

Yes thats understandable the feeReport rpc call is very inefficient currently it scans the DB 3 times for different timescales. However the forwardinghistory will give you only 100 event by default, if you try to query everything or at least 1 month of data you will also see the bottleneck there.

admin@main-node~ ₿ time lncli fwdinghistory --start_time -4w

real    0m0.061s
user    0m0.020s
sys     0m0.013s

still fast with 4weeks of data which is quiet alot here

[ziggie1984]

try setting --max_events to a huge number otherwise the default is 100 as mentioned before !

[warioishere]

try setting --max_events to a huge number otherwise the default is 100 as mentioned before !

sorry bud

while using the command, I read:

--max_events value the max number of events to return (default: 0)

says default 0 so I thought its unlimited

admin@main-node~ ₿ time lncli fwdinghistory --start_time -4w --max_events 100000

real    0m3.549s
user    0m0.182s
sys     0m0.306s

thats acceptable for me with my burked PG setup :D

[Abdulkbk]

I am considering making some research and eventually working on this, but considering this comment:

However I don't think it's necessary to use TLV here now, as I'm looking forward to sunset the forwarding log bucket and relying on revocation log only one day - the latter plus the channel commitments gives us enough info to construct the forwarding events on the fly, maybe a simple query would be sufficient once we've done SQLizing the channeldb.

It suggests that there is a plan to change how forwarding logs are constructed altogether?

[ziggie1984]

I guess we can hold off working on this one until the switch/channel is in proper SQL which eventually might remove the need for the fwd_log althougher.