Supporting Real-Time: Views, Ranking Indexes, & More
VoltDB protects your data. VoltDB uses fully serializable ACID transactions to manage concurrent access to your data. But how does it help you build powerful data-based applications?
One of the most popular ways users leverage VoltDB’s real-time analysis tools is by computing aggregates on a mutating dataset. This dataset is often a timestamped event stream, but it could take many forms.
For example, questions users want fast answers to frequently take the form of:
- Who are the top three salespeople, by revenue and by region?
- How many ads were shown for each customer account?
- What is the average latency of my servers by minute?
- Which regions are using more electricity than a certain threshold?
Materialized views allow the VoltDB user to declaratively pre-aggregate these values whenever the base data changes. Rather than scanning billions of rows at query time and sorting them into buckets, VoltDB’s live aggregation does a little extra work every time a row is inserted, deleted or changed, with the benefit that it can answer complex aggregation queries in less than a millisecond.
Read related blog posts.
VoltDB supports ordered and unordered indexes to support fast key lookups, range queries and sorts. It supports primary key indexes and secondary indexes. Tables can have any number of indexes, between zero and hundreds. This level of support is expected in a relational database; the speed at which VoltDB can insert into a table with 30 indexes is the innovation. We have added special features to our indexes to make many types of problems easier to solve.
- Ranking Indexes are tree-based, ordered indexes that maintain a count of the nodes under each subtree. In practice, this means fast queries with large offsets, fast counts of large ranges, and easy queries to determine the rank of a particular record in a sorted set. These features are added by default to all ordered indexes.
- Expression/Function Indexes add the ability to pre-compute common expressions in an index, speeding many different types of lookups. You can use arbitrary expressions in indexes and the VoltDB SQL optimizer will determine when they can be used to speed up queries. The following DDL statement creates a case-insensitive index:
1CREATE INDEX n ON table (LOWER(name));
Now you can find all of the rows with names that start with a certain prefix fast:
1SELECT * FROM table WHERE LOWER(name) LIKE 'mc%'
- Filtered/Sparse Indexes allow you to add focus to indexes to handle special cases or to add focus THIS IS REDUNDANT. If your column contains many null values, the following DDL statement will create a more efficient index:
1CREATE INDEX s ON table (state) WHERE state IS NOT NULL;
Create an index just for Canada:
1CREATE INDEX p ON table (province) WHERE country == 'CA';
And of course you can combine expression/function indexes with filtered/sparse indexes.