Recommendations on table partitioning

Hi Gaurav Pant,

Greetings!!

It looks like you're looking to migrate a massive table with a billion rows from non-partitioned to partitioned in Azure PostgreSQL, and you're keen on minimizing downtime. Here’s a comprehensive overview to help you out:

1. Deciding on Partition Type and Key:
   • Partition Method: Consider using range partitioning for time-based data or hash partitioning for even distribution of rows. Range partitioning is great for queries that filter on a date range.
   • Partition Key: Choose a column that is frequently used in query filters or joins. It should ideally have high cardinality (many unique values) to ensure an even distribution of data across partitions.
2. Number of Partitions:
   • While PostgreSQL allows a large number of partitions, it's usually best to limit it to a few hundred for manageability. Recommendations suggest having at most 1,000 partitions due to performance implications.
   • Think about partitioning based on time intervals (e.g., monthly or yearly) if applicable to your data.
3. Estimating Time for Partitioning:
   • The time taken can vary massively based on the current load, the size of the data, and the complexity of the partitioning logic.
   • Start by running a test on a smaller subset to gauge the performance.
4. Speeding Up the Partitioning Activity:
   • Offload the partitioning process during off-peak hours to minimize disruptions.
   • Use the pg_partman extension to automate the creation and management of partitions based on time periods.
5. Minimizing Disruption:
   • Consider using a new table and the INSERT INTO ... SELECT method to gradually move data from the non-partitioned table to the new partitioned table.
   • During this process, ensure that the application continues to read from the old table while using triggers or application logic to write into the new partitioned table.

Similar thread, it might help you: https://free.blessedness.top/en-us/answers/questions/1660620/what-is-the-best-partitioning-strategy-in-azure-da

Reference links:

https://free.blessedness.top/en-us/azure/postgresql/flexible-server/how-to-use-pg-partman

https://techcommunity.microsoft.com/blog/modernizationbestpracticesblog/optimizing-data-archival-with-partitioning-in-azure-postgresql-for-oracle-migrat/4399268

https://windowsforum.com/threads/azure-postgresql-flexible-server-online-migration-minimal-downtime-with-cdc.384313/

I hope this information helps. Please do let us know if you have any further queries.

Hi Gaurav Pant,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Hi Gaurav Pant,

Thanks for your patience.

Here are precise and actionable answers to your four key questions:

1.How to Decide on the Number of Partitions

Guidelines:

Range Partitioning (e.g., by date)

• Choose intervals that balance query performance and maintenance.
• Example: Monthly partitions for 5 years = 60 partitions.

Hash Partitioning:

• Use powers of 2 (e.g., 64, 128, 256) for even distribution.
• Match number of partitions to number of CPU cores or parallel workers.

Best Practices:

• Keep partitions between 50–200 for manageability.
• Avoid over-partitioning: too many partitions can slow planning and increase metadata overhead.
• Use partition pruning to ensure queries only scan relevant partitions

For more information, please refer the document: https://www.postgresql.org/docs/current/ddl-partitioning.html

2.Estimate the time taken for such activity?

Estimation Strategy:

• Run a test on 1% of the data using INSERT INTO partitioned_table SELECT ... FROM old_table WHERE ....
• Measure time and extrapolate.

Example:

• If 10 million rows take 10 minutes, expect ~16 hours for 1 billion rows.
• With parallel inserts and disabled indexes, this can drop to 4–6 hours.

Tools:

• Use timescaledb-parallel-copy or parallel COPY for faster ingestion

For more information, please refer the document: https://www.tigerdata.com/blog/handling-billions-of-rows-in-postgresql

3.How to speed up the partitioning activity?

Techniques:

• Disable indexes and constraints during bulk load.
• Use UNLOGGED tables temporarily to avoid WAL overhead.
• Use pg_partman for automated partition creation and maintenance. [free.blessedness.top]
• Load data in parallel batches using multiple sessions.

PostgreSQL Tuning Parameters:

• max_wal_size: Increase to 4–16 GB to reduce checkpoint frequency.
• checkpoint_timeout: Increase to 15–30 minutes to delay checkpoints.
• wal_buffers: Increase to 16–64 MB for better write performance.
• maintenance_work_mem: Increase to 1–2 GB for faster index creation.

https://free.blessedness.top/en-us/azure/postgresql/flexible-server/how-to-use-pg-partman

https://www.howtouselinux.com/post/the-max_wal_size-parameter-in-postgresql-balancing-write-performance-and-recovery-time

https://www.postgresql.org/docs/current/wal-configuration.html

https://www.mydbops.com/blog/postgresql-parameter-tuning-best-practices

4.How to ensure minimum disruption in the running workload on the table getting partitioned, i.e. how to minimize time which affects DML commands to be run on the table.

Strategies:

Shadow Table Approach:

• Create a partitioned table.
• Use INSERT INTO ... SELECT to backfill data.
• Use triggers or logical replication to sync new data during migration.

Cutover Plan:

• Switch application writes to the new table during a short downtime window.
• Ensure reads continue from the old table until migration is complete.

Operational Tips:

• Perform migration during off-peak hours.
• Monitor write latency and throttle batch sizes if needed.
• Use pg_stat_activity and pg_stat_progress_copy to monitor progress.

I hope this information helps. Please do let us know if you have any further queries.Hi