PostgreSQL Indexing Trade-Offs | Dataford Interview Questions

Explain the performance trade-offs between different indexing strategies in PostgreSQL.

Compare B-tree, Hash, GIN, GiST, BRIN, multicolumn, partial, and covering indexes. Relate the choices to the representative query and explain read performance, write overhead, storage, selectivity, and maintenance considerations.

Output

Return one row per qualifying asset with asset_id, owner_id, created_at, event_type, and event_time.
Include active assets owned by owner_id 101 whose metadata contains {"channel": "web"}. Preserve assets without a matching event.
Order by created_at descending, then asset_id ascending.

Schema

assets

Column	Type	Description
idPK	INT	Asset identifier
owner_id	INT	Owning account identifier
status	VARCHAR(20)	Current asset status
created_at	TIMESTAMP	Asset creation timestamp
metadata	JSONB	Searchable asset metadata

asset_events

Column	Type	Description
idPK	INT	Event identifier
asset_id	INT	Referenced asset
event_type	VARCHAR(30)	Event classification
event_time	TIMESTAMP	Event timestamp

Your question is PostgreSQL Indexing Trade-Offs. 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