DATA2001 · Data Science, Big Data and Data Variety
Scalable Data Analytics
Week 5 explains why big data needs a storage-aware approach: the memory hierarchy (RAM vs disk, with disk roughly 100,000 times slower), block/page-wise transfer and caching, then indexing as a separate pointer structure that avoids full scans, and partitioning/sharding for scale-out. It is the most systems-flavoured chapter and a reliable source of short-answer and MCQ questions on the Week 9 midterm and the 50% final.
What this chapter covers
- 01Storage hierarchy: RAM (fast, volatile, dear) vs HDD/SSD (slow, big, cheap) vs tape; READ = disk to RAM, WRITE = RAM to disk
- 02Reducing I/O cost: block/page-wise transfer, caching/buffering hot vs cold data
- 03Indexing as a separate pointer structure (book-index analogy); search key; composite/multi-attribute indexes
- 04Clustered index (rows in key order, one per table) vs non-clustered (key+pointer, many allowed) vs covering index
- 05CREATE INDEX / DROP INDEX; index selection from WHERE-clause columns; the fits-in-RAM trade-off
- 06Distributed processing: shared-nothing clusters; parallelise first, then distribute
- 07Data partitioning: horizontal (rows by predicate) vs vertical (columns, PK repeated) and sharding with co-located joins
Choosing a covering index for a hot query
- +1Pick the search-key columns from the query. The filter is on state, so state should lead the index; ORDER BY postcode means postcode should follow so the index also supplies the sort order. Start from a non-clustered index on (state, postcode).
- +1Make it covering. The query only reads name, postcode and state, so include name in the index: CREATE INDEX StationsCoverIdx ON Stations (state, postcode, name). Now every column the query needs lives in the index.
- +1Explain why it avoids the table. Because the index contains all queried columns, the DBMS answers the query from the index alone — no lookup back into the base table (a covering index), and the (state, postcode) prefix both filters state='NSW' and returns rows already ordered by postcode.
- +1State a cost. The index is extra data that must be stored and, crucially, maintained on every INSERT/UPDATE/DELETE, slowing writes; if it does not fit in RAM it also adds its own I/O. Indexes speed reads at the expense of writes and space.
Key terms
- Memory hierarchy
- Layers of storage trading speed for cost and size: RAM is fast, volatile and expensive; disk (HDD/SSD) is slow, stable and cheap (an HDD is roughly 100,000 times slower to access than RAM); tape is cheapest and slowest. Good design minimises data movement between layers.
- Index
- A separate pointer data structure that maps a search key to the location of matching rows, so the DBMS can find data without scanning the whole table — like consulting a book's index instead of reading every page. Much smaller than the data it points to.
- Clustered vs non-clustered index
- A clustered index stores the table's rows physically in key order, so there can be only one per table (default on the primary key). A non-clustered index keeps key+pointer pairs while the table stays in its own order, and a table may have many.
- Covering index
- A non-clustered index that contains every column a particular query needs, so the query is answered from the index alone with no lookup into the base table.
- Horizontal vs vertical partitioning
- Horizontal partitioning splits rows by a predicate (e.g. state = 'NSW' vs 'VIC') across tables or nodes; vertical partitioning splits columns into separate tables, repeating the primary key in each so rows can be rejoined.
- Sharding
- Distributing partitions across sites in a distributed database, assuming most queries touch one shard. Cross-shard joins are costly unless the tables are co-located — partitioned on the join attribute so matching tuples sit on the same node.
Scalable Data Analytics FAQ
Why is disk access such a bottleneck?
Reading from an HDD is on the order of 100,000 times slower than reading from RAM, so moving data between disk and memory dominates the cost of large-data operations. The whole toolkit of this week — block/page-wise transfer, caching hot data in RAM, indexing to avoid full scans, and partitioning to shrink the data each query touches — exists to minimise that movement.
When does an index actually help?
An index helps a query that filters or sorts on its search-key columns and is selective (returns a small fraction of rows). If the index fits in RAM, lookups add no extra I/O and searching is much faster than a full scan. But indexes cost storage and must be maintained on every write, so on a write-heavy table or a query that reads most rows, an index can be a net loss. Choose search keys from WHERE-clause columns and favour indexes that benefit many queries.
What is the difference between partitioning and sharding?
Partitioning stores subsets of a dataset separately — horizontally by rows (by a predicate) or vertically by columns (repeating the primary key) — to query smaller sets and parallelise. Sharding is partitioning spread across the nodes of a distributed database. Both gain scalability, but sharding assumes queries hit one shard; cross-shard joins are counter-productive unless the tables are co-located on the join key.
Can AI help me with indexing and partitioning decisions?
Yes. Sia can talk through which index to build for a given query and workload, why a clustered index differs from a covering one, and how to partition a large table, and it will check your reasoning about I/O cost and trade-offs. Bring your own query. It is a study aid and does not complete graded assessment; the University of Sydney academic-integrity policy applies.
Exam move
This chapter rewards clear cause-and-effect reasoning, so practise justifying design choices rather than memorising facts. Be able to rank RAM, disk and tape on speed, cost and volatility, and explain why block transfer, caching, indexing and partitioning all reduce data movement. Drill index selection on sample queries: for each, name the columns to index, their order (equality first, then range/sort), and whether a covering index is worth its write cost; contrast clustered vs non-clustered vs covering in a sentence each. Do the same for partitioning — decide horizontal vs vertical, pick the partition key, and explain co-location for cross-shard joins. Because this block is examined in the Week 9 midterm (Weeks 4-8) and again in the final, rehearse short 'which index / which partition would you choose and why?' answers. Ask Sia to set fresh design questions and mark your justifications; confirm details on Canvas.
Working through Scalable Data Analytics in DATA2001? Sia is AskSia’s AI Data Science tutor — ask any DATA2001 Scalable Data Analytics question and get a clear, step-by-step explanation grounded in how DATA2001 is taught and assessed. Read this chapter free, then take your hardest questions to Sia.
