Recently, due to heavy traffic, CPU overload (over 98% utilization) in our database instance. So we decided to do shard our db into multiple instances. As I understand, in postgres, db level sharding is mostly done by partitioning the tables and moving each partition into seperate instance like shown bellow.
I thought this might make the query faster, but not reduce the load on the main partition by that much, since all queries are made to the main partition itself. So I decided to implement application level sharding in our node backend.
Application level sharding works great for all CRUD operations done using partitioned key. But if query needs to be done by key other then the partition key, then we need to go through each partition one by one. So, in this case it would be better to have a table that is un-partitioned, so that all data can be queried using the same table.
What might be a good approach to fix this? Will just implementing db level sharding as shown in the above image be enough to reduce CPU utilization in the main instance.