Bugs on observations endpoint

[LDiazN]

• The observations endpoint is returning and empty list for a valid report_id, example:
  https://api.ooni.org/api/v1/observations?report_id=20260413T235348Z_webconnectivity_GR_6799_n4_boA76qdHKZE0iqbi

curl https://api.ooni.org/api/v1/observations?report_id=20260413T235348Z_webconnectivity_GR_6799_n4_boA76qdHKZE0iqbi
{
  "metadata": {
    "count": -1,
    "current_page": 1,
    "limit": 100,
    "next_url": "https://api.ooni.io/api/v1/observations?offset=100&limit=100",
    "offset": 0,
    "pages": -1,
    "query_time": 0.33141021709889174
  },
  "results": []
}

• Some queries fail when you include a probe_cc

curl https://api.ooni.org/api/v1/observations\?probe_cc\='VE'
Internal Server Error

cc @majakomel

[LDiazN]

The first one is explained by the fact that we have set the observations endpoint to have a default value for since and until, so if you just provide report_id like in the example, it will implicitly add a since and until range that might not contain the entries with the matching report_id

[LDiazN]

regarding the probe_cc, this error is risen when querying by probe_cc='VE'

DB::Exception: Received from clickhouse1.prod.ooni.io:9000. DB::Exception: Memory limit (for query) exceeded: would use 954.64 MiB (attempt to allocate chunk of 4201806 bytes), maximum: 954.63 MiB.: (while reading column dns_engine): (while reading from part /var/lib/clickhouse/store/fbf/fbf562b8-e836-4f70-8cf0-3ba3fe2340e0/66ed98bd748b514d72ea172a58440c6e_0_3145_5/ in table ooni.obs_web (fbf562b8-e836-4f70-8cf0-3ba3fe2340e0) located on disk default of type local, from mark 301 with max_rows_to_read = 4823): While executing MergeTreeSelect(pool: ReadPool, algorithm: Thread). Stack trace:

It seems like this query is too memory intensive

By running the same query again but manually in clickhouse, we get the following:

Query:

SELECT measurement_uid,input,report_id,ooni_run_link_id,measurement_start_time,software_name,software_version,test_name,test_version,bucket_date,probe_asn,probe_cc,probe_as_org_name,probe_as_cc,probe_as_name,network_type,platform,origin,engine_name,engine_version,architecture,resolver_ip,resolver_asn,resolver_cc,resolver_as_org_name,resolver_as_cc,observation_idx,created_at,target_id,hostname,transaction_id,ip,port,ip_asn,ip_as_org_name,ip_as_cc,ip_cc,ip_is_bogon,dns_query_type,dns_failure,dns_engine,dns_engine_resolver_address,dns_answer_type,dns_answer,dns_answer_asn,dns_answer_as_org_name,dns_t,tcp_failure,tcp_success,tcp_t,tls_failure,tls_server_name,tls_outer_server_name,tls_echconfig,tls_version,tls_cipher_suite,tls_is_certificate_valid,tls_end_entity_certificate_fingerprint,tls_end_entity_certificate_subject,tls_end_entity_certificate_subject_common_name,tls_end_entity_certificate_issuer,tls_end_entity_certificate_issuer_common_name,tls_end_entity_certificate_san_list,tls_end_entity_certificate_not_valid_after,tls_end_entity_certificate_not_valid_before,tls_certificate_chain_length,tls_certificate_chain_fingerprints,tls_handshake_read_count,tls_handshake_write_count,tls_handshake_read_bytes,tls_handshake_write_bytes,tls_handshake_last_operation,tls_handshake_time,tls_t,http_request_url,http_network,http_alpn,http_failure,http_request_body_length,http_request_method,http_runtime,http_response_body_length,http_response_body_is_truncated,http_response_body_sha1,http_response_status_code,http_response_header_location,http_response_header_server,http_request_redirect_from,http_request_body_is_truncated,http_t,probe_analysis
FROM obs_web
WHERE probe_cc = 'VE'
  AND measurement_start_time >= '2026-05-01'
  AND measurement_start_time <= '2026-05-08'
ORDER BY measurement_start_time DESC
LIMIT 100
OFFSET 0

Result

100 rows in set. Elapsed: 18.440 sec. Processed 82.54 million rows, 99.09 GB (4.48 million rows/s., 5.37 GB/s.)
Peak memory usage: 1.66 GiB

It seems like querying by probe_cc is running a full scan of the DB