Re: n_ins_since_vacuum stats for aborted transactions

From: Sami Imseih <samimseih(at)gmail(dot)com>
To: "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>
Cc: Euler Taveira <euler(at)eulerto(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: n_ins_since_vacuum stats for aborted transactions
Date: 2025-04-09 19:56:25
Message-ID: CAA5RZ0viXbvJrBwbWWFfYDF6w5FK-i0sKT-88hj=gwZ4U9h0+A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
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)

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Mark Dilger 2025-04-09 19:58:32 Re: n_ins_since_vacuum stats for aborted transactions
Previous Message Ilia Evdokimov 2025-04-09 19:47:03 Re: Sample rate added to pg_stat_statements