While many tables have simple key/value pairs for records, WiredTiger also supports more complex data patterns.
A table is a logical representation of data consisting of cells in rows and columns. For example, a database might have a simple table including an employee identifier, last name and first name, and a salary:
|Employee ID||Last Name||First Name||Salary|
A row-oriented database would store all of the values for the first employee in the first row, then the next employee's values in the next row, and so on:
1,Smith,Joe,40000 2,Jones,Mary,50000 3,Johnson,Cathy,44000
A column-oriented database would store all of the values of a column together, then the values of the next column, and so on:
1,2,3 Smith,Jones,Johnson Joe,Mary,Cathy 40000,50000,44000
WiredTiger supports both storage formats, and can mix and match the storage of columns within a logical table.
A table in WiredTiger consist of one or more "column groups" that together hold all of the columns in primary key order; and zero or more indices that enable fast lookup of records by columns in orders other than the primary key.
Applications describe the format of their data by supplying a schema to WT_SESSION::create. This specifies how the application's data can be split into fields and mapped onto rows and columns.
By default, WiredTiger works as a traditional key/value store, where the keys and values are raw byte arrays accessed using a WT_ITEM structure. Key and value types may also be chosen from a list, or composed of multiple columns with any combination of types. Keys and values may be up to (
4GB - 512B) bytes in size.
See Key/Value pairs for more details on raw key / value items.
WiredTiger's uses format strings similar to those specified in the Python struct module to describe the types of columns in a table: http://docs.python.org/library/struct
|Format||C Type||Java type||Python type||Notes|
|N/A||N/A||N/A||pad byte, no associated value|
|fixed-length bit field|
|raw byte array|
'r' type is used for record number keys in column stores. It is otherwise identical to the
's' type is used for fixed-length strings. If it is preceded by a size, that indicates the number of bytes to store; the default is a length of 1 byte.
'S' type is encoded as a C language string terminated by a NUL character.
't' type is used for fixed-length bit field values. If it is preceded by a size, that indicates the number of bits to store, between 1 and 8. That number of low-order bits will be stored in the table. The default is a size of 1 bit: that is, a boolean. C applications must always use a
uint8_t type (or equivalently,
unsigned char) for calls to WT_CURSOR::set_value, and a pointer to the same for calls to WT_CURSOR::get_value. If a bit field value is combined with other types in a packing format, it is equivalent to
'B', and a full byte is used to store it.
When referenced by a record number (that is, a key format of
't' type will be stored in a fixed-length column-store, and will not have an out-of-band value to indicate the record does not exist. In this case, a 0 byte value is used to indicate the record does not exist. This means removing a record with WT_CURSOR::remove is equivalent to storing a value of 0 in the record with WT_CURSOR::update (and storing a value of 0 in the record will cause cursor scans to skip the record). Additionally, creating a record past the end of an object implies the creation of any missing intermediate records, with byte values of 0.
'u' type is for raw byte arrays: if it appears at the end of a format string (including in the default
"u" format for untyped tables), the size is not stored explicitly. When
'u' appears within a format string, the size is stored as a 32-bit integer in the same byte order as the rest of the format string, followed by the data.
There is a default collator that gives lexicographic (byte-wise) comparisons, and the default encoding is designed so that lexicographic ordering of encoded keys is usually the expected ordering. For example, the variable-length encoding of integers is designed so that they have the natural integer ordering under the default collator.
See Packing and Unpacking Data for details of WiredTiger's packing format.
WiredTiger can also be extended with custom collators by implementing the WT_COLLATOR interface.
For example, a simple row-store table with strings as both keys and values would be created as follows:
A simple column-store table with strings for values would be created as follows:
Cursors for a table have the same key format as the table itself. The key columns of a cursor are set with WT_CURSOR::set_key and accessed with WT_CURSOR::get_key. WT_CURSOR::set_key is analogous to
printf, and takes a list of value in the order the key columns are configured in
For example, setting the key for a row-store table with strings as keys would be done as follows:
For example, setting the key for a column-store table would be done as follows:
A more complex example, setting a composite key for a row-store table where the key_format was
"SiH", would be done as follows:
The key's values are accessed with WT_CURSOR::get_key, which is analogous to
scanf, and takes a list of pointers to values in the same order:
The columns in a table can be assigned names by passing a
columns key to WT_SESSION::create. The column names are assigned first to the columns in the
key_format, and then to the columns in
value_format. There must be a name for every column, and no column names may be repeated.
For example, a column-store table with an employee ID as the key and three columns (department, salary and first year of employment), might be created as follows:
In this example, the key's column name is
id, and the value's column names are
id maps to the column format
department maps to the column value format
salary maps to the value format
year-started maps to the value format
Once the table is created, there is no need to call WT_SESSION::create during subsequent runs of the application. However, it's worthwhile making the call anyway as it both verifies the table exists and the table schema matches the schema expected by the application.
Once column names are assigned, they can be used to configure column groups. Column groups are primarily used to define storage in order to tune cache behavior, as each column group is stored in a separate file.
There are two steps involved in setting up column groups: first, pass a list of names for the column groups in the
colgroups configuration key to WT_SESSION::create. Then make a call to WT_SESSION::create for each column group, using the URI
colgroup:<table>:<colgroup name> and a
columns key in the configuration. Every column must appear in at least one column group; columns can be listed in multiple column groups, causing the column to be stored in multiple files.
For example, consider the following data being stored in a WiredTiger table:
If we primarily wanted to access the population information by itself, but still wanted population information included when accessing other information, we might store all of the columns in one file, and store an additional copy of the population column in another file:
Column groups always have the same key as the table. This is particularly useful for column stores, because record numbers are not stored explicitly on disk, so there is no repetition of keys across multiple files. Keys will be replicated in multiple files in the case of row-store column groups.
A cursor can be opened on a column group by passing the column group's URI to the WT_SESSION::open_cursor method. For example, the population can be retrieved from both of the column groups we created:
Key columns may not be included in the list of columns for a column group. Because column groups always have the same key as the table, key columns for column groups are retrieved using WT_CURSOR::get_key, not WT_CURSOR::get_value.
Columns are also used to create and configure indices on tables.
Table indices are automatically updated whenever the table is modified.
Table index cursors are read-only and cannot be used for update operations.
To create a table index, call WT_SESSION::create using the URI
index:<table>:<index name>, listing a column in the configuration.
Continuing the example, we might open an index on the
Cursors are opened on indices by passing the index's URI to the WT_SESSION::open_cursor method.
To create an index with a composite key, specify more than one column to the WT_SESSION::create call:
To retrieve information from a composite index requires a more complicated WT_CURSOR::set_key call, but is otherwise the same:
It is possible to create an index with the
immutable configuration setting enabled. This setting tells WiredTiger that the index keys for a record do not change when records are updated. This is an optimization that it saves a remove and insert into the index whenever a value in the primary table is updated.
If immutable is configured when updates should alter the content of the index it is possible to corrupt data.
An example of using an immutable index is:
By default, index cursors return all of the table's value columns from WT_CURSOR::get_value. The application can specify that a subset of the usual columns should be returned in calls to WT_CURSOR::get_value by appending a list of columns to the
uri parameter of the WT_SESSION::open_cursor call. This is called a projection, see Projections for more details.
In the case of index cursors, a projection can be used to avoid lookups in column groups that do not hold columns relevant to the operation.
The following example will return just the table's primary key (a record number, in this case) from the index:
Here is an example of a projection that returns a subset of columns from the index:
For performance reasons, it may be desirable to include all columns for a performance-critical operation in an index, so that it is possible to perform index-only lookups where no column group from the table is accessed. In this case, all of the "hot" columns should be included in the index (always list the "real" index key columns first, so they will determine the sort order). Then, open a cursor on the index that doesn't return any value columns, and no column group will be accessed.
Index cursors for column-store objects may not be created using the record number as the index key (there is no use for a secondary index on a column-store where the index key is the record number).
The code included above was taken from the complete example program ex_schema.c.
Here is another example program, ex_call_center.c.