does sharding affect querying speed

Question

I am implementing horizontal scaling or my sql database. I am spreading my table onto multiple dataservers with sharding designs. I know that this technique is very good for handling large volumes of data, and/or big data analysis. Also if spread across with geographical design, could serve some users data faster on specific regions. Besides all these, does sharding increase query speed by much? Since indexes already come into place. What would be other horizontal scaling options than sharding, or tips on that ?

To sum up my question: Imagine 2 identical table structures, one has 100 million rows, and the other has 1 million rows. They both have indexes set up, will 1 million one run queries faster than the other? Horizontal scaling help with that ? (Also more tips)

Answer 1

I am spreading my table onto multiple dataservers with sharding designs. I know that this technique is very good for handling large volumes of data, and/or big data analysis.

Not necessarily. It's just an alternative way to provision hardware resources to the database. It's no more performant than vertical provisioning, when done properly, and can be more complex and costly to manage.

Also if spread across with geographical design, could serve some users data faster on specific regions.

It's not really realistic for most people to implement such a design for their database perfectly. In fact, it can cause your queries to be slower for users when they need to wait for data to be pulled from multiple shards and re-assembled.

Besides all these, does sharding increase query speed by much? Since indexes already come into place.

Indexes already split up your data in an efficient manner, with a search time complexity of O(log(n)). Sharding is just distributing the data in a linear fashion, O(n), across a network. Theoretically, with the same total provisioning of hardware, proper indexing will always be equal to or faster than sharding. O(log(n)) is literally exponentially faster than O(n). But of course nothing stops you from implementing both.

Imagine 2 identical table structures, one has 100 million rows, and the other has 1 million rows. They both have indexes set up, will 1 million one run queries faster than the other?

Depends on the query. But generally speaking, with how efficient indexes are at storing the data, the runtime difference between seeking for a set of rows from a 1 million row table vs a 100 million row table vs a 1 billion row table vs a 1 trillion row table, etc, will be negligible. The difference will be somewhere on the order of nanoseconds to milliseconds. That's all just from indexing, without sharding.

Horizontal scaling help with that ?

There's nothing to help in the above scenario when indexing is properly implemented and doing its job well.

Answer 2

I upvoted the answer from J.D. but I'll add some points of my own.

Searching an index data structure for orders of magnitude more rows does have some difference. The more rows, the more the index depth. Traversing more levels of depth has some performance impact. It is logarithmic, as J.D. points out, so the cost increases slowly, but it's measurable. Read https://www.percona.com/blog/the_depth_of_a_b_tree/ for some of the math.

Given this, it might make sense to use sharding if you need your index searches in each respective shard to be more shallow. However, at the end of the day, the complexity of implementing sharding is too large to justify the minor performance improvement.

J.D. alludes to the next point, but I want to state it more clearly: sharding only has a performance advantage if a given query naturally visits only one shard.

For example, if you shard by date, but then you need to run a query to gather all data for a specific user, regardless of date. This means your query has to run the query for that user on every shard, because you don't know which dates for which the user might have data. Then implement some kind of collection code to merge the results from the queries on each shard.

This means the more shards you have, the longer it takes. Even if you can run queries against shards in parallel (more code you have to write), you'll at least be waiting for the slowest shard. You might think all shards are equal, therefore they'll all complete their respective query at the same time. Let me know how that turns out. :-)

Where I have seen sharding used more effectively is scaling out writes.

Reading data can usually be sped up by using RAM to cache the data. But writes on an ACID database must write to storage, and that's much slower. There's a finite limit to the throughput you can do on a given storage device, therefore a cap to the rate of writes. So if you can scale out to multiple servers with sharding, this can us many storage devices, and multiply the effective throughput of your writes.

Answer 3

The two previous responses focus on the latency of a single query performed using an index. They rightfully point out that sharding the database won’t improve the latency of a single isolated query. However, that’s not the problem that sharding (and horizontal scaling in general) aims to solve. They aim to enable capacity and performance scaling.

The purpose of scaling out a database is to increase the capacity of the database beyond the limits of a single node, and to scale the performance by increasing the rate of read/write operations, and the duration (latency) it takes to perform long and complex queries.

Sharding has many considerations you should think about. The main thing I would consider is that definitionally you are breaking the application/data abstraction (the application needs to know about the sharding model) and that ACID properties and database functionality won't hold across shards. The answer to "what are my other options”' would be true distributed systems, and there are a few that are transactional relational systems. You should check out Regatta. We built it to be exactly that - a distributed transactional relational database, and it always looks like a simple single node instance to the app (no broken abstraction). ANSI SQL + Linear Performance Scaling + Transactional Analytics. The analytical performance is such that you can run complex SELECTS, JOINS, etc. directly on the transactional data in flight, without degrading the transactional throughput. Our performance testing so far is well north of the scale (row counts) you're describing. And hey, Regatta can perform joins that their intermediate data size exceeds the RAM of a single node by distributing it over the cluster nodes. Disclosure: I'm a Regatta employee, so I naturally have bias, but check us out! Oh, and to answer your example ("sum up") question - that can't be answered without knowing the query, schema, and indexes. If you had a SELECT query where there was a WHERE clause that mapped to the index that the SQL Execution engine if it were smart would resolve it and just get those rows, regardless of the number of rows. BUT, there are a lot of "if this then that" in the answer. The generalized answer (for most systems) is that with more rows/data the generalized queries would take longer. With Regatta the overall throughput scales linearly as you add nodes, and transaction latencies stay pretty constant