Hi, we are new to YugabyteDB. We have huge data to handle, and we are investigating if Yugabyte can help us.
The requirement is as follows:
Data structure: 5 levels of master-detail table
Record count: the detail table of the last level may contain 50 billions of records.
Record size: each record contains an average of 30 fields, total record size < 1k bytes.
Typical query 1:
We have 100 Ids in the first level, with all 5 levels of joining, it may return 1 million records with all 5 levels. However, pagination will be used, and each page is 200 records.
Query per second: 4,000 request/second. We expect the execution time to be less than 1 second.
Typical query 2:
30% of the records in the last detail table need to be scanned and updated.
Frequency of this query: every 60 seconds or shorter.
The YugabyteDB solution
The solution I received is to use Yugabyte, and it works like this:
For typical query 1
Do NOT use JOIN, instead, use SELECT FROM table_level_1 WHERE ...
Suppose 200 results are returned in the last step, then send 200 concurrent queries likes this: SELECT FROM table_level_2 WHERE id=....
Suppose 1000 results are returned in the last step, then send 1000 concurrent queries likes this: SELECT FROM table_level_3 WHERE id=....
repeat until all the levels are JOINed in the application code. And in the last level, we will send 1 million concurrent queries like this: SELECT FROM table_level_5 WHERE id=...
For query 2
for 50Billion records, 30% is 15B.
We use 1000 machines, each sends 15 million commands to scan and update each individual record. If this cannot be completed in 60 seconds, then use 10000 machines, and each sends 1.5 million commands.
My question is
Is this a practical way? I am curious about this 1 million concurrent SQL requests, I think this is a better practice: WHERE id IN (...), which is only one request.
Hi, I’ll look at it further (if you can share the create table statements to reproduce it may help), but in short
Query 1 should not be a problem with the right index, but the critical question is: which table drives the pagination (the ORDER BY part of the LIMIT clause)? Ideally, such query starts with this table, where an index provides rows in the proper order, and other joins preserve the order
Query 1 returns 1000 records, paginated by 200 records. How many pages are typically read?
Query 2 reads and updates 15 billion records? What kind of update is it? Every 60 seconds? What kind of update is it? That’s huge and may have consequences on Query1
The solution I received is to use Yugabyte, and it works like this
Using YugabyteDB is a good idea for such volumes; however, the solution you describe is not. Running millions of requests that process one row is an anti-pattern and optimization, in all SQL databases, is to do all with one SQL statement that the database can optimize, batch and parallelize.
I’m curious to know where this recommendation comes from because, instead, the opposite should be done: avoid network roundtrips with the database, and let the database do the joins and just provide the right indexes for it
Thank you for your reply. I will give more details. In this post, I just want confirm that this is anti-pattern (A yes/no question):
//Send many SQL queries in GO:
runSQL('select * from table where id=1');
runSQL('select * from table where id=2');
runSQL('select * from table where id=3');
runSQL('select * from table where id=4');
runSQL('select * from table where id=5');
And this is suggested:
//Send only one SQL query:
runSQL('select * from table where id in (1,2,3,4,5...)');
Yes, I confirm. Sending many SQL queries is an anti-pattern and your suggestion of one query is good.
An example:
yugabyte=# explain(analyze, dist,summary off) select * from demo where id=1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Index Scan using demo_pkey on demo (cost=0.00..4.11 rows=1 width=8) (actual time=0.332..0.334 rows=1 loops=1)
Index Cond: (id = 1)
Storage Table Read Requests: 1
Storage Table Read Execution Time: 0.282 ms
Storage Table Rows Scanned: 1
(5 rows)
Time: 22.095 ms
yugabyte=# explain(analyze, dist,summary off) select * from demo where id=2;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Index Scan using demo_pkey on demo (cost=0.00..4.11 rows=1 width=8) (actual time=0.590..0.591 rows=1 loops=1)
Index Cond: (id = 2)
Storage Table Read Requests: 1
Storage Table Read Execution Time: 0.486 ms
Storage Table Rows Scanned: 1
(5 rows)
Time: 19.024 ms
yugabyte=# explain(analyze, dist,summary off) select * from demo where id=3;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Index Scan using demo_pkey on demo (cost=0.00..4.11 rows=1 width=8) (actual time=0.754..0.756 rows=1 loops=1)
Index Cond: (id = 3)
Storage Table Read Requests: 1
Storage Table Read Execution Time: 0.648 ms
Storage Table Rows Scanned: 1
(5 rows)
Time: 19.339 ms
Each call took 20 milliseconds (the roundtrip to the database), and each one is a read request to the storage (0.5 milliseconds here)
One query can read all rows in one call and one read request:
yugabyte=# explain(analyze, dist,summary off) select * from demo where id in (1,2,3);
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Index Scan using demo_pkey on demo (cost=0.00..4.11 rows=1 width=8) (actual time=0.776..0.778 rows=3 loops=1)
Index Cond: (id = ANY ('{1,2,3}'::bigint[]))
Storage Table Read Requests: 1
Storage Table Read Execution Time: 0.665 ms
Storage Table Rows Scanned: 3
(5 rows)
Great! Thank you for your detailed explenantion!
Next question:
If the system is implemented according to the solution I posted above, do you think any part of the system will be overwhelmed as the mount of data increases? Network, disk, CPU, or any components in YugabyteDB will become bottleneck? Will adding more YugabyteDB node solve the bottleneck?
Based on the execution plan above, the table size doesn’t impact this type of access. I used an example with 1000 rows, but the same will occur with 1000 million rows because it involves an index range scan. The key factor here is the number of rows being returned, not the size of the table.
If multiple queries of this nature execute concurrently, adding more nodes will indeed improve scalability. All components in YugabyteDB are designed to scale linearly. However, scalability also relies on the application’s behavior and how the indexes are constructed. If thousands of sessions are all reading the same row, then that particular row becomes a bottleneck, by design. On the other hand, if they are reading different rows, the workload is distributed across the nodes without requiring synchronization.