Teradata is a relational database management system designed specifically for large warehouse applications, based on Massively Parallel Processing (MPP) architecture. It optimizes query performance through parallelism, using the AMP as a basic unit. AMPs (Access Module Processor) is a virtual processor used to manage the database. They receive execution plans from the parser and are able to receive, manipulate, and store data. A pre-defined number of AMPs is shared across a Teradata system to perform tasks including queries, dataload, backups, index builds, etc.
Teradata was founded based on research done in CalTech and incorporated in 1979 with the goal of creating a database computer that could beyond a terabyte of data. Teradata was acquired by NCR in 1991. In the following year, Teradata became the first system to store over 1 terabyte of data. In 2007, Teradata would be split off from NRC becoming its own separate entity. In 2011, Teradata would acquire both Aprimo and Aster Data Systems Inc. beginning Teradata’s involvement in the big data market. From 2016 - 2017, Teradata has become available on both AWS and Azure.
For concurrency control, Teradata utilizes the proxy lock strategy. The systems requires that each request has a pseudo lock on it before obtaining an actual lock. For each table, proxy locking defines an AMP which manages the actual locks. Lock requests are to be queued up on this AMP. It is noted that not all deadlocks can be prevented with this strategy. By default Teradata checks for deadlocks globally every four minutes and locally every thirty seconds. Teradata doesn’t adhere to two-phase locking.
Teradata supports write ahead logs (WAL). During any transaction commit, Teradata automatically writes back to disk. During crashes, Teradata can retrieve redo logs back from disk to restore the system.
All columns from both tables are compared and joined based on matching column names. The resulting table retains one column for each pair of matching columns.
This type of join merges tables based on conditions such as less than, greater than, equal, etc.
Teradata also supports inner and outer joins.
Teradata supports a hybrid storage model having both row and column storage features. Based on query optimization plans, Teradata can change between row and column storage schemes. Teradata also supports two types of primary indexing for AMP partitioned tables. CPPI, provides row and column partitions on an AMP sorted by primary index attribute. Since these values are stored on the row identifier for each record, column partitions are able to maintain the same sort order without communication. CPPA, doesn’t sort by primary index hash, but instead contains a record that can be determined with a value of the primary index attribute.
During execution, the parsing engine first transforms the user-defined query into an execution plan which it relays to a message passing layer known as the BYNET which effectively serves as a communication channel between the parser and AMPs. Execution plans are sent from the parse to the AMP layer while results from the query are channeled upwards to the parser from the AMPs. Teradata supports query parallelism by hash partition data across all AMPs declared in the system. This way all relational operations can execute in parallel across AMPs. Teradata supports inter-operator parallelism as well as intra-operator parallelism. Teradata also utilizes a vectorized model of query execution.
Teradata supports hash table based indexes.
Teradata by default offers a Start-of-Data checkpoint and an End-of-Data checkpoint. If the job failed before the End-of-Data checkpoint was taken, all work done after the Start-of-Data checkpoint will be repeated by the restarted job. For further protection, Teradata also provides interval checkpoints. Instead of automated checkpointing, the user can specify a time interval for checkpoint placement.
Teradata supports foreign keys.
Teradata supports a relational model. It also supports document store, Graph DBMS (Teradata Aster), and time series DBMS as secondary models.
Teradata supports a shared-nothing system architecture. The parsing engine, BYNET, and AMPS do not share memory nor disk space.
During query compilation, Teradata issues Stored Procedure Language (SPL) errors and response. SPL compilation errors and warnings are stored in a buffer. If this buffer size is exceeded, the rest of the messages is lost.
Teradata allows for the following possible compression options.
Multivalue Compression (MVC)
MVC compresses repeating values in a column after specifying the value in the compression list in the column definition. When column data matches the specified value, the database stores the value once in the table header, regardless of how many times it occurs as a value in the column. No decompression is necessary when accessing the data from memory.
This is generally used as an alternative to MVC when column values are mostly unique. Teradata includes several standard compression algorithms that can be used to compress many dataypes including ARRAY, BYTE, VARBYTE, BLOB, CHARACTER, VARCHAR, CLOB, JSON, DATASET). Custom compression algorithms are also allowed to be used.
Row compression is a lossless method. This type of compression stores a repeating column value set a single time, while non-repeating column values that belong to that set are stored as extensions of the base set.