Migrating From PostgreSQL to DynamoDB
Welcome to the world of migration! In technology, migration becomes necessary as our applications grow, change, and new challenges emerge. To help you with migration and prevent mistakes, I would like to share my learnings from a migration my squad recently completed.
The Challenge: A Bottleneck in PostgreSQL
In one of our applications, we were using the popular RDBMS PostgreSQL. During the initial few months it was working well because we had less frequent write and read operations. Gradually, as we increased the volume of writing data, our application got slower. There were times when we needed to continuously write to the database, which was increasing the CPU utilization up to 100% and impacting the overall performance. This was a roadblock for an upcoming major release in production as we did not want to impact our customer-facing flow due to a potential slow reading operation.
To resolve the issue we tried to make improvements to mitigate the impact and solve the root cause. First, we increased CPU cores in PostgreSQL but it did not help significantly and the root cause was still present. Then we optimized the database by adding additional indexes, increasing the connection pool size, and refactoring code wherever possible, but still the problem persisted. We realized that to resolve the issue large-scale changes would be necessary so we started looking for an alternate persistence solution.
Considering our use case, with the number of current records in the range of millions with many more to come in the future, we decided to use a clustered NoSQL solution.
The Solution: NoSQL to the Rescue
Among many options, we shortlisted DocumentDB, MongoDB, and DynamoDB as possible solutions that provided the necessary technical requirements. In the end we decided on DynamoDB for two reasons: first, we had the required skillset within the team so we could implement it faster, and second, the pricing model scales with storage and throughput.
Moving Forward With Implementation
After much discussion it was time for action. To implement DynamoDB in our application and have existing data readily available to the end users, we divided the implementation into six main tasks:
1. Design
2. Data Migration
3. Application Integration
4. New Data Consistency
5. Data Validation
6. Application Migration
Step 1: Design
Before implementing, we designed the DynamoDB table structure. We carefully analyzed the current tables in PostgreSQL, their relationships, and the existing data. There were 3 major entities. As per AWS recommendation, we designed to accommodate all the required data in a single table with the help of a composite primary key (Partition Key + Sort Key).
What are partition keys and sort keys? The AWS documentation describes them in more detail:
- “Partition key — A simple primary key, composed of one attribute known as the partition key.”
- “Partition key and sort key — Referred to as a composite primary key, this type of key is composed of two attributes. The first attribute is the partition key, and the second attribute is the sort key.”
“DynamoDB uses the partition key value as input to an internal hash function. The output from the hash function determines the partition (physical storage internal to DynamoDB) in which the item will be stored.”
Step 2: Data Migration
While we were making changes in the microservices (see more in Step 3) to modify the flow of traffic, we were also preparing for the data migration. We created an event-driven serverless application using each entity’s IDs to migrate data from PostgreSQL to DynamoDB. The figure below shows the overall flow for the product entity.
In order to have more control of the flow of migration, and to offset some of the work to the managed infrastructure, we opted for the mechanism illustrated in figure 2 below.
The migration process we followed can be broken down into the following steps:
- We created multiple CSV files with IDs for each entity. Each CSV had a range of 100,000 IDs, as shown in Figure 3. The reason we restricted the range to 100,000 was to avoid a lambda timeout. We then uploaded all the CSV files into an S3 bucket.
2. For each CSV file a lambda was triggered, which read data from the CSV and published entity IDs in batches of 10 into an SQS queue. Batch publishing improved overall performance. The lambda took around 5 minutes to publish 100,000 IDs.
3. The queue triggered another lambda which fetched data from the PostgreSQL DB for a given ID. It prepared data as per the new DynamoDB format and saved it in the table. Our team decided to use this approach as opposed to a script to batch query PostgreSQL because we wanted to control the triggering point. It gave us the flexibility to first process a few hundred products and analyze and validate the data before increasing the number of products in each execution.
We used limited concurrent requests (reservedConcurrency = 3 and provisionedConcurrency = 3) in the lambdas because we didn’t want to overload our PostgreSQL DB with the migration, which could impact other applications using it.
It went very smoothly and without any errors.
Step 3: Application Integration
Once our database design was finalized, we worked on migration scripts and ran them. After that we pointed out all our microservices to write to and read from DynamoDB in order to gradually transition away from PostgreSQL.
We used 3 feature flags to direct the routing of traffic, toggling them in a phased rollout in the following sequence:
- Write to both PostgreSQL and DynamoDB:
As a first step, we turned on ‘writeToBothDatabases’ to start writing data to both DynamoDB and PostgreSQL. Now that we had the data in sync in both databases we were ready to read from DynamoDB. - Read from DynamoDB:
In the second step, we enabled ‘readFromNewDatabase’ to read data only from DynamoDB. After this we waited a few days to make sure everything was working well, did some data validation, and ran load tests. - Write only to DynamoDB:
In the final step, after verifying everything, we used ‘writeToNewDatabase’ to write data only to DynamoDB. Voila! With that we had 100% of traffic moved to DynamoDB and could free up our resources and code that had been used for PostgreSQL.
These feature flags were important because we wanted to make sure that all the microservices were working as expected before completely moving to the new database.
Step 4: New Data Consistency
We knew that migration on Production takes time, hours, or even days. In our case it was expected to be 1 to 2 days. During the migration our application received new data, and to ensure both PostgreSQL and DynamoDB were consistent, we started writing all new data into both databases by enabling the feature flag. This helped to avoid any data missing before and after migration.
Step 5: Data Validation
After data migration was complete, we manually checked records in DynamoDB as well as performed spot checks of randomly selected product codes to make sure all the data from PostgreSQL was migrated into DynamoDB and that the data was written in the expected format.
Step 6: Application Migration
On the application side, we updated to initially serve the client read requests from either DynamoDB or PostgreSQL based on a feature flag. Once data migration was complete, and data was validated, we turned the feature flag on and tested the application to make sure it was serving data correctly from DynamoDB. After running the application for a few days, we turned the feature flag off for writing data to PostgreSQL and started using DynamoDB only for both read and write operations.
Performance Monitoring
After completing the migration to DynamoDB, we monitored the overall performance and it was as expected. We did not see any issues and CPU usage was normal during continuous data writing.
Cleanup
Finally, we did a cleanup for both codebase and infrastructure. We removed PostgreSQL DB from our cloud infrastructure and did a code cleanup to remove all the references to PostgreSQL.
In conclusion, migration is an important aspect of our software development. It is not easy to carry out a migration but one can make a migration successful with proper planning and design, feature flags, and the correct setup, execution and validation. The main points to keep in mind are the need to take care of new incoming data while doing a migration, and reading data from old databases until the new database is ready to serve traffic to clients.
Editorial reviews by Catherine Heim, Mario Bittencourt & Nicole Tempas.
Want to work with us? Click here to see all open positions at SSENSE!
