Partition Skew Detection & Monitoring: Metrics, Queries, Dashboards

Partition skew — one partition or shard accumulating far more data or traffic than its siblings — quietly cancels every benefit partitioning was supposed to deliver: pruning still works, but the pruned-to partition is now the whole problem. This page is part of the Partition Monitoring & Failover Automation guide and covers the detection side of the discipline: which skew dimensions to measure, the catalog queries that measure them, how to turn raw sizes into skew coefficients, and how to wire the whole thing into Prometheus dashboards and alerts. Its siblings handle the reactive side — replication lag & capacity alerting watches how far replicas trail their primaries, and automated failover orchestration takes over when a node actually dies.

Problem Framing

A payments platform runs a hash-partitioned orders table split across 64 partitions, each mapped to its own tablespace. The partition key is merchant_id, chosen two years ago when merchants were roughly equal in volume. One merchant has since grown to 30% of total order flow. Every row for that merchant hashes to partition orders_p42.

Nobody notices for months because aggregate dashboards look healthy: total disk usage grows linearly, total QPS is flat, average query latency is fine. Meanwhile orders_p42 is 41 GB while its siblings average 9 GB. Autovacuum on that one partition can no longer finish between write bursts, dead tuples accumulate, index bloat doubles the working set, and p99 latency for the platform’s largest customer climbs from 12 ms to 340 ms. The first “detection” is a support escalation.

The failure here is not the partition layout — it is the absence of per-partition observability. Aggregate metrics mathematically hide skew: a 4.5x outlier among 64 partitions moves the mean by about 5%. Skew is only visible when you measure the distribution, not the sum. That requires three things this page builds in order: per-partition measurements from the database catalogs, a skew coefficient that compresses the distribution into an alertable number, and thresholds tuned so the alert fires weeks before the incident.

What to Measure: Skew Dimensions and Coefficients

Skew is not one metric. Three dimensions matter, and they diverge often enough that measuring only one produces blind spots:

• Size skew — bytes and rows per partition. This is the slow-moving dimension: it drives disk exhaustion on individual tablespaces or shards, autovacuum starvation, and backup-window overruns. Measure both pg_relation_size (heap only) and pg_total_relation_size (heap + indexes + TOAST), because index bloat can make a partition operationally hot even when its row count looks normal.
• Write hot-spotting — insert/update/delete rate per partition. A partition can be small but absorb a disproportionate share of writes (a new tenant ramping up, or a DEFAULT partition absorbing unmapped values). Write skew hits WAL throughput, lock contention, and replication before it shows up in size.
• Read hot-spotting — sequential and index scan rates per partition. Read skew saturates the buffer cache with one partition’s pages and evicts everyone else’s working set. It is the dimension most likely to exist without any size skew at all: recent-time-range queries on a range-partitioned table always hammer the newest partition.

Raw per-partition numbers are dashboards; alerts need a single number per parent table. Three coefficients cover the practical range:

Alert on max/avg, dashboard the CV trend, and compute the Gini-style number weekly if you feed skew data into rebalancing decisions.

Step 1 — Measure Size Skew from the PostgreSQL Catalogs

PostgreSQL 12+ exposes the whole partition hierarchy through pg_partition_tree(), which handles multi-level (composite) layouts and flags leaf partitions. Join it against the size functions:

-- Per-partition size for one partitioned table
SELECT
  relid::regclass::text                          AS partition_name,
  pg_relation_size(relid)                        AS heap_bytes,
  pg_total_relation_size(relid)                  AS total_bytes,
  pg_size_pretty(pg_total_relation_size(relid))  AS total_pretty
FROM pg_partition_tree('public.orders')
WHERE isleaf
ORDER BY total_bytes DESC;

For a fleet-wide view across every partitioned table in the database, aggregate by parent:

-- Size skew summary for every partitioned table in the database
SELECT
  p.partrelid::regclass::text                      AS parent_table,
  agg.partitions,
  pg_size_pretty(agg.max_size)                     AS max_size,
  pg_size_pretty(agg.avg_size::bigint)             AS avg_size,
  round(agg.max_size::numeric
        / nullif(agg.avg_size, 0), 2)              AS max_avg_ratio
FROM pg_partitioned_table p
CROSS JOIN LATERAL (
  SELECT count(*)                            AS partitions,
         max(pg_total_relation_size(relid))  AS max_size,
         avg(pg_total_relation_size(relid))  AS avg_size
  FROM pg_partition_tree(p.partrelid)
  WHERE isleaf
) agg
WHERE agg.partitions > 0
ORDER BY max_avg_ratio DESC NULLS LAST;

The LATERAL iteration over pg_partitioned_table visits sub-partitioned intermediates as their own rows too, which is useful — skew hidden inside one branch of a composite layout shows up separately. The exporter-facing view built on this pattern is assembled line by line in detecting partition skew with PostgreSQL catalog queries.

Rows matter as well as bytes: a partition full of TOASTed JSON can be huge with few rows, and a partition of narrow rows can be enormous in tuple count while modest on disk. Use pg_stat_user_tables.n_live_tup for an estimate that costs nothing, and reserve exact count(*) for offline analysis.

Step 2 — Measure Size Skew in MySQL

MySQL exposes the equivalent data through information_schema.PARTITIONS. TABLE_ROWS and DATA_LENGTH are estimates refreshed by ANALYZE TABLE, which is accurate enough for skew ratios:

SELECT
  PARTITION_NAME,
  TABLE_ROWS,
  ROUND(DATA_LENGTH / 1024 / 1024, 1)  AS data_mb,
  ROUND(INDEX_LENGTH / 1024 / 1024, 1) AS index_mb,
  ROUND(TABLE_ROWS / NULLIF((
    SELECT AVG(p2.TABLE_ROWS)
    FROM information_schema.PARTITIONS p2
    WHERE p2.TABLE_SCHEMA = 'shop'
      AND p2.TABLE_NAME   = 'orders'
      AND p2.PARTITION_NAME IS NOT NULL
  ), 0), 2)                            AS rows_vs_avg
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = 'shop'
  AND TABLE_NAME   = 'orders'
  AND PARTITION_NAME IS NOT NULL
ORDER BY TABLE_ROWS DESC;

Two MySQL-specific caveats. First, TABLE_ROWS for InnoDB is a statistics-based estimate that can be off by 10–15% between ANALYZE TABLE runs — fine for a 3x skew alert, useless for billing. Second, filter PARTITION_NAME IS NOT NULL, otherwise non-partitioned tables appear as single rows and pollute fleet-wide aggregations.

Step 3 — Capture Write and Read Hot-Spotting

Size is the lagging indicator; traffic is the leading one. In PostgreSQL, pg_stat_user_tables maintains cumulative counters per relation — including per partition, since each leaf partition is its own relation:

SELECT
  s.relname                      AS partition_name,
  s.n_tup_ins,                   -- cumulative inserts
  s.n_tup_upd,                   -- cumulative updates
  s.n_tup_del,                   -- cumulative deletes
  s.seq_scan,                    -- cumulative sequential scans
  s.idx_scan,                    -- cumulative index scans
  s.n_dead_tup,
  s.last_autovacuum
FROM pg_stat_user_tables s
WHERE s.relid IN (
  SELECT relid FROM pg_partition_tree('public.orders') WHERE isleaf
)
ORDER BY s.n_tup_ins DESC;

Do not compute rates in SQL. These counters are cumulative since the last statistics reset, so export them as Prometheus counters and let rate() do the windowing — that way a stats reset (or failover to a standby, which starts counters fresh) produces one harmless gap instead of a corrupted stored delta. The one in-database rate trick that is useful for ad-hoc triage is a two-sample diff: snapshot the counters into a temp table, pg_sleep(60), and join.

Read hot-spotting uses the same query: a partition whose seq_scan rate dwarfs its siblings usually means a query shape defeats pruning for that value, and a partition dominating idx_scan is your buffer-cache hog. If the hot partition maps to one categorical key, the mitigation toolkit — key salting, write fan-out, sub-partitioning — is covered in handling hot keys in list-partitioned tables.

Step 4 — Compute Skew Coefficients in SQL

Turn the per-partition distribution into the three coefficients from the table above. This is the query worth wrapping in a view, because both humans and the exporter will read it:

CREATE SCHEMA IF NOT EXISTS monitoring;

CREATE OR REPLACE VIEW monitoring.partition_skew AS
WITH leaves AS (
  SELECT
    relid,
    pg_total_relation_size(relid)                                  AS bytes,
    row_number() OVER (ORDER BY pg_total_relation_size(relid))     AS rn,
    count(*)    OVER ()                                            AS n
  FROM pg_partition_tree('public.orders')
  WHERE isleaf
)
SELECT
  'public.orders'                                                  AS parent_table,
  max(n)                                                           AS partition_count,
  max(bytes)                                                       AS max_bytes,
  avg(bytes)::bigint                                               AS avg_bytes,
  round(max(bytes)::numeric / nullif(avg(bytes), 0), 2)            AS max_avg_ratio,
  round(stddev_pop(bytes)   / nullif(avg(bytes), 0), 3)            AS cv,
  round((2 * sum(rn * bytes)::numeric / (max(n) * sum(bytes)))
        - (max(n) + 1)::numeric / max(n), 3)                       AS gini
FROM leaves;

Interpretation guide: max_avg_ratio of 1.0–1.5 is healthy for hash layouts; 1.5–2.5 is normal for range layouts where the newest partition is still filling; above 3.0 sustained means one partition is doing triple its fair share of work. A gini above 0.4 with a modest max/avg ratio means the imbalance is spread across many partitions rather than one outlier — a layout problem, not a hot-key problem.

Step 5 — Export Per-Partition Metrics with postgres_exporter

postgres_exporter runs custom SQL from a queries file and exposes the results as Prometheus metrics. Point the exporter at it with --extend.query-path=/etc/postgres_exporter/queries.yaml:

# /etc/postgres_exporter/queries.yaml
pg_partition:
  query: |
    SELECT
      pt.parentrelid::regclass::text AS parent,
      pt.relid::regclass::text       AS partition,
      pg_total_relation_size(pt.relid)::float8 AS size_bytes,
      s.n_live_tup::float8           AS rows,
      (s.n_tup_ins + s.n_tup_upd + s.n_tup_del)::float8 AS writes_total,
      (s.seq_scan + s.idx_scan)::float8                 AS reads_total
    FROM pg_partition_tree('public.orders') pt
    JOIN pg_stat_user_tables s ON s.relid = pt.relid
    WHERE pt.isleaf
  master: true
  cache_seconds: 300
  metrics:
    - parent:
        usage: "LABEL"
        description: "Parent partitioned table"
    - partition:
        usage: "LABEL"
        description: "Leaf partition name"
    - size_bytes:
        usage: "GAUGE"
        description: "Total on-disk size including indexes and TOAST"
    - rows:
        usage: "GAUGE"
        description: "Estimated live tuples"
    - writes_total:
        usage: "COUNTER"
        description: "Cumulative tuple writes (ins+upd+del)"
    - reads_total:
        usage: "COUNTER"
        description: "Cumulative scans (seq+idx)"

Three settings matter operationally. master: true runs the query only against the primary connection, preventing duplicate series when the exporter also watches replicas. cache_seconds: 300 decouples the expensive size functions from the scrape interval — Prometheus can scrape every 15 s while the SQL runs every 5 minutes. And every partition becomes one time series per metric, so a 2 000-partition table produces 8 000 series from this file alone; keep the query scoped to parents you actually alert on.

Step 6 — Recording Rules, Dashboards, and Alert Thresholds

Compute the skew ratio in Prometheus rather than SQL so the alert keeps working even when the database is degraded enough that ad-hoc queries stall:

# prometheus/rules/partition_skew.yml
groups:
  - name: partition-skew
    interval: 60s
    rules:
      - record: parent:pg_partition_size_bytes:avg
        expr: avg by (parent) (pg_partition_size_bytes)
      - record: parent:pg_partition_size_skew:ratio
        expr: |
          max by (parent) (pg_partition_size_bytes)
            / avg by (parent) (pg_partition_size_bytes)
      - record: parent:pg_partition_write_skew:ratio
        expr: |
          max by (parent) (rate(pg_partition_writes_total[15m]))
            / avg by (parent) (rate(pg_partition_writes_total[15m]))

      - alert: PartitionSizeSkewHigh
        expr: |
          parent:pg_partition_size_skew:ratio > 3
            and parent:pg_partition_size_bytes:avg > 1e9
        for: 6h
        labels:
          severity: warning
        annotations:
          summary: "Size skew on {{ $labels.parent }}"
          description: "Largest partition is {{ $value | printf \"%.1f\" }}x the average. Identify the hot partition with topk(3, pg_partition_size_bytes{parent=\"{{ $labels.parent }}\"})."

      - alert: PartitionWriteHotspot
        expr: parent:pg_partition_write_skew:ratio > 4
        for: 30m
        labels:
          severity: warning
        annotations:
          summary: "Write hot-spot on {{ $labels.parent }}"
          description: "One partition is absorbing {{ $value | printf \"%.1f\" }}x the average write rate."

Threshold design principles, in order of importance:

1. Gate ratios on absolute size. The and ... avg > 1e9 clause is what keeps a freshly created table with 10 MB in one partition and 1 MB in the others from paging anyone. Ratios without floors are the single biggest source of skew-alert noise.
2. Use long for: durations for size, short for writes. Size skew develops over weeks, so for: 6h costs nothing and suppresses backfill jobs and bulk loads. Write hot-spots can be a live incident, so 30 minutes balances noise against reaction time.
3. Different baselines per layout. Hash layouts should sit near 1.0 and warrant a 2.0 warning threshold; range layouts legitimately run 2–3x while the current period fills, so alert on write skew and on the trend of size skew (deriv() over a day) instead of the instantaneous ratio.

For dashboards, three Grafana panels cover the workflow: a table of topk(10, pg_partition_size_bytes) sorted descending (the “which partition” panel), a time series of parent:pg_partition_size_skew:ratio per parent (the trend panel), and a time series of topk(5, rate(pg_partition_writes_total[15m])) (the traffic panel). Resist the heatmap-of-everything temptation — at hundreds of partitions it renders as noise, while topk shows exactly the outliers the alert will name.

Configuration Reference

Operational Contrast

Skew detection, replication lag & capacity alerting, and automated failover orchestration form a timescale ladder, and confusing their roles produces monitoring gaps.

Skew detection operates on days to weeks. Its output is never a page at 3 a.m.; it is a capacity-planning signal that says “partition p42 will be a problem in three weeks — rebalance, salt the key, or split it now while it is cheap.” The correct response is a planned change, typically one of the workflows in shard rebalancing & data movement.

Replication lag alerting operates on minutes. Lag is a symptom metric — and notably, unexplained lag on exactly one replica chain is often the first visible consequence of write skew, because the hot partition’s WAL volume saturates one link. If you run lag alerts without skew metrics, you will repeatedly “fix” lag incidents whose root cause is distributional.

Failover orchestration operates on seconds. It consumes health checks, not distribution metrics — but it depends on skew detection indirectly: a badly skewed layout means the shard that fails over is the overloaded one, and its replacement inherits the same hot traffic. Failover automation restores availability; only rebalancing restores headroom. Keep the three signals on separate alert routes with separate severities, and annotate lag and failover incidents with the current skew ratio so the postmortem can tell symptom from cause.

Failure Modes

Common Mistakes

• Alerting on the instantaneous ratio. A single bulk load into one partition trips max/avg for an hour. Every skew alert needs a for: duration measured in hours, or you train the on-call rotation to ignore it.
• Mixing size functions. Computing max with pg_total_relation_size but average with pg_relation_size (or comparing MySQL DATA_LENGTH against PostgreSQL totals) inflates ratios by whatever the index-to-heap ratio is. Pick one definition and use it in the query, the view, and the dashboard.
• Measuring only size. The most damaging skew incidents are traffic skew on evenly sized partitions — recent-partition read hot-spots on range layouts being the canonical example. If your dashboard has no rate() panels, you are monitoring last quarter’s problem.
• Letting per-partition cardinality grow unbounded. Exporting four counters for every leaf of every partitioned table in the fleet can add tens of thousands of series. Scope exports to parents with alert rules, and aggregate the rest to a single per-parent skew ratio in SQL.

FAQ

Related

• Detecting Partition Skew with PostgreSQL Catalog Queries — hands-on walkthrough building the skew view and exporter wiring end to end
• Handling Hot Keys in List Partitioned Tables — mitigation playbook once detection identifies a hot categorical key
• Replication Lag & Capacity Alerting — the minutes-timescale sibling that often surfaces skew as unexplained lag
• Shard Rebalancing & Data Movement — the corrective workflows a sustained skew alert should trigger