Partition (database)

Partitioning criteria and methods
– Range partitioning selects a partition based on a certain range of values for the partitioning key.
– List partitioning assigns a partition based on a list of specific values for the partitioning key.
– Composite partitioning allows for combinations of range and hash partitioning.
– Round-robin partitioning ensures uniform data distribution by assigning tuples based on insertion order.
– Hash partitioning applies a hash function to the partitioning key to determine the partition number.
– Horizontal partitioning involves putting different rows into different tables.
– Vertical partitioning involves creating tables with fewer columns and using additional tables to store the remaining columns.
– Separate smaller databases can be built for partitioning, each with its own tables, indices, and transaction logs.
– Selected elements, such as a single table, can be split for partitioning.
– A view with a union can be created to provide a complete view of all partitions.

Horizontal partitioning
– Customers with ZIP codes less than 50000 are stored in CustomersEast.
– Customers with ZIP codes greater than or equal to 50000 are stored in CustomersWest.
– Horizontal partitioning involves distributing rows across different tables.
– A view can be created to combine the partitioned tables and provide a complete view.
– Horizontal partitioning is useful for managing large datasets and improving query performance.

Vertical partitioning
– Vertical partitioning involves splitting columns into separate tables.
– It can be done to normalize data or further partition columns even when already normalized.
– Static data can be stored separately from dynamic data for faster access.
– Vertical partitioning can be achieved by using different physical machines for different columns.
– Creating a view across the partitioned tables can restore the original table with a performance penalty.

Sharding
– Shard is a term used in database architecture related to partitioning.
– It refers to a subset of data that is stored separately from the rest of the database.
– Sharding is often used in distributed database systems to improve scalability and performance.
– Each shard can be stored on a different node or server.
– Sharding can be based on different criteria such as range, hash, or list partitioning.

Database architecture
– Partitioning is a concept in database architecture that involves dividing data into smaller subsets.
– Different partitioning criteria and methods can be used, such as range, list, composite, round-robin, and hash partitioning.
– Horizontal partitioning involves distributing rows across different tables, while vertical partitioning involves splitting columns into separate tables.
– Sharding is a technique used in distributed database systems to improve scalability and performance by storing subsets of data separately.
– Database architecture can be designed to include partitioning and sharding strategies to optimize data management.

Partition (database) (Wikipedia)

This article needs additional citations for verification. Please help improve this article by adding citations to reliable sources. Unsourced material may be challenged and removed. Find sources: "Partition" database – news · newspapers · books · scholar · JSTOR (March 2014) (Learn how and when to remove this template message)

A partition is a division of a logical database or its constituent elements into distinct independent parts. Database partitioning is normally done for manageability, performance or availability reasons, or for load balancing. It is popular in distributed database management systems, where each partition may be spread over multiple nodes, with users at the node performing local transactions on the partition. This increases performance for sites that have regular transactions involving certain views of data, whilst maintaining availability and security.