Redundancy, Consistency, Throughput, OLAP and OLTP

The redundancy/consistency/throughput trade-off

The relationship between redundancy, consistency, and throughput can be illustrated along a spectrum:

• Low redundancy: need only lock a few data items per update. Write throughput is high. Consistency is strong.
• High redundancy: fewer files or blocks need be accessed per query. Read latency is low. Consistency is relaxed.

Read-oriented databases

Introducing data redundancy can speed up read-oriented transactions at the expense of slowing down write-oriented transactions.

Database indexes demonstrate this point clearly. An index is redundant data (it duplicates key values from the base table) that speeds up reads but must be maintained on every write. Each INSERT, UPDATE, or DELETE incurs additional work to update the index structures.

Structure	Read effect	Write effect
B-tree index	Faster key lookups ($O(\log n)$ vs. $O(n)$)	Must update index on every write
Materialised view	Precomputed query results, instant reads	Must refresh after base table changes
Replica	Reads distributed across copies	All copies must be kept in sync

OLAP vs. OLTP

Databases are broadly categorised by their primary workload:

OLAP: Online Analytical Processing

Characteristic	Description
Write pattern	Write once, or journal/ledger updates
Associated with	Decision support, data warehousing
Data type	Historical data
Access pattern	Mostly reads
Optimisation	Optimised for reads
Data redundancy	High

OLTP: Online Transaction Processing

Characteristic	Description
Write pattern	Rich mix of queries and updates to live data
Associated with	Day-to-day operations
Data type	Current data
Access pattern	Mostly updates
Optimisation	Optimised for updates
Data redundancy	Low

The ETL pipeline

Data flows from operational systems to analytical systems through an ETL pipeline:

OLTP  ----Extract---->  Transform  ----Load---->  OLAP
(live, normalised)                             (historical, denormalised)

1. Extract: pull data from OLTP databases.
2. Transform: clean, reshape, and aggregate the data. This is where denormalisation and redundancy are introduced to optimise for analytical queries.
3. Load: insert the transformed data into the OLAP system (data warehouse).

Update history

An update to a relational database occludes the previous value of a field. The old value is overwritten and lost.

A revision control system (like Git) stores the update history — an additional dimension. Even for OLTP systems, an update history within a limited time horizon is stored for ACID durability (via write-ahead logs).

System	Historical state
Relational DBMS	Only current state (previous values overwritten)
Version control	Full history of every change
OLTP with WAL	Recent history maintained for crash recovery

Further concepts (no longer on syllabus)

• Data cube model: multi-dimensional views of data for OLAP.
• Embedded databases (FIDO = Fetch Intensive Data Organisation): read-optimised snapshots extracted from a normalised database, stored on devices for fast local queries.

Summary

• Low redundancy optimises for writes; high redundancy optimises for reads.
• Indexes are a form of controlled redundancy.
• OLAP systems prioritise reads and use high redundancy.
• OLTP systems prioritise writes and use low redundancy.
• ETL pipelines bridge the gap between OLTP and OLAP.