Detecting Partition Skew with PostgreSQL Catalog Queries

This walkthrough builds a working skew detector from nothing but PostgreSQL system catalogs — partition tree, size skew, write skew, a reusable ratio view, and the postgres_exporter and Prometheus wiring on top — implementing the detection pipeline described in Partition Skew Detection & Monitoring, part of the Partition Monitoring & Failover Automation guide.

Prerequisites

• PostgreSQL 12 or later (pg_partition_tree() and the isleaf column arrived in 12)
• A declaratively partitioned table to measure — examples use public.orders partitioned by hash on merchant_id
• A monitoring role granted pg_monitor (covers pg_stat_user_tables and the size functions)
• track_counts = on in postgresql.conf (the default; write-skew detection is blind without it)
• postgres_exporter 0.11+ running with permission to add a --extend.query-path file
• Prometheus scraping the exporter, with rule files under your control
• psql access to the primary — statistics counters are instance-local

Step 1 — Map the partition tree

Everything downstream needs the set of leaf partitions, and pg_partition_tree() is the reliable way to get it: it recurses through multi-level layouts and marks which relations actually hold data.

SELECT
  relid::regclass::text        AS relation,
  parentrelid::regclass::text  AS parent,
  isleaf,
  level
FROM pg_partition_tree('public.orders')
ORDER BY level, relation;

A two-level layout returns the root at level 0 with isleaf = f, any intermediate partitioned tables at level 1, and the heap-bearing leaves below them. Every later query in this walkthrough filters WHERE isleaf — non-leaf relations own no storage, and including them drags averages toward zero.

Operational note: pg_partition_tree() on a plain (non-partitioned) table returns an empty set rather than an error. Scripts that iterate many tables should treat zero rows as “not partitioned, skip”, not as a failure.

DBA tip: Pass the argument as 'schema.table'::regclass in automation. A bare unqualified name resolves through search_path, and a monitoring role with a different search_path than your interactive session is a classic source of “works in psql, empty in the exporter”.

Step 2 — Measure size skew per leaf

Join the leaves against the size functions. Report both the heap-only and total numbers — index bloat concentrated on one partition is skew too, and only the difference between the two columns reveals it.

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
LIMIT 10;

The ORDER BY ... LIMIT 10 shape is deliberate: during an incident you want the offenders first, not an alphabetical wall of 64 rows.

Operational note: pg_relation_size(relid) measures the main fork only; pg_total_relation_size(relid) adds indexes, TOAST, and the visibility/free-space maps. Pick one for your skew ratio and use it everywhere — mixing them between the max and the average silently inflates the ratio by the index-to-heap factor.

DBA tip: These functions stat files per relation and never block writers, but on a 3 000-leaf tree that is thousands of filesystem calls per run. Schedule them (the cache_seconds setting in Step 5) instead of letting every 15-second scrape pay the cost.

Step 3 — Measure write skew from pg_stat_user_tables

Size lags traffic by weeks. Each leaf partition has its own row in pg_stat_user_tables, so cumulative write and scan counters per partition are one join away:

SELECT
  s.relname                                  AS partition_name,
  s.n_tup_ins + s.n_tup_upd + s.n_tup_del    AS writes_total,
  s.seq_scan + s.idx_scan                    AS reads_total,
  s.n_live_tup,
  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 writes_total DESC;

Operational note: These counters are cumulative since the last statistics reset — a promotion, a crash recovery, or a manual pg_stat_reset() zeroes them. Export them as Prometheus counters and let rate() compute traffic; never store deltas in the database, where a reset turns into a huge negative spike.

DBA tip: While you are here, watch n_dead_tup relative to n_live_tup on the hottest partition. A hot partition whose dead-tuple fraction keeps climbing is telling you autovacuum has already lost the race — that partition will show up in latency graphs before it shows up in size skew. If the hot rows all share one key value, the fix lives in handling hot keys in list-partitioned tables.

Step 4 — Build the skew-ratio view

Fold the distribution into per-parent coefficients — max/avg for alerting, coefficient of variation for trend dashboards — and wrap it in a view so psql users and the exporter read the same definition:

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
  FROM pg_partition_tree('public.orders')
  WHERE isleaf
)
SELECT
  'public.orders'                                          AS parent_table,
  count(*)                                                 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
FROM leaves;

A healthy hash layout sits near max_avg_ratio = 1.0; a range layout mid-period runs 1.5–2.5 legitimately; sustained values above 3.0 mean one partition is doing triple its fair share.

Operational note: The nullif(avg(bytes), 0) guard matters on day one: a parent whose partitions are all freshly created and empty would otherwise divide by zero and error the exporter’s whole query file, taking every other custom metric down with it.

DBA tip: Monitoring more than one parent? Replace the hardcoded 'public.orders' with a LATERAL iteration over pg_partitioned_table: SELECT p.partrelid::regclass::text, agg.* FROM pg_partitioned_table p, LATERAL (SELECT ... FROM pg_partition_tree(p.partrelid) WHERE isleaf) agg. Grant SELECT ON monitoring.partition_skew to the exporter role, not to the underlying catalogs individually.

Step 5 — Wire the view into postgres_exporter

Expose the view plus raw per-partition series through a custom queries file, and start the exporter with --extend.query-path=/etc/postgres_exporter/queries.yaml:

# /etc/postgres_exporter/queries.yaml
pg_partition_skew:
  query: |
    SELECT parent_table AS parent,
           partition_count::float8,
           max_bytes::float8,
           avg_bytes::float8,
           max_avg_ratio::float8,
           cv::float8
    FROM monitoring.partition_skew
  master: true
  cache_seconds: 300
  metrics:
    - parent:
        usage: "LABEL"
        description: "Parent partitioned table"
    - partition_count:
        usage: "GAUGE"
        description: "Number of leaf partitions"
    - max_bytes:
        usage: "GAUGE"
        description: "Largest leaf partition in bytes"
    - avg_bytes:
        usage: "GAUGE"
        description: "Mean leaf partition size in bytes"
    - max_avg_ratio:
        usage: "GAUGE"
        description: "Size skew ratio (max/avg)"
    - cv:
        usage: "GAUGE"
        description: "Coefficient of variation of leaf sizes"

The top-level key becomes the metric prefix, so the ratio lands in Prometheus as pg_partition_skew_max_avg_ratio{parent="public.orders"}.

Operational note: master: true restricts the query to the primary. Without it, an exporter pointed at primary and standby emits the same series twice with different values — the standby’s statistics counters are instance-local — and your skew ratio becomes whichever scrape won last.

DBA tip: cache_seconds: 300 means the SQL runs at most every five minutes regardless of scrape interval. Verify the exporter actually loaded the file after a restart: curl -s localhost:9187/metrics | grep pg_partition_skew returning nothing usually means a YAML indentation error, which postgres_exporter logs once at startup and never again.

Step 6 — Add the Prometheus alert

Alert on the exported ratio, gated on absolute size so small tables cannot page anyone, with a for: window long enough to ignore bulk loads:

# prometheus/rules/partition_skew.yml
groups:
  - name: partition-skew
    rules:
      - alert: PartitionSizeSkewHigh
        expr: |
          pg_partition_skew_max_avg_ratio > 3
            and pg_partition_skew_avg_bytes > 1e9
        for: 6h
        labels:
          severity: warning
        annotations:
          summary: "Partition size skew on {{ $labels.parent }}"
          description: >-
            Largest partition of {{ $labels.parent }} is
            {{ $value | printf "%.1f" }}x the average leaf size.
            Investigate before autovacuum and backup windows degrade.
      - alert: PartitionSkewMetricsAbsent
        expr: absent(pg_partition_skew_max_avg_ratio)
        for: 30m
        labels:
          severity: warning
        annotations:
          summary: "Partition skew metrics missing"

Operational note: The absent() companion alert is not optional. A broken queries file, a revoked grant, or a renamed view all fail silently — the skew alert simply never fires again. Alert on the metric’s existence, not just its value.

DBA tip: Validate before reloading: promtool check rules prometheus/rules/partition_skew.yml. A rule file with a syntax error is rejected wholesale on reload, taking every other group in the file offline with it.

Verification

Run the view and confirm the ratio matches a manual spot check:

SELECT * FROM monitoring.partition_skew;

Expected output for the skewed 64-partition example — max_avg_ratio well above 1 and a cv confirming the imbalance is one outlier rather than general spread:

 parent_table  | partition_count |  max_bytes  | avg_bytes  | max_avg_ratio |  cv
---------------+-----------------+-------------+------------+---------------+-------
 public.orders |              64 | 44023414784 | 9721742336 |          4.53 | 0.441
(1 row)

Then confirm the metric is exported and scrapeable:

curl -s localhost:9187/metrics | grep pg_partition_skew_max_avg_ratio

# HELP pg_partition_skew_max_avg_ratio Size skew ratio (max/avg)
# TYPE pg_partition_skew_max_avg_ratio gauge
pg_partition_skew_max_avg_ratio{parent="public.orders",server="db-primary:5432"} 4.53

Finally, query pg_partition_skew_max_avg_ratio in the Prometheus UI: the value should match psql, and the PartitionSizeSkewHigh alert should show as pending once the expression holds (it fires after the 6-hour for: window).

Failure mode table

FAQ

Related

• Partition Skew Detection & Monitoring — parent guide covering skew dimensions, coefficients, MySQL catalogs, dashboards, and threshold design
• Handling Hot Keys in List Partitioned Tables — what to do once these queries identify a hot categorical key
• Alerting on Cross-Shard Replication Lag with Prometheus — companion walkthrough for the lag symptoms that skew frequently causes