Will it make sense to add an index on the tag column of databasechangelog?
Will it make sense to add an index on the tag column of databasechangelog?
Seems unlikely that in practice the size of a databasechangelog table would be so large that a query planner would choose an index over a table scan. If you truly have that many ChangeSets, the time spent calculating md5sums will dwarf the tag lookups. And if you rebaseline periodically you can delete the old records in databasechangelog.
Depending on the product we use it for it could go into the thousands.
Currently we use Liquibase in the following manner:
a) At very first use we tag our different databases with an initial tag.
b) After every installation, patch, hotfix, service pack we tag the databases with the version of that artifact.
If b) should fail and rollback we need to know which tag to rollback to, so we do a query to search for the most recent tag before installing the artifact.
On a product/patch/etc uninstall (effectively a rollback) we do a query to get the tag of the specific artifact and then do a query to find the tag that was executed before that.
Typically we have a major install with lots of SQL, followed by lots of patches with small amounts of SQL and then we will have major upgrades which basically will do modifications and new SQL that has not been installed before, so we will probably never rebaseline.
I probably won’t add an index to the table by default, since I try to keep those tables as simple as possible, to ensure that they work without problem on any database. You should be able to add a changeSet that adds an an index to the table yourself if you are finding that it would be helpful for you, though.
Nathan
Good idea Nathan.
You have any comments on how I’m using tags?
I think your usage of tag make sense.
I am generally in the “don’t rewrite existing changesets” camp. As your changelog file grows, updates will take longer, but it should generally be quite efficient, even with a lot of changes. I think the problems that could be introduced by changing changesets that have been tested and deployed would outweigh any performance benefits you get, especially if you don’t restart your app/update your database often.
Nathan
Yes, I have an issue finding a good use case for modifying an existing change set that has already been applied in place. If I understand your statement correctly then you also believe that its better to do any structure changes in additional changesets.
In our environment we always need to be able to rollback to a previous state. Yes sometimes it can’t be done perfectly and you lose data, but its something we have to live with. We also cannot assume what has been installed before. So if we have a major version, patches and then a major upgrade, the major upgrade will only execute new changesets with the modifications, If the major upgrade detects a new install, it will execute ‘updated’ versions of the original changesets if that makes sense.