As an application evolves, the database schema often needs changing. This is particularly true during the early stages of development and testing but also happens periodically with established applications, as the database is tuned for performance or adjusted to meet new requirements. In the case of VoltDB, these updates may involve changes to the table definitions, to the indexes, or to the stored procedures. The following sections explain how to:
Perform live schema updates
Change unique indexes and partitioning using save and restore
There are two ways to update the database schema for a VoltDB database: live updates and save/restore updates. For most updates, you can update the schema while the database is running. To perform this type of live update, you use the DDL CREATE, ALTER, and DROP statements to modify the schema interactively as described in the section on modifying the schema in the Using VoltDB manual.
You can make any changes you want to the schema as long as the tables you are modifying do not contain any data. The only limitations on performing live schema changes are that you cannot:
Add or broaden unique constraints (such as indexes or primary keys) on tables with existing data
Reduce the datatype size of columns on tables with existing data (for example, changing the datatype from INTEGER to TINYINT)
These limitations are in place to guarantee that the schema change will succeed without any pre-existing data violating the constraint. If you know that the data in the database does not violate the new constraints you can make these changes using the save and restore commands, as described in the following section.
If you need to add unique indexes or reduce columns to database tables with existing data, you must use the voltadmin save and restore commands to perform the schema update. This requires shutting down and restarting the database to allow VoltDB to validate the existing data against the new constraints.
To perform a schema update using save and restore, use the following steps:
Create a new schema file containing the updated DDL statements.
Pause the database (voltadmin pause).
Save a snapshot of the database contents to an specific location (voltadmin save --blocking {path} {file-prefix}).
Shutdown the database (voltadmin shutdown).
Re-initialize and restart the database starting in admin mode (voltdb init --force and voltdb start --pause).
Load the stored procedures and new schema (using the sqlcmd LOAD CLASSES and FILE directives)
Restore the snapshot created in Step #3 (voltadmin restore {path} {file-prefix}).
Return the database to normal operations (voltadmin resume).
For example:
$ # Issue once $ voltadmin pause $ voltadmin save --blocking /opt/archive/ mydb $ voltadmin shutdown $ # Issue next two commands on all servers $ voltdb init --dir=~/mydb --config=config.ysml --force $ voltdb start --dir=~/mydb --host=svr1,svr2 --count=5 $ # Issue only once $ sqlcmd 1> load classes storedprocs.jar; 2> file newschema.sql; 3> exit $ voltadmin restore /opt/archive mydb $ voltadmin resume
The key point to remember when adding new constraints is that there is the possibility that the restore operation will fail if existing records violate the new constraint. This is why it is important to make sure your database contents are compatible with the new schema before performing the update.
Documentation