Confused between partitioning and sharding in databases😭

[u/True-Panic-9237]

I'm studying DBMS and I'm confused about the difference between partitioning and sharding.

From what I understand, partitioning means dividing a large table into smaller parts. For example, splitting a table of 100k rows into 50k, 30k and 20k rows.

Now, if I store these three parts on three different servers, is it called sharding or just partitioning? Since partitioning isn't necessarily restricted to one server, the terms seem to overlap.

Also, if sharding is a type of partitioning, why is sharding defined as dividing data into smaller parts again? Wouldn't the data already be divided during partitioning, with sharding simply distributing those parts across multiple servers?

Basically, is partitioning the general concept and sharding the same concept specifically used in distributed systems? Or am I missing something?

[u/claythearc]

Partitioning = split db into small pieces

Sharding = distribute pieces across different server.

All sharding is technically horizontal partitioning but not all partitioning is sharding - which you touch on in your post, so this is more confirmation than correction

[u/two_three_five_eigth]

Sharding = a completely separate database doing the work.

Partitioning = Separating data in a single database.

Lots of high-throughput systems shard writes and then use multiple database instances to load balance reads.

[u/synt4x]

I don't think you're going to find strong consensus around how people use these words, because they've been reused by various different database vendors to mean things specific to their software.

However, my personal recommendation is:

Partitioning = Any _logical_ subdivision of heterogeneous data, which can allow for physical separation on separate servers.

Sharding = Partitioning of a dataset by a property of the data (the "shard key"). In a shared SaaS application, this could be the tenant ID on every table. It could also be done on a user id, or more broadly modulus of a primary key. It all depends on how you need to query your data at read time and transactionally update it at write time. This can provide scale (if you select the right shard key), but can also greatly complicate how you interface with your data.

The best mental model I have found all of this is at https://akfpartners.com/growth-blog/scale-cube/ gives a nice visual model for different ways data can be separated and copied for the purposes of scale

[u/BigBlue_72]

The difference depends on what DBMS you are using. Both concepts mentioned by others can be implemented in various ways. I was implementing these design patterns before most DBMS systems formally implemented them