Do you put your indexes in source control?

Question

And how do you keep them in synch between test and production environments?

When it comes to indexes on database tables, my philosophy is that they are an integral part of writing any code that queries the database. You can't introduce new queries or change a query without analyzing the impact to the indexes.

So I do my best to keep my indexes in synch betweeen all of my environments, but to be honest, I'm not doing very well at automating this. It's a sort of haphazard, manual process.

I periodocally review index stats and delete unnecessary indexes. I usually do this by creating a delete script that I then copy back to the other environments.

But here and there indexes get created and deleted outside of the normal process and it's really tough to see where the differences are.

I've found one thing that really helps is to go with simple, numeric index names, like

idx_t_01
idx_t_02

where t is a short abbreviation for a table. I find index maintenance impossible when I try to get clever with all the columns involved, like,

idx_c1_c2_c5_c9_c3_c11_5

It's too hard to differentiate indexes like that.

Does anybody have a really good way to integrate index maintenance into source control and the development lifecycle?

Answer 1

Yes, any DML or DDL changes are scripted and checked in to source control, mostly thru activerecord migrations in rails. I hate to continually toot rails' horn, but in many years of building DB-based systems I find the migration route to be so much better than any home-grown system I've used or built.

However, I do name all my indexes (don't let the DBMS come up with whatever crazy name it picks). Don't prefix them, that's silly (because you have type metadata in sysobjects, or in whatever db you have), but I do include the table name and columns, e.g. tablename_col1_col2.

That way if I'm browsing sysobjects I can easily see the indexes for a particular table (also it's a force of habit, wayyyy back in the day on some dBMS I used, index names were unique across the whole DB, so the only way to ensure that is to use unique names).

Answer 2

Indexes are a part of the database schema and hence should be source controlled along with everything else. Nobody should go around creating indexes on production without going through the normal QA and release process- particularly performance testing.

There have been numerous other threads on schema versioning.

Answer 3

I do not put my indexes in source control but the creation script of the indexes. ;-)

Index-naming:

• IX_CUSTOMER_NAME for the field "name" in the table "customer"
• PK_CUSTOMER_ID for the primary key,
• UI_CUSTOMER_GUID, for the GUID-field of the customer which is unique (therefore the "UI" - unique index).

Answer 4

The full schema for your database should be in source control right beside your code. When I say "full schema" I mean table definitions, queries, stored procedures, indexes, the whole lot.

When doing a fresh installation, then you do: - check out version X of the product. - from the "database" directory of your checkout, run the database script(s) to create your database. - use the codebase from your checkout to interact with the database.

When you're developing, every developer should be working against their own private database instance. When they make schema changes they checkin a new set of schema definition files that work against their revised codebase.

With this approach you never have codebase-database sync issues.

Answer 5

I always source-control SQL (DDL, DML, etc). Its code like any other. Its good practice.

Answer 6

I am not sure indexes should be the same across different environments since they have different data sizes. Unless your test and production environments have the same exact data, the indexes would be different.

As to whether they belong in source control, am not really sure.

Answer 7

I think there are two issues here: the index naming convention, and adding database changes to your source control/lifecycle. I'll tackle the latter issue.

I've been a Java programmer for a long time now, but have recently been introduced to a system that uses Ruby on Rails for database access for part of the system. One thing that I like about RoR is the notion of "migrations". Basically, you have a directory full of files that look like 001_add_foo_table.rb, 002_add_bar_table.rb, 003_add_blah_column_to_foo.rb, etc. These Ruby source files extend a parent class, overriding methods called "up" and "down". The "up" method contains the set of database changes that need to be made to bring the previous version of the database schema to the current version. Similarly, the "down" method reverts the change back to the previous version. When you want to set the schema for a specific version, the Rails migration scripts check the database to see what the current version is, then finds the .rb files that get you from there up (or down) to the desired revision.

To make this part of your development process, you can check these into source control, and season to taste.

There's nothing specific or special about Rails here, just that it's the first time I've seen this technique widely used. You can probably use pairs of SQL DDL files, too, like 001_UP_add_foo_table.sql and 001_DOWN_remove_foo_table.sql. The rest is a small matter of shell scripting, an exercise left to the reader.

Answer 8

On my current project, I have two things in source control - a full dump of an empty database (using pg_dump -c so it has all the ddl to create tables and indexes) and a script that determines what version of the database you have, and applies alters/drops/adds to bring it up to the current version. The former is run when we're installing on a new site, and also when QA is starting a new round of testing, and the latter is run at every upgrade. When you make database changes, you're required to update both of those files.

Answer 9

Using a grails app the indexes are stored in source control by default since you are defining the index definition inside of a file that represents your domain object. Just offering the 'Grails' perspective as an FYI.