This page needs a newer browser to run its tools. You can still read everything here.

Database Management Systems (DBMS) Module 8

Storage and file organisation interview questions

Interview questions on storage and file organisation: short warm-ups, then what each harder question tests, the first step of a good answer and its follow-ups.

16 questions: 4 warm-ups and 12 with model answers · Levels: Fresher to staff

Warm-up questions

Short questions with short answers, each answered in full.

Fresher

What is a slotted page, and why does a record id name a slot instead of a byte offset?

Answered in the lesson Pages, records and the slotted-page layout
Fresher

What is the difference between a heap file and a sorted file, and what does each make cheap?

Answered in the lesson File organisations: heap, sorted, hashed, clustered
Fresher

In a buffer pool, what are a pin count and a dirty bit, and what does each stop the buffer manager from doing?

Answered in the lesson Buffer pool management and page replacement
Fresher to mid

When does a column store beat a row store, and when does it lose?

Answered in the lesson Row stores, column stores and compression

Questions with model answers

What each question tests, the first step of a good answer and the follow-ups to expect.

Mid to senior

Estimate how many 8 kB PostgreSQL pages a table of 50 million rows needs, with columns id bigint, customer_id integer, quantity smallint, paid boolean and a 10-character note. What changes if paid is declared first, or if note is usually NULL?
What it tests

Estimating from page, line-pointer, row-header and alignment sizes instead of summing column widths, and seeing what column order and NULLs do to a row.

First step

Write down the fixed costs first: a 24-byte page header, a 4-byte line pointer per row and a 23-byte row header padded to 24. Then lay out the columns in order at their alignments, round the row up to 8 bytes and divide the 8,168 bytes left in a page by the row plus its line pointer.
Follow-ups
- The table also has a primary-key index on id. Roughly how big is it?
- Why can a single NULL make a row of 12 columns longer?
- How would you check your estimate on a real server?
From the lesson Pages, records and the slotted-page layout
Model answer, Pro. Model answers open with any pass; prices are in the notice on this page.
Opening the model answers with your pass…
Model answers open with any MySmartCoPilot pass.
Pro pass · 1 week₹79$2.99
30 days₹179$5.99
1 year₹1,699$45
One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.
Passes can’t be bought right now, so the model answers can’t be opened at the moment. If you have a pass, enter your licence key.
Choose a pass I have a licence key
It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

To skip index lookups, a team caches the ctid of each hot row. After a VACUUM FULL, and sometimes after ordinary updates, cached ctids return the wrong rows or none. Explain what happened and what you would store instead.
What it tests

What a ctid identifies, the operations that change or reuse it, and why keys, covering indexes or cached data are the safer tools.

First step

Recall what a ctid is: the location of one row version, a page number and an item number. An UPDATE writes a new version at a new location, and VACUUM FULL rewrites the whole table, so the cache holds addresses that now belong to other rows or to nothing.
Follow-ups
- Why does a ctid survive the compaction of its page?
- How could the application detect a stale ctid cheaply?
- What would you do if the index lookups really are too slow?
From the lesson Pages, records and the slotted-page layout
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

Each row of a table carries a 6 KB JSON document beside a few small columns, and a report that reads only the small columns has become slow. Explain what happens to such rows in SQLite and in PostgreSQL, and what you would change.
What it tests

Where each engine stores a value larger than its page allows, what a scan of the small columns has to read, and the schema changes that make it cheap again.

First step

Work out where the 6 KB goes. In SQLite a 4,096-byte table leaf keeps at most 4,061 bytes of payload, so part of each row stays on the leaf and the rest goes to overflow pages; in PostgreSQL a row wider than about 2 kB goes through TOAST. Then count how many rows each leaf or heap page still holds.
Follow-ups
- How many leaf pages does an SQLite scan read for 100,000 such rows, against rows without the document?
- Does column order matter in SQLite?
- When would you move the document into a table of its own?
From the lesson Pages, records and the slotted-page layout
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

A query reads 2 % of the rows of a 10 GB PostgreSQL table through a range on an indexed column, and the planner chooses a sequential scan. Is the planner wrong? Use page counts to decide what each plan costs.
What it tests

Counting heap pages fetched by an index range scan, the role of physical order and correlation, and random against sequential page reads.

First step

Turn the sizes into pages: 10 GB is about 1.31 million pages of 8 kB. Then ask how the matching rows are spread. If they are scattered, a page holding 100 rows contains at least one of them with probability 1 − 0.98¹⁰⁰, about 87 %, so the index scan touches most pages anyway, in random order.
Follow-ups
- What does the column's correlation in pg_stats tell you here?
- Where does a bitmap heap scan fit between the two plans?
- What would you change if this range query is important?
From the lesson File organisations: heap, sorted, hashed, clustered
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

An InnoDB orders table uses a random UUID stored as CHAR(36) as its primary key and has three secondary indexes. Inserts slow down as the table grows, and the indexes are larger than expected. Explain both with numbers and propose a better key design.
What it tests

The clustered index and what secondary index records contain, random against increasing insert positions, and sizing the cost of a long primary key.

First step

Recall what InnoDB stores where: the rows live in the clustered index, ordered by the primary key, and every secondary index record also holds the primary key. Then follow one insert with a random key, and one with an increasing key, through the clustered index.
Follow-ups
- How much space does the long key cost across 50 million rows?
- Why does the slowdown start only after the table outgrows memory?
- What changes if the UUIDs are time-ordered?
From the lesson File organisations: heap, sorted, hashed, clustered
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Fresher to mid

A static hashed file sized for 2 million customers now holds 5 million, and lookups have slowed. A second hashed file, keyed by order ids that all end in 000, was slow from the first day. Diagnose both and say what you would change.
What it tests

Load factor and overflow chains, how a key pattern interacts with the number of buckets, and the remedies for each problem.

First step

Compute the load factor of the first file: 5 million records in space for 2 million is 2.5, so the average bucket has a chain of about three blocks. For the second file, check whether the ids and the number of buckets share a factor: ids that are multiples of 1,000 use only M ÷ gcd(M, 1,000) buckets.
Follow-ups
- How many blocks does a lookup of an absent key read in the first file?
- With 50,000 buckets, how many buckets does the second file use?
- Why would extendible or linear hashing have avoided the first problem?
From the lesson File organisations: heap, sorted, hashed, clustered
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 2 minutes
Mid to senior

Every night a report scans a 200 GB table on a server with a 32 GB buffer pool, and the next morning the order-entry service is slow for about an hour. Explain the mechanism and what you would change, in the database and in the schedule.
What it tests

Recognising sequential flooding, how real engines resist it (scan rings, midpoint insertion), the operating system's cache, and warming up after eviction.

First step

Compare sizes: the scan reads more than six times the pool, each page once. With plain LRU every page of the scan becomes the most recent in turn, so by the end the order service's hot pages have been evicted and the first hour of the morning reads them back from storage.
Follow-ups
- Does PostgreSQL protect its shared buffers from this scan, and does it protect the operating system's cache?
- What does InnoDB do with a page that a scan reads once?
- How would you warm the pool before the morning traffic?
From the lesson Buffer pool management and page replacement
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Senior to staff

A teammate proposes replacing your storage engine's buffer pool with mmap to simplify the code. What do you object to, what would you measure, and is there a case where mmap is reasonable?
What it tests

The write-ahead rule against the kernel's freedom to write pages, blocking page faults, I/O errors as signals, eviction costs, and a fair benchmark.

First step

Start from the property the buffer pool exists to keep: a dirty page may reach the disk only after its log records. With mmap the operating system may write a dirty page at any moment and gives no notice, so ask how the design would keep the write-ahead rule before discussing speed.
Follow-ups
- How do mmap-based engines keep transactions safe, and what does each approach cost?
- What would a fair benchmark of the two designs look like?
- When is mmap a sensible choice?
From the lesson Buffer pool management and page replacement
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

A database serves 40,000 page requests a second at a 96 % hit ratio, and its storage handles 3,000 random reads a second. Is the buffer pool big enough? What hit ratio do you need, and how would you work out how much memory would buy it?
What it tests

Turning a hit ratio into a read rate, the leverage of the miss ratio, and estimating the working set before buying memory.

First step

Work with misses, not hits: 4 % of 40,000 is 1,600 reads a second, about half of what the storage can do, so the system is not saturated now, but a 50 % rise in traffic or a drop to 94 % would reach 2,400 to 3,600 reads a second.
Follow-ups
- What hit ratio keeps the reads under a third of the storage's capacity?
- How would you estimate the memory that hit ratio needs?
- Why can a bigger pool fail to raise the hit ratio?
From the lesson Buffer pool management and page replacement
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

A monthly report sums two columns of a 120-column PostgreSQL table of 300 million rows and takes 40 minutes. Estimate what it reads, explain why, and propose two designs that cut the time, with what each costs.
What it tests

Estimating bytes read by a row store against a column layout, and weighing summary tables, columnar copies, covering indexes and vertical splits.

First step

Estimate the row width first: with values of about 8 bytes, a row is about 1 KB with its header, so the table is about 300 GB, and a sequential scan reads all of it to use 2 of 120 columns. The two columns alone are about 2 × 8 × 300 million bytes, 4.8 GB.
Follow-ups
- At what read speed does 300 GB take 40 minutes, and what would 4.8 GB take?
- What does a covering index for the report cost the writes?
- Why not simply split the table into two tables?
From the lesson Row stores, column stores and compression
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

A team writes daily event files in a columnar format. After a change to the writer, the same day's data takes 9 GB instead of 3 GB. Explain how sort order can change compressed size this much, and how you would choose a sort order.
What it tests

How run-length, dictionary and delta encodings depend on neighbouring values, and choosing sort keys by cardinality and query filters.

First step

Ask what changed in the order of the rows. Run-length encoding stores a value per run, so a column sorted into long runs costs almost nothing, while the same column in arrival order may change at nearly every row and cost more than raw. Compare the run counts of the low-cardinality columns before and after.
Follow-ups
- Which columns should come first in the sort key?
- What does the new order cost the writer?
- How do you check a sort order before changing production?
From the lesson Row stores, column stores and compression
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 2 minutes
Senior to staff

Queries on a store of Parquet files filter on customer_id and match about 0.1 % of rows, yet they read nearly every byte of the column. The writer emits row groups of 10,000 rows in arrival order. What is wrong, and what would you change?
What it tests

How min and max statistics skip row groups and pages, why arrival order defeats them, and choosing sort order, row group size and filters for equality lookups.

First step

Look at the statistics: in arrival order every row group holds customers from across the whole id range, so each row group's minimum and maximum include almost any id and nothing can be skipped. Small row groups only add metadata and small reads.
Follow-ups
- How much would sorting by customer_id let a reader skip?
- What row group size would you choose, and why?
- What helps when the data cannot be sorted by the filter column?
From the lesson Row stores, column stores and compression
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes

The lessons of this module

Disks, SSDs, RAID and the I/O cost model Free: 25 minutes
Pages, records and the slotted-page layout Free core, Pro depth: 10 of 30 minutes free
File organisations: heap, sorted, hashed, clustered Free core, Pro depth: 8 of 25 minutes free
Buffer pool management and page replacement Free core, Pro depth: 9 of 30 minutes free
Row stores, column stores and compression Free core, Pro depth: 11 of 30 minutes free

All Database Management Systems (DBMS) interview questions · The Database Management Systems (DBMS) track

Warm-up questions

What is a slotted page, and why does a record id name a slot instead of a byte offset?

What is the difference between a heap file and a sorted file, and what does each make cheap?

In a buffer pool, what are a pin count and a dirty bit, and what does each stop the buffer manager from doing?

When does a column store beat a row store, and when does it lose?

Questions with model answers

Estimate how many 8 kB PostgreSQL pages a table of 50 million rows needs, with columns id bigint, customer_id integer, quantity smallint, paid boolean and a 10-character note. What changes if paid is declared first, or if note is usually NULL?

To skip index lookups, a team caches the ctid of each hot row. After a VACUUM FULL, and sometimes after ordinary updates, cached ctids return the wrong rows or none. Explain what happened and what you would store instead.

Each row of a table carries a 6 KB JSON document beside a few small columns, and a report that reads only the small columns has become slow. Explain what happens to such rows in SQLite and in PostgreSQL, and what you would change.

A query reads 2 % of the rows of a 10 GB PostgreSQL table through a range on an indexed column, and the planner chooses a sequential scan. Is the planner wrong? Use page counts to decide what each plan costs.

An InnoDB orders table uses a random UUID stored as CHAR(36) as its primary key and has three secondary indexes. Inserts slow down as the table grows, and the indexes are larger than expected. Explain both with numbers and propose a better key design.

A static hashed file sized for 2 million customers now holds 5 million, and lookups have slowed. A second hashed file, keyed by order ids that all end in 000, was slow from the first day. Diagnose both and say what you would change.

Every night a report scans a 200 GB table on a server with a 32 GB buffer pool, and the next morning the order-entry service is slow for about an hour. Explain the mechanism and what you would change, in the database and in the schedule.

A teammate proposes replacing your storage engine's buffer pool with mmap to simplify the code. What do you object to, what would you measure, and is there a case where mmap is reasonable?

A database serves 40,000 page requests a second at a 96 % hit ratio, and its storage handles 3,000 random reads a second. Is the buffer pool big enough? What hit ratio do you need, and how would you work out how much memory would buy it?

A monthly report sums two columns of a 120-column PostgreSQL table of 300 million rows and takes 40 minutes. Estimate what it reads, explain why, and propose two designs that cut the time, with what each costs.

A team writes daily event files in a columnar format. After a change to the writer, the same day's data takes 9 GB instead of 3 GB. Explain how sort order can change compressed size this much, and how you would choose a sort order.

Queries on a store of Parquet files filter on customer_id and match about 0.1 % of rows, yet they read nearly every byte of the column. The writer emits row groups of 10,000 rows in arrival order. What is wrong, and what would you change?