Postgres vs OpenSearch Trade-offs | Dataford Interview Questions

What are the trade-offs between using Postgres and OpenSearch for DomainTools data retrieval needs?

Using the supplied retrieval benchmark records, write a PostgreSQL query that compares the two systems across workload types. Include latency, reliability, and average result volume so the output supports the trade-off analysis.

Output

One row per system and workload type, with product_name, workload_type, benchmark_count, avg_latency_ms, p95_latency_ms, success_rate_pct, and avg_rows_returned.
Include only Postgres and OpenSearch records, ordered by product_name, then workload_type.

Schema

retrieval_systems

Column	Type	Description
system_idPK	INT	Unique retrieval system identifier
product_name	VARCHAR(50)	Retrieval product name
environment	VARCHAR(30)	Deployment environment

retrieval_benchmarks

Column	Type	Description
benchmark_idPK	INT	Unique benchmark observation identifier
system_id	INT	Referenced retrieval system
workload_type	VARCHAR(40)	Type of retrieval workload
latency_ms	DECIMAL(10,2)	Observed request latency in milliseconds
rows_returned	INT	Number of records returned
success	BOOLEAN	Whether the benchmark request succeeded

Your question is Postgres vs OpenSearch 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