How To Dev

Introduction These are Query Tuning techniques based on common errors encountered when querying data and the process you can apply: Avoid Using SELECT * When you use queries like this SELECT * FROM users WHERE id = 100 SELECT username FROM users WHERE id = 100 If there is no Index , Postgres will have to use Heap Scan to load the corresponding entire data page into RAM At this point, whether you use SELECT * or SELECT name , Postgres handles it the same way But the difference starts here, if you only SELECT the necessary columns, Postgres only needs to process those columns without spending additional CPU cost to process all columns as with SELECT * If any of the requested columns are too large and stored in the TOAST Table , it incurs additional I/O and data decompression costs At the same time, if you deploy a Web Server to respond with this data to FE , larger amounts of data will consume more network bandwidth, RAM to parse JSON and take longer to process Solu...

Introduction In the era of big data, when storage capacity reaches the Terabyte threshold or the number of requests exceeds the physical limit of a single server ( Vertical Scaling / Scale-up ), Sharding is the optimal solution for Horizontal Scaling / Scale-out It is achieved by dividing a massive data table (billions of rows) into multiple small, independent and self-managed parts called Shards Each Shard is a separate physical database located on a different physical Server . The key point is that the data in the Shards does not overlap, but when combined, it forms a complete dataset. Horizontal Partitioning vs Sharding Horizontal Partitioning : Splitting a large table into smaller tables (such as by month) but all of these child tables still reside on the same physical server. Sharding : Distributing those child tables after partitioning across multiple different physical servers. Therefore, Sharding is the architecture of horizontal data partitioning in a distributed environ...