![]() There are multiple methods of partitioning tables such as PARTITION BY RANGE, LIST, and HASH. Partitioning the tables in the database is very useful to keep them efficient and allows the query process to scan tables quickly. That’s all about using PARTITION BY in PostgreSQL. Use the following query to get the data stored in the second partition: SELECT * FROM employee_p2 Running the above code displays the data stored in the first partition: Insert some data in the table and use this query to select data from the first partition: SELECT * FROM employee_p1 Use this code to create another partition of the employee table with a modulus value equal to 4 and 1 as the remainder: CREATE TABLE employee_p2 PARTITION OF employeeįOR VALUES WITH (MODULUS 4, REMAINDER 1) The remainder value is calculated for each row using the hash value and then stores the row in the partition accordingly: The modulus value determines the number of partitions a table will be divided into and the remainder determines where the row will be stored. The above code creates a partition with hash values containing modulus and the remainder on the id column. Use the following code to create a HASH partition from the employee table using its id column: CREATE TABLE employee_p1 PARTITION OF employeeįOR VALUES WITH (MODULUS 4, REMAINDER 0) The table has been created successfully having multiple columns but the partition will be applied on the id column: Use this query to check the data from the second partition: SELECT * FROM employee_hr įollow this example to create an employee table with the PARTITION BY HASH statement at the end of it: CREATE TABLE employee ( Insert the same values in the table and check if the first partition has any data stored in it: SELECT * FROM employee_it Įxecuting the above query displays the data stored in the first partition: Use the following query to create another partition containing a list having “ HR” and “ Admin” values: CREATE TABLE employee_hr PARTITION OF employee The above query creates a partition with the list having “ IT” and “ Software” values: Provide values for the LIST partition while creating a partition to the employee table: CREATE TABLE employee_it PARTITION OF employee Use another example to create an employee table and apply PARTITION BY LIST on the department column: CREATE TABLE employee ( ![]() The data stored in this range has been displayed on the screen: Use the following query to fetch all the data stored in the partition range less than the start of the year 2023 which is the second partition: SELECT * FROM my_partitioned_table Use the following query to insert data into the table: INSERT INTO my_partitioned_table (id, name, created_at) The second partition has been created for the table: The following query creates a second partition by providing the range from the mid of the year 2022 to the end of 2022: CREATE TABLE my_partition_2 PARTITION OF my_partitioned_table The above query creates a partition by giving the range of dates from the first of the year 2023 to mid of 2023: Use the following query to create partitions in the table by giving ranges for the created_at column: CREATE TABLE my_partition_1 PARTITION OF my_partitioned_table The my_partitioned_table has been created successfully: The following example creates a table with multiple fields to store data and apply to partition using the created_at column: CREATE TABLE my_partitioned_table ( The HASH partition has the column name which acts as the hash key for the partitioning of the table: PARTITION BY HASH (col_name) The change in this query is just the keyword of LIST instead of RANGE which contains the column name and divides the table using that list: PARTITION BY LIST (col_name) RANGE partition will act on the given ranges and divides the tables according to these ranges: PARTITION BY RANGE(col_name) ![]() The following is the syntax containing the PARTITION BY RANGE statement with the name of the column on which the partition will be applied. It also helps PostgreSQL to scale by splitting large logical tables into smaller physical tables. Using partitioning allows the query processor to scan smaller tables and indexes to fetch data more efficiently. Partition helps the database by dividing the large tables into smaller chunks to have better control and manageability over them. This guide will explain the use of PARTITION BY in PostgreSQL. In any case, partitioning is a good solution to keep the performance of the database up to date. The table's size can not exceed 32 GB in normal circumstances but before reaching that size performance issues may arise. Partition is crucial for the performance of databases with extensive tables and complicated schemas.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |