Column-store vs row-store in DBMS Theory - Visual Side-by-Side Comparison

Concept Flow - Column-store vs row-store

Start Query

↓

Choose Storage Type

↓

Row-store

↓

End Query

The flow shows how a database query chooses between row-store and column-store, then reads and processes data accordingly before returning results.

Execution Sample

DBMS Theory

SELECT name, age FROM users WHERE age > 30;
-- Row-store reads full rows
-- Column-store reads only 'name' and 'age' columns

This query fetches names and ages of users older than 30, showing how row-store and column-store access data differently.

Analysis Table

Step	Storage Type	Data Access	Data Read	Processing	Output
1	Row-store	Read full rows	All columns of each row	Filter rows where age > 30	Selected rows with all columns
2	Row-store	Extract needed columns	From filtered rows	Select 'name' and 'age'	Final output rows with name and age
3	Column-store	Read only needed columns	'name' and 'age' columns only	Filter ages > 30	Filtered columns data
4	Column-store	Combine columns	Filtered 'name' and 'age'	Form rows for output	Final output rows with name and age
5	End	Query complete	-	-	Results returned to user

💡 Query ends after data is read, filtered, and output is prepared.

State Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	Final
Rows Read (Row-store)	0	All rows with all columns	Filtered rows (age > 30)	N/A	N/A	Filtered rows with name and age
Columns Read (Column-store)	0	N/A	N/A	'name' and 'age' columns	Filtered columns (age > 30)	Filtered columns combined as rows
Output Rows	0	0	Filtered rows with name and age	0	Filtered rows with name and age	Final output

Key Insights - 3 Insights

Why does row-store read all columns even if only some are needed?

Row-store stores data by rows, so reading a row means reading all its columns together, as shown in execution_table step 1.

How does column-store improve performance for queries needing few columns?

Column-store reads only the requested columns, reducing data read and speeding filtering, as seen in execution_table step 3.

Why must column-store combine columns after filtering?

Because columns are stored separately, they must be combined back into rows for output, shown in execution_table step 4.

Visual Quiz - 3 Questions

According to the execution_table, what data does row-store read at step 1?

A All columns of each row

B Only 'name' and 'age' columns

C Only rows where age > 30

D Filtered columns combined as rows

💡 Hint

See execution_table row 1 under 'Data Read' column.

At which step does column-store filter the ages greater than 30?

A Step 2

B Step 3

C Step 4

D Step 1

💡 Hint

Check execution_table row 3 under 'Processing' column.

If the query requested all columns, how would column-store's data read change?

A It would read all columns like row-store

B It would still read only 'name' and 'age'

C It would read no columns

D It would read only filtered rows

💡 Hint

Refer to variable_tracker 'Columns Read (Column-store)' and think about reading all columns.

Concept Snapshot

Column-store vs Row-store:
- Row-store stores data row by row.
- Reading a row reads all columns.
- Column-store stores data column by column.
- Reading only needed columns speeds queries.
- Column-store combines columns back into rows for output.

Full Transcript

This visual execution compares column-store and row-store database storage. When a query runs, it chooses a storage type. Row-store reads full rows including all columns, then filters rows and selects needed columns. Column-store reads only requested columns, filters data, then combines columns into rows for output. Variables track how many rows or columns are read and filtered at each step. Key moments clarify why row-store reads all columns and how column-store improves performance by reading fewer columns. The quiz tests understanding of data access and filtering steps. The snapshot summarizes the main differences and behaviors of both storage types.