HyperByte

A dramatic story of tech tinkering with DB performance

2026-01-28

A business originally starts with a smaller code project, usually with monolithic architecture. Through the ongoing maintenance over a time, SQLs uprises little by little, eventually，the main direction of queries are likely to incline a certain pattern or trend. for much better understanding, let me explain with a story.

[ Jan 2026 ]

First release of the project went online, the product received positive feedback and users are increasing.

↓

[ Aug 2026 ]

A good news is, daily active users surpassed 300, users’ data in DB is growing exponentially. The bed one is, DB’s performance is declining. As the SQL monitor, following SQLs are the most frequently called:

SQL Time spend (ms) query rate per day
select *
from transaction
where customer_ref = #{custRef}
and deleted = 0;
980ms
33%
select *
from transaction
where customer_ref = #{custRef}
and org = #{org}
and country = #{country}
and deleted = 0;
1100ms
18%
1st SQL seemingly is easy to optimize, give it an index can be enhanced a lot (the value of \`customer_ref\` sufficiently diverse, of high cardinality). But, this is only solution of 1st SQL, we also have to account for 2nd SQL. Over the business, a composite index would be more appropriate for this scenario. users only can query theirown transactions, org and country usually of course somehow can get if you have the customer ref, or it's usually available in login user's context, it's all depends on business design, okay then let's say that exactly is how our business logic works, then the index should be composited by three fields : `idx_country_org_cust_ref`. Let both query use 2nd SQL, abosolutely can be sure to they both follow the Leftmost Prefix rule , execution time reduced to 50ms.
Question：
• Why don't add indexes `idx_country_org_cust_ref`, `idx_customer_ref` respectively ?
We usually avoid this approach bcz it increases the overhead of update and insert , we should avoid maintaining multiple indexes for a single update or insert , whenever possible.


The main idea is to handle over 80% queries via very less composited indexes , then use very less secondary indexes to cover the remaining non-typical queries as possible.

↓

[ Nov 2026 ]

The date is now Nov 2026. slow SQLs mostly adjusted, we’ve done all we can do. Due to the exponentially growing user base and their visits , performance hit a bottleneck, again. Now SQL optimization is no longer a silver bullet as it used to be, then we consider read / write splitting to split the load.

MASTER
Primary (Read/Write)
Replication ↓
SLAVE 01
Read Replica
SLAVE 02
Read Replica
Master Active
Slave Synced
We decided master-slaves architecture comprising 1 master and 2 slaves. Route all read traffic to slaves , and all write traffic to the master.
Access Control Pattern
• Why don't add indexes `idx_country_org_cust_ref`, `idx_customer_ref` respectively ?
We usually avoid this approach bcz it increases the overhead of update and insert , we should avoid maintaining multiple indexes for a single update or insert , whenever possible.
• Why don't add indexes `idx_country_org_cust_ref`, `idx_customer_ref` respectively ?
We usually avoid this approach bcz it increases the overhead of update and insert , we should avoid maintaining multiple indexes for a single update or insert , whenever possible.

As a result, the performance inhanced a lot , reads and writes got faster. That’s why we say “三个臭皮匠胜过一个诸葛亮” (Three heads are better than one).

Cool.

↓

[ Jan 2027 ]

Data volume is continuously increasing, has now reached a massive scale. We have observed that some table like order table, already reached a skyrocketing count. We then adopted database sharding, sharding the table across multiple databases and tables.

Database Sharding

Splitting order table into multiple databases/tables

Order Table

• Single database
• Large monolithic table
• Performance bottleneck

↓ sharding

Shard 0

DB: cluster_alpha
Table: order_000
Table: order_001
Table: order_...

Shard 1

DB: cluster_beta
Table: order_000
Table: order_001
Table: order_...

Shard 2

DB: cluster_gamma
Table: order_000
Table: order_001
Table: order_...

Tech-stack: I prefer to sharding-jdbc if the language is JAVA. Using sharding-jdbc, the design of splitting algorithm can be implemented by ourselves.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
// pseudocode
public class CoursePreciseDSShardingAlgorithm implements PreciseShardingAlgorithm<Long> {

    @Override
    public String doSharding(Collection<String> availableTables, PreciseShardingValue<BigInteger> snowflakeId) {
				
        // let's say there are 5 horizontal 
        // 		splitting tables in one sharding DB
        int tableNo = snowflakeId % 5; 
        if (availableTables.contains(tableNo)) {
            return tableNo;
        }
        throw new UnsupportedOperationException("route to shard"+ shardNo +"failed");
    }
}

Above pseudocode is only for table level sharding , I haven’t implemented DB-level sharding before , honestly. but sharding-jdbc absolutely can do this. However, the sharding brings some other issues.

There is a SQL:

1
select * from order where order_id = #{}

Through the above algorithm, query calls the correct DB shard and order_x table.

Another one:

1
select * from order where order_country = #{} and order_type = #{}

Through the above algorithm, query calls all DB shards and order_x tables , this is exactly what we don’t want to see.

Should make decisions based on the business requirements and existing database queries conditions, should proceed things with caution, how many DB and tables should be sharded? What are the sharding keys ? algorithm logic ? and whether additional indexes should be added within the tables…

If the business data evolved to here , that is not interesting , that means it is time to more hard work . I wish I won’t encounter such a tough tech situation , God bless ^^

← Back to Home