MySQL-to-PostgreSQL Migration & Optimization

I’m migrating a production MySQL dataset (just under 50 GB) to PostgreSQL and want the move handled end-to-end with future growth in mind. Beyond a clean data transfer, I need the new cluster tuned for speed and modern feature use.

Key requirements
• JSONB support for semi-structured payloads
• Native full-text search for rapid look-ups
• Table partitioning and array-based storage so the schema scales gracefully

I already apply query-level optimisations in MySQL; I’d like the same discipline carried over with tools such as EXPLAIN, pg_stat_statements and proper index design.

Deliverables
1. Migration roadmap and rollback strategy
2. Schema translation scripts or pgloader/pg_dump commands ready to run
3. Feature implementation (JSONB columns, tsvector indexes, declarative partitions) with performance benchmarks before-vs-after
4. Final documentation covering configuration choices, tuning parameters and how to repeat the process on staging

All work should be reproducible via shell scripts or SQL files I can rerun. Once the last benchmark shows comparable or better query times than my current setup, the job is done and the milestone released.