Database Partitioning vs Sharding

 

Partitioning is dividing your logical data into multiple entity to achieve more performance,Availability or maintainability. It has two types:

 

  1. Vertical Partitioning:-“Vertical partitioning” is the act of splitting up the data stored in one entity into multiple entities – again for space and performance reasons.  For example, a customer might only have one billing address, yet I might choose to put the billing address information into a separate table with a CustomerId reference so that I have the flexibility to move that information into a separate database, or different security context, etc.
  2. Horizontal Partitioning(Sharding):- When you shard a database, you create replica’s of the schema, and then divide what data is stored in each shard based on a shard key.  For example, I might shard my customer database using CustomerId as a shard key – I’d store ranges 0-10000 in one shard and 10001-20000 in a different shard.  When choosing a shard key, the DBA will typically look at data-access patterns and space issues to ensure that they are distributing load and space across shards evenly.This also improves the write speed because Sharding is like Shared nothing architecture.

Summary: Partitioning is generic term which is dividing the logical data into multiple entities. Either you can do it horizontally by dividing the data of an Entity by replicating the schema over multiple machine and dividing the data over multiple machine based on the shard key (Sharding). or we can divide the data into multiple entities on the same machine which is Vertical Scaling.

 

Finding Nth highest salary using SQL interview question

This is one of very common question to find the Nth highest salary using sql.

Create table Command:-

 

  1. Generic Solution:- This solution use correlated subquery. Below given query can be use to find 2nd, 3rd……nth highest salary in an employee table. This solution will work on any database but it can little slower due to subquery.

     
  2. Mysql Specific Solution:- In Mysql we can use limit to solve this problem and it is fast solution but it is vendor specific.

     

    Please add comment if there is any better solution. Thanks in advance.