Schema Evolutions at Gilt Groupe

Slide 1

Slide 1 text

Schema Evolutions how we do it at Gilt Groupe PGDay NYC 2013 March 22, 2013 Michael Bryzek michael@gilt.com @mbryzek http://tech.gilt.com

Slide 2

Slide 2 text

Today's Talk •  Context / Scaling as teams grow •  Schema Evolution Manager (open source) •  Supporting QA and Development environments https://github.com/gilt/schema-evolution-manager

Slide 3

Slide 3 text

It always starts small One app One database One schema (public) One user (superuser) Couple developers

Slide 4

Slide 4 text

Schema Evolution In App "Great - don't need to worry about this"

Slide 5

Slide 5 text

release app + schema at same time > cap production release ...upgrade db... ...deploy app... Easy (but not simple!)

Slide 6

Slide 6 text

What user's see

Slide 7

Slide 7 text

Luckily we have Rollback! up: alter table users drop column email_address; down: alter table users add column email_address text;

Slide 8

Slide 8 text

Oops... That table had DATA in production. Good thing we have reliable backups.

Slide 9

Slide 9 text

Meanwhile...

Slide 10

Slide 10 text

We then really try code review

Slide 11

Slide 11 text

Works great at first

Slide 12

Slide 12 text

If we're lucky, business grows. So does Database. Aren't we lucky to have this problem? Fake chart showing # of tables in 1 database over time

Slide 13

Slide 13 text

Schema change commits dwarfed in volume

Slide 14

Slide 14 text

What happened to app performance?

Slide 15

Slide 15 text

No content

Slide 16

Slide 16 text

Some stats from gilt •  Primary DB > 1000 tables • pg_dump of schema is 6MB by itself • Largest tables > 1B rows historical data, 100s of million rows active data • 100+ developers need data stores

Slide 17

Slide 17 text

How can we fix this?

Slide 18

Slide 18 text

IDEA: Lots of Small Applications A core strategy at gilt (LOSA) •  "micro services" •  primarily solving the problem of how to scale a technical organization •  requires significant automation •  creates new problems, but we think they are easier to solve

Slide 19

Slide 19 text

Treat Schema as 1st Class Code •  Lots of Small Databases •  Dedicated git repositories •  Code review by the right people •  Small incremental changes applied frequently •  Real releases - tags and artifacts

Slide 20

Slide 20 text

So you want to add a table? •  One application / major feature •  One database / application •  One git repository / database Requires automation and good tools to manage

Slide 21

Slide 21 text

Schema Evolution Manager (SEM) •  Manages schema evolutions as text files •  100% command line based •  Creates releases as simple .tar.gz files •  Minimal dependencies https://github.com/gilt/schema-evolution-manager Open sourced today!

Slide 22

Slide 22 text

Typical workflow 1. New git repo (e.g. schema-users) 2. Write a sql script w/ incremental change 3. Create a release 4. Test the release 5. Deploy to production 6. Iterate 100% independent of application lifecycle

Slide 23

Slide 23 text

No content

Slide 24

Slide 24 text

Create a migration = new.sql

Slide 25

Slide 25 text

add script

Slide 26

Slide 26 text

git status

Slide 27

Slide 27 text

Apply to local db [snip]

Slide 28

Slide 28 text

git commit

Slide 29

Slide 29 text

Create a release

Slide 30

Slide 30 text

Release to QA/Stage 1. scp dist/sample-0.0.2.tar.gz :~/ 2. ssh 3. tar xfvz sample-0.0.2.tar.gz 4. cd sample-0.0.2 5. sem-apply ... 6. Optional "--dry_run" flag

Slide 31

Slide 31 text

Schema Evolution Manager (SEM) •  If useful, we'd love your feedback! https://github.com/gilt/schema-evolution-manager

Slide 32

Slide 32 text

Multi step schema changes Only real challenge is managing multi step deploys across schema / application for changes

Slide 33

Slide 33 text

Renaming a column Release 1: Deploy schema to add new column Release 2: Deploy app to use old + new Release 3: Deploy schema to drop old column Release 4: Deploy app to use only new More complicated, but each step is simpler

Slide 34

Slide 34 text

Real benefits •  Clear team ownership of individual databases •  Much higher fidelity code reviews - standards and conventions easily enforced across databases and teams •  Each individual database is smaller and thus easier to manage (horizontal scale) •  ~100% of application deploys have simple rollback •  Schema changes rarely affect production application response time anymore.

Slide 35

Slide 35 text

Overall Workflow 1.  Create your script 2.  sem-add new.sql 3.  git commit -m "New script to blah" 4.  git push origin HEAD:refs/for/master 5.  automated tests verify the change 6.  people code review 7.  author merges 8.  sem-dist 9.  deploy to QA 10.  deploy to Production

Slide 36

Slide 36 text

https://code.google.com/p/gerrit/

Slide 37

Slide 37 text

Some stats from Gilt 50 changes / month avg to largest DB at gilt 19 active production postgresql DBs today # of changes to initial db decreasing with LOSD

Slide 38

Slide 38 text

Supporting QA / Development Envs •  Make available two types of databases for engineering: o  Sanitized (pre production, more data) o  Reference (unit tests, basic development) •  Reference created hourly •  Sanitized created daily For large databases, a complex process w/ lots of moving parts. Foreign keys are hard. For small databases, simple configuration.

Slide 39

Slide 39 text

Creating a Snapshot DB •  White list of tables to extract data •  Optional standard SQL queries to extract data if partial data or PII •  Extract to CSV files using COPY •  One CSV file / table •  Configuration inside each schema's git repository •  Automated tests in jenkins •  Validated artifacts published

Slide 40

Slide 40 text

Developer CLI rake postgresql:create:single_database [env,logical_name,type] env: development, test, stage, etc. logical_name: gilt, ondemandinventory, etc. type: reference or sanitized Creates a new db on localhost with latest published data.

Slide 41

Slide 41 text

Developer CLI rake postgresql:create_queued_dbs Background process that will fetch latest published database data and create local psql databases. When ready, user can: rake postgresql:flip_queued_dbs

Slide 42

Slide 42 text

http://tech.gilt.com Thank you!