Great work, @arcange!
Few advices that could help.
- Read-only replicas could offload a great portion of read requests and greatly reduce locks. Master node should only write and send WALs to replicas, so, no locks there essentially. And replcas could be cheaper or even community-maintained.
- Most of the queries are probably made againt last week data, so I would expand by building a separate nodes containing only a portion of data. Ideal solution - JSON handlers on fully RAM cache. For example, storing data from last week would require 7*80MB=560MB or RAM.
- With main DWH you could consider Yandex Clickhouse solution. It's free and it could serve as stream write + read cluster from-the-box and do it fast.
My upvotes, @bronevik.
@bronevikm, Please apologize for my late reply
Thanks for your comment and suggestions.They are really interesting and I will analyse them in deep.