[epic] move in-memory datastructures to the database

[2opremio]

What problem does your feature solve?

Reduces memory consumption so that we can extend the default history retention to one week (currently at 2 hours for transactions and 24 hours for events). See the analysis document for details.

What would you like to see?

I would like to see the in-memory data-structures (Currently based around LedgerBucketWindow) stored in the database instead of in-memory.

Regarding the DB schema, we can either roughly mirror what's kept in memory or use some sort of indexing on the txmeta which is already kept in the DB.

We should also have the bigger picture in mind when modifying the schema. The chosen schema should consider extensibility to implement the new Soroban RPC endoints.

I suggest presenting a schema design which should be peer reviewed before jumping to the implementation.

As an output of this ticket we should also get a performance comparison (e.g. through a purposely built Golang benchmark)

What alternatives are there?

The performance shouldn't considerably degrade. If it does, we should consider caching recent data (particularly for transactions). However, a hybrid in-memory/db approach increases complexity.

Subtasks

Design

Beta Give feedback

1. Design doc for Transactions: New DB design document for supporting transactions endpoint #130
2. Design doc for Events: New DB design document for supporting events endpoint  #131

Implementation (Transactions)

Beta Give feedback

1. Add support for reading transactions with an offset (ingest: Make LedgerTransactionReader seekable and lazy go#5274)
2. Design: Maintaining retention window asynchronously in the DB #153
   rpc-sdk-scrum +1
3. Enhance the existing getTransaction RPC call to leverage a larger history window #140
   +0
   
4. HealthCheck endpoint now should fetch LedgerRange from DB instead of in memory store #154
   +0
   
5. Add ledger retention window of 7 days #155
   objective-5 +1
   
6. add getTransactions endpoint #110
   objective-5 +1
   

Implementation [Events]

Beta Give feedback

1. Introduce events table and ingest events metadata in DB #189
   objective-5 +1
   
2. Refactor getEvents RPC to fetch events from DB #190
   rpc-sdk-scrum +1
   
3. Benchmark latency and storage of new events DB design #191
   rpc-sdk-scrum +1
   
4. Add migration for events and optimize initStorage in daemon.go #257
   rpc-sdk-scrum +1
   

[2opremio]

Related: #60

[2opremio]

@psheth9 can we close this?

Did you do performance tests in AWS?

[psheth9]

Yes I am closing this !!

Also performance and sanity checks are being done in this issue