In broad terms, Vector uses columnar storage. While data is stored and retrieved in familiar relational rows, the internal storage is different. Instead of storing all column values for a single row next to each other, all rows for a single column are stored together. This storage format has benefits for data warehouse applications, such as reduced I/O and improved efficiency.

A relational table may have dozens or hundreds of columns. In traditional row-oriented relational storage, the entire row must be read even if a query requests only a few columns. With column-oriented storage, a query that needs only a few columns will not read the remaining columns from disk and will not waste memory storing unnecessary values. A side benefit of this columnar storage is that compression (see Data Compression) can be more effective.

While Vector is fundamentally a column store, it is not strictly so. For certain types of tables, Vector stores data from more than one column together in a data block on disk. Within a data block the values for each column are kept together so that data for a column can be processed efficiently in vectors. Vector also provides a storage type that lets you indicate you want to store all columns for a table in a single block.

In VectorH, tables are also partitioned horizontally, based on a hash value of the partition key column(s). A single data file stored in HDFS will contain data for a single column (with the exceptions mentioned above) and a single partition.

A database consists of multiple files and can reside in multiple locations. Tables can also be spread across multiple locations.

Updates, inserts, and deletes to the data and the layout of the data in the data files are stored as log information on HDFS in II_HDFSDATA/ingres/data/vectorwise/dbname/wal). The data files and the wal directory must be considered together as files that represent the database.

The data files consist of several blocks, which can be seen as the equivalent of pages. Each block contains possibly compressed data from one or more attributes. The size of the block can be configured with the [cbm] block_size parameter before creating a database.

Also, for better sequential access performance, multiple blocks from the same column can be stored contiguously in a block group. The size of this group can be configured with the [cbm] group_size parameter.

Database size is unlimited because data can be spread across multiple disks. If a table or a column is dropped, its corresponding file or files are deleted, and the disk space is reclaimed by the operating system.

The default storage format for a Vector table (X100) is also known as a RAW table. This format stores the data on disk in columns in the order in which it is inserted, and the data is compressed.

In addition, for each column the system automatically maintains simple statistics about the data distribution by storing the minimum and maximum value for ranges of tuples. If a query includes a filter on the column data value, the execution engine uses these min-max data values to decide whether to examine the data in a given block. Since min-max information is maintained in memory outside of the data block, this simple form of automatic indexing can dramatically reduce disk I/O and memory usage. You can configure the granularity of the min-max index with the [cbm] minmax_maxsize parameter.

Columnar storage inherently makes compression (and decompression) more efficient than does row-oriented storage.

For row-oriented data, choosing a compression method that works well for the variety of data types in a row can be challenging, because compression for text and numeric data work best with different algorithms.

Column storage allows the algorithm to be chosen according to the data type and the data domain and range, even where the domain and range are not declared explicitly. For example, an alphabetic column GENDER defined as CHAR(1) will have only two actual values (M and F), and rather than storing an eight-bit byte, the value can be compressed to a single bit, and then the bit string can be further compressed.

Vector uses different algorithms from those found in most other products. Because Vector processes data so efficiently, compression —and especially decompression—are designed to use little CPU and to reduce disk I/O. While on-disk compression ratios may be slightly lower than other products, overall performance is improved.

Compression in Vector is automatic, requiring no user intervention. Vector chooses a compression method for each column, per data block, according to its data type and distribution.

Data types are stored internally in a specific format. The type of compression used depends on the data type and distribution.

Vector can use any of the following compression methods:

Most INTEGER, DECIMAL, and DATE and TIME types internally are compressed using any of the first four compression methods.

FLOAT and FLOAT4 types are stored without compression in Vector tables.

Character types (CHAR, VARCHAR, NCHAR, NVARCHAR) of lengths larger than one are stored internally as variable width strings. This data can be automatically compressed using either a per-block dictionary or LZ4 algorithm.

NULL values are stored internally as a single byte column and are compressed using the RLE method. The null indicator, if needed, is represented internally as a separate column. Loading and processing of nullable columns can be slower than non-nullable columns.

Analytical queries are the focus of Vector. It is essential that such queries have useful information about the characteristics of the underlying data because the amount of data accessed can be great, and the sizes of intermediate processing stages must be properly estimated to produce optimal query plans.

To allow the system to generate optimal query plans, we highly recommend running optimizedb or CREATE STATISTICS if 10% or more of the data in a table has changed since the last optimization. Vector will automatically construct histograms from live data for those columns that do not already have histograms stored in the catalog.

Vector provides a rich set of data updating capabilities.

Vectorwise distinguishes between two granularities of DML operations:

For more information, see Batch and Bulk Updates on page 87.

A COMMIT statement makes all changes from a transaction become persistent (including batch operations). In addition, Vector provides full automatic recovery. If a system crashes during a transaction, any changes made by that transaction will not be visible after the system restart, but all successfully committed transactions will be restored.

Batch updates are stored in a special highly-optimized differential data structure, called the Positional Delta Tree (PDT), in main memory. Data is also written to disk immediately to ensure recoverability.

Batch updates that reside in memory can potentially consume a significant percentage of useful system memory. As a result, if the user is performing batch operations, Vector automatically propagates the changes buffered in memory to the disk-resident table. Propagation is triggered by factors such as table sizes and the number of DML operations buffered.

The volume of batch updates in such a propagation cycle should fit in main memory.

Update propagation can be a relatively expensive operation if there are many modified rows (especially in case of even distribution of updates across the table). Vector tries to rewrite only the affected blocks unless it would result in excessive table fragmentation.

The best practice is to avoid propagation to large tables (see How to Avoid Propagation on page 92) and use appends where possible.

The amount of memory reserved for Positional Delta Trees and the amount of differences after which propagation is triggered can be configured with system configuration parameters. For more information, see PDT Parameters in the System Administrator Guide. In cases with high update rates, the memory required for PDTs can become too high, in which case you will have to work with raw tables or use bulk operations, such as the MODIFY...TO COMBINE statement.

Vector uses a snapshot isolation transaction isolation model with optimistic concurrency control.

Snapshot isolation is a transaction isolation model in which a given transaction always sees the same, consistent state of the system representing the moment when a transaction has started. As a result, phenomena seen in certain other isolation levels (dirty, non-repeatable, and phantom reads) are avoided. Snapshot isolation is closely related to multiversion concurrency control.

Snapshot isolation is slightly weaker than the serializable isolation level available in some systems.

The main benefit of snapshot isolation is that, while providing a high transaction isolation level, it avoids locking for read operations. Such locks can be detrimental to database performance. By combining it with the optimistic concurrency control model, it allows Vector to achieve high performance of update operations without affecting the performance of read-only queries.

For more information, see:

Unlike some systems, Vector uses the optimistic concurrency control model. In this model, transactions can perform changes to the data without checking for conflicts possibly caused by other transactions that have committed in the meantime. Conflicts are checked only when a transaction is committed. This refers not only to conflicts caused by changing the same data, but also to constraint violations that occur only because of two transactions committing (for example, two concurrent transactions inserting a record with the same UNIQUE field).

Compared to other approaches, optimistic concurrency control avoids locking records for concurrently working transactions. This results in superior performance in cases where transactions typically do not conflict with each other.

The most visible aspect of optimistic concurrency control that sometimes causes problems for users accustomed to other solutions is that the conflicts are detected only at commit time. The application logic must take this into account.

For more information, see http://en.wikipedia.org/wiki/Optimistic_concurrency_control.

This example demonstrates how committing transactions are not visible to concurrently running transactions. In this case, Transaction A will insert a value, but Transaction B will not see it until a new transaction is started.

In this example, two transactions concurrently insert a record with the same value in a Primary Key attribute. This causes a conflict for the second committing transaction due to constraint violation.

Snapshot isolation and optimistic concurrency control are applied to operations on data (DML).

For operations that change the database schema (DDL), a different isolation model is used. In this model, transactions only see DDL changes performed by other transactions after they committed. Still, if there are conflicting DDL operations (for example, two transactions creating a table with the same name), the system locks the second transaction until the first transaction either commits or aborts.

Vector is a relational database with SQL access based on Ingres Database. The higher software layers—the layer database administrators, database users, or database applications interact with, such as management tools and the JDBC API—are identical to the traditional Ingres Database.

The difference from traditional Ingres is that the data is stored in Vector tables. This data is processed using the specialized Vector engine, allowing much higher performance for analytical database tasks.

Although it is possible to have both Vector and Ingres Database installations on the same server, this is not desirable. We recommend that Vector be installed on a dedicated server.

While a database can contain a mixture of both Vector and traditional Ingres tables, Vector does not support mixing both types of tables in the same query. You can, however, easily move data between Ingres and Vector tables directly and create tables in traditional Ingres or Vector using CREATE TABLE AS SELECT or INSERT...SELECT statements.

By default, Vector uses the X100 storage structure.

The X100 storage type differs from traditional Ingres storage types in the following ways:

A Vector table can be created with one of two storage types:

The result_structure parameter in config.dat sets the default storage structure for the Vector instance. When a table is created, the default storage structure is assumed.

To override the default, specify SET RESULT_STRUCTURE for the session or use WITH STRUCTURE=vw_structure in the CREATE TABLE statement.

You can create a traditional Ingres table in a Vector instance by overriding the default storage setting on a table basis by using WITH STRUCTURE = HEAP in the CREATE TABLE statement.

Vector stores CHAR and VARCHAR data types as UTF-8, using UCS-2 with UCS_BASIC collation. The default normalization form is NFC, but it can be changed to the less common NFD using createdb ‑n.

By default, autocommit is turned off, which means each transaction consists of multiple statements ended by either a COMMIT or ROLLBACK statement.

If an error occurs during the execution of an SQL statement, the default is to roll back the statement. You can change this behavior and roll back the entire transaction if an error occurs by issuing SET SESSION WITH ON_ERROR = ROLLBACK TRANSACTION.

To determine the status of transaction error handling, issue the SELECT DBMSINFO('ON_ERROR_STATE') statement.