Teradata is pre-announcing Teradata 14, for delivery by the end of this year, where by “Teradata 14″ I mean the latest version of the DBMS that drives the classic Teradata product line. Teradata 14’s flagship feature is Teradata Columnar, a hybrid-columnar offering that follows in the footsteps of Greenplum (now part of EMC) and Aster Data (now part of Teradata).
The basic idea of Teradata Columnar is:
- Each table can be stored in Teradata in row format, column format, or a mix.
- You can do almost anything with a Teradata columnar table that you can do with a row-based one.
- If you choose column storage, you also get some new compression choices.
The “mix” option is like Vertica’s FlexStore, in that different columns (e.g. different components of a street address) can be grouped into a mini-row, even if you otherwise choose to store that table in a columnar way. Teradata does not at this time offer the Greenplum or Aster way of mixing rows and columns, whereby some of the rows in a table can be stored in a column-store way, while other rows are stored in entire-row row-store solidarity
Thus, Teradata Columnar gives you many of the basic I/O and compression benefits of columnar DBMS, along with all the usual Teradata goodness of concurrency, workload management, system management, concurrency, SQL support, and so on. By way of comparison:
- Similar things are true of Greenplum’s offering (except for the parts about concurrency, advanced workload management, and so on).
- Aster doesn’t have columnar compression.
- Oracle has columnar compression but no true columnar storage.*
Also, as I noted above, Teradata mixes rows and columns in a different way than Aster or EMC Greenplum do.
*However, I won’t be surprised if Oracle soon announces true hybrid-columnar as well. I originally heard about Teradata Columnar and Oracle’s efforts to develop true hybrid-columnar storage the same week, 23 months ago.
Going hybrid-columnar is a big deal. Aster Data, for example, told me that a considerable fraction of all its workloads ran faster with columnar than row-based storage.* And it’s of extra importance to a vendor that, like Teradata, needs to play catch-up in the compression derby.
*Anything in which the queries eliminated more than half or so of the columns (60%, if I recall correctly, but it was definitely an approximate figure). That pretty much means any query except full and near-full table scans.
Teradata’s columnar compression story is pretty complicated. To quote from a forthcoming press release:
Teradata automatically chooses from among six types of compression: run length, dictionary, trim, delta on mean, null and UTF8. based on the column demographics.
The trickiest words in that are “automatic” and “dictionary”. Teradata divides column-store data into “column containers” of, say, 8 KB. (Current thinking is 8 KB default, 65 KB maximum, but that could change by the time of product release.) By default, Teradata software decides separately for each column container which compression algorithm(s) to use. It can even change its mind dynamically over time, as the contents of the container change.
What I find weird about Teradata’s columnar dictionary compression is that the dictionary is container-specific. One benefit versus having a more global dictionary is that, since you compress fewer items, compression tokens can each be shorter. (The length of a typical token is a lot like the log of the cardinality of the dictionary.) Another benefit is that smaller dictionaries are faster to search. The obvious offsetting drawback is that a larger and more global dictionary has the potential to compress various items that wind up being left uncompressed in this smaller-scale scheme.
Other notes about Teradata compression include:
- Teradata has for a while had a more manual form of dictionary compression.
- Teradata also has block-level compression.
- You can do block-level compression even on top of the columnar compression described above.
- The Teradata/Rainstor partnership for archiving-level compression that Rainstor made so much fuss about doesn’t seem to actually be happening; Teradata seems content with the other compression choices it offers.
And finally, Teradata 14 extends Teradata Virtual Storage with a feature called Compress on Cold. The idea is that “cold” data can safely get (extra) compression — that block-level stuff — automatically. If the data heats up again (e.g. by becoming relevant for a while to the latest year-over-year comparisons) it can be just as automatically removed from compression. Teradata thinks this is significantly better than the alternative of making manual compression choices based on not-so-granular range partitions.
Unsurprisingly, Teradata lacks some features and benefits found in certain columnar-first analytic DBMS. One biggie is that, absent clever workarounds such as Vertica’s in-memory write-optimized store, columnar DBMS have a single-row-update performance problem, because you are putting the information in many places on disk rather than just one. I generally take it for granted that a columnar-first vendor has such a workaround. Row-based vendors gone columnar, however, are a different story. Teradata et al. are also likely to decompress data and reassemble it into full rows as soon as it hits RAM, which obviates the potential benefit that you have less data per row clogging up cache.* (Edit: As per Todd Walter’s comments below, this is not accurate — and that’s a potentially important feature.)
*Late decompression actually depends on columnar compression, not columnar storage, and hence can also be enjoyed by row-based DBMS such as DB2.
To use Teradata Columnar, you need to be using round-robin data distribution rather than, say, hash. Teradata jargon for this is NoPI, where the “PI” stands for Primary Index.* Drawbacks to that include:
- You don’t get the hash distribution benefit of saving a data redistribution step on joins whose join key happens to be the same as the hash key.
- In Teradata-land, NoPI implies append-only, so you get the garbage collection/compactification that implies.
However, that’s a physical append-only; you can still do logical updates.
*PI is not to be confused with PPI, which stands for Primary Partition Index, and is Teradata’s name for range (or case-statement-based) partitioning. PPI works just fine with Teradata Columnar. As of Teradata 14, you can do PPI up to 62 levels deep.
The Teradata folks also sent along a slide deck laying out parts of the Teradata Columnar story. But it’s not one of the better Teradata decks I’ve ever posted.