Database Query Optimizer

Build a database query optimizer that loads data from PostgreSQL using different transfer protocols based on dataset characteristics and network conditions.

Problem Description

You need to implement a query optimizer that intelligently selects transfer protocols when loading data from PostgreSQL. The optimizer should choose between binary and CSV protocols based on the size of the dataset and available network bandwidth.

Your implementation should:

1. Accept a database connection string, SQL query, and network condition indicator
2. Automatically select the appropriate protocol (binary for speed, CSV for bandwidth-constrained scenarios)
3. Load the data into a pandas DataFrame
4. Return both the DataFrame and the protocol used

Requirements

Protocol Selection Logic

The optimizer should select protocols based on these rules:

• Binary protocol: Use when network bandwidth is HIGH (default for speed)
• CSV protocol: Use when network bandwidth is LOW (optimizes for smaller transfer size)

Function Interface

Implement a function with this signature:

def optimized_query(conn_str: str, query: str, bandwidth: str) -> tuple:
    """
    Execute a query with optimized protocol selection.

    Args:
        conn_str: PostgreSQL connection string
        query: SQL query to execute
        bandwidth: Either "high" or "low" indicating network conditions

    Returns:
        A tuple of (DataFrame, protocol_used) where protocol_used is either "binary" or "csv"
    """
    pass

Test Cases

• Given a PostgreSQL connection, a simple SELECT query, and "high" bandwidth, the function returns a DataFrame and "binary" as the protocol @test
• Given a PostgreSQL connection, a simple SELECT query, and "low" bandwidth, the function returns a DataFrame and "csv" as the protocol @test
• Given an invalid bandwidth value (not "high" or "low"), the function raises a ValueError @test

Implementation

@generates

API

def optimized_query(conn_str: str, query: str, bandwidth: str) -> tuple:
    """
    Execute a query with optimized protocol selection.

    Args:
        conn_str: PostgreSQL connection string
        query: SQL query to execute
        bandwidth: Either "high" or "low" indicating network conditions

    Returns:
        A tuple of (DataFrame, protocol_used) where protocol_used is either "binary" or "csv"
    """
    pass

Dependencies { .dependencies }

connectorx { .dependency }

Provides high-performance database connectivity with protocol selection support.