PostgreSQL Table partitions now supported in Flyway
This blog post was originally authored by Prajakta Tamhankar, whose insights and expertise shaped much of the content you’ll read here.
We are thrilled to announce the General Availability (GA) of Table Partitions for PostgreSQL users in Flyway v8.0.2. This new functionality is designed to enhance your database management experience by providing robust support for table partitions, including sub-partitions and range partitions.
What are Partitions and Sub-partitions?
Partitions are a powerful database feature that allows you to divide a large table into smaller, more manageable pieces called partitions. Each partition can be managed and queried independently, which can significantly improve performance and simplify maintenance tasks. For example, a sales database might partition a large sales table by year, with each year’s data stored in a separate partition.
Sub-partitions take this concept a step further by allowing partitions to be divided into even smaller sub-partitions. This hierarchical structure can be particularly useful for complex datasets that require multiple levels of organization. For instance, a sales table partitioned by year could have sub-partitions for each month within a year.
Database Administrators (DBAs) and developers frequently use table partitions to manage large datasets more efficiently. Partitions can significantly improve query performance by allowing the database to scan only the relevant partitions rather than the entire table. This can lead to faster query response times and reduced resource consumption.
Sub-partitions add another layer of flexibility, enabling more granular control over data organization. This can be particularly beneficial in scenarios where data needs to be accessed or managed at multiple levels of granularity.
Depth of Functionality
With the release of Flyway v8.0.2, we are introducing comprehensive support for table partitions, including:
- Range Partitions: These allow you to partition your tables based on a range of values, such as dates or numerical ranges. This is particularly useful for time-series data or any dataset where data naturally falls into contiguous ranges. As a part of our functionality, we support partitioning by range, while ignoring range partitions for comparison.
- Sub-partitions: You can now create sub-partitions within your primary partitions, providing an additional layer of data organization and management.
We support all the partitioning methods – Range, Hash and List.
Benefits of the New Functionality
The new table partitioning functionality in Flyway v8.0.2 will enable DBAs and developers to manage database changes more efficiently and effectively. Here are some key benefits:
- Improved Performance: By partitioning large tables, you can reduce the amount of data scanned during queries, leading to faster query performance.
- Simplified Maintenance: Partitions make it easier to manage large datasets by allowing you to perform maintenance tasks, such as backups and archiving, on individual partitions rather than the entire table.
- Enhanced Flexibility: With support for sub-partitions and range partitions, you can organize your data in a way that best suits your application’s needs.
Examples of Using Partitions in Flyway
Here are some practical examples of how you can use partitions in Flyway:
1.Creating Partitions:
|
1 2 3 4 5 6 7 8 |
CREATE TABLE sales ( id SERIAL PRIMARY KEY, sale_date DATE NOT NULL, amount DECIMAL(10, 2) NOT NULL ) PARTITION BY RANGE (sale_date); CREATE TABLE sales_2023 PARTITION OF sales FOR VALUES FROM ('2023-01-01') TO ('2024-01-01'); |
2.Creating Sub-partitions:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE sales ( id SERIAL PRIMARY KEY, sale_date DATE NOT NULL, amount DECIMAL(10, 2) NOT NULL ) PARTITION BY RANGE (sale_date); CREATE TABLE sales_2023 PARTITION OF sales FOR VALUES FROM ('2023-01-01') TO ('2024-01-01') PARTITION BY RANGE (EXTRACT(MONTH FROM sale_date)); CREATE TABLE sales_2023_jan PARTITION OF sales_2023 FOR VALUES FROM (1) TO (2); |
3.Managing Partitions with Flyway:
- a. Upgrade to the Flyway v8.0.2 or later
- b. Create Migrations: Make changes involving partitions to your development database, then use Flyway to create a new project and generate migrations for the partitioned tables.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
CREATE SCHEMA new_schema; -- hash partition CREATE TABLE new_schema.hash_partitioned ( city_id int not null, logdate date not null, peaktemp int, unitsales int ) PARTITION BY HASH (logdate); CREATE TABLE new_schema.hash_partition_0 PARTITION OF new_schema.hash_partitioned FOR VALUES WITH (MODULUS 2, REMAINDER 0); CREATE TABLE new_schema.hash_partition_1 PARTITION OF new_schema.hash_partitioned FOR VALUES WITH (MODULUS 2, REMAINDER 1); -- range partition CREATE TABLE new_schema.range_partitioned ( city_id int not null, logdate date not null ) PARTITION BY RANGE (logdate); CREATE TABLE new_schema.range_partition PARTITION OF new_schema.range_partitioned FOR VALUES FROM ('2006-03-01') TO ('2006-04-01'); CREATE INDEX idx_logdate ON new_schema.range_partition(logdate); -- list CREATE TABLE new_schema.list_partitioned ( city_id int not null, cityname VARCHAR, logdate date not null, peaktemp int, unitsales int ) PARTITION BY LIST (cityname); CREATE INDEX idx_cityname ON new_schema.list_partitioned(cityname); CREATE INDEX idx_peaktemp ON new_schema.list_partitioned(peaktemp); CREATE TABLE new_schema.list_partition_A PARTITION OF new_schema.list_partitioned FOR VALUES IN ('A'); CREATE INDEX idx_unitsales ON new_schema.list_partition_A(unitsales); CREATE TABLE new_schema.list_partition_BC PARTITION OF new_schema.list_partitioned FOR VALUES IN ('B', 'C'); |
- c. Run Migrations: Apply the generated migrations to your target database to implement the partitioned tables.
Best Practices for Using Partitions
To make the most out of table partitions in Flyway, consider the following best practices:
- Plan Your Partitioning Strategy: Before implementing partitions, carefully plan how you will partition your tables. Consider factors such as data access patterns, query performance, and maintenance requirements.
- Use Range Partitions for Time-Series Data: Range partitions are particularly effective for time-series data. Partitioning by date ranges can significantly improve query performance and simplify data management.
- Keep Partitions Manageable: Avoid creating too many partitions, as this can lead to increased complexity and overhead. Aim for a balance between the number of partitions and the size of each partition.
- Monitor Partition Performance: Regularly monitor the performance of your partitions to ensure they are providing the expected benefits. Adjust your partitioning strategy as needed based on performance metrics.
- Document Your Partitioning Strategy: Maintain clear documentation of your partitioning strategy, including the rationale behind your partitioning decisions and any specific configurations. This can help with future maintenance and troubleshooting.
We are excited to see how you will leverage table partitions to optimize your PostgreSQL databases. As always, we value your feedback and look forward to hearing about your experiences with this new functionality.
For more detailed instructions and examples on how to use table partitions in Flyway, please refer to our documentation.
Thank you for being a part of our community and helping us make our tools better for everyone.
Tools in this post
You may also like
Product article
Defining and Using Multiple Flyway Environments in TOML
Blog