How we implement Table Partitioning Without Any Downtime — Planning

In the fast-paced world of B2B fintech, a scalable system is a must. At OY!, transaction volumes are skyrocketing, and we need to keep latency to a minimum to support this growth. In this article, we’ll show you how OY! is boosting database performance, focusing on partitioning in the settlement process. This article delves into the planning phase of our partitioning strategy, covering the different partitioning options, key considerations for selecting the right strategy, and the migration approaches we evaluated.

Section 1: Background

At OY!, as a payment gateway company, we handle settlements for our partners every working day. With the huge volume of transactions going through our settlement table, we’ve run into performance issues related to database load and latency. As transactions keep growing, we’re worried our database might not be able to keep up in the future.

Previously, our settlement table was over 50 GB, with an average 99th percentile latency of 24.1 seconds across multiple queries. Our database load had a CPU utilization of 69.08%, with lock wait at 0.001, IO wait at 0.255, and CPU & CPU wait at 0.578. With these metrics, we’re able to achieve a settlement rate around 29,600 transactions per minute.

Given these conditions, we believed that our current performance was still not optimal, especially considering the future challenges of handling exponentially growing transactions. We expected that we would need to handle up to four times the current transaction volume. We have already tried to improve the situation through query adjustments, caching, indexing, and limiting the data retrieved. However, there are still some queries that remain slow despite implementing these improvements.

Considering these issues, we had two options: vertical scaling or table partitioning. Vertical scaling would increase our operational costs, so we aimed to avoid it if possible. Therefore, we decided to migrate the table to a partitioned table with the aim of reducing overall latency, improving our settlement rate, and simplifying the table maintenance process. Further details on what partitions offer will be explained in the following section. Additionally, as downtime is very costly for our company, we needed to migrate it with zero downtime.

Section 2: Understanding Partitioning

Before we get into the specifics, let’s go over what partitioning is all about. Partitioning is a database optimization technique where a large table is split into smaller, more manageable parts based on a specific partition key. Each partition works independently, offering big advantages in performance, maintenance, and data management.

Key Benefits of Partitioning:

1. Reduced Row Scanning: Partitioning reduces the number of rows that need to be scanned during queries.
2. Index Replacement: It can optimize indexes, positively impacting query performance.
3. Seamless Data Migration: Infrequently-used data can be smoothly migrated to slower and more economical storage media.
4. Parallel Query Execution: PostgreSQL 13 enhances parallel query capabilities for partitioned tables, enabling parallel execution of queries across multiple partitions, which can significantly improve query performance, especially for complex analytical queries.

(Reference, Reference)

For our delayed settlement process, only data with settlement times >= today are frequently accessed and updated, making older data ‘cold.’ Given this time-series nature, partitioning is a logical solution for our settlement table. During our daily settlement process, accessing a table over 50 GB would be cumbersome. Partitioning lets us work with smaller chunks, reducing row scanning and boosting query performance.

Partitioning Strategies

When setting up partitioned tables, it’s important to carefully consider how data is spread across partitions to avoid future performance problems.

1. Range Partitioning
   Here, the table is divided into “ranges” based on a key column or set of columns, with no overlap between the ranges assigned to different partitions.
2. List Partitioning
   In this method, the table is split by explicitly listing which key values appear in each partition.
3. Hash Partitioning
   With hash partitioning, the table is divided by specifying a modulus and a remainder for each partition.

(Reference)

Key Considerations in Strategy Selection

1. Most Common Filter: Align partitioning strategy with frequently used filters to minimize partition scans.
2. Effective Distribution: Ensure an even distribution of data among partitions to prevent data skew and optimize resource utilization.
3. Reduced Join Operations: Minimize the need for joins between partitions by selecting a partitioning strategy that aligns with your data access patterns and query requirements.
4. Stable Partition Keys: Maintain the stability of partition keys to minimize data movement across partitions. Choose partition keys that exhibit infrequent updates or changes, as frequent updates to partition key values can lead to data redistribution and increased overhead. Additionally, consider the long-term stability of partition keys to avoid potential maintenance challenges.
5. Query Performance Optimization: Evaluate the performance implications of different partitioning strategies on specific types of queries commonly executed in your application. Consider factors such as query complexity, data access patterns, and indexing requirements when choosing a partitioning strategy.

After careful consideration, we’ve chosen range partitioning as it aligns well with our table’s patterns and offers the most optimal benefits for our use case

Section 3: Migration Strategy

Once the partition strategy has been decided, the next step is to consider migration approach options for transitioning from a regular table to a partitioned table.

1. Attach Old Table as Child Partition

The first option is to create a new partition table and then attach the old table as a child partition. The steps involved in this approach are relatively simple:

1. Create a partition table

CREATE TABLE partition_settlement (
 id varchar(255) NOT NULL,
 ...,
 settlement_time timestamp NOT NULL,
 CONSTRAINT partition_settlement_pkey PRIMARY KEY (id, settlement_time)
) PARTITION BY RANGE (settlement_time);

2. Attach existing table to new partition table

a. Create unique index concurrently for existing table for tuple (id, settlement_time)

CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS "settlement_unique_key" 
ON settlement (id, settlement_time);

b. Add constraint from index created before

ALTER TABLE settlement
ADD CONSTRAINT "settlement_unique_key" 
UNIQUE USING INDEX "settlement_unique_key";

c. Attach existing table to partition table

ALTER TABLE partition_settlement ATTACH PARTITION settlement 
FOR VALUES FROM ('-infinity') TO ('2023-12-01');

While relatively simple, this approach introduces table locking, potentially causing a locking period of approximately 58 seconds based on past experiences with a 10GB dataset. This locking may impact system availability and necessitate downtime for this approach.

2. View with Union

This approach draws inspiration from the reference provided here. The fundamental idea behind using a view with union is to replace the table used by the application with a view. This view combines data from both the old table and the new partitioned table. Here’s a detailed breakdown of the approach:

1. Create Partition Table: Begin by creating the partitioned table.
2. Rename and Create View: Rename the old table and instead create a view (with the same name as the old table). This view combines results from both tables. Side note, renaming database tables raises lock concerns (Reference)
3. Implement Functions and Triggers: Set up the necessary functions and triggers to manage INSERT, UPDATE, and DELETE statements for future data, although this might introduce some latency.
   a. Insert: Insert values into the partitioned table.
   b. Update: Check if data exists in the old table, delete it, and insert the values into the new table.
   c. Delete: Remove rows from both tables.
4. Migrate Data: Transfer data from the old table to the partitioned one by deleting from the old table and inserting into the partitioned table.
5. Cleanup Process: Remove the view and rename the partitioned table to match the view’s original name.
6. Delete Old Table: Finally, delete the old table.

Despite its effectiveness, this approach raises lock concerns, which might be manageable in some use cases. However, considering our specific scenario, we cannot afford these locks. Hence, this approach is not suitable for our use case.

3. Double Write Migration via App Layer

The final approach involves a double-write process to two tables (the old table and the partitioned table) and gradually rolling out usage to the partitioned table. This entire process is managed entirely from the application layer, making it more manageable for our specific use case. Here are the detailed steps:

1. Create Partition Table: Begin by creating the partitioned table.
2. Backfill Data: Transfer data from the old table to the partitioned table using the COPY method. According to PostgreSQL documentation, this method offers the fastest bulk loading of data.
3. Activate Double Write Mechanism: Enable the double-write mechanism for both the partition and non-partition tables.
4. Synchronize Data Between Tables:
   The process of synchronizing data between tables varies depending on each table’s specific use case. This process is managed from the app layer, providing us with greater manageability and flexibility.
5. Monitor Traffic: Closely monitor traffic during the transition phase. If the process performs well with the partition table similar to the previous setup, proceed to roll out 100% of the workload to the partitioned table.

Out of the three approaches mentioned, we have decided to proceed with the ‘Double Write Migration via App Layer’ approach. This decision is based on the fact that the migration process is fully managed at the application layer, making it more manageable and flexible according to our needs. Importantly, we can execute this migration with zero downtime, ensuring uninterrupted operations.

Conclusion

In summary, the planning phase of our partitioning strategy involved a thorough examination of different partitioning methods and their implications on data distribution and query performance. We carefully considered factors such as common filters, data distribution, and query optimization to select the most suitable partitioning strategy. After evaluating multiple migration approaches, we chose the ‘Double Write Migration via App Layer’ approach for its flexibility and zero downtime benefits. With this groundwork laid, we are now ready to move forward with the actual implementation. For a detailed look at how we executed this migration and the impact it had on our system, continue reading our next article.

How we implement Table Partitioning Without Any Downtime — Execution

Reference

1. https://www.postgresql.org/docs/current/ddl-partitioning.html
2. https://www.postgresql.fastware.com/postgresql-insider-prt-ove
3. https://www.enjoyalgorithms.com/blog/data-partitioning-system-design-concept
4. https://engineering.workable.com/postgres-live-partitioning-of-existing-tables-15a99c16b291