Skip to content

Latest commit

 

History

History
62 lines (41 loc) · 4.1 KB

19. Partitioning.md

File metadata and controls

62 lines (41 loc) · 4.1 KB
Raw

PARTITIONING

Partitioning in a Database Management System (DBMS) refers to the technique of dividing a large database table into smaller logical units, more manageable segments or partitions based on a specific criterion.

Each partition operates as a separate sub-table that holds a distinct subset of the data, i.e. each partition can be treated individually by the DBMS.

The goal of partitioning is to enhance database performance, manageability, and maintenance, especially for tables with a large number of rows or high data volumes.

VERTICAL AND HORIZONTAL PARTITIONING

image

EXAMPLE OF PARTITIONING

image

VERTICAL PARTITIONING

image

HORIZONTAL PARTITIONING

image

WHEN TO APPLY PARTITIONING

  1. LARGE TABLES
    Partitioning is particularly beneficial for large tables with millions or billions of rows. It helps improve query performance, data management, and maintenance for such tables.
  2. IMPROVED QUERY PERFORMANCE
    If you have tables where certain columns are frequently accessed, and others are seldom used, vertical partitioning can be employed to separate frequently accessed columns from the rest, leading to improved query performance.
  3. DATA DISTRIBUTION AND PARALLELISM
    Horizontal partitioning is useful when you need to distribute data across multiple storage devices or servers. This enables load balancing and parallel processing, which can significantly enhance performance for large-scale systems.
  4. TIME-BASED DATA
    If you have time-based data, such as logs or historical records, partitioning the data by time intervals (e.g., daily, monthly) can help with efficient data retrieval and data management.
  5. EASE OF MAINTENANCE
    Partitioning can make data maintenance tasks, such as backups, restores, index rebuilds, and data archiving, more manageable and less intrusive to the overall system.
  6. COST-EFFECTIVE MANAGEMENT
    In some cases, partitioning can be a cost-effective way to manage large volumes of data since licensing costs for certain database systems might be based on the number of partitions used rather than the total size of the table.
  7. REGULATORY COMPLIANCE
    In scenarios where data retention policies or regulatory requirements dictate the separation of data for different periods, partitioning can help in efficiently handling data retention and archiving processes.
  8. PERFORMANCE OPTIMIZATION
    In OLAP (Online Analytical Processing) databases, partitioning can significantly enhance performance for data warehousing and analytics workloads.
  9. DATA SECURITY
    Partitioning can also be used to segregate sensitive data from less sensitive data, thereby improving data security and access control.
  10. OPTIMIZATION FOR SPECIFIC WORKLOADS
    For certain types of queries or reports that primarily access a subset of the data, partitioning can be designed to optimize those specific workloads.

ADVANTAGES OF PARTITIONING

  1. Improved query performance for large tables.
  2. Efficient data management and maintenance.
  3. Effective retrieval of time-based data.
  4. Cost-effective management of large data volumes.
  5. Load balancing and parallel processing capabilities.
  6. Compliance with data retention requirements.
  7. Enhanced data security and access control.
  8. Performance optimization for specific queries.

DISADVANTAGES OF PARTITIONING

  1. Increased database complexity.
  2. Not always guaranteed performance improvements.
  3. Additional query optimization overhead.
  4. Challenges in upgrading or migrating databases.
  5. Potential data skew and resource imbalance.
  6. Slower data insertion and updates.
  7. Limitations and restrictions in some database systems.
  8. Suboptimal strategies can reduce performance.