Queenstown Technology Group
Mongo & MySQL ⇒ PostgreSQL
What are databases?
Different technologies
have
different strengths
MySQL
Low barrier to entry
LAMP Stack
A lot of blog posts for n00bs who don't RTFM
"Tries" to follow ANSI SQL
Very forgiving aka "good enough for most"
MongoDB
Schemaless NoSQL
Max doc size: 16Mb
JS-esque query language
No joins
No transactions
Locks everywhere
Biggest advertising budget of NoSQL DBs
PostgreSQL
Setup involves more
MVCC slower reads, faster writes
TOAST compression
Max row size: 1.6TB
Max field size: 1GB
Strict - NOT NULL - Types
ACID, Multiple Triggers, Stored Procedures, Query Planner, etc...
Wherewolf migrates
with zero downtime
1,200,000+ rows @ 70GB
in 50 hours
Beginning
Rename models in ORM
to explicitly reference the database to which they're wired
- Client ⇒ ClientMySQL
- Guest ⇒ GuestMySQL
- GuestLog ⇒ GuestLogMongo
- Photo ⇒ PhotoMongo
- Product ⇒ ProductMySQL
- Signature ⇒ SignatureMongo
- TripLog ⇒ TripLogMongo
- User ⇒ UserMySQL
- ...
Prepare PostgreSQL
- Install / Launch RDS
- Create users
- Create tables
- Create sequences
- Leave indexes off
- Create Models (*Postgre)
Writes go to PostgreSQL as well
"Truth" is still on legacy databases
Code deployed where all API /endpoints and methods() write to legacy & next-gen in parallel. Response upon completion of legacy DB write
Begin copying from MySQL & Mongo
Worker minion goes through, row by row in MySQL and Mongo and ensures each row from both DBs is copied to PostgreSQL
Only after worker minion is finished, create indexes (concurrently)
Reads come from PostgreSQL
"Truth" is now PostgreSQL
Code deployed where all API /endpoints and methods() read from PostgreSQL
Disconnect MySQL & Mongo
Code deployed to PROD that nothing interacts with legacy databases anymore
Linting tools to help find *Mongo & *MySQL references
Rename ORM Models sans DB suffix
Turn off MySQL & Mongo
Result
- 30% reduction of Time-In-Database
- 45% reduction in disk usage
- 50% reduction in number of databases to manage
- 1 less server
Wherewolf is hiring
