Database partitioning - Horizontal and Vertical sharding - Difference between Normalization and Row Splitting?

Question

I am trying to grasp the different concepts of Database Partitioning and this is what I understood of it:

Horizontal Partitioning/Sharding: Splitting a table into different tables that will contain a subset of the rows that were in the initial table (an example that I have seen a lot if splitting a Users table by Continent, like a sub table for North America, another one for Europe, etc...). Each partition being in a different physical location (understand 'machine'). As I understood it, Horizontal Partitioning and Sharding are the exact same thing(?).

Vertical Partitioning: From what I understood (http://technet.microsoft.com/en-us/library/ms178148%28v=sql.105%29.aspx ), there are 2 sorts of Vertical Partitioning:

• Normalization (which consists of removing redundancies from a the database by splitting tables and linking them with a foreign key).

• Row Splitting, here is what I don't understand, what is the difference between Normalization and Row Splitting? In what those 2 techniques differ from each other?

I have also read in this post (https://stackoverflow.com/questions/11707879/difference-between-scaling-horizontally-and-vertically-for-databases ) that the difference between Horizontal Partitioning and Vertical Partitioning is that in the first you scale by adding more machines, while in the second one you scale by adding more power (CPU, RAM) to your existing machine, is that a correct definition? I thought that the core difference between those 2 techniques resides in the way you split your tables. This answer does make sense according to MongoDB's definition of Vertical Partitioning: http://docs.mongodb.org/manual/core/sharding-introduction/

but it goes in contradiction with other answers or articles I have come across:

https://stackoverflow.com/questions/18302773/what-are-horizontal-and-vertical-partitions-in-database-and-what-is-the-differen http://technet.microsoft.com/en-us/library/ms178148%28v=sql.105%29.aspx http://building.wanelo.com/post/42361472646/the-case-for-vertical-sharding

I am sorry for the load of questions but I am a bit confused as a lot of different websites that I have came across say different things.

Any help clarifying would be greatly appreciated. Any link to a clear and simple demonstration with a few tables would also be very helpful.

Answer 1

Although Normalization and partitioning both produce a rearrangement of the columns between tables they have very different purposes.

Normalization is first considered during logical datamodel design. It is a set of rules which ensure that each entity type has a well-defined primary key and each non-key attribute depends solely and fully upon that primary key.

Partitioning comes in during physical database design, when we start to map logical attributes to physical columns and determine the operational characteristics required from the system. Sometimes it is an optimisation added after testing under load because performance was found to be inadequate. It can also play a role in implementing a data retention policy.

In partitioning we recognise that a table is made from rows and columns. When we partition we separate some of those rows (or columns) from the others and hold them in a physically different location.

Horizontal partitioning is when some rows are stored in one table, and some in another. There could be many sub-tables. A typical example is when currently-active transactional data is separated from old "archive" data. This keeps "hot" data compact, with associated performance improvements. We many be able to make the archive tables read-only, compressed and on cheaper disk, too.

As the next step each partition may be moved onto separate hardware. This is commonly know as "sharding." Advantages include being able to use many cheaper boxes rather than one very large, very expensive server, and being able to position a user's data geographically close to her. The cost is increased application complexity. Some DBMS incorporate this ability natively.

Vertical partitioning is when some columns are moved to a different table or tables. Similar to horizontal partitioning the motivation is to keep the "hot" table small so access is faster. Say you run an e-marketing company. 99% for the time you need a person's name and email address and nothing else. These will go in one table and all the other stuff which is useful but seldom-used - birthday, golf handicap, PA's phone number etc. - go in a different table. It can also help when the partitions have different update regimes or are owned by different sections of the business. The two tables can have the same primary key column, and corresponding rows could have the same key value. While it is possible to have multiple vertical partitions for a table, and to shard vertically, I've never come across it.

Vertical and horizontal partitioning can be mixed. One may choose to keep all closed orders in a single table and open ones in a separate table i.e. two horizontal partitions. For the open orders, order data may be in one vertical partition and fulfilment data in a separate partition.

The techniques I've talked about are ways to change the design to improve performance. Scaling is when you change the hardware. One can scale up by buying a bigger box with more RAM, CPU or faster disk, or scale out by moving some of the work onto a different box. Scale up is sometimes called scaling vertically whereas scale out can be called horizontal scaling. While horizontal scaling and sharding have an obvious relationship they are not synonymous. It would be possible to use replication technologies to copy an entire database to another location for use by the users there, thus achieving scale-out, without having to partition any tables.

Answer 2

I am not sure what you mean by "row splitting". You may mean the same thing as what's commonly called "table decomposition". This involves decomposing a table into two tables. They generally have different primary keys and often have different numbers of rows. Every column from the original table goes in one table or the other. In addition, an extra column is added in one table that serves as a foreign key reference to the other table's primary key.

What does decomposition have to do with normalization? Well normalization classically refers to adherence to the restrictions on certain well known normal forms. These normal forms are known as:

• First Normal form
• Second Normal form
• Third Normal form
• Boyce-Codd Normal form
• Fourth normal form
• Fifh Normal form.

Two other forms you might see mentioned are Sixth Normal form and Domain-Key Normal form

In general, when you analyze a system of tables and determine that it departs from one of these normal forms, you can obtain conformance by decomposing tables, where the particular decomposition is described in the tutorial for that normal form.

There are benefits and drawbacks to conforming to each of the normal forms. You need to learn how to normalize data and when not to.

Decomposing tables is a transformation on the logical model of the data. It's visible to anyone who looks at the table structure.

Partitioning tables usually refers to a split that occurs within a data table, but the table remains defined as just one table. The placement of certain rows on certain disks might be an example somewhat like the example you cited in your question.

This is a split that transforms the physical model of the data, but not the logical model of the data. This split will be transparent to someone who looks at the structure of the system of tables but not inside each table.

Partitioning is generally done for the sake of speed.

Normalization is generally done for the sake of eliminating certain kinds of update anomalies, in order to make managing the data correctly a little easier. It does have implications for speed, both positive and negative.

Answer 3

• Normalisation: methodology of data organisation, resulting in not storing the same information several times, possibly at the expense of data update and access speed (denormalisation might be used to reduce memory and cpu load required by table join).
• Partitioning: splitting the datas of a table in several tables

Vertical partitioning: Splitting the data by columns. (less accessed columns may be stored on storage having slower access, allowing more accessed columns to be more easily cached)

Horizontal partitioning: Splitting the data by group of lines naturally given its primary keys (Row Splitting). Allow lighter joins. Union views might provide the full original table view

• Sharding: Partitionning over several server, allowing parallel access (of different datas as opposed to replication) and, as such, memory and cpu load distribution.

• Replication: Another story than partitionning and sharding: Table duplication on several servers, ensuring availability and failover mecanisms.

• Scalability: Ensured by both Sharding and Replication.

Answer 4

Sharding is a subset of horizontal scaling (so sharding is an example of horizontal scaling) but not all horizontal scaling is sharding (as the example above shows, you can do complete replication with horizontal scaling also)