Horton

Thread

  1. Vacuum Freeze and relfrozenxid - How to check which tables need to be vacuumed

    Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de> — 2026-05-15T09:15:27Z

    
I ran the below SQL in my sandbox to get a list of all the tables in my DB that have a relfrozenxid older than 500 ( A ridiculously low number, I know ).

Based on the results I ran a Vacuum freeze ( and later a vacuum FULL )  on all the tables that appeared in the list.

After the first vacuum I expected the tables to disappear IF I ran the sql the second time. That did not happen. ( either with Vacuum FREEZE or FULL )

Then I realized that it was possibly because Vacuum does not reset the old Transaction_ids but rather the hint bits in infomask. --> May be I am wrong here too.

How can I get a clear list of tables which have unfrozen transaction ids older than 500 ( or any number for that matter ).

Thank you all in advance for your time and help.

Regards

Ram



SELECT
       cast (txid_current() as integer) - cast (age(PGCL.relfrozenxid) as integer) as GAP,
       INTB.table_catalog as DB_NAME,
       INTB.table_schema || '.' ||  PGCL.relname as TABLE_NAME,
       pg_table_size(PGCL.oid) as TABLE_SIZE
FROM
      pg_class PGCL,
      information_schema.tables INTB
WHERE
     PGCL.relname=INTB.table_name
AND
     PGCL.relkind='r'
AND
     cast (txid_current() as integer) - cast (age(PGCL.relfrozenxid) as integer) > 500
ORDER BY                                                    \
        1 DESC ;


Freundliche Grüße

i. A. Ramachandran Subramanian
Zentralbereich Informationstechnologie

Alte Leipziger Lebensversicherung a.G.


Hallesche Krankenversicherung a.G.





Alte Leipziger Lebensversicherung a.G., Alte Leipziger-Platz 1, 61440 Oberursel
Vors. des Aufsichtsrats: Dr. Walter Botermann · Vorstand: Christoph Bohn (Vors.), Dr. Jürgen Bierbaum (stv. Vors.), Frank Kettnaker, Dr. Jochen Kriegmeier, Alexander Mayer, Christian Pape, Wiltrud Pekarek, Udo Wilcsek
Sitz Oberursel (Taunus) · Rechtsform VVaG · Amtsgericht Bad Homburg v. d. H. HRB 1583 · USt.-IdNr. DE 114106814





 
Hallesche Krankenversicherung a.G.,  Löffelstraße 34-38, 70597 Stuttgart
Vors. des Aufsichtsrats: Dr. Walter Botermann · Vorstand: Christoph Bohn (Vors.), Dr. Jürgen Bierbaum (stv. Vors.), Frank Kettnaker, Dr. Jochen Kriegmeier, Alexander Mayer, Christian Pape,
Wiltrud Pekarek, Udo Wilcsek
Sitz Stuttgart · Rechtsform VVaG · Amtsgericht Stuttgart HRB 2686 · USt.-IdNr. DE 147802285
Beiträge zu privaten Kranken- und Pflegekrankenversicherungen unterliegen nicht der Versicherungsteuer (§ 4 Nr. 5 VersStG) · Versicherungsleistungen sowie Umsätze aus Versicherungsvertreter-/Maklertätigkeiten sind umsatzsteuerfrei
 



 
Die Pflichtangaben der ALH Gruppe gemäß § 35a GmbHG bzw. § 80 AktG finden Sie hier: https://www.alte-leipziger.de/impressum 





______________________

ALH Gruppe
Alte Leipziger-Platz 1, 61440 Oberursel
Tel.: +49 (6171) 66-4882
Fax: +49 (6171) 66-800-4882
E-Mail: ramachandran.subramanian@alte-leipziger.de
www.alte-leipziger.de
www.hallesche.de

  2. IGNORE MY MESSAGE - AW: Vacuum Freeze and relfrozenxid - How to check which tables need to be vacuumed

    Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de> — 2026-05-15T09:47:24Z

    I apologize to have sent the earlier message.

The function AGE(relfrozenxid)) is itself the gap between the oldest frozen xid and current xid. There is no need for additional subtraction.


Once again.. I am sorry for asking a frivolous question.

Regards

Ram



#Build the SQL needed to get the list of tables that are approaching the SAFE_XID_GAP
SELECT
       cast (age(PGCL.relfrozenxid) as integer) as GAP,
       INTB.table_catalog as DB_NAME,
              INTB.table_schema || '.' ||   PGCL.relname as TABLE_NAME,
       pg_table_size(PGCL.oid) as TABLE_SIZE
FROM
      pg_class PGCL,
      information_schema.tables INTB
WHERE
     PGCL.relname=INTB.table_name
AND
     PGCL.relkind='r'
AND
     age(PGCL.relfrozenxid)  > "$SAFE_XID_GAP"  <-- 500 or any number for that matter.
ORDER BY
        1 DESC ;"


Freundliche Grüße

i. A. Ramachandran Subramanian
Zentralbereich Informationstechnologie

Alte Leipziger Lebensversicherung a.G.


Hallesche Krankenversicherung a.G.






______________________

ALH Gruppe
Alte Leipziger-Platz 1, 61440 Oberursel
Tel.: +49 (6171) 66-4882
Fax: +49 (6171) 66-800-4882
E-Mail: ramachandran.subramanian@alte-leipziger.de
www.alte-leipziger.de
www.hallesche.de




Von: Subramanian,Ramachandran <ramachandran.subramanian@alte-leipziger.de>
Gesendet: Freitag, 15. Mai 2026 11:15
An: pgsql-novice@lists.postgresql.org
Betreff: Vacuum Freeze and relfrozenxid - How to check which tables need to be vacuumed



I ran the below SQL in my sandbox to get a list of all the tables in my DB that have a relfrozenxid older than 500 ( A ridiculously low number, I know ).

Based on the results I ran a Vacuum freeze ( and later a vacuum FULL )  on all the tables that appeared in the list.

After the first vacuum I expected the tables to disappear IF I ran the sql the second time. That did not happen. ( either with Vacuum FREEZE or FULL )

Then I realized that it was possibly because Vacuum does not reset the old Transaction_ids but rather the hint bits in infomask. --> May be I am wrong here too.

How can I get a clear list of tables which have unfrozen transaction ids older than 500 ( or any number for that matter ).

Thank you all in advance for your time and help.

Regards

Ram




SELECT
       cast (txid_current() as integer) - cast (age(PGCL.relfrozenxid) as integer) as GAP,
       INTB.table_catalog as DB_NAME,
       INTB.table_schema || '.' ||  PGCL.relname as TABLE_NAME,
       pg_table_size(PGCL.oid) as TABLE_SIZE
FROM
      pg_class PGCL,
      information_schema.tables INTB
WHERE
     PGCL.relname=INTB.table_name
AND
     PGCL.relkind='r'
AND
     cast (txid_current() as integer) - cast (age(PGCL.relfrozenxid) as integer) > 500
ORDER BY                                                    \
        1 DESC ;


Freundliche Grüße


i. A. Ramachandran Subramanian

Zentralbereich Informationstechnologie

Alte Leipziger Lebensversicherung a. G.

Hallesche Krankenversicherung a. G.

______________________

ALH Gruppe
Alte Leipziger-Platz 1, 61440 Oberursel
Tel: +49 (6171) 66-4882
Fax: +49 (6171) 66-800-4882
E-Mail: ramachandran.subramanian@alte-leipziger.de<mailto:ramachandran.subramanian@alte-leipziger.de>
www.alte-leipziger.de<https://www.alte-leipziger.de>
www.hallesche.de<https://www.hallesche.de>

Alte Leipziger Lebensversicherung a. G., Alte Leipziger-Platz 1, 61440 Oberursel

Vors. des Aufsichtsrats: Dr. Walter Botermann · Vorstand: Christoph Bohn (Vors.), Dr. Jürgen Bierbaum (stv. Vors.), Frank Kettnaker, Dr. Jochen Kriegmeier, Alexander Mayer, Christian Pape, Wiltrud Pekarek, Udo Wilcsek

Sitz Oberursel (Taunus) · Rechtsform VVaG · Amtsgericht Bad Homburg v. d. H. HRB 1583 · USt.-IdNr. DE 114106814


Hallesche Krankenversicherung a. G., Löffelstraße 34-38, 70597 Stuttgart

Vors. des Aufsichtsrats: Dr. Walter Botermann · Vorstand: Christoph Bohn (Vors.), Dr. Jürgen Bierbaum (stv. Vors.), Frank Kettnaker, Dr. Jochen Kriegmeier, Alexander Mayer, Christian Pape, Wiltrud Pekarek, Udo Wilcsek

Sitz Stuttgart · Rechtsform VVaG · Amtsgericht Stuttgart HRB 2686 · USt.-IdNr. DE 147802285

Beiträge zu privaten Kranken- und Pflegekrankenversicherungen unterliegen nicht der Versicherungsteuer (§ 4 (1) Nr. 5 b VersStG) · Versicherungsleistungen sowie Umsätze aus Versicherungsvertreter-/Maklertätigkeiten sind umsatzsteuerfrei


Pflichtangaben<https://www.alte-leipziger.de/impressum> der ALH Gruppe gemäß § 35a GmbHG bzw. § 80 AktG