Classification Based on Access Pattern

Types of systems

Before we can understand classification of DBMS on access patterns, we need to understand the different types of systems.

Online systems

Online systems wait for a request from a client. Once the request arrives, it processes the demand to produce a response. Online systems are called request/response systems since the client is waiting for a response from the system. The prime requirement for online systems is response with millisecond latency.

Typical applications of online systems include web applications and transactional systems.

Offline systems

Offline systems are batch-processing systems that take a batch input, process it, and produce an output batch. These systems run jobs at regular intervals. Since the client is not waiting for the output, response time is not a prime metric for offline systems. Rather, the throughput of the jobs is the critical metric for these systems.

Typical applications of offline systems include data warehouses, business intelligence, and reporting.

Near real-time systems

Near real-time systems act as an intermediary between offline and online systems. Near real-time systems process data as soon as it arrives, but the client doesn't wait for the response. Unlike offline systems, these systems act on input and do not get triggered at regular intervals. Near real-time systems are called stream processing systems.

Typical applications of near real-time systems include clickstream analysis and user recommendations.

Classification

There are two types of DBMS based on data access pattern:

• Online Transaction Processing Systems (OLTP)

• Online Analytical Processing Systems (OLAP)

Online transaction processing DBMS

OLTP systems manage applications that provide short and predictable response times. In addition, they handle applications that require transactionality and consistency. For example, OLTP systems are commonly used for user-facing applications, which require millisecond response times. All online systems fall into this bucket. DBMS built on the principle of OLTP mandates that queries be predefined, short lived, targeted, and fetch finite sets of records.

In OLTP systems, specific query patterns are optimized, such as point-in-time and range queries. Examples of OLTP DBMS include MySQL, Postgresql, and DynamoDB.

Online analytical processing DBMS

OLAP systems handle applications capable of running complex computations and aggregations on various dimensions. In addition, they manage applications that need to fetch large batches of records. OLAP systems find their applications in analytical workloads that execute long-running ad hoc queries. All offline systems fall into this bucket. DBMS built on the principle of OLAP can handle ad hoc queries that can span from a few minutes to hours.

Examples of OLAP DBMS include Vertica and Redshift.

Note: OLTP and OLAP systems coexist and are not mutually exclusive.