The Nuts and Bolts of Managing Database Upgrades

DZone 's Guide to

The Nuts and Bolts of Managing Database Upgrades

Six tips and tricks to optimize performance during changes to your production database as part of an agile development team.

· Agile Zone ·
Free Resource

Any project following an Agile methodology will usually find itself releasing to production at least 15 - 20 times per year. Even if only half of these releases involve database changes, that's ten changes to production databases, so you need a good lean process to ensure you get a good paper trail. But at the same time, you don't want something that that will slow you unduly down. So here are some tips in this regard: 

Tip 1: Introduce a DB Log table

Use a DB Log table to capture every script run, who ran it, when it was run, what ticket it was associated with, etc. Here is an example DDL for such a table for PostGres:

create sequence db_log_id_seq;
create table db_log (id int8 not null DEFAULT nextval('db_log_id_seq'), created timestamp not null, db_owner varchar(255), db_user varchar(255), project_version varchar(255), script_link varchar(255), jira varchar(255));
W.R.T. the table columns:
  • id - primary key for table. 
  • timestamp - the time the script was run. This is useful.  Believe me. 
  • db_owner - the user who executed the script
  • db_user - the user who wrote the script
  • project_version_number - the version of your application / project the script was generated in
  • scrip_link - a URL link to a source controlled version of the script
  • jira - a URL to the ticket associated with the script

Tip 2: All Scripts Should be Transactional

For every script, make sure it happens within a transaction and within the transaction make sure there is an appropriate entry into the db log table. For example, here is a script which removes a column:
ALTER TABLE security.platform_session DROP COLUMN IF EXISTS ttl;
INSERT INTO db_log (
 db_owner, db_user, project_version, script_link, jira, created)

Tip 3: Scripts Should be Idempotent

Try to make the scripts idempotent. If you have 10 developers on a team, every now and again someone will run a script twice by accident. Your db_log will tell you this, but try to ensure that when accidents happen that there is no serious damage. This means you get a simple fail safe,  rather than some newbie freaking out. In the above script, if it is run twice, the answer will be the exact same.

Tip 4: Source Control Your Schema

Source control a master DDL for the entire project. This is updated anytime the schema changes. Meaning you have update scripts and a complete master script containing the DDL for entire project. The master script is run at the beginning of every CI. It means that:
  • Your CI always starts with a clean database.
  • If a developer forgets to upgrade the master script, the CI will fail and your team will quickly know the master script needs to be updated.
  • When you have a master script it gives you two clear advantages:
    • New developers get up and running with a clean database very quickly
    • It becomes very easy to provision new environments. Just run the master script.

Tip 5: Be Dev Friendly

Make it easy for developers to generate the master script. Otherwise when the heat is on, it won't get done.

Tip 6: Upgrade and Revert

For every upgrade script write a corresponding revert script. Something unexpected happens in production, you gotta be able to reverse the truck back out!

ALTER TABLE security.platform_session ADD COLUMN hard_ttl INT4;
UPDATE security.platform_session SET hard_ttl = -1 WHERE hard_ttl IS NULL;
ALTER TABLE security.platform_session ALTER COLUMN hard_ttl SET NOT NULL;

ALTER TABLE security.platform_session ADD COLUMN ttl INT4;
UPDATE security.platform_session SET ttl = -1 WHERE ttl IS NULL;
ALTER TABLE security.platform_session ALTER COLUMN ttl SET NOT NULL;

INSERT INTO db_log (
 db_owner, db_user, platform_version, script_link, jira, created)
 values (


 Until the next time take care of yourselves.

agile adoption, agile practices, database, db, postgres

Published at DZone with permission of Alex Staveley , DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}