Large Scale MySQL Migration to PostgreSQL
summary
A Top-10 photo site moves its data and code off MySQL to PostgreSQL under a fixed deadline: 32 million users 1 billion photos >6TB get exported and reshuffled into 256 partitions across 16 DB servers, with a six‑week copy window and a six‑hour restore plan for failed nodes. Migration uses csv exporters, pgloader, SQL ETL, pljava to decode Google Protocol Buffers blobs, plus pgbouncer pgq plproxy and Amazon hosting; the result runs in production and stays stable.
AI-written summary · about
chapters (21)
- 0:00PostgreSQL migration intro
- 1:08Photolog architecture change
- 3:16Old MySQL architecture
- 7:06Business reasons for migration
- 9:15New PostgreSQL architecture
- 12:10PL/Proxy sharding in PostgreSQL
- 14:44Choosing Amazon Web Services for hosting
- 18:56Data migration challenges
- 23:11Migrating with pgLoader and ETL
- 26:02PL/Java blobs and legacy code
- 31:08Processing photo comments
- 33:18Post-migration performance
- 35:05PGQ batch processing
- 37:57PgBouncer benefits
- 43:22Automating extensions
- 45:45Timeline and budget
- 48:13Handling node failures
- 51:03Performance trade-offs
- 53:05AWS evaluation after year
- 56:01Cost comparison
- 58:32Q&A
related talks
38mSwitching Teams: Moving an Application from MySQL to PostgreSQL
26mMastering Large-Scale PostgreSQL Migration
49mPractical Guide to Migrating +100 Large PostgreSQL to the Cloud
46mBig databases, many servers, data center or cloud - it's all here!
37mData Migration With Confidence
42mMigrating a database over 10TB from DB2 to PostgreSQL with zero downtime
42mBBM’s 150M+ users Oracle to Postgres migration without downtime
29mTales from the Field - Oracle to PostgreSQL migrations