Wow, what a summer. It feels like our interns just arrived and now they’re all back to school. With their help we’ve shipped some great features in V8.3:

Better SPI balance after a failed node rejoins the cluster

Volt Active Data’s K-safety feature is achieved by having a leader for each unique partition (also known as a Single Partition Initiator, or SPI). The SPI coordinates transactions for all copies of data within a cluster. If a node containing an SPI fails, the rest of the cluster “elects” one of the remaining nodes to host the SPI in the original host’s absence. Prior to V8.3, newly rejoined nodes would have no SPIs assigned them, causing the leadership overhead to become unevenly balanced across the older nodes of the cluster. In V8.3, once all nodes are rejoined, some SPIs will migrate to the newly rejoined nodes in order to rebalance the leadership overhead.

Optimize some LIKE ? queries by replacing them with STARTS WITH ?

In stored procedures, if you can supply a prefix for a text comparison, the STARTS WITH clause can use an index, whereas the LIKE clause cannot. When a VARCHAR column has an index, querying with STARTS WITH allows the Volt Active Data query planner to use a range scan to quickly get to the data. Here’s a neat trick – if you have a complex LIKE expression and you know you can always provide a prefix, you can combine STARTS WITH ? and LIKE ? and speed up the filtering. Here’s an example of looking up useragent strings in an website’s access logs. Try this on V8.3; you’ll like the new plan.

CREATE TABLE accesslogs (ipaddress VARCHAR(64), ts TIMESTAMP, useragent VARCHAR(256));
CREATE INDEX idx_accesslogs_useragent ON accesslogs(useragent);
CREATE PROCEDURE scanplan AS SELECT * FROM accesslogs WHERE useragent LIKE ?;
CREATE PROCEDURE rangeplan AS SELECT * FROM accesslogs WHERE useragent STARTS WITH ? AND useragent LIKE ?;
explainproc scanplan;
explainproc rangeplan;

Timezone-aware function to convert timestamps

FORMAT_TIMESTAMP(timestamp-value, timezone-or-offset) returns the timestamp-value as a formatted string in the specified timezone. Applications can use this function to convert the GMT values stored in the database to their local timezone. For example:

SELECT FORMAT_TIMESTAMP(ts,'America/New_York'),ipaddress FROM accesslogs;

Table selectors for snapshot restore

Sometime you only want to load certain tables from a snapshot. ‘voltadmin restore’ now supports the arguments –skiptables and –tables to help you choose which data to load (voltadmin save supports these arguments, too). This is a great way to load staging systems, skipping sensitive customer tables. Or you could use these arguments to truncate a table and restore its contents from the previous hourly snapshot.

For a full list of V8.3 features and fixes, visit our Release Notes. If you have questions or feedback, drop a note to support@voltactivedata.com.

Download Volt Active Data and give it a try.