Does Postgres remove dead tuples when restarted?

user_9499019 0
Viewed 427

Does restarting Postgres cause dead tuples to get removed?

Context:

I have a relation that contains many wide fields and Postgres is storing the values out of line in a toast table. When doing so it generates references as OIds that point to the values in the toast table.

Generating OIds can be slow as Postgres will try will make sure oids do not collide as there is an upper limit of how many of them it can generate.

My relation has a very low row count (around 20) but it gets updated very frequently. This of course leads to a big number of dead tuples.

When I restarted Postgres, the slowness disappeared. So I had a hypothesis that says dead tuples are removed when restarting Postgres.

SELECT relname    AS TableName
     , n_live_tup AS LiveTuples
     , n_dead_tup AS DeadTuples
FROM pg_stat_user_tables;

I launched this query before and after the restart. The number of live and dead tuples went from X to 0.

Is this enough to validate the hypothesis?

postgresql
1 Answers

Kind of, indirectly.

Any sessions with open transactions can prevent dead tuples from being removed, including sessions opened 3 months ago in a screen session and since forgotten about. Restarting the server will kill all those sessions and make the dead tuples available for removal (or for index micro-vacuum). It doesn't actually remove them, though, just frees them up so that they can be removed. Particularly likely to show immediate improvement is the restored ability to do index micro-vacuum.

user_17212390
Related