Join Massive Tables with Low Memory | Dataford Interview Questions

Can you perform a join on two massive tables while minimizing memory usage?

Write a PostgreSQL query that returns matching active records from both tables. The approach should avoid unnecessary rows and columns before joining, while preserving duplicate matches for the same join key.

Output

One row per matching pair of active records
Columns: a_record_id, b_record_id, join_key, a_payload, b_payload
Exclude inactive records and rows with no match
Order by a_record_id, then b_record_id

Schema

massive_table_a

Column	Type	Description
record_idPK	BIGINT	Unique identifier for a record in table A
join_key	BIGINT	Key used to match records across both tables
is_active	BOOLEAN	Whether the record is eligible for joining
payload	VARCHAR(100)	Payload value from table A

massive_table_b

Column	Type	Description
record_idPK	BIGINT	Unique identifier for a record in table B
join_key	BIGINT	Key used to match records across both tables
is_active	BOOLEAN	Whether the record is eligible for joining
payload	VARCHAR(100)	Payload value from table B

Your question is Join Massive Tables with Low Memory. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem