[Tracking issue] PostgreSQL & ShardLayout

[khorolets]

Motivation

The data size is growing, it becomes really hard to handle the load we tend to handle. It leads to different issues like:

• Slowing down of some contracts due to state provision in contract runtime #150
• View calls are either too slow or return HTTP/502 (Bad Gateway) error #193
• Maybe even Performance problem ft. near-vm #236

After a lot of conversations and brain storming we believe the mentioned issues can be solved using more traditional relational database management system, PostgreSQL in particular.

The main plan is:

• Leverage the approach of designing the schemas for the queries (from NoSQL)
• Respect the shard layout of nearcore by having multilple database instances
• Utilize the partitioning to make the tables smaller, thus more effective
• Build up read-replicas

Making this happen we expect to be able to:

• serve even more requests per second due to relational db ability to get data in batches effectively
• finally implement the long-standing query.access_key_list method (re: Redesign data collection and storage for query.view_access_key_list RPC method #187)

We realize all the disadvantages and complexity that's coming with relational DBMS. No faith in unicorns, we fully understand what we are doing.

Tracking issue

This issue serves the purpose of the container to track the work related to the bigger milestone.

Tasks

Beta Give feedback

1. Improve or refactor the database crate to respect and follow the shard layout from nearcore #289
   4 of 4
   enhancement +1
   
2. Redesign and design the missing parts of the PostgreSQL schemas respecting the shard layout #290
   +0