A data engineering team at QuintoAndar migrated their Metabase analytics database from MySQL to PostgreSQL to improve performance and infrastructure management. The migration process involved several key steps and considerations:

Key Components

• Schema migration using Metabase's Liquibase utility
• Data migration using PgLoader tool
• Handling of data integrity and constraint issues

Migration Process

1. Planning and Testing

   • Evaluated migration tools (PgLoader vs AWS DMS)
   • Identified potential issues with JVM heap space and data constraints
   • Created detailed migration scripts

2. Implementation Steps

   • Service shutdown to prevent data loss
   • Schema creation using Liquibase scripts
   • Data migration with PgLoader
   • Service restart with new database configuration
   • Verification and testing

Challenges Addressed

• JVM Heap Space: Resolved by breaking down the loading process for large tables
• Data Integrity: Fixed null constraint violations and missing values
• Schema Compatibility: Managed differences between MySQL and PostgreSQL implementations

Key Learnings

• PgLoader proved more effective than AWS DMS for handling object dependencies
• Separating schema and data migration provided better control
• Having a rollback plan was essential for risk management
• While downtime was required, the approach worked well for their use case

This migration improved QuintoAndar's analytics infrastructure by standardizing their database platform and isolating Metabase's performance metrics.