Forum: Building VoltDB Applications

Post: Indexes and partitioning

Indexes and partitioning
David Best
Sep 14, 2010
I have some questions regarding how indexes interact with partitioning. In particular, are indexes fragmented over the partitions?

Considering the Volt architecture, I have a mental model that each partition includes both table and index instances, so when a single-partition proc is directed to a partition, any row DML will update the index instances within that partition, whether or not the index columns partake of the partition key.
For example:

CREATE TABLE User (
  UserID INTEGER NOT NULL,
  Email VARCHAR(256) UNIQUE NOT NULL,
  PRIMARY KEY (UserID)
);
CREATE TABLE UserItems (
  UserID INTEGER NOT NULL,
  Item VARCHAR(20) NOT NULL,
  PRIMARY KEY (UserID, Item)
);
<partitions>
  <partition table="User" column="UserID">
  <partition table="UserItems" column="UserID">
</partitions>



I would think that DML on UserItems could be done in a single-partition proc because the index partitions on the table's partition key.

But what about the index on Email that doesn't include the partition key column?
(This seems analogous to the v_votes_by_contestant_number view in the voter example.)

Is it allowed? If so, and the proc is single-partition, is the uniqueness only enforced within the partition?

If the proc is multi-partition, will the uniqueness be globally checked, or does the proc have to do a select count(*) to aggregate across the partitions before insert or update?

Thanks
re: Indexes and Partitioning
tcallaghan
Sep 15, 2010
David,

You bring up a good point. If you create a unique index on a partitioned table then the index is only checked for uniqueness within the partition. Uniqueness is only guaranteed, in this case, if the partition column is part of the index key.

I have created a ticket in our tracking system for this issue, we will refuse to create the application catalog at compile time if we detect this condition. You can view it at http://issues.voltdb.com/browse/ENG-731.

Thank you.
-Tim