Views

Materialized Views

Ingres produces materialized views.

System Architecture

Shared-Everything

Ingres is a single-node database management system, and therefore it is "Share-Everything".

Storage Architecture

Disk-oriented

Although it is currently branded as the "Actian Hybrid", the "Hybrid" refers to its capability of performing both OLTP and OLAP tasks by employing a hybrid storage model (i.e. both row and column), not that it has a hybrid storage architecture. It is still disk oriented.

Query Compilation

Stored Procedure Compilation

Ingres allows users to write stored procedures in one of the supported query languages.

Storage Organization

Heaps Indexed Sequential Access Method (ISAM)

Heaps and ISAM are supported by Ingres.

Data Model

Relational

Ingres is a relational database system.

Isolation Levels

Read Uncommitted Read Committed Serializable Repeatable Read

Ingres supports four isolation levels, from favoring consistency to maximizing concurrency: Serializable, Repeatable Read, Read Committed. and Read Uncommitted, Serializable is the default isolation level because it provides the strongest consistency guarantee.

Stored Procedures

Supported

Ingres supports stored procedures in both SQL and QUEL.

Logging

Logical Logging Physical Logging Command Logging

Storage Model

Hybrid

Although the original Ingres was row-based, Actian has incorporated columnar storage into its latest version of Ingres (Actian X) to improve its performance on OLAP tasks.

Foreign Keys

Supported

After primary keys are created, a user can create foreign keys using the New Foreign Key Dialog.

Concurrency Control

Multi-version Concurrency Control (MVCC) Deterministic Concurrency Control Two-Phase Locking (Deadlock Detection)

Ingres uses the terms "logical lock" and "physical lock" to refer to transaction locks and latches, respectively. It supports multiversion concurrency control (MVCC). Its available lock levels are, from most to least granular, are Row, MVCC, Page, Table, Database. It supports the following lock modes: NL(no lock), IS(intent to share), IX(intent to update), S(shared), SIX(shared with intent to update), and X(exclusive access). The locks in Ingres are managed by a lock manager. They are tracked using their unique lock IDs. It supports deadlock detection and aborts one of the transactions that are deadlocked.

Query Interface

SQL Stored Procedures Command-line / Shell QUEL

Ingres was originally built on UNIX and supports a command-line interface. It also supports SQL and QUEL and stored procedures in these two query languages.

Query Execution

Materialized Model

Ingres executes queries by producing materialized views.

Joins

Nested Loop Join Hash Join Sort-Merge Join

Ingres supports hash join, sort-merge join, and nested loop join algorithms.

Compression

Null Suppression

Compression of tables only work on character and text columns. Trailing nulls and blanks are compressed. Non-nullable types such as Integer, floating point, and date are not compressed.

Indexes

B+Tree Hash Table R-Tree

One can create an index on an existing table in Ingres using the CREATE INDEX command. An index cannot contain more than 32 columns, but one can create as many indexes as needed on a table. Ingres chooses ISAM (Index Sequential Access Method) as the index data structure by default, but also supports B+ Tree, Hash Table, and R-Tree for this purpose. It can also create secondary indexes. In addition, it can create an index in parallel to reduce the time needed.

Checkpoints

Consistent Blocking

Ingres supports two types of checkpointing: online and offline. Offline checkpointing requires all users to disconnect from the database. It acquires a global lock on the entire database and performs backup on all the data. Online checkpointing does not require users to disconnect and is the default checkpointing scheme. Therefore, it is faster than offline checkpointing as it results in less down-time. However, it is still blocking. Users cannot proceed until the Ingres creates a consistent point of the database. The DBMS records all changes in the database but does not support operations such as drop or create during online checkpointing.