Partition Monitoring & Failover Automation

A partitioned database multiplies every operational question by the number of shards: one replication stream becomes twelve, one disk-usage graph becomes a skew distribution, one failover procedure becomes a fleet-wide orchestration problem. This guide covers the observability and resilience layer for horizontally scaled databases — what to measure per partition and per shard, how to build the metrics pipeline that watches it, how to design alert thresholds that page before users notice, and how to automate primary failover safely with fencing against split-brain. It assumes the architectural grounding of Database Partitioning Fundamentals & Architecture; when monitoring reveals that a shard needs to be split or drained rather than merely watched, Shard Migration & Rebalancing Operations picks up where this guide ends.

Architectural Drivers

Monitoring an unpartitioned database answers one question: is this node healthy? Monitoring a partitioned system must answer a harder one: is load, data, and risk evenly distributed across segments — and if not, where is the imbalance heading? Five per-partition and per-shard signals develop silently and account for the majority of production incidents in sharded fleets:

1. Size skew. One partition grows faster than its siblings because a hot tenant, a hot time window, or a poorly chosen key concentrates rows. Pruning benefits erode, VACUUM on the oversized child slows, and eventually that segment alone exhausts its volume while the fleet average looks fine.
2. Write and read skew. Even with balanced sizes, request traffic can concentrate on one shard. A single tenant’s product launch turns shard 3 into the whole system’s bottleneck while shards 0–2 idle.
3. Replication lag. Each shard replicates independently; the fleet’s lag is the worst shard’s lag. Lag defines both your stale-read exposure and your data-loss window on failover.
4. Connection saturation. Scatter-gather queries multiply connection demand, and a slow shard causes fan-out queries to hold connections on every shard while they wait for the straggler.
5. Autovacuum debt. Dead tuples accumulate per child table. A partition that autovacuum never finishes bloats indexes, degrades pruned scans, and — at the extreme — risks transaction-ID wraparound on that segment alone.

Averaged, fleet-level metrics hide all five. The mean partition size of a 12-shard system can sit comfortably at 20 GB while one shard crosses 60 GB. Every query and every alert in this guide is therefore expressed per shard and per partition, with skew computed as a ratio against the fleet, never as an absolute average.

Measure your starting point before building any pipeline. This catalog query is the baseline inventory — run it on each shard:

-- Per-partition inventory: size, rows, dead tuples, last autovacuum
SELECT parent.relname                                        AS parent_table,
       child.relname                                         AS partition,
       pg_total_relation_size(child.oid)                     AS size_bytes,
       pg_stat_get_live_tuples(child.oid)                    AS live_rows,
       pg_stat_get_dead_tuples(child.oid)                    AS dead_rows,
       s.last_autovacuum
FROM   pg_inherits
JOIN   pg_class parent ON parent.oid = pg_inherits.inhparent
JOIN   pg_class child  ON child.oid  = pg_inherits.inhrelid
LEFT   JOIN pg_stat_user_tables s ON s.relid = child.oid
ORDER  BY pg_total_relation_size(child.oid) DESC;

If the largest partition already exceeds three times the median, skip ahead to the rebalancing decision — Shard Migration & Rebalancing Operations covers splitting and draining — because no amount of alerting fixes a distribution that is already broken.

Monitoring Dimension Taxonomy

Each dimension has a distinct signal source, a distinct threshold shape, and a distinct remediation. Treating them as one generic “database health” alert produces pages nobody can act on. Partition Skew Detection & Monitoring drills into the skew dimensions; the table below is the working map for all five.

Size and write skew

Size skew is a slow signal (evaluate daily); write skew is fast (evaluate per minute). Compute write skew from insert counters rather than sizes so you catch a hot key within minutes of it going hot:

-- Write skew: insert share per partition over the stats window
WITH ins AS (
  SELECT relname, n_tup_ins
  FROM   pg_stat_user_tables
  WHERE  relname LIKE 'events_%'
)
SELECT relname,
       n_tup_ins,
       round(n_tup_ins::numeric / NULLIF(sum(n_tup_ins) OVER (), 0) * 100, 1) AS pct_of_writes
FROM   ins
ORDER  BY n_tup_ins DESC;

A partition receiving more than 2 / partition_count × 100 percent of writes is running at twice its fair share. Whether that is tolerable depends on how the routing layer distributes keys — hash routing algorithms aim for uniform shares by construction, so any sustained deviation there indicates a hot key rather than expected variance.

Replication lag

Export lag from the primary’s view so the metric survives a dead replica (a replica that has crashed cannot report its own lag):

-- Lag per replica, measured from the primary
SELECT application_name,
       client_addr,
       state,
       pg_wal_lsn_diff(sent_lsn, replay_lsn)          AS lag_bytes,
       extract(epoch FROM now() - reply_time)          AS lag_seconds
FROM   pg_stat_replication
ORDER  BY lag_bytes DESC;

Alerting design for this dimension — including byte-based versus time-based lag and capacity forecasting — is the subject of Replication Lag & Capacity Alerting.

Autovacuum debt

Dead-tuple ratio is the leading indicator; last_autovacuum age is the lagging confirmation that the daemon has fallen behind:

-- Partitions where autovacuum is losing ground
SELECT relname,
       n_dead_tup,
       n_live_tup,
       round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 1) AS dead_pct,
       last_autovacuum
FROM   pg_stat_user_tables
WHERE  n_dead_tup > 100000
ORDER  BY dead_pct DESC
LIMIT  20;

Any partition above 20 % dead with last_autovacuum older than 24 hours needs a manual VACUUM (VERBOSE) and a lower autovacuum_vacuum_scale_factor set as a per-table storage parameter on that child.

Building the Metrics Pipeline

The reference pipeline is exporter → Prometheus → Grafana + Alertmanager. For PostgreSQL shards, run one postgres_exporter per node (including replicas); for MySQL shards, mysqld_exporter fills the same role and the rest of the pipeline is identical. Two design rules keep the pipeline shard-aware:

1. Label every target with shard and role. All the skew math in this guide is max by (shard) versus a fleet aggregate; without a shard label you cannot write those expressions, and without role you cannot distinguish an acceptable replica restart from a primary outage.
2. Scrape the orchestrator too. Patroni exposes /metrics on its REST API; scraping it gives you leader state, timeline, and pending-restart flags as first-class time series, so failover events appear in the same system as the metrics that triggered them.

# prometheus.yml — shard-labelled scrape configuration
global:
  scrape_interval: 15s
  evaluation_interval: 15s

alerting:
  alertmanagers:
    - static_configs:
        - targets: ['alertmanager.internal:9093']

rule_files:
  - /etc/prometheus/rules/partition-alerts.yml

scrape_configs:
  - job_name: postgres-shards
    static_configs:
      - targets: ['pg-shard-0.internal:9187']
        labels: { shard: 'shard0', role: 'primary' }
      - targets: ['pg-shard-0-r1.internal:9187']
        labels: { shard: 'shard0', role: 'replica' }
      - targets: ['pg-shard-1.internal:9187']
        labels: { shard: 'shard1', role: 'primary' }
      - targets: ['pg-shard-1-r1.internal:9187']
        labels: { shard: 'shard1', role: 'replica' }

  - job_name: patroni
    metrics_path: /metrics
    static_configs:
      - targets: ['pg-shard-0.internal:8008', 'pg-shard-0-r1.internal:8008',
                  'pg-shard-1.internal:8008', 'pg-shard-1-r1.internal:8008']

  - job_name: pgbouncer
    static_configs:
      - targets: ['pgbouncer-0.internal:9127', 'pgbouncer-1.internal:9127']

The stock exporter does not know about your partition hierarchy, so per-partition size and dead-tuple metrics come from a custom queries file:

# queries.yaml — postgres_exporter custom collector for partition metrics
pg_partition:
  query: |
    SELECT parent.relname AS parent,
           child.relname  AS partition,
           pg_total_relation_size(child.oid)  AS size_bytes,
           pg_stat_get_live_tuples(child.oid) AS live_rows,
           pg_stat_get_dead_tuples(child.oid) AS dead_rows
    FROM pg_inherits
    JOIN pg_class parent ON parent.oid = pg_inherits.inhparent
    JOIN pg_class child  ON child.oid  = pg_inherits.inhrelid
  metrics:
    - parent:     { usage: "LABEL", description: "Parent table name" }
    - partition:  { usage: "LABEL", description: "Child partition name" }
    - size_bytes: { usage: "GAUGE", description: "Total partition size in bytes" }
    - live_rows:  { usage: "GAUGE", description: "Live tuples in partition" }
    - dead_rows:  { usage: "GAUGE", description: "Dead tuples in partition" }

Keep the partition collector on a slower cadence than the default scrape — the catalog query walks pg_inherits and calls pg_total_relation_size per child, which is cheap at 50 partitions and noticeable at 5,000. Setting a 60-second cache TTL on the collector (or a separate scrape job with scrape_interval: 60s) removes the overhead without losing the signal; size skew does not change in 15 seconds.

Grafana dashboards should follow the same shard-first discipline: one fleet overview row (max lag, max skew ratio, min idle connections — always the worst case, never the average) and a per-shard drill-down using a shard template variable. The overview row is what on-call looks at during an incident; if it shows averages, the incident stays invisible.

Alert Threshold Design

Threshold design for a sharded fleet has one governing principle: alert on the worst shard and on the trend, not on the fleet aggregate. Beyond that, three practical rules prevent the two classic failure modes — pages that fire too late and pages that fire constantly:

• Two tiers per signal. A warning at roughly 60–80 % of the hard ceiling routed to a ticket queue, and a critical at the ceiling routed to a pager. The warning tier is where automation (pre-provisioning, partition splits) should act, so the page tier rarely fires.
• for: durations sized to the signal’s noise. Replication lag spikes for 30 seconds during a checkpoint; a for: 5m clause absorbs that. Primary-down must page in one minute — its false-positive cost is a redundant check, while its false-negative cost is an outage.
• Ratios over absolutes for skew. An absolute size threshold goes stale as the fleet grows; max / median (or max / fair-share for rates) stays meaningful at any scale.

# /etc/prometheus/rules/partition-alerts.yml
groups:
  - name: partition-health
    rules:
      - alert: PartitionSizeSkewHigh
        expr: |
          max by (parent) (pg_partition_size_bytes)
            / on (parent) group_left
          quantile by (parent) (0.5, pg_partition_size_bytes) > 3
        for: 6h
        labels: { severity: warning }
        annotations:
          summary: "Partition size skew above 3:1 on {{ $labels.parent }}"
          runbook: "/partition-monitoring-failover-automation/partition-skew-detection-monitoring/"

      - alert: ShardReplicationLagHigh
        expr: max by (shard) (pg_replication_lag_seconds{role="primary"}) > 30
        for: 5m
        labels: { severity: critical }
        annotations:
          summary: "Replica on {{ $labels.shard }} is {{ $value }}s behind"

      - alert: ShardConnectionsExhausted
        expr: min by (shard) (pgbouncer_pools_server_idle_connections) < 3
        for: 2m
        labels: { severity: critical }
        annotations:
          summary: "Fewer than 3 idle server connections on {{ $labels.shard }}"

      - alert: ShardPrimaryDown
        expr: up{job="postgres-shards", role="primary"} == 0
        for: 1m
        labels: { severity: critical }
        annotations:
          summary: "Primary exporter unreachable on {{ $labels.shard }}"

      - alert: AutovacuumDebtGrowing
        expr: |
          pg_partition_dead_rows
            / (pg_partition_live_rows + pg_partition_dead_rows) > 0.2
        for: 1h
        labels: { severity: warning }
        annotations:
          summary: "Dead tuples above 20% on {{ $labels.partition }}"

Two PromQL patterns are worth memorising because they generalise across dimensions. Fair-share deviation for any rate:

# Write skew: any shard taking more than 2x its fair share of inserts
max by (shard) (rate(pg_stat_user_tables_n_tup_ins[10m]))
  / scalar(avg(rate(pg_stat_user_tables_n_tup_ins[10m]))) > 2

And trend-based capacity prediction, which turns a ceiling alert into an early warning:

# Predict: will any partition cross 50 GB within 14 days at current growth?
predict_linear(pg_partition_size_bytes[7d], 14 * 86400) > 50e9

The prediction rule is the highest-value alert in the whole file: it converts an emergency split at 2 a.m. into a planned rebalance during business hours.

One caution on ShardPrimaryDown: exporter unreachability is not proof the primary is dead — the exporter process itself may have crashed. Treat that alert as a signal to check Patroni’s view (patronictl list), and let the orchestrator’s own quorum-based detection make the promotion decision. The alerting pipeline observes; the DCS decides.

Automated Failover per Shard

Detection tells you a primary is unhealthy; failover automation acts on it. In a sharded topology every shard is an independent replication group with its own leader, so failover must be scoped per shard — a fleet-wide “failover everything” action is almost never correct. Automated Failover Orchestration covers the implementation in depth; the strategy choice comes first.

For self-managed PostgreSQL shards, Patroni is the default choice: each shard runs as an independent Patroni cluster (a scope) against a shared etcd or Consul DCS, and promotion is decided by consensus rather than by any single monitor. The essential per-node configuration:

# patroni-shard0.yml — one Patroni scope per shard
scope: shard0
namespace: /db/
name: pg-shard0-node1

restapi:
  listen: 0.0.0.0:8008
  connect_address: 10.0.1.11:8008

etcd3:
  hosts: 10.0.0.5:2379,10.0.0.6:2379,10.0.0.7:2379

bootstrap:
  dcs:
    ttl: 30                       # leader lease; missed renewal for 30s => election
    loop_wait: 10                 # main loop cadence
    retry_timeout: 10             # DCS operation retries before self-demotion
    maximum_lag_on_failover: 1048576   # bytes; lagging replicas are not promotable
    synchronous_mode: true        # no acknowledged commit is lost on failover
    postgresql:
      use_pg_rewind: true         # rejoin old primary without full re-clone
      parameters:
        wal_level: replica
        max_wal_senders: 10
        synchronous_commit: "on"

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 10.0.1.11:5432
  data_dir: /var/lib/postgresql/16/main
  authentication:
    replication:
      username: replicator
      password: "{{ vault:pg/shard0/replicator }}"

watchdog:
  mode: required                  # refuse to run as leader without a watchdog
  device: /dev/watchdog
  safety_margin: 5

Three of these parameters embody the core failover tradeoffs. ttl bounds detection time and false-failover risk together: lower is faster but flappier. maximum_lag_on_failover bounds data loss when running asynchronous replication — a replica further behind than this many bytes is ineligible for promotion. synchronous_mode removes that data-loss window entirely by refusing to acknowledge commits until a synchronous replica has them, at the cost of added write latency and reduced availability if the replica stalls; where you land on that dial is exactly the tradeoff described in Consistency Models in Distributed Databases.

For MySQL fleets, orchestrator plays the equivalent role: it probes every instance, reconstructs the replication topology, and on primary failure elects the most advanced replica, repoints the remaining replicas, and fires pre- and post-failover hooks where you attach fencing and routing updates.

Fencing and split-brain avoidance

Promotion is the easy half of failover. The hard half is guaranteeing the old primary can no longer accept writes — otherwise a network partition produces two nodes both believing they are the leader, each accumulating divergent commits that cannot be merged. Defense is layered:

1. Consensus-held leadership. The leader key in etcd can be held by exactly one node per scope. A primary partitioned away from the DCS cannot renew its lease and, after retry_timeout, Patroni demotes the local PostgreSQL to read-only before the other side promotes.
2. Watchdog self-fencing. If the Patroni process itself hangs (so it can neither renew nor demote), the kernel watchdog reboots the node when Patroni stops petting it. mode: required makes this non-optional for leaders.
3. Routing that follows the DCS, not static config. Clients and proxies must resolve the primary through Patroni’s REST API health checks (/primary returns 200 only on the leader) or a DCS-driven service catalog — never a hardcoded address. HAProxy health-checking every node’s :8008/primary endpoint converges to the new leader within one check interval.
4. pg_rewind for safe rejoin. When the fenced ex-primary returns, use_pg_rewind rolls back its divergent WAL so it can rejoin as a replica without a multi-hour re-clone.

The routing layer deserves emphasis because it is the piece most often forgotten: a perfect promotion is still an outage if the application keeps connecting to the demoted node. Whatever resolves shard addresses — proxy, service mesh, or a shard map consulted by application code — must subscribe to leadership changes with a propagation time budgeted inside your RTO.

Runbook Automation and the Failover Drill

Failover automation you have never exercised is a liability, not a safety net. The following drill validates the full control loop — detection, promotion, fencing, routing convergence, and alerting — on one shard at a time. Run it quarterly per shard, first in staging, then in production during a low-traffic window.

1. Record the pre-drill topology and lag. You need a baseline to compare against and proof that the shard is safe to drill (lag near zero, both replicas streaming):

patronictl -c /etc/patroni/patroni-shard0.yml list
psql -h pg-shard-0.internal -c \
  "SELECT application_name, state,
          pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytes
   FROM pg_stat_replication;"

2. Start a write canary. A loop that inserts one timestamped row per second into the drilled shard gives you a precise measurement of the write-unavailability window and detects any lost commits afterward:

while true; do
  psql "host=pg-shard-0.internal dbname=events user=canary" -c \
    "INSERT INTO failover_canary (ts) VALUES (now());" 2>> canary.err
  sleep 1
done &
CANARY_PID=$!

3. Trigger a controlled switchover (planned drills use switchover, which coordinates a clean handoff; only chaos-style drills kill the primary process to exercise the crash path):

patronictl -c /etc/patroni/patroni-shard0.yml switchover \
  --leader pg-shard0-node1 --candidate pg-shard0-node2 --force

4. Watch the DCS-driven election converge. Within loop_wait seconds the candidate should hold the leader key and the old leader should be demoted:

watch -n 2 "patronictl -c /etc/patroni/patroni-shard0.yml list"
etcdctl get /db/shard0/leader --print-value-only

5. Verify routing convergence. Confirm the proxy or shard map now resolves shard 0 writes to the new primary, and measure how long it took:

# HAProxy: which backend server is UP for the shard0 write pool?
echo "show stat" | socat /var/run/haproxy.sock stdio \
  | awk -F',' '$1=="shard0_write" {print $2, $18}'

curl -s -o /dev/null -w "%{http_code}\n" http://pg-shard0-node2.internal:8008/primary
# expect: 200

6. Stop the canary and audit the gap. Count missing seconds and check for errors that are not clean connection failures:

kill $CANARY_PID
psql -h pg-shard-0.internal -d events -c \
  "SELECT max(ts) - min(ts) AS window,
          count(*)          AS rows_written,
          extract(epoch FROM max(ts) - min(ts))::int + 1 - count(*) AS seconds_lost
   FROM failover_canary
   WHERE ts > now() - interval '30 minutes';"

7. Rejoin the old primary as a replica and confirm it streams. With use_pg_rewind enabled Patroni does this automatically; verify rather than assume:

patronictl -c /etc/patroni/patroni-shard0.yml list
# old leader should show Role: Replica, State: streaming, Lag: 0

8. Audit the observability trail. The drill is only a pass if Prometheus captured the event: ShardPrimaryDown (or the Patroni leader-change series) fired and resolved, Grafana shows the write gap, and Alertmanager delivered exactly one page — not twelve duplicates. Any silence or noise here is a monitoring bug you just found for free.

Record seconds_lost and routing-convergence time in the drill log. Rising numbers across quarters mean configuration drift; investigate before the number is measured in a real incident. When drills reveal a shard whose failover is slow because the node is overloaded rather than misconfigured, the fix is capacity work — moving data off the shard via the workflows in Shard Migration & Rebalancing Operations, not more tuning of ttl.

Common Mistakes

FAQ

Related

• Partition Skew Detection & Monitoring — catalog queries, exporter collectors, and skew-ratio alerting for size and traffic imbalance
• Automated Failover Orchestration — Patroni and orchestrator deployment patterns, DCS design, and fencing hooks per shard
• Replication Lag & Capacity Alerting — lag metrics, burn-rate thresholds, and predictive capacity alerts across shards
• Shard Migration & Rebalancing Operations — the remediation layer when monitoring shows a shard must be split, drained, or rebalanced
• Consistency Models in Distributed Databases — the synchronous-versus-asynchronous replication tradeoffs that set your failover data-loss window