[bug]: listpayments hangs node and eats RAM when lots of payments in the DB

[ZZiigguurraatt]

Your environment

--lnd.db.backend=sqlite --lnd.db.use-native-sql

39bfbbb23c5a:/$ lncli -v
lncli version 0.19.0-beta.rc1 commit=lightning-terminal-
39bfbbb23c5a:/$

Steps to reproduce

I have a big database

39bfbbb23c5a:/$ ll -h ~/.lnd/data/graph/regtest/channel.sqlite
-rw-r--r--    1 litd     litd       13.1G Apr 14 06:44 /home/litd/.lnd/data/graph/regtest/channel.sqlite
39bfbbb23c5a:/$

If I constrain with --max_payments 2, I can find that I have 5,835,721 payments in the DB. This takes less than 7 seconds.

39bfbbb23c5a:/$ time lncli listpayments --count_total_payments --max_payments 2|jq .total_num_payments
"5835721"

real	0m6.220s
user	0m0.023s
sys	0m0.009s

However, if I try to run

lncli listpayments > /dev/null

That churns forever at around 77% CPU and 26MB/s disk read and keeps growing RAM usage. If I Control+C the above command, the node keeps churning the query (I assume). I assume the command will eventually finish if the system doesn't run out of RAM, but I haven't waited to find out.

Expected behaviour

1. We should have some reasonable default value for --max_payments. This should avoid accidentally making such queries that take forever and use all system RAM and even if there is enough RAM to complete the query, it will avoid blasting a bunch of data back to the client (above I piped to /dev/null as a precaution).
2. Control+C to the command should cause the node to abort the query
3. Have some kind of checks if the query is too big so that it will fail if there is not going to be enough RAM to complete the query.

[ZZiigguurraatt]

Here's another reason to limit --max_payments, we are going to hit a data transfer limit anyway:

39bfbbb23c5a:/$ time lncli listpayments --max_payments=330000 > /dev/null
[lncli] rpc error: code = ResourceExhausted desc = grpc: received message larger than max (211200010 vs. 209715200)

real	2m51.730s
user	0m0.020s
sys	0m0.014s
39bfbbb23c5a:/$ 

I'm not sure if this limit is because of lncli or the gRPC in general, but it makes sense to have this limit though because you don't want to get an insane amount of data back and then potentially crash the client application machine.

[ZZiigguurraatt]

I would expect to have the same kind of issue with listinvoices, but I'm hitting #9716 instead.

[ZZiigguurraatt]

Performance is even worse for listpayments when using postgres: #9716 (comment) .

[ZZiigguurraatt]

Also mildly related: #9729 .

Generally, I think we should not recommend postgres for large nodes right now until #9147 is complete, which should mitigate this issue (but not solve it, particularly items 1, 2, and 3 of #9709 (comment)).

If this issue is not fixed and #9147 is complete we may no longer crash our node from RAM exhaustion (I have always killed the node before it crashed from RAM exhaustion) but we will still hit #9709 (comment) . Completing #9147 makes things more scalable, but the same issues defined above will still be hit once a certain scale is reached.