Database Partitioning

What is a database table partitioning?

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

Angry Summer GIF by Joan Kamberaj

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

What are different ways of partitioning?

snl season 44 GIF by Saturday Night Live

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.
Shark Tank Difference GIF by ABC Network

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s