Postres Migration

[wobloz]

Hi there,

Is there a plan to speed up the Postgres migration feature? I've been running a hobby none for over two years now, and it somehow became a Top50 LN node. But now the node is becoming too slow mainly because of the channel DB. I can even hardly restart the node without a major outage.

Any idea?

Thank you,
Pavel

[guggero]

Do you regularly compact your channel DB (e.g. use db.bolt.auto-compact=true)? That should reduce the startup time if done regularly (by default every week if lnd is restarted).

Also, turning on db.bolt.nofreelistsync=true can speed up opening the DB.

But to answer your question: You can follow along the progress to migrate to "native" SQL tables here: #6288

For new nodes there is also a way to use Postgres as a backend (but with the data still being in the key/value format, just stored in Postgres tables): https://github.com/lightningnetwork/lnd/blob/master/docs/postgres.md

There are also plans to finally make progress on the data migration tool that would allow you to migrate your node over from bbolt to Postgres: lightninglabs/lndinit#21
Expect some progress in the coming months there.

[wobloz]

The auto compact feature is on. What does it mean to disable the free list sync, please?

BTW, I usually restart every other week and it takes about an hour to start it up.

[guggero]

The free list sync is a feature of the bbolt database. It basically gives you the tradeoff between startup time and write speed. So with the sync turned off, the node will start up quicker but individual writes are a bit slower.

What part takes that long? The compaction or opening the DB? You can see that in the logs. Is this on an SSD?

[wobloz]

It is on an SSD disk...

2023-09-21 04:18:52.791 [INF] LTND: Active chain: Bitcoin (network=mainnet)
2023-09-21 04:18:52.793 [INF] LTND: Pprof listening on 127.0.0.1:12345
2023-09-21 04:18:52.797 [INF] RPCS: RPC server listening on 0.0.0.0:10009
2023-09-21 04:18:52.822 [INF] RPCS: gRPC proxy started at 0.0.0.0:10080
2023-09-21 04:18:52.823 [INF] LTND: Opening the main database, this might take a few minutes...
2023-09-21 04:18:52.823 [INF] LTND: Opening bbolt database, sync_freelist=false, auto_compact=true
2023-09-21 04:18:52.832 [INF] CHDB: Compacting database file at /home/bitcoin/.lnd/data/graph/mainnet/channel.db
...
2023-09-21 05:04:25.615 [INF] CHDB: DB compaction of /home/bitcoin/.lnd/data/graph/mainnet/channel.db successful, 8421371904 -> 6130335744 bytes (gain=1.37x)
2023-09-21 05:04:25.642 [INF] CHDB: Swapping old DB file from /home/bitcoin/.lnd/data/graph/mainnet/temp-dont-use.db to /home/bitcoin/.lnd/data/graph/mainnet/channel.db
2023-09-21 05:04:27.185 [INF] CHDB: Compacting database file at /home/bitcoin/.lnd/data/chain/bitcoin/mainnet/macaroons.db
2023-09-21 05:04:27.234 [INF] CHDB: DB compaction of /home/bitcoin/.lnd/data/chain/bitcoin/mainnet/macaroons.db successful, 32768 -> 32768 bytes (gain=1.00x)
2023-09-21 05:04:27.235 [INF] CHDB: Swapping old DB file from /home/bitcoin/.lnd/data/chain/bitcoin/mainnet/temp-dont-use.db to /home/bitcoin/.lnd/data/chain/bitcoin/mainnet/macaroons.db
2023-09-21 05:04:27.237 [INF] CHDB: Compacting database file at /home/bitcoin/.lnd/data/graph/mainnet/sphinxreplay.db
...
2023-09-21 05:04:25.615 [INF] CHDB: DB compaction of /home/bitcoin/.lnd/data/graph/mainnet/channel.db successful, 8421371904 -> 6130335744 bytes (gain=1.37x)
2023-09-21 05:04:25.642 [INF] CHDB: Swapping old DB file from /home/bitcoin/.lnd/data/graph/mainnet/temp-dont-use.db to /home/bitcoin/.lnd/data/graph/mainnet/channel.db
2023-09-21 05:04:27.185 [INF] CHDB: Compacting database file at /home/bitcoin/.lnd/data/chain/bitcoin/mainnet/macaroons.db
2023-09-21 05:04:27.234 [INF] CHDB: DB compaction of /home/bitcoin/.lnd/data/chain/bitcoin/mainnet/macaroons.db successful, 32768 -> 32768 bytes (gain=1.00x)
2023-09-21 05:04:27.235 [INF] CHDB: Swapping old DB file from /home/bitcoin/.lnd/data/chain/bitcoin/mainnet/temp-dont-use.db to /home/bitcoin/.lnd/data/chain/bitcoin/mainnet/macaroons.db
2023-09-21 05:04:27.237 [INF] CHDB: Compacting database file at /home/bitcoin/.lnd/data/graph/mainnet/sphinxreplay.db

[guggero]

No, that is a one-time option that only does something the first time you use it.

[wobloz]

So, I tried to prune the revocations but it is still 5.7GB and it took an hour to open the DB. Perhaps I should do the opposite and disable the compacting.

[guggero]

Do you have a lot of invoices or payments? Or channels with a lot of updates (num_updates in lncli listchannels)? Your best bet to get rid of that long startup time is to identify and then remove data that you don't need anymore.

[wobloz]

I've got 100+ channels for about two years. Most of the channels have hundreds of thousands of num_updates.

[guggero]

Well, then one thing that you can do is close the channels with most updates and re-open them. Not very nice from a chain fee perspective but that will clean up a lot of state in the DB (will only be apparent after compacting though).