How to Track Partition Row Counts Without Lock Contention Using pg_stat_user_tables on Aurora PostgreSQL

The Problem When using LIST-partitioned tables on Aurora PostgreSQL, a common pattern for load balancing is to track per-partition row counts in a metadata table. Many implementations use a row-level trigger that updates a counter row on every INSERT or DELETE. At scale, this creates the Hot Row Problem: every concurrent writer acquires a row lock on the same counter row. Writers serialize through that single lock, and throughput degrades linearly with load. Alternative trigger-based approaches (delta tables, application-level counters) either shift the lock contention to a rollup phase or add operational complexity without fully eliminating the problem.

The Solution: pg_stat_user_tables

PostgreSQL already tracks row-level statistics for every table and partition internally — no triggers or additional tables required. The pg_stat_user_tables system view exposes these statistics, providing two methods to derive partition row counts with zero write-path overhead.

Key Columns

Column	Update Frequency	Description
n_live_tup	After ANALYZE	Approximate live row count. Refreshed when ANALYZE runs
n_tup_ins	Real-time	Total rows inserted since last instance restart (cumulative). Updated in near-real-time
n_tup_del	Real-time	Total rows inserted since last instance restart (cumulative). Updated in near-real-time
n_mod_since_analyze	Per DML	Rows modified since last ANALYZE. Resets to 0 after ANALYZE
last_autoanalyze	After ANALYZE	Timestamp of last automatic ANALYZE

Two Ways to Get Row Counts

-- Method 1: ANALYZE-dependent (may be stale between ANALYZE runs)
SELECT relname, n_live_tup
FROM pg_stat_user_tables
WHERE relname LIKE 'my_table_part_%';

-- Method 2: Always real-time (updated on every committed DML)
SELECT relname,
       n_tup_ins - n_tup_del AS live_row_count
FROM pg_stat_user_tables
WHERE relname LIKE 'my_table_part_%';

Use Method 2 (n_tup_ins - n_tup_del) as your primary metric for monitoring and rebalancing. It requires no ANALYZE and is always current. Use n_live_tup as the authoritative, ANALYZE-verified confirmation.

The Challenge: Keeping n_live_tup Fresh on Large Tables

While n_tup_ins - n_tup_del is always current, n_live_tup is only refreshed when ANALYZE runs. PostgreSQL's autovacuum decides when to trigger ANALYZE using this formula:

Trigger ANALYZE when:
  n_mod_since_analyze > autovacuum_analyze_threshold + (autovacuum_analyze_scale_factor × n_live_tup)

Why Defaults Fail at Scale

Table Size	Default Threshold (scale_factor=0.10)	Time to Trigger at 2,000 txn/hr
100,000 rows	10,050	~5 hours
500,000 rows	50,050	~25 hours
10,000,000 rows	1,000,050	~500 hours

At millions of rows, the default 10% scale factor produces thresholds so high that ANALYZE effectively never triggers under normal transaction volume. PostgreSQL does not alert on stale statistics — the outdated values are served silently.

Fix: Per-Partition Autovacuum Tuning

Apply aggressive ANALYZE settings to each partition individually. This is surgical — it does not affect other tables.

ALTER TABLE my_table_part_001 SET (
    autovacuum_analyze_threshold    = 50,
    autovacuum_analyze_scale_factor = 0.001
);

With scale_factor = 0.001 on a 500,000-row partition:

Threshold = 50 + (0.001 × 500,000) = 550

At 2,000 txn/hr, ANALYZE now triggers in ~16 minutes instead of ~25 hours — a 94% improvement.

Apply to All Partitions Dynamically

DO $$
DECLARE
    tbl TEXT;
BEGIN
    FOR tbl IN
        SELECT inhrelid::regclass::text
        FROM pg_inherits
        WHERE inhparent = 'my_partitioned_table'::regclass
          AND inhrelid::regclass::text NOT LIKE '%default%'
    LOOP
        EXECUTE format(
            'ALTER TABLE %s SET (
                autovacuum_analyze_threshold    = 50,
                autovacuum_analyze_scale_factor = 0.001
            )', tbl
        );
    END LOOP;
END $$;

Refresh Timing Comparison

scale_factor	Threshold (500K rows)	@ 2,000 txn/hr	@ 10,000 txn/hr
0.10 (default)	50,050	~25 hrs	~5 hrs
0.005	2,600	~78 min	~16 min
0.001 (recommended)	550	~16 min	~3 min

Global Settings (RDS Parameter Group)

Parameter	Default	Recommended	Why
autovacuum_naptime	60s	15–30s	Autovacuum checks tables more frequently
autovacuum_max_workers	3	5–8	More partitions analyzed in parallel

Safety Net: Scheduled ANALYZE via pg_cron

To cap maximum staleness regardless of autovacuum behavior:

CREATE EXTENSION IF NOT EXISTS pg_cron;

SELECT cron.schedule(
    'analyze_partitions',
    '*/5 * * * *',
    $$ANALYZE my_partitioned_table;$$
);

This guarantees stats are never more than 5 minutes stale, even if autovacuum is delayed by long-running transactions.

Monitoring Stats Freshness

SELECT
    relname AS partition,
    n_live_tup AS reported_rows,
    n_tup_ins - n_tup_del AS realtime_rows,
    (n_tup_ins - n_tup_del) - n_live_tup AS drift,
    n_mod_since_analyze AS mods_pending,
    now() - GREATEST(last_analyze, last_autoanalyze) AS time_since_refresh,
    CASE
        WHEN n_mod_since_analyze = 0 THEN 'VERIFIED'
        WHEN n_mod_since_analyze < 1000 THEN 'SLIGHTLY STALE'
        ELSE 'STALE — CONSIDER MANUAL ANALYZE'
    END AS status
FROM pg_stat_user_tables
WHERE relname LIKE 'my_table_part_%'
ORDER BY n_mod_since_analyze DESC;

Key signal: n_mod_since_analyze = 0 means ANALYZE ran recently and stats are verified. A growing value means changes are accumulating without verification.

Edge Cases to Monitor

Scenario	Impact	Mitigation
Transaction ROLLBACK	n_tup_ins temporarily	overcounts
TRUNCATE	n_tup_ins - n_tup_del becomes inaccurate	Use n_live_tup after TRUNCATE operations
Long-running transactions	Block autovacuum from running ANALYZE	Monitor pg_stat_activity. Set idle_in_transaction_session_timeout
Many partitions (100+)	Limited parallel ANALYZE (max_workers=3)	Increase autovacuum_max_workers to 5–8

Summary

Aspect	Result
Write-path overhead	Zero — no triggers, no extra tables
Application code changes	None — entirely DB-side
Accuracy	Real-time via n_tup_ins - n_tup_del; verified by tuned ANALYZE
Self-healing	Yes — each ANALYZE overwrites with ground truth
Worst-case staleness	Bounded by pg_cron interval (e.g., 5 minutes)
Lock contention	None at any scale

Conclusion

For LIST-partitioned tables on Aurora PostgreSQL or RDS for PostgreSQL that require per-partition row counts for load balancing or rebalancing decisions, pg_stat_user_tables provides a production-ready solution with zero write-path overhead. By combining real-time row counts (n_tup_ins - n_tup_del) with aggressively tuned autovacuum ANALYZE settings (autovacuum_analyze_scale_factor = 0.001) and a pg_cron safety net, you can achieve near-real-time partition statistics accuracy without triggers, additional tables, or application code changes. The key takeaways:

• Consider replacing triggers for row counting — at scale, they can create the Hot Row Problem, and PostgreSQL already tracks this data internally through its statistics infrastructure.
• Tune autovacuum per partition — the default scale_factor of 0.10 may produce thresholds too high for large tables, which can lead to silently stale statistics. Adjusting this per partition helps ensure timely ANALYZE runs.
• Monitor n_mod_since_analyze — this column serves as your primary signal for stats freshness. If it is consistently growing large on any partition, it may be worth tightening that partition's autovacuum settings.
• Use pg_cron as a safety net — scheduling ANALYZE every 2–5 minutes helps bound worst-case staleness, providing protection against autovacuum delays or long-running transactions.

This approach leverages infrastructure PostgreSQL already maintains — requiring only configuration knowledge to unlock it.