Re: [PATCH v1] Add pg_stat_multixact view for multixact membership usage monitoring - Mailing list pgsql-hackers

Hi Naga,

Thank you for your thoughtful response and for providing those benchmarks.

I'd like to address your concerns about the potential for statistical
lag with clarifications and empirical data:

> its updates are asynchronous and may lag — either due to the stats_fetch_consistency setting

When operators configure `stats_fetch_consistency` to keep statistical
data static during transactions, this is by design - it gives them
explicit control over the consistency/freshness tradeoff. As the
documentation notes, `pg_stat_clear_snapshot()` provides up-to-date
visibility when needed [0].

> [stats] may lag...in environments with high load or memory pressure

This is a valid concern. In order to investigate the extent of
potential statistical lag, I conducted a test to quantify the amount
of lag between `pg_stat_multixact` and `pg_get_multixact_count()`.

=Testing Methodology=
==Build Configuration==
```
configure --enable-cassert=no --enable-debug=no
```

==Test Schema (Multixact-Intensive Workload)==
```
DROP TABLE IF EXISTS orders CASCADE;
DROP TABLE IF EXISTS customers CASCADE;
DROP TABLE IF EXISTS products CASCADE;

CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
name TEXT
);

CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
name TEXT
);

CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INTEGER REFERENCES customers(customer_id),
product_id INTEGER REFERENCES products(product_id),
order_date TIMESTAMP DEFAULT NOW(),
amount DECIMAL(10,2)
);

INSERT INTO customers (name) VALUES ('Customer 1'), ('Customer 2');
INSERT INTO products (name) VALUES ('Product 1'), ('Product 2');

INSERT INTO orders (customer_id, product_id, amount)
SELECT
(RANDOM() + 1)::INTEGER,
(RANDOM() + 1)::INTEGER,
RANDOM() * 100
FROM generate_series(1, 100000);

CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_product ON orders(product_id);

ANALYZE;
```

==Load Generation (Terminal 1)==
```
pgbench -n -c 100 -j 100 -T 300 -f =(cat << 'EOF'
BEGIN;
SELECT o.*, c.name, p.name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id
WHERE o.customer_id = 1
FOR SHARE;
COMMIT;
EOF
) postgres
```

==Lag Measurement (Terminal 2)==
```
psql postgres << 'EOF'
\timing on
DO $$
DECLARE
func_result BIGINT;
view_result BIGINT;
i INTEGER;
diff BIGINT;
timestamp_ms BIGINT;
BEGIN
RAISE NOTICE 'timestamp_ms,sample,function_members,view_members,diff,view_status';

FOR i IN 1..3000 LOOP
timestamp_ms := EXTRACT(EPOCH FROM clock_timestamp()) * 1000;

SELECT members INTO func_result FROM pg_get_multixact_count();

PERFORM pg_stat_clear_snapshot();
SELECT members INTO view_result FROM pg_stat_multixact;

diff := view_result - func_result;

RAISE NOTICE '%,%,%,%,%,%',
timestamp_ms,
i,
func_result,
view_result,
diff,
CASE WHEN diff > 0 THEN 'AHEAD'
WHEN diff < 0 THEN 'BEHIND'
ELSE 'EQUAL' END;

PERFORM pg_sleep(0.1);
END LOOP;
END $$;
EOF
```

==Results==

From 2,744 samples collected during 279 seconds of sustained 100-client load:

Lag Statistics:
- P95 Percentage Difference: 0.95%
- P50 Percentage Difference: 0.12%
- Mean Percentage Difference: 0.25%
- Max Percentage Difference: 1.30%

I've attached the results of my test to this email as a CSV file named
`lag_test_results.csv`.

From the data, the percentage differences between `pg_stat_multixact`
and `pg_get_multixact_count()` generally stay below 1% under heavy
load, maxing out at ~1.3%. The two statistics converge very quickly at
the end of the test when the heavy load is removed.

The data suggests that even under extreme load designed to maximize
multixact allocation, the operational lag remains marginal.

To address your other concerns:

> Since these counters are global, not aggregatable per backend

I understand this perspective, though I'd suggest that PostgreSQL
already uses the pgstats pattern for similar global metrics, such as:

- pg_stat_wal
- pg_stat_bgwriter
- pg_stat_checkpointer
- pg_stat_archiver

These all share similar characteristics with multixact member counts:
global scope and used for cluster-wide resource monitoring.

> it seemed reasonable to expose them without adding new statistics infrastructure.
> adding the complexity of statistics infrastructure.

The pgstats approach follows a well-established design principle:
ensuring that monitoring queries never impact the performance of the
monitored subsystem, provide reasonably up-to-date statistics, and
offer a predictable interface for future developers to extend when
adding related metrics. Given the marginal statistical lag, I believe
it's beneficial to avoid even the theoretical possibility of lock
contention.

> and don’t reset meaningfully,

You're right that these metrics don't reset in the traditional sense.
However, this characteristic doesn't necessarily disqualify the
approach given the reasons mentioned above.

I appreciate your feedback and believe both proposals address vital
observability gaps. I'm open to collaborating on a path forward that
meaningfully addresses the problem and serves the broader needs of the
Postgres community.

[0] https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-STATS-VIEWS

--
Respectfully,

Andrew Johnson
Software Engineer
Metronome, Inc