[LND-Dev] Native SQL schema for invoice storage
Jordi Montes Sanabria
I am writing to provide an overview of an epic that I am currently working on. It will involve multiple PRs to implement. I have already submitted the first three, with more to come.
KV and SQL paradigms have their unique characteristics and trade-offs. KV stores offer simplicity and high-speed operations, especially for key-based lookups. However, the SQL approach provides a more expressive query language, supporting complex operations like joins, or the ability to filter by field values.
As we migrate LND to a SQL-first approach, I would expect substantial changes in the APIs that we can surface to LND users. With a defined schema, LND will support more complex queries. On top of that it will enable external tools to analyze the node data in-depth, possibly from a read replica.
That being said, transitioning the engine in a moving vehicle is always challenging, especially when the vehicle isn't entirely under our control or when we can't access the current state (data) in it. Hence, my proposal is a "soft" transition to SQL-first. The idea is to identify self-contained areas and migrate them one by one to a SQL-first implementation. A good starting point is Invoices.
In the first phase, the plan to offer this change as an optional feature to new nodes. LND will provide a SQL implementation that maps to the functionalities of our current KV in channeldb. New nodes can target sqlite/postgres for invoice data, while existing nodes continue using their current KV engine. Once the schema is thoroughly tested and optimized for production workloads, we can provide a one-time script for users to migrate their data from channeldb to sqldb. Until then, all the flows in LND need to work for both backends.
The goal is to support sqlite/postgres out of the box. The SQL code for migrations/queries files should work for both engines with minimal modifications. We aim to avoid engine-specific functionalities like Postgres' SEQUENCEs and use sqlc to generate Go code from SQL.
There may be notable differences in how LND behaves depending on the assumptions the application code makes about the DB. An example is that sqlite may be able to handle thousands of queries per second, while a postgres db might take longer due to network round trips. Our focus should be to minimize db hits and to do as much work as possible in the db in each round trip.
LND currently supports BOLT11 and AMP invoices, so both need to be catered for. The intention is to reuse as many tests as possible across all back ends. The invoice registry should operate seamlessly across different back ends, irrespective of their db txs.
The sql implementation will also populate the rows of a table caller `invoice_events`. The idea is to keep a log of all the major events an invoice goes through so we do not only have its latest state but records of how what happened since its creation. Currently the granularity is at invoice/setid level, but we can expand it to invoice htlcs too.
Here are the first three PRs for this epic, they are ready for review.
- #7354 sqldb: add invoice schema and sql queries
- #7343 new package supporting sql backends
- #7357 sqldb: InvoiceDB interface implementation [AMP in progress]
I'm also working on a fourth PR where LND can start up and use the selected DB engine with the appropriate paradigm. This requires ensuring both systems implement the same interface and can handle the new config values to provide the invoice registry with the correct implementation.
You can follow this epic's progress on the main issue:
- #6288 channeldb+invoices: create native SQL schema for invoice storage
I'm looking forward to hearing your thoughts and suggestions on this initiative and your feedback on the current implementation.
Best regards,
Jordi.