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.