How to query rtcstats-server features

Once your rtcstats-server has extracted features into its SQL database, the value is in the querying. This guide gives you the schema, the join pattern that stitches a whole session back together, and a cookbook of practical queries you can adapt.

The examples are written for PostgreSQL (the DATE_TRUNC, FILTER, and quoting syntax are Postgres-flavoured), which is the common deployment. Adjust dialect specifics for your database. Feature names in the reference are camelCase (connectionTime). The database columns are snake_case (connection_time). The examples below use the column names.

The five tables

The tables are keyed together from the dump down to its tracks:

"rtcstats-server" is quoted in SQL because the table name contains a hyphen.

The master join

To pull every feature for a single dump, follow the key chain from the dump down to its tracks:

SELECT *
FROM "rtcstats-server" AS server
JOIN      features_metadata   AS metadata   ON metadata.dump_id     = server.id
LEFT JOIN features_client     AS client     ON client.dump_id       = metadata.id
LEFT JOIN features_connection AS connection ON connection.dump_id   = metadata.id
LEFT JOIN features_track      AS track      ON track.connection_id  = connection.id
WHERE server.id = 12345;

Most analytical queries only join the two or three tables they need. A session-level count joins metadata, a connection metric joins connection, and a track metric joins track.

Cookbook

1. Daily call volume and minutes

SELECT
  DATE_TRUNC('day', server.created_at) AS day,
  COUNT(DISTINCT server.id)            AS calls,
  SUM(connection.duration) / (60 * 1000) AS minutes
FROM "rtcstats-server" AS server
JOIN features_metadata   AS metadata   ON metadata.dump_id   = server.id
JOIN features_connection AS connection ON connection.dump_id = metadata.id
GROUP BY day
ORDER BY day ASC;

duration is the connection lifetime in milliseconds, so divide by 60,000 for minutes. This is the workhorse trend query. Plot calls and minutes over time and you have a usage dashboard.

2. TURN relay usage by country

SELECT
  metadata.location_country,
  COUNT(*) FILTER (
    WHERE connection.first_candidate_pair_local_type = 'relay'
       OR connection.first_candidate_pair_remote_type = 'relay'
  ) AS relayed,
  COUNT(*) AS total
FROM "rtcstats-server" AS server
JOIN features_metadata   AS metadata   ON metadata.dump_id   = server.id
JOIN features_connection AS connection ON connection.dump_id = metadata.id
WHERE connection.connection_time IS NOT NULL
GROUP BY metadata.location_country
ORDER BY relayed DESC;

A high relay share in a region usually points to restrictive NATs or firewalls there, and to how much TURN bandwidth you are paying for. See selected candidate pair.

3. Codec distribution

SELECT
  track.codec_mime_type,
  COUNT(*) AS tracks
FROM features_track AS track
WHERE track.codec_mime_type IS NOT NULL
GROUP BY track.codec_mime_type
ORDER BY tracks DESC;

Confirms what your endpoints actually negotiated, not just what you offered. Watch this after a codec rollout. See codec.

4. Connection time percentiles

SELECT
  PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY connection.connection_time) AS p50_ms,
  PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY connection.connection_time) AS p90_ms,
  PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY connection.connection_time) AS p99_ms
FROM features_connection AS connection
WHERE connection.connection_time IS NOT NULL;

connection_time is the DTLS handshake duration in milliseconds. Percentiles beat averages here, because the tail is where users feel setup pain. Add ice_connection_time for the full setup cost. See connection setup time.

5. getUserMedia failure rate

SELECT
  SUM(client.get_user_media_error_count)::float
    / NULLIF(SUM(client.get_user_media_error_count + client.get_user_media_success_count), 0)
    AS failure_rate
FROM features_client AS client;

A rising failure rate is often a permissions, device, or browser-version regression, one of the earliest signals of a broken funnel. See getUserMedia.

6. Quality limitation breakdown

SELECT
  AVG(track.cpu_quality_limitation_percentage)       AS avg_cpu_limited,
  AVG(track.bandwidth_quality_limitation_percentage) AS avg_bw_limited,
  AVG(track.other_quality_limitation_percentage)     AS avg_other_limited
FROM features_track AS track
WHERE track.kind = 'video' AND track.direction = 'outbound';

Splits why outbound video degraded: CPU (the encoder cannot keep up) versus bandwidth (the network cannot carry it). The two call for opposite fixes. See quality limitation.

7. Simulcast layer analysis

SELECT
  track.rid,
  track.encoding_index,
  COUNT(*)             AS tracks,
  AVG(track.max_width) AS avg_max_width
FROM features_track AS track
WHERE track.rid IS NOT NULL
GROUP BY track.rid, track.encoding_index
ORDER BY track.encoding_index;

Confirms your simulcast layers are actually being produced and shows the resolution each layer reached. See simulcast.

8. Video freeze correlation

SELECT
  DATE_TRUNC('week', server.created_at) AS week,
  SUM(track.freeze_count)                    AS freezes,
  SUM(track.total_freezes_duration) / 1000.0 AS freeze_seconds
FROM "rtcstats-server" AS server
JOIN features_metadata   AS metadata   ON metadata.dump_id     = server.id
JOIN features_connection AS connection ON connection.dump_id   = metadata.id
JOIN features_track      AS track      ON track.connection_id  = connection.id
WHERE track.kind = 'video' AND track.direction = 'inbound'
GROUP BY week
ORDER BY week ASC;

Freezes are the single most user-visible video defect. Trended weekly, this is your video-quality regression alarm. See freezes.

A note on schema drift

The feature list expands as rtcstats-features grows, so a column referenced here may be named slightly differently, or a new one may exist, in your deployment. When in doubt, run the master join against one known dump id and read the column list straight from your database explorer. That is always the source of truth.

See also

• rtcstats-server feature extraction: what each feature means, grouped by level.
• Can I query aggregated data across sessions?: the short-form overview.
• Requirements and operations: running the server that produces this data.