SQLAlchemy: Allow only slow queries to be recorded

What problem do you want to solve?

After integrating the SQLAlchemy instrumentor, there are spans recorded for every single query being run which in some contexts generates a lot of noise and makes the spans difficult to follow.

For example, I'm working on a project where we group spans in the context of a job and some of these jobs run dozens of queries among other actions. As a result, when there are so many small events (we care only about slow queries) it's easy to miss an important one in between

Describe the solution you'd like

This can be solved by adding a configuration option such as exec_time_treshold which when set will only create spans for queries which take longer than that. For example, when exec_time_treshold=100 only queries which take longer than 100ms will create spans. For backwards compatibility (also a sane default) when not set all queries will create spans.

Describe alternatives you've considered

We've also investigated grouping queries done in the same transaction together (sample implementation bellow), so that we can at least collapse them. But in reality most of our transactions contain a single query and in these cases it doesn't help much.

    engine_tracer: EngineTracer | None = SQLAlchemyInstrumentor().instrument(
        engine=dbengine.sync_engine, enable_commenter=True
    )

    if engine_tracer is None:
        return

    def on_begin(conn: Connection) -> None:
        transaction_span_ctx_mngr = engine_tracer.tracer.start_as_current_span(
            "SQLAlchemy Transaction",
            kind=trace.SpanKind.CLIENT,
            attributes={
                SpanAttributes.DB_NAME: dbengine.url.database,
                SpanAttributes.DB_STATEMENT: "BEGIN",
                SpanAttributes.DB_OPERATION: "BEGIN",
            },
        )

        transaction_span_ctx_mngr.__enter__()

        conn.info["otel_transaction_span_ctx_mngr"] = transaction_span_ctx_mngr

    def on_commit(conn: Connection) -> None:
        transaction_span_ctx_mngr = conn.info.pop("otel_transaction_span_ctx_mngr", None)

        if transaction_span_ctx_mngr is not None:
            transaction_span_ctx_mngr.__exit__(None, None, None)

    def on_rollback(conn: Connection) -> None:
        transaction_span_ctx_mngr = conn.info.pop("otel_transaction_span_ctx_mngr", None)

        if transaction_span_ctx_mngr is not None:
            transaction_span_ctx_mngr.__exit__(None, None, None)

    engine_tracer._register_event_listener(dbengine.sync_engine, "begin", on_begin)
    engine_tracer._register_event_listener(dbengine.sync_engine, "commit", on_commit)
    engine_tracer._register_event_listener(dbengine.sync_engine, "rollback", on_rollback)

Additional Context

No response

Would you like to implement a fix?

Yes

Start with the SQLAlchemy instrumentor and trace where query spans are created and how execution time becomes available. Add coverage for the proposed execution-time threshold, including the default behavior, and verify that only queries exceeding the configured value are recorded when it is set.