Re: n_ins_since_vacuum stats for aborted transactions

Sami Imseih <samimseih@gmail.com>

From: Sami Imseih <samimseih@gmail.com>
To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: Euler Taveira <euler@eulerto.com>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2025-04-09T19:56:25Z
Lists: pgsql-hackers
> So why is it important we not account for the aborted insert in both n_ins_since_vacuum and n_dead_tup?

I am saying n_dead_tup should continue to account for n_dead_tup. I am
not saying it should not.
What I am saying is n_ins_since_vacuum should not account for aborted inserts.

> When would you ever add them together so that an actual double-counting would reflect in some total.

I would never add them together. n_ins_since_vacuum uses this value
for vacuuming purposes.

> You aren't upset that n_live_tup and this both include the non-aborted inserts.

n_live_tup only shows the non-aborted inserts. I will put a better formatted
version of the repro below. IN the exampleI have 2k dead tuples from the rolled
back transactions and 3k live tuples from the committed transactions.

```
DROP TABLE t;
CREATE TABLE t (id INT);
ALTER TABLE t SET (autovacuum_enabled = OFF);
BEGIN;
INSERT INTO t SELECT n FROM generate_series(1, 1000) AS n;
INSERT INTO t SELECT n FROM generate_series(1, 1000) AS n;
ROLLBACK;
INSERT INTO t SELECT n FROM generate_series(1, 1000) AS n;
INSERT INTO t SELECT n FROM generate_series(1, 1000) AS n;
INSERT INTO t SELECT n FROM generate_series(1, 1000) AS n;
\x
SELECT
    n_tup_ins,
    n_tup_upd,
    n_tup_del,
    n_live_tup,
    n_dead_tup,
    n_mod_since_analyze,
    n_ins_since_vacuum
FROM
    pg_stat_all_tables
WHERE
    relname = 't';

-[ RECORD 1 ]-------+-----
n_tup_ins           | 5000
n_tup_upd           | 0
n_tup_del           | 0
n_live_tup          | 3000
n_dead_tup          | 2000
n_mod_since_analyze | 3000
n_ins_since_vacuum  | 5000
```


--
Sami Imseih
Amazon Web Services (AWS)



Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →
  1. Add code comment explaining ins_since_vacuum and aborted inserts

  2. Trigger autovacuum based on number of INSERTs