Database Partitioning

What is a database table partitioning?

Partitioning is the process of dividing large database table into smaller parts.

Partitioning helping in making queries fast, as after dividing large table, queries have to access smaller data.

What are different ways of partitioning?

Two ways:

• Horizontal partitioning
• Vertical partitioning

What is Vertical Partitioning?

Vertical table partitioning is mostly used to increase database Server performance especially in cases where a query retrieves all columns from a table that contains a number of very wide text or BLOB columns. 

So to reduce the access times, this big column or columns having text or blog is split to a separate table and can be joined original table if required using foreign key.

Another reason to do Vertical Partitioning will be to restrict access to sensitive data like: passwords, salary information etc. 

Vertical partitioning splits a table into two or more tables containing different columns:

What is Horizontal Partitioning?

Horizontal partitioning is about dividing database table into multiple tables with same schema (same columns) but fewer rows.

For example, if we divide the above discussed STUDENT_REPORT table into multiple tables where each table represents yearly reports.

This way queries requiring data for a specific year will only reference the appropriate table. 

Tables are horizontally partitioned based on a column which will be used for partitioning and the ranges associated to each partition. 

Partitioning column is usually a datetime column but all data types that are valid for use as index columns can be used as a partitioning column, except a timestamp column. 

What Is the Difference between Sharding and Partitioning?

Sharding and partitioning are both about breaking large data set into smaller parts.

• Partitioning is generic term for dividing large database table into smaller parts.
• Sharding is about dividing table on the basis of rows in separate schemas that are on different servers.