Ingres

View Current Viewing Revision #26 from 01/11/2024 9:23 p.m.

Acquired Company

Ingres is one of the first ever relational database management systems (DBMSs). Actian Corporation currently oversees the development of the database while providing certified binaries and support. The latest version is Ingres 11, which is also known commercially as the Actian X Hybrid Database. It is mainly intended for OLTP tasks, but the latest version has support for columnar storage which is suitable for OLAP tasks.

History

When two University of California, Berkeley professors, Michael Stonebraker and Eugene Wong, read the papers about System R at IBM in 1973, they became interested in applying the concepts in those papers on a research project of their own, and thus Ingres was born.

Michael Stonebraker founded the Ingres Corporation in 1980 to commercialize the research project. In the mid-1980s, Ingres competed against Oracle in the then-emerging relational DBMS market. These two systems were considered to have comparable functionalities and performance, and were the market leaders. However, from then on, Ingres slowly lost market share to Oracle presumably due to the latter's more aggressive marketing, and more importantly, the recognition of SQL as the go-to query language, which Ingres did not use at the time. It used a query language called QUEL and the transition to SQL took three years, after which many of its customers had already shifted to Oracle.

Nevertheless, Ingres reincarnated as the underlying source code of many commercial databases, such as Microsoft SQL Server, and Postgres (post-Ingres). The Ingres Corporation continued to develop and support the original Ingres database. Although Ingres never achieved the same market prominence as its once head-to-head contender Oracle, it has endured over the years and never completely faded out of the market.

In 2011, Ingres Corporation was renamed Actian Corporation and has continued its support on the classic DBMS since then. It is currently branded as the Actian X Hybrid Database.

The system was briefly made open-source but has since switched back to a closed-source implementation.

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.

Compression

Null Suppression

Ingress's compression of tables only works on character and text columns. Trailing nulls and blanks are compressed. Non-nullable types such as Integer, floating point, and date are not compressed. It is not mentioned in the documentation that Ingres supports other types of compression schemes. Compression is applied to each page independently. It is shown in both the documentation and the original paper that compression in Ingres is useful in storing variable length domains. For example, if there is a column storing customer's reviews on a local business, then each review is padded at the end with nulls if it is shorter than the maximum allowed length to maintain homogeneity across the column. By applying compression on this column, these trailing nulls are compressed and the reviews take much less disk space because most of them are expected to be shorter than the maximum allowed length. However, the documentation suggests that compression should be used situationally, as decompression consumes CPU resources. It is up to the application to weight the trade-off between CPU utilization and disk space.

Concurrency Control

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

Ingres has five lock levels, which are, from most to least granular, Row, MVCC, Page, Table, Database. The MVCC level stands for multi-version concurrency control. It works by providing a snapshot of the database at a previous time at which the data is guaranteed to be consistent. The snapshot is reverse-constructed using the REDO and UNDO logs. MVCC guarantees that writing transactions do not contend with reading transactions on the same resource, thus improving concurrency.

There are four types of locks: physical locks, logical locks, control locks, and value locks. A logical lock is acquired when a transaction starts. It is held while the transaction is alive and ends at transaction commit or abort. A physical lock is used within a transaction to protect resources. A control lock is a physical lock on the Table Level that protects a table while its schema is being modified to ensure that no transactions can access the table while such a modification is in progress. A value lock is a physical lock on the Row or MVCC level. It is used when the isolation level is set to serializable to avoid phantom reads, which can occur under ranged queries if this type of locks is not present.

Ingres 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. The DBMS implicitly requests, escalates, and releases locks based on the statement or command, but it also provides commands for explicit lock management.

Ingres supports deadlock detection and aborts one of the transactions that are deadlocked. The aborted transaction is rolled back and the user is notified of an error on the aborted transaction.

Data Model

Relational

Ingres is a relational database system.

Foreign Keys

Supported

After primary keys are created, a user can create foreign keys using the New Foreign Key Dialog. Constraints and indexes on the foreign keys are also supported.

Indexes

B+Tree Hash Table R-Tree

A user 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 offers B+ Tree, Hash Table, and R-Tree as options. It can also create secondary indexes. In addition, it can create an index in parallel to reduce the time needed. All indexes are stored on disk.

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 and it provides the strongest consistency guarantee.

Joins

Nested Loop Join Hash Join Sort-Merge Join

Ingres supports joins with hash join, sort-merge join, and nested loop join algorithms. The query optimizer determines which type of join algorithm to use based on its analysis of the query. Nested-loop joins are most often seen on disjoint queries, where correlation variables and table names are arbitrarily used in random order. When there are no restrictions on either table in the join clause, and the rows being joined are spatially continuous, then the query optimizer is likely to choose sort-merge join or hash join.

Logging

Physical Logging

Ingres uses physical logging, keeping BEFORE and AFTER images for database updates. It chooses to record these images, not the updates, to make sure that all logs are idempotent. Ingres optimizes BEFORE images to save disk space such that if multiple records on the same page are to be updated, then only one BEFORE image is generated.

Query Compilation

Not Supported

Ingres does not support query compilation.

Query Execution

Materialized Model

Ingres executes queries by producing materialized views.

Query Interface

Custom API SQL Stored Procedures Command-line / Shell QUEL

Ingres was originally built on UNIX, hence it supports a command-line interface. It also supports SQL and QUEL and stored procedures in these two query languages. In addition, applications can use OpenAPI, a set of APIs in the C programming language, to interact with Ingres.

Storage Architecture

Disk-oriented

Although it is currently branded as the "Actian X Hybrid Database", the term "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.

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.