We've explored three fundamental file access methods: sequential access (reading linearly from start to end), direct access (jumping to arbitrary positions), and indexed access (using auxiliary structures to map keys to locations). Each has distinct strengths and weaknesses.

But knowing how each method works isn't enough. The critical skill is knowing when to use each one. This decision profoundly impacts application performance—sometimes by orders of magnitude.

A common mistake is applying the same access pattern everywhere. A developer comfortable with databases might over-index everything, incurring write overhead on workloads that are naturally sequential. Conversely, someone used to streaming data might perform linear scans where a simple index would reduce runtime from hours to milliseconds.

This page brings together everything we've learned to provide a decision framework. We'll compare the methods head-to-head across multiple dimensions, examine hybrid strategies, and work through real-world scenarios to build intuition for access method selection.

Let's systematically compare the three access methods across critical performance dimensions. These comparisons assume typical hardware (HDD or SSD) and reasonable implementation quality.

Interpretation:

• Sequential access excels when you process data in order (logs, streams, batch ETL). It achieves maximum throughput because it aligns with physical storage characteristics.

• Direct access excels when you know the exact position of what you need (record-based files, memory-mapped editing, fixed-size record arrays).

• Indexed access excels when you need to find records by key (databases, search, any key-value lookup). It trades space and write complexity for dramatically faster reads.

The key insight: None of these methods is universally superior. Each optimizes for different access patterns. The right choice depends entirely on your workload.

Understanding the quantitative performance differences is crucial for making informed decisions. Let's examine realistic throughput numbers across storage media.

Scenario: Reading 1 million 100-byte records (100MB total)

Key observations:

1. HDD random vs. sequential: 10,000x difference

   • Sequential: ~150 MB/s
   • Random: ~0.015 MB/s per operation
   • This explains why databases, file systems, and storage engines obsess over sequential I/O patterns on spinning disks.

2. SSD narrows but doesn't eliminate the gap

   • The ratio drops from 10,000x to ~10-50x
   • Random access becomes viable but sequential still wins for throughput
   • Write amplification on SSDs makes the random write penalty larger than raw numbers suggest

3. Indexed access is worthwhile even on SSD

   • Finding 1 record among 1 million: 0.08ms (indexed) vs. 10ms (sequential scan half)
   • For single lookups, indexing provides 100x improvement even on NVMe

4. Batch matters

   • If you need 1,000 random records from 1 million, indexed access with batching (sort reads by offset) can approach sequential performance

To select the right access method, analyze your workload along these dimensions:

1. Read vs. Write Ratio

• Read-heavy (>90% reads): Favor indexed access; write overhead is amortized over many reads
• Write-heavy (>50% writes): Consider sequential (append-only) or be very selective with indexes
• Mixed: Balance carefully; each index multiplies write cost

2. Access Pattern

• Full scans: Sequential wins decisively
• Point lookups by key: Indexed is essential
• Point lookups by position: Direct access
• Range queries: Sorted data + sparse index or B-tree
• Mixed: May need multiple access paths

3. Data Size

• Fits in RAM: Access method matters less; everything is fast
• Fits on SSD: Random access viable; index benefits significant
• Spinning disk: Sequential critical; random access is expensive
• Distributed storage: Network latency dominates; batch and prefetch

4. Latency Requirements

• Batch processing (minutes okay): Sequential scans acceptable
• Interactive (100ms): Need indexes for large datasets
• Real-time (<10ms): Require indexes and caching

5. Consistency Requirements

• Strict (ACID): Indexed access with WAL
• Eventual: Can defer index updates, use async writes
• Immutable data: Simpler; build indexes once

Real-world systems rarely use a single access method in isolation. The most effective designs combine methods strategically, using each where it excels.

Pattern 1: Index + Sequential (Common in Databases)

Use an index to find the starting position, then read sequentially:

SELECT * FROM orders WHERE date > '2024-01-01' ORDER BY date;

1. Use B-tree index to find first matching row (indexed access)
2. Scan forward through sorted data (sequential access)

This combines O(log n) initial lookup with O(k) streaming of k results.

Pattern 2: Append-Only with Index (Log-Structured)

Write sequentially for performance; build index for lookup:

1. All writes append to log file (sequential)
2. Background process builds/updates index
3. Reads use index, then follow pointers

Used by: Log-structured merge trees (LSM), Kafka, many modern databases.

Pattern 3: Time-Partitioned with Per-Partition Index

Partition data by time; each partition has its own index:

/data/2024/01/data.db   ← index for January 2024
/data/2024/02/data.db   ← index for February 2024
/data/2024/03/data.db   ← index for March 2024

Query for February data only touches February index/files

Benefits: Old partitions are immutable (simple), queries skip irrelevant partitions.

Pattern 4: Tiered Storage (Hot/Warm/Cold)

Different access methods at different tiers:

Pattern 5: Materialized Views with Different Access

Maintain multiple representations optimized for different queries:

• Base table: B-tree indexed by primary key (for point lookups)
• Materialized view 1: Sorted by date (for time-range scans)
• Materialized view 2: Hash index by customer_id (for customer lookups)

Trade-off: Storage multiplication for query flexibility.

Pattern 6: Index-Organized Tables

Store data directly in the index structure (clustered index):

• The B-tree leaf nodes contain the actual records
• Primary key lookup = single index traversal
• Range queries on primary key are extremely efficient
• No separate heap file to maintain

Used by: MySQL InnoDB primary indexes, Oracle IOTs.

Here's a systematic decision process for selecting access methods:

Step 1: Characterize the Workload

• What are the primary operations? (read all, read by key, read by range, append, update, delete)
• What's the data volume? (fits in RAM, fits on SSD, requires HDD/distributed)
• What are latency requirements? (batch, interactive, real-time)
• What's the read/write ratio?

Step 2: Identify the Dominant Access Pattern

For each operation type, estimate frequency and criticality:

Operation          | Frequency | Latency Requirement | Priority
-------------------|-----------|---------------------|----------
Full data export   | Daily     | Hours OK            | Low
Lookup by user_id  | 10K/sec   | <50ms               | Critical
Recent records scan| 100/sec   | <200ms              | High
New record append  | 1K/sec    | <100ms              | High

Step 3: Match Patterns to Methods

Using the dominant pattern, apply this decision tree:

Step 4: Validate with Prototyping

Before committing to an architecture:

1. Build a minimal prototype with realistic data volumes
2. Run representative query workloads
3. Measure actual latencies and throughput
4. Compare against requirements
5. Iterate if needed

Step 5: Plan for Evolution

Workloads change. Design for flexibility:

• Abstract access patterns behind interfaces
• Monitor access patterns in production
• Be prepared to add/remove indexes
• Consider tiered approaches for growing data

Let's examine how real systems combine access methods to meet their requirements.

Case Study 1: Apache Kafka

Workload: High-throughput message streaming. Producers append; consumers read sequentially from their last position.

Solution:

• Primary access: Sequential append/read within partitions
• Index: Sparse offset index (message offset → file position)
• Consumer reads: Sequential from a starting offset
• Seek to timestamp: Use timestamp index to find approximate position, then scan

Why it works: The workload is inherently sequential. Indexes are minimal (sparse) because consumers almost never need random access.

Case Study 2: SQLite

Workload: Embedded database for applications. Mixed OLTP-style queries.

Solution:

• Primary: B-tree indexes (data stored in B-tree leaves)
• Table scans: Sequential read of B-tree leaves
• Index lookups: B-tree traversal O(log n)
• Writes: In-place update + WAL for durability

Why it works: B-trees handle the diverse query patterns. Single-file design simplifies embedding.

Case Study 3: Elasticsearch

Workload: Full-text search. Write once, query many times.

Solution:

• Primary: Inverted index (term → document IDs)
• Document retrieval: Forward index (doc ID → stored fields)
• Segment merging: Sequential I/O for compaction
• Immutable segments: Once written, never modified

Why it works: Inverted indexes are optimal for text search. Immutability simplifies concurrency and enables aggressive caching.

Case Study 4: RocksDB/LevelDB

Workload: High-write-volume key-value store.

Solution:

• Writes: All writes go to in-memory buffer (memtable)
• Flush: Memtable → immutable SSTable on disk (sequential write)
• Reads: Check memtable, then SSTables level by level
• Compaction: Merge SSTables in background (sequential I/O)
• Index: Per-SSTable index blocks + bloom filters

Why it works: LSM design converts random writes to sequential, trading read amplification for write performance.

Understanding common mistakes helps you avoid them:

Pitfall 1: Over-Indexing

Symptom: Slow writes, excessive disk usage, minimal read improvement.

Cause: Adding indexes 'just in case' without profiling actual queries.

Solution: Only index columns that are actually used in WHERE clauses of frequent queries. Remove unused indexes. Measure before and after.

Pitfall 2: Under-Indexing

Symptom: Queries that should be fast take seconds or minutes.

Cause: Assuming small data will stay small; not adding indexes as data grows.

Solution: Monitor query latencies. Add indexes when full scans become unacceptable. Plan indexing strategy early.

Pitfall 3: Sequential Access on Random Workload

Symptom: Log processing takes hours; each lookup scans entire file.

Cause: Using append-only logs for key-based lookup without indexing.

Solution: If you need key-based lookup, add an index. Consider a database instead of raw files.

Pitfall 4: Random Access on Sequential Workload

Symptom: Batch job orders of magnitude slower than expected.

Cause: Processing records in random order instead of disk order.

Solution: Sort work by file offset before processing. Batch related operations.

Pitfall 5: Ignoring Cache Effects

Symptom: Index lookups slower than expected; 'random' reads actually sequential.

Cause: Not accounting for OS page cache behavior.

Solution: Understand your working set size vs. available RAM. Hot data stays cached; cold data requires disk I/O. Design for cache friendliness.

We've conducted a comprehensive comparison of file access methods and developed a framework for selection. Let's consolidate the key insights:

What's next:

We've covered sequential, direct, and indexed access using traditional read/write system calls. But there's another powerful technique: memory-mapped file access. The next page explores how mapping files directly into virtual memory provides an elegant alternative that combines the simplicity of memory access with the persistence of files.